在MySQL的单个单元格中存储多个值 [英] Storing multiple values in a single cell in MySQL

问题描述

我正在使用分隔符(例如"1,5,10")和TEXT数据类型在MySQL单元中存储多个数字.

I'm storing multiple numbers in a MySQL cell by using a delimiter (eg "1,5,10") and the TEXT datatype.

我如何执行这样的查找?

How do I perform lookups like this?

SELECT * FROM MyTable WHERE MultiVals CONTAINS "5"

最后，这是存储此类值的首选方法吗?我像链接器表一样使用它，将某些行链接到另一个表中的其他多个行(通过ID).由于我正在尝试最小化数据库的文件大小，因此我认为这将是一种更紧凑的链接方法，而不是使用另一个这样的表:

And lastly, is this the preferred approach to store such values? I'm using this like a linker table, to link certain rows to multiple other rows in another table (via IDs). Since I'm trying to minimize the filesize of the DB, I thought this would be a more compact approach to linking instead of using another table like this:

Person ID       Product ID
-----------     -----------
3               1
3               2
3               3
7               5
7               7

推荐答案

建议在具有两个外键ID列的两个链接表之间创建第三个所谓的映射表.

It's advised to create a 3rd so called mapping table between the two linked table with two foreign key ID columns.

Project ID       Tag ID
-----------     -----------
3               1
3               2
3               3
7               5
7               7

使用这些，两个外键也可以用作索引.因此，与FIND_IN_SET方法相比，它将大大加快查找速度. 您正在使文件大小最小化，因为TEXT数据类型为未使用的位保留空白，而数字存储是最好的，而使用的本地存储最少.

Using these, the two foreign keys acts as an index too. So it will speed up dramatically the lookup compared to a FIND_IN_SET method. You are minimizing the file size, as the TEXT data type holds empty spaces for unused bits, while the numeric storage is the best, and uses minimal local storage.

无论如何，如果您想保持自己的原始方式，请按以下步骤操作:

Anyway if you want to keep your original way you go this:

SELECT * FROM MyTable WHERE FIND_IN_SET(5,MultiVals) >0

这将确保使用逗号将值匹配，因此值5将仅匹配"5"而不匹配"15"

this will make sure the values are matched by using comma-s so a value of 5 will match just "5" and not "15"

这篇关于在MySQL的单个单元格中存储多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！