我正在尝试创建一个表，该表将跟踪报告网站评论的用户。现在，我有一张桌子，看起来像这样：
id | num_reports | users
-----------------------------------
12345 1
12489 4
对于此表，我希望id是唯一的，并且number_reports始终从1开始递增。但是对于用户，我感到困惑，因为我想保留创建报告的user_ids的记录，但不确定如何使它可以存储多个user_id。
我想到做类似的事情
id | user_id
---------------
123 567
123 689
在这种情况下，您只需要计算id重复且user_id唯一的行数，但这似乎效率很低。
我一直在四处张望，看起来正确的方法将是创建另一个表，但是这如何允许我存储多个user_id？
最佳答案
这是正确的方法。这是您应该拥有的：
USERS COMMENTS
+---------+------+ +------------+---------+------------+---------------------+
| id_user | name | | id_comment | id_user | id_article | date |
+---------+------+ +------------+---------+------------+---------------------+
| 171 | Joe | | 245 | 245 | 24 | 2015-03-22 10:12:00 |
| 180 | Jack | | 1245 | 180 | 68 | 2015-03-23 23:01:19 |
| ... | ... | | ... | ... | ... | ... |
+---------+------+ +------------+---------+------------+---------------------+
COMMENT_REPORTS
+-----------+------------+---------+---------------------+
| id_report | id_comment | id_user | date |
+-----------+------------+---------+---------------------+
| 1 | 245 | 171 | 2015-03-24 16:11:15 |
| 2 | 654 | 180 | 2015-03-24 18:13:42 |
| 3 | 1245 | 180 | 2015-03-24 18:34:01 |
| 4 | 1245 | 456 | 2015-03-25 09:58:10 |
| ... | ... | ... | ... |
+-----------+------------+---------+---------------------+
然后，您将能够获得：
# Every reports made by an user
SELECT *
FROM comment_reports
WHERE user_id = 180
# Every reports related to a comment
SELECT *
FROM comment_reports
WHERE comment_id = 1245
# Every reports made today
SELECT *
FROM comment_reports
WHERE date >= CURDATE()
# The amount of reports related to an user's comments
SELECT c.id_user AS User, COUNT(cr.id_report) AS Reported
FROM comment_reports cr
JOIN comments c ON (cr.id_comment = c.id_comment)
WHERE c.id_user = 180
GROUP BY c.id_user
关于mysql - MySQL-列内的列表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29250437/