mysql - 将数据存储在表中，而不在列中使用 CSV 列表

Question

我有下表:

+-----------+--------------+
|categoryID | categoryName |
+-----------+--------------+
| 0         | Bills        |
| 1         | Gas          |
| 2         | Coffee       |
| 3         | Restaurants  |
+-----------+--------------+

我的所有用户都可以访问此表，但是，用户希望能够按照他们认为合适的方式对表进行排序(不一定按字母顺序或顺序)并保存他们的排序顺序首选项。他们还想隐藏不使用的类别。

例如:

用户 1 希望他们的表格如下所示:

+-----------+--------------+
|categoryID | categoryName |
+-----------+--------------+
| 3         | Restaurants  |
| 0         | Bills        |
| 1         | Gas          |
+-----------+--------------+

用户 2 希望他们的表格如下所示:

+-----------+--------------+
|categoryID | categoryName |
+-----------+--------------+
| 0         | Bills        |
| 1         | Gas          |
| 2         | Coffee       |
+-----------+--------------+

用户 3 希望他们的表格如下所示:

+-----------+--------------+
|categoryID | categoryName |
+-----------+--------------+
| 3         | Restaurants  |
| 1         | Gas          |
| 0         | Bills        |
| 2         | Coffee       |
+-----------+--------------+

我能想到的完成这项工作的唯一方法是制作一张像这样的表格:

+-------+---------+
|userID | sort    |
+-------+---------+
| 1     | 3,0,1   |
| 2     | 0,1,2   |
| 3     | 3,1,0,2 |
+-------+---------+

问题:有人告诉我，将 CSV 列表存储在列中并不是一个好的做法，但我不知道还有什么方法可以实现此目的。如何在不将 CSV 列表存储在列中的情况下完成我想要的任务？

Answer 1

不要在数据库中存储分隔值，因为您将无法正常维护和查询数据。

而是创建多对多表(我们称之为 user_categories)，如下所示

CREATE TABLE user_categories
(
  `userid` int, 
  `categoryid` int, 
  `sort_order` int,
  primary key (userid, categoryid),
  foreign key (userid) references users (userid),
  foreign key (categoryid) references categories (categoryid)  
);

您问题中提到的三个用户的示例内容如下所示

| USERID | CATEGORYID | SORT_ORDER ||--------|------------|------------||      1 |          3 |          1 ||      1 |          0 |          2 ||      1 |          1 |          3 ||      2 |          0 |          1 ||      2 |          1 |          2 ||      2 |          2 |          3 ||      3 |          3 |          1 ||      3 |          1 |          2 ||      3 |          0 |          3 ||      3 |          2 |          4 |

Then you can do

SELECT c.categoryid, c.categoryname
  FROM user_categories uc JOIN categories c
    ON uc.categoryid = c.categoryid
 WHERE userid = 1
 ORDER BY uc.sort_order

示例输出:

| CATEGORYID | CATEGORYNAME ||------------|--------------||          3 |  Restaurants ||          0 |        Bills ||          1 |          Gas |

这里是SQLFiddle 演示