在MySQL字段中存储csv –不好的主意吗? [英] Storing csv in MySQL field – bad idea?

问题描述

我有两个表，一个用户表和一个项目表.在用户表中，存在项目"字段."items"表仅包含一个唯一的ID和一个item_name.

现在每个用户可以拥有多个项目.我想避免创建第三个表来将项目与用户连接，而是在user_table中有一个字段，用于在"csv"字段中存储与用户连接的项目ID.

因此，任何给定的用户都将具有一个字段"items"，该字段的值可能类似于"32,3,98,56".

也许值得一提的是，每个用户的项目数上限是相当有限的(< 5).

问题:与拥有包含用户->项对的第三个表相比，这种方法通常不是一个好主意吗?

当您要查找用户的所有项目时，第三个表不会产生相当大的开销(我将不得不逐个遍历MySQL返回的所有元素).

解决方案

您不想以逗号分隔的形式存储值.

考虑当您决定将此列与其他表合并时的情况.

考虑一下，

  x个项目1 1 2 31 1、42 1 

，您想为每个x查找不同的值，即:

  x个项目1 1、2、3、42 1 

或者可能要检查其中是否有3

或者可能希望将它们转换为单独的行:

  x个项目1 11 21 31 11 42 1 

这将是巨大的痛苦.

使用至少标准化的第一原则-每个值都有单独的行.

现在，假设您原来在餐桌上有这个东西:

  x项1 11 21 31 11 42 1 

您可以轻松地将其转换为csv值:

 选择x，group_concat(按项目排序)从T按x分组 

如果要搜索x = 1是否具有第3项.

 从t中选择*其中x = 1且item = 3 

在较早的情况下会使用可怕 find_in_set :

 从t中选择*，其中x = 1并find_in_set(3，items); 

如果您认为可以将like与CSV值一起使用，则首先 like％x％不能使用索引.其次，它将产生错误的结果.
假设您要检查项目ab是否存在，并且您执行％ab％，它将返回带有abc abcd abcde ......p的行

如果您有很多用户和项目，那么我建议创建一个单独的表 users 和一个PK用户ID，另一个 items 和一个PK用户ID，最后一个映射表 user_item 具有userid，itemid列.

如果您知道只需要存储和检索这些值，而不需要对它进行任何操作，例如连接，搜索，不重复，转换为单独的行等，等等-可能就是这样，您可以(我还是不会).

I have two tables, one user table and an items table. In the user table, there is the field "items". The "items" table only consists of a unique id and an item_name.

Now each user can have multiple items. I wanted to avoid creating a third table that would connect the items with the user but rather have a field in the user_table that stores the item ids connected to the user in a "csv" field.

So any given user would have a field "items" that could have a value like "32,3,98,56".

It maybe is worth mentioning that the maximum number of items per user is rather limited (<5).

The question: Is this approach generally a bad idea compared to having a third table that contains user->item pairs?

Wouldn't a third table create quite an overhead when you want to find all items of a user (I would have to iterate through all elements returned by MySQL individually).

解决方案

You don't want to store the value in the comma separated form.

Consider the case when you decide to join this column with some other table.

Consider you have,

x  items
1  1, 2, 3
1  1, 4
2  1

and you want to find distinct values for each x i.e.:

x  items
1  1, 2, 3, 4
2  1

or may be want to check if it has 3 in it

or may be want to convert them into separate rows:

x  items
1  1
1  2
1  3
1  1
1  4
2  1

It will be a HUGE PAIN.

Use atleast normalization 1st principle - have separate row for each value.

Now, say originally you had this as you table:

x  item
1  1
1  2
1  3
1  1
1  4
2  1

You can easily convert it into csv values:

select x, group_concat(item order by item) items
from t
group by x

If you want to search if x = 1 has item 3. Easy.

select * from t where x = 1 and item = 3

which in earlier case would use horrible find_in_set:

select * from t where x = 1 and find_in_set(3, items);

If you think you can use like with CSV values to search, then first like %x% can't use indexes. Second, it will produce wrong results.
Say you want check if item ab is present and you do %ab% it will return rows with abc abcd abcde .... .

If you have many users and items, then I'd suggest create separate table users with an PK userid, another items with PK itemid and lastly a mapping table user_item having userid, itemid columns.

If you know you'll just need to store and retrieve these values and not do any operation on it such as join, search, distinct, conversion to separate rows etc. etc. - may be just may be, you can (I still wouldn't).

这篇关于在MySQL字段中存储csv –不好的主意吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！