MySQL 数据库中存储多选项调查答案的最佳方法

在本文中，我们将介绍如何在 MySQL 数据库中存储多选项调查答案的最佳方法。多选项调查通常包含许多选项，比如问卷调查中的多项选择题。我们将讨论如何设计表结构以存储这些答案，并提供一些额外的技巧和建议。

阅读更多：MySQL 教程

数据库设计

为了存储多选项调查答案，我们需要至少两个表：一个存储调查问题，另一个存储选项和选项值。下面是一个例子：

CREATE TABLE survey_questions (
  id INT PRIMARY KEY,
  question_text TEXT
);

CREATE TABLE survey_options (
  id INT PRIMARY KEY,
  question_id INT,
  option_value VARCHAR(255)
);

现在，我们可以将问题插入到 survey_questions 表中：

INSERT INTO survey_questions (id, question_text)
VALUES (1, '您喜欢哪些颜色？');

然后，我们可以将选项插入到 survey_options 表中，引用问题的 ID：

INSERT INTO survey_options (id, question_id, option_value)
VALUES (1, 1, '红色');

INSERT INTO survey_options (id, question_id, option_value)
VALUES (2, 1, '绿色');

INSERT INTO survey_options (id, question_id, option_value)
VALUES (3, 1, '蓝色');

现在我们已经有了一个问题和几个选项，接下来我们需要在数据库中记录选择的答案。最简单的方法是使用逗号分隔符将所选选项的 ID 列表保存到 survey_results 表中。例如：

CREATE TABLE survey_results (
  id INT PRIMARY KEY,
  question_id INT,
  selected_options TEXT
);

在 selected_options 列中，我们可以将所选选项 ID 分隔开，例如：

INSERT INTO survey_results (id, question_id, selected_options)
VALUES (1, 1, '1,3');

这表示用户选择了红色和蓝色作为他们喜欢的颜色。

但是，这种方法有一个严重的缺点：SQL 查询不支持对逗号分隔字符串进行高级搜索或分析，而且也很难使用子查询，因此不适合于大型数据集。因此，我们需要一种更好的方法。

使用联结表

解决这个问题的一种方法是使用联结表。我们可以创建一个新表 survey_responses，它将存储每个答案的单个选项 ID：

CREATE TABLE survey_responses (
  id INT PRIMARY KEY,
  question_id INT,
  option_id INT
);

现在，我们可以将每个响应插入到 survey_responses 表中：

INSERT INTO survey_responses (id, question_id, option_id)
VALUES (1, 1, 1);

INSERT INTO survey_responses (id, question_id, option_id)
VALUES (1, 1, 3);

这个表的好处是，我们可以在任何时候轻松地对数据进行过滤和查询。例如，如果我们想知道选择每个选项的人数，我们可以使用以下查询：

SELECT option_value, COUNT(*) FROM survey_responses
JOIN survey_options ON survey_options.id = survey_responses.option_id
WHERE question_id = 1
GROUP BY option_value;

这将返回：

+--------------+----------+
| option_value | COUNT(*) |
+--------------+----------+
| 红色         |        1 |
| 绿色         |        0 |
| 蓝色         |        1 |
+--------------+----------+

这种方法还有一个额外的好处：如果我们想要为选项添加额外的属性，例如描述或图像 URL，我们可以在 survey_options 表中添加新列。这个新增的属性将会通过 option_id 实现与每个响应的选项的关系，我们可以通过联结表轻松地获取它并进行过滤和查询。这大大增加了系统的灵活性。

数据库索引

当我们设计一个存储大量数据的数据库时，性能往往是最重要的。在多选项调查的情况下，我们可能需要对问题 ID、选项 ID 和其他一些列进行搜索。这就是为什么索引对性能如此关键的原因。

在上面的示例中，我们需要为 survey_questions.id、survey_options.id 和 survey_responses 表中的 question_id 和 option_id 列创建索引：

CREATE INDEX idx_survey_questions_id ON survey_questions(id);

CREATE INDEX idx_survey_options_id ON survey_options(id);

CREATE INDEX idx_survey_responses_question_id_option_id ON survey_responses(question_id, option_id);

请注意，我们使用联结索引（也称为复合索引）对 survey_responses 表中的二个列进行索引。这是因为我们通常会对这两个列同时进行搜索。联结索引将数据物理上存储在一起，导致查询性能得到极大提升。