mysql如何设计问卷调查系统_mysql动态字段存储实践

用json字段存问卷答案更靠谱：支持格式校验、虚拟列索引、语义清晰；避免频繁改表结构，适配多题型异构数据；需用stored虚拟列建索引、json_set正确更新、json_table导出，注意字符集与结构演进。

用 JSON 字段存问卷答案比加一堆 answer_1 列更靠谱

MySQL 5.7+ 原生支持 JSON 类型，字段可校验格式、支持索引（虚拟列）、查询语义清晰。硬建几十个 answer_1～answer_100 字段，后期改题、加逻辑、导数据全要改表结构，维护成本爆炸。

• 问卷题型多变（单选/多选/填空/日期/文件上传），每种答案结构不同，JSON 能自然表达嵌套和异构数据
• ALTER TABLE 加字段在大表上可能锁表数分钟，而 JSON 字段一次建好，后续题目增删只改应用逻辑
• 别用 TEXT 存 JSON 字符串——失去类型校验、无法直接 -> 查询、不能建函数索引
• 示例建表：

  CREATE TABLE survey_responses (
    id BIGINT PRIMARY KEY,
    survey_id INT NOT NULL,
    user_id INT,
    answers JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

查某道题的所有答案？用 JSON_EXTRACT + 虚拟列加速

直接 WHERE answers->"$.q5" = "A" 在大数据量下会全表扫描。MySQL 不会对 JSON 字段内部路径自动建索引，必须显式提取成虚拟列再索引。

• 先加虚拟列：

  ALTER TABLE survey_responses ADD COLUMN q5_answer VARCHAR(255) 
    AS (JSON_UNQUOTE(JSON_EXTRACT(answers, "$.q5"))) STORED;

• 再建索引：

  CREATE INDEX idx_q5 ON survey_responses(q5_answer);

• 注意 STORED 和 VIRTUAL 区别：前者占磁盘但可索引，后者不存盘但某些旧版本不支持索引；推荐 STORED
• 多选题存为数组（如 ["A","C"]），查“选了 A”的写法是：JSON_CONTAINS(answers, '"A"', "$.q7")，不能用 ->

JSON_SET 更新单个答案时，别漏掉整个 JSON 的重写逻辑

MySQL 的 JSON_SET 是不可变更新——它返回新 JSON 值，不会原地修改。应用层必须把新值完整写回字段，否则其他题的答案会被清空。

• 错误写法（只设一道题，覆盖整个 answers）：

  UPDATE survey_responses SET answers = JSON_SET('{}', "$.q3", "2024-03-15") WHERE id = 123;

• 正确写法（保留原有内容，只更新指定路径）：

  UPDATE survey_responses 
  SET answers = JSON_SET(answers, "$.q3", "2024-03-15") 
  WHERE id = 123;

• 如果字段初始为 NULL，JSON_SET(NULL, ...) 返回 NULL，得先 COALESCE(answers, '{}')
• 并发更新同一记录时，JSON_SET 不保证原子性——两个请求同时读-改-写，后写入的会覆盖前一个的修改。高并发场景需加应用层锁或改用行级乐观锁

导出问卷数据到 Excel？别在 SQL 里拼 CSV，用 JSON_TABLE 拆开再 JOIN

直接 SELECT id, answers->"$.q1", answers->"$.q2" 导出，字段名是乱码（$."q1"），多选题还展不开。MySQL 8.0+ 的 JSON_TABLE 能把 JSON 数组/对象转成行集，配合 UNION ALL 或子查询做宽表展开。

• 假设答案结构是：{"q1":"男","q2":["A","B"],"q3":"上海"}，想把多选题每项拆一行：

  SELECT r.id, j.question, j.answer
  FROM survey_responses r,
  JSON_TABLE(r.answers, "$" COLUMNS (
    q1 VARCHAR(10) PATH "$.q1",
    NESTED PATH "$.q2[*]" COLUMNS (answer VARCHAR(10) PATH "$"),
    q3 VARCHAR(50) PATH "$.q3"
  )) AS j;

• JSON_TABLE 不支持 MySQL 5.7，低版本只能靠应用层解析，或用 JSON_EXTRACT 配合固定字段名硬编码
• 导出前务必确认字符集：如果问卷含中文，表和连接都得是 utf8mb4，否则 JSON_EXTRACT 可能返回 NULL