mysql - 将多个数据存储到一个字段中以便更好地管理？

Question

我有一个 MySQL 表如下:

id, user, culprit, reason, status, ts_register, ts_update

我正在考虑将原因用作 int 字段，并仅存储用户可以选择的原因的 id，并且原因本身可以由管理员增加。

我所说的增加是指管理员可以注册新的原因，例如目前我们有:

Flood, Racism, Hacks, Other

但是管理员可以添加一个新的原因，例如:

Refund

现在我的问题是我想允许我的用户选择多个原因，例如:

报告 01 的原因是洪水和黑客。

我应该如何存储原因字段，以便可以选择多个原因，同时保持良好的表格格式？

我应该继续将其存储为字符串，并在搜索时将其转换为 INT ，还是有更好的形式来存储它？

根据乔纳森的回复更新:

SELECT mt.*, group_concat(r.reason separator ', ') AS reason
  FROM MainTable AS mt
  JOIN MainReason AS mr ON mt.id = mr.maintable_ID
  JOIN Reasons AS r ON mr.reason = r.reason_id
  GROUP BY mt.id

Answer 1

标准化的解决方案是让第二个表包含每个原因的一行:

CREATE TABLE MainReasons
(
    MainTable_ID INTEGER NOT NULL REFERENCES MainTable(ID),
    Reason       INTEGER NOT NULL REFERENCES Reasons(ID),
    PRIMARY KEY(MainTable_ID, Reason)
);

(假设您的主表名为 MainTable，并且您有一个定义有效原因代码的表，名为 Reasons。)

---

来自评论:

[W]ould you be [so] kind [as] to show me an example of selecting something to retrieve a report's reason? I mean if I simple select it SELECT * FROM MainTABLE I would never get any reasons since MainTable doesnt know it right? Because it is only linked to the MainReasons and Reasons table so I would need to do something like SELECT * FROM MainTable LEFT JOIN MainReasons USING (MainTable_ID) or something alike but how would I go about getting all the reasons if multiples?

SELECT mt.*, r.reason
  FROM MainTable AS mt
  JOIN MainReason AS mr ON mt.id = mr.maintable_ID
  JOIN Reasons AS r ON mr.reason = r.reason_id

这将为每个原因返回一行 - 因此它将为单个报告返回多行(记录在我所谓的 MainTable 中)。我从结果中省略了原因 ID 号 - 如果您愿意，可以将其包括在内。

您可以向查询添加条件，向 WHERE 子句添加术语。如果您想查看指定了特定原因的报告:

SELECT mt.*
  FROM MainTable AS mt
  JOIN MainReason AS mr ON mt.id = mr.maintable_ID
  JOIN Reasons AS r ON mr.reason = r.reason_id
 WHERE r.reason = 'Flood'

(您不需要结果中的原因 - 您知道它是什么。)

---

如果您想查看以“洪水”和“黑客”为原因的报告，那么您可以这样写:

SELECT mt.*
  FROM MainTable AS mt
  JOIN (SELECT f.MainTable_ID
          FROM (SELECT MainTable_ID
                  FROM MainReason AS mr1
                  JOIN Reasons    AS r1  ON mr1.reason = r1.reason_ID
                 WHERE r1.reason = 'Floods'
               ) AS f ON f.MainTable_ID = mt.MainTable_ID
  JOIN (SELECT f.MainTable_ID
          FROM (SELECT MainTable_ID
                  FROM MainReason AS mr2
                  JOIN Reasons    AS r2  ON mr2.reason = r2.reason_ID
                 WHERE r1.reason = 'Hacks'
               ) AS h ON h.MainTable_ID = mt.MainTable_ID