时间戳

首页 > 解决方案 > 试图从 JSON 格式构建 mySQL 数据库，但不知道如何

mysql - 试图从 JSON 格式构建 mySQL 数据库，但不知道如何

问题描述

我目前有这个 JSON 文件，它记录用户对给定问题的回答，并根据回答进行一些计算。当我在前端工作时，使用 json 更方便。用户名和密码系统没有任何安全性，仅供测试。现在我正在尝试使用更安全的用户帐户系统构建一个 mySQL 数据库，但我似乎无法找到一种方法来构建与该文件执行相同操作的表。

{ 
"username" : "a6",
"password" : "password", 
"joined" : 1621995190170,
"progress" : [
   { 
      "Question ID" : "00001", 
      "Question" : "-----",
      "SetAnswer" : "xxxxx", 
      "Recorded" : "yyyyy", 
      "Score" : 945, 
      "AttemptedAt" : 1611682870221, 
      "Factor" : 4,
      "AttemptAgain" : 1617014020591
   }, 
   { 
      "Question ID" : "00002", 
      "Question" : "-----",
      "SetAnswer" : "xxxxx", 
      "Recorded" : "yyyyy", 
      "Score" : 945, 
      "AttemptedAt" : 1611682870254, 
      "Factor" : 4,
      "AttemptAgain" : 1617014020564
   }, 
   { 
      "Question ID" : "00003", 
      "Question" : "-----",
      "SetAnswer" : "xxxxx", 
      "Recorded" : "yyyyy", 
      "Score" : 945, 
      "AttemptedAt" : 1611682870254, 
      "Factor" : 4,
      "AttemptAgain" : 1617014020564
   }...
(it goes on for 10,000+ entries)

我遇到的问题是每个用户的每个问题都有很多东西要记录，为每个用户制作一个表格将是一个非常糟糕的主意。任何有关设计 SQL 数据库的帮助将不胜感激。

标签： mysqljsondatabase-design

解决方案

欢迎来到实体关系数据设计的世界。你有三个实体

  • 用户
  • 问题
  • 回答

每个实体都有自己的表。

您的实体具有这些关系。

  • 每个用户可以有零个或多个答案。所以你的答案表需要一个user_id列。
  • 每个问题也可以有零个或多个答案。所以你的答案表question_id也需要一列。

从您的 Javascript 对象示例中，我得到了这三个表定义。

CREATE OR REPLACE TABLE user (
  user_id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username        VARCHAR(255) NOT NULL,
  hashed_password VARCHAR(255) NOT NULL,
  joined          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id)
)
COLLATE='utf8mb4_general_ci';

CREATE OR REPLACE TABLE question (
  question_id     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  question        VARCHAR(1000),
  set_answer      VARCHAR(1000),
  factor          FLOAT NOT NULL DEFAULT 1.0,
  PRIMARY KEY (question_id)
)
COLLATE='utf8mb4_general_ci';

CREATE OR REPLACE TABLE answer (
  answer_id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id         BIGINT UNSIGNED NOT NULL,
  question_id     BIGINT UNSIGNED NOT NULL,
  recorded_answer VARCHAR(1000),
  score           FLOAT,
  attempted       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  attemted_again  TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (answer_id)
)
COLLATE='utf8mb4_general_ci';

填充这些表后，您可以通过这样的查询获得与示例类似的结果集。

SELECT user.user_id, user.username, user.joined,
       question.question_id, question.question, 
       question.set_answer, question.factor,
       answer.recorded_answer, answer.score, 
       answer.attempted, answer.attempted_again
  FROM user
  LEFT JOIN answer   ON user.user_id = answer.user_id
  JOIN question ON answer.question_id = question.question_id

这是正在发生的事情。

  • 、和数字唯一标识每个用户user_id、问题和答案。question_idanswer_id
  • SELECT 子句枚举您想要的数据。
  • FROM 子句表示您需要逐个用户的结果。
  • LEFT JOIN 子句实现了用户可以有零个或多个答案的关系。您可以将其answer.user_id视为指向用户。
  • JOIN 子句实现了每个分析器必须有一个问题的关系。question_id回到问题。

顺便说一句，包括 MySQL 在内的 SQL 数据库系统可以毫不费力地处理各种表中的数百万行，所以不用担心。（当表变得那么大时，您可能需要添加索引，但这是在您使应用程序正常工作之后。）

推荐阅读