我正在开发一个类似调查的应用程序。对于多项选择题,我想将项目组合存储在一个数字中,以便将其存储在一行中。
问题的数据库结构是这样的:
id type text item_id
-- --------------- --------------------------- -------
1 multiple_choice which one would you choose? 42
1 multiple_choice which one would you choose? 43
1 multiple_choice which one would you choose? 46
这是一个有3个选项的问题,选项将从另一个表items调用,id为42,43,46
所以我想把这个组合变成一个数字来实现这个:
id type text items
-- --------------- --------------------------- -------
1 multiple_choice which one would you choose? x*
- x应该是一个数字,表示它来自数字42、43、46
所以数学上应该是
f({42,43,46}) = x and f^-1(x)={42,43,46}
解决这个问题的一种方法可能是使用素数作为项目id。所以它们的乘法是唯一的,但是有没有一种方法可以对所有的数字都这样做呢?
不,不,不。这不是你想在关系数据库中做的。
你原来的questions
表更接近正确的方法。
如果您想获得所有可能的答案,只需执行以下查询:
select id, type, text, group_concat(item_id)
from questions q
group by id, type, text;
但是,您实际上应该有两个表(至少)。Questions,列出每个问题,并有如下列:
- question_id
每个问题有1行(即question_id
是主键)。
第二个表question_items
,列如下:
- question_items_id
- question_id
- 关于商品的更多信息
select q.question_id, q.type, q.text, group_concat(qi.question_item_id)
from questions q join
question_items qi
on q.question_id = qi.question_id
group by q.question_id, q.type, q.text;
尽管查询可能看起来有点复杂,但底层数据结构更好地表示问题域。这使得整个系统更加健壮和可维护。
这不是最好的方法。您应该使用多表结构,例如
- Questions =每个问题1行,包含文本和问题类型
- 项 =每项1行
- QuestionHasItems =每项每题1行
这些名字只是为了说明。按照你已经开始的路线,字段"type"、"id"one_answers"text"是多余的,因此非常浪费/低效。
如果选中这个示例,那么您将看到预填充的表结构和一个示例查询。http://sqlfiddle.com/!2/fdf5f/4
下面的查询将生成您想要的数据
SELECT
q.questionid,
q.questiontype,
q.questiontext,
GROUP_CONCAT(i.itemid) AS itemIDs,
GROUP_CONCAT(i.itemtext) AS itemTexts
FROM questions q
INNER JOIN questionHasItems qhi
ON qhi.questionid = q.questionid
INNER JOIN items i
ON i.itemid = qhi.itemid
GROUP BY q.questionid;
同时允许您有效地获得,例如,通过执行诸如
之类的查询来获得问题可用的选项SELECT
i.itemid AS itemIDs,
i.itemtext AS itemTexts
FROM questionHasItems qhi
INNER JOIN items i
ON i.itemid = qhi.itemid
WHERE qhi.questionid = 1;