如何将SQL表列设置为自定义类型？——Quiz应用场景疑问

作为SQL新手，你提到的需求有两种可行路径，下面分别说明：

一、直接存储嵌套数组（可行但不推荐） #

部分SQL数据库（比如云SQL支持的PostgreSQL）确实支持存储嵌套结构或数组类型：

1. 使用JSONB类型（PostgreSQL） #

如果你的云SQL用的是PostgreSQL，可以用JSONB类型直接存储符合TypeScript结构的嵌套数据：

CREATE TABLE quizzes (
  quiz_id SERIAL PRIMARY KEY,
  title VARCHAR(150),
  questions JSONB, -- 存储_question数组的JSON结构
  created_on_epoch BIGINT
);

插入数据时可以直接传入JSON格式的数组：

INSERT INTO quizzes (title, questions, created_on_epoch)
VALUES (
  '前端基础测验',
  '[
    {"question_id": 1, "title": "HTML是什么？", "prompts": ["超文本标记语言", "编程语言", "样式语言"], "correct_answer": 0},
    {"question_id": 2, "title": "CSS用于？", "prompts": ["逻辑处理", "页面样式", "数据存储"], "correct_answer": 1}
  ]',
  1718000000
);

2. 自定义复合类型+数组（PostgreSQL） #

你也可以先定义_question复合类型，再用数组存储：

CREATE TYPE _question AS (
  question_id INT,
  title VARCHAR(255),
  prompts TEXT[],
  correct_answer INT
);

CREATE TABLE quizzes (
  quiz_id SERIAL PRIMARY KEY,
  title VARCHAR(150),
  questions _question[], -- 直接用自定义类型的数组
  created_on_epoch BIGINT
);

但这种方式灵活性不如JSONB，且MySQL等其他数据库不支持自定义复合类型。

这种方案的缺点：

• 无法高效查询或修改单个问题，必须操作整个数组
• 难以对问题的字段（比如correct_answer）创建索引，查询性能差
• 不利于后续扩展，比如要给题目加分数标签时，修改结构成本高

二、规范化的关系型设计（更优方案） #

关系型数据库的核心优势是数据规范化，推荐拆分表来存储，这也是行业通用的做法：

1. 拆分核心表 #

创建两张关联表：

测验表（quizzes）

CREATE TABLE quizzes (
  quiz_id SERIAL PRIMARY KEY,
  title VARCHAR(150),
  created_on_epoch BIGINT
);

题目表（questions）

每个题目关联到对应的测验：

CREATE TABLE questions (
  question_id SERIAL PRIMARY KEY,
  quiz_id INT NOT NULL REFERENCES quizzes(quiz_id) ON DELETE CASCADE, -- 关联测验ID，删除测验时自动删除对应题目
  title VARCHAR(255),
  correct_answer INT,
  created_on_epoch BIGINT
);

2. 处理题目选项（prompts） #

对于prompts这个字符串数组，有两种处理方式：

方式一：用数组类型存储（PostgreSQL）

如果不需要单独查询选项，可以直接用TEXT[]类型：

ALTER TABLE questions ADD COLUMN prompts TEXT[];

方式二：拆分选项表（更规范）

如果以后需要统计选项的使用情况，推荐再拆一张选项表：

CREATE TABLE question_prompts (
  prompt_id SERIAL PRIMARY KEY,
  question_id INT NOT NULL REFERENCES questions(question_id) ON DELETE CASCADE,
  content VARCHAR(255),
  prompt_order INT -- 记录选项的顺序
);

这种方案的优点： #

• 可以单独查询、修改某个题目或选项，操作更灵活
• 能对quiz_id、correct_answer等字段创建索引，大幅提升查询性能
• 扩展性强，后续新增题目难度、分数等字段只需修改questions表即可
• 符合关系型数据库的设计规范，数据冗余度低

类型映射建议 #

如果采用规范化设计，你可以通过SQL的JOIN查询，把测验和对应的题目、选项组装成TypeScript的_quiz类型：

// 示例：从数据库查询后组装数据
const getQuizWithQuestions = async (quizId: number): Promise<_quiz> => {
  const quizResult = await db.query('SELECT * FROM quizzes WHERE quiz_id = $1', [quizId]);
  const questionsResult = await db.query('SELECT * FROM questions WHERE quiz_id = $1', [quizId]);
  const promptsResult = await db.query('SELECT * FROM question_prompts WHERE question_id = ANY($1)', [questionsResult.rows.map(q => q.question_id)]);

  const questions = questionsResult.rows.map(question => {
    const prompts = promptsResult.rows.filter(p => p.question_id === question.question_id)
      .sort((a, b) => a.prompt_order - b.prompt_order)
      .map(p => p.content);
    return {
      question_id: question.question_id,
      title: question.title,
      prompts,
      correct_answer: question.correct_answer
    };
  });

  return {
    quiz_id: quizResult.rows[0].quiz_id,
    title: quizResult.rows[0].title,
    questions,
    created_on_epoch: quizResult.rows[0].created_on_epoch
  };
};

内容的提问来源于stack exchange，提问作者awoldt