MySQL 如何在使用较少内存的单列中存储多个值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18605669/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to store multiple values in single column where use less memory?
提问by harsh4u
I have a table of userswhere 1 column stores user's "roles". We can assign multiple rolesto particular user.
我有一个用户表，其中 1 列存储用户的“角色”。我们可以为特定用户分配多个角色。
Then I want to store role IDs in the "roles" column.
然后我想在“角色”列中存储角色 ID。
But how can I store multiple values into a single column to save memory in a way that is easy to use? For example, storing using a comma-delimited field is not easy and uses memory.
但是如何将多个值存储到单个列中以易于使用的方式节省内存？例如，使用逗号分隔的字段进行存储并不容易，并且会占用内存。
Any ideas?
有任何想法吗？
回答by
If a user can have multiple roles, it is probably better to have a user_roletable that stores this information. It is normalised, and will be much easier to query.
如果一个用户可以有多个角色，最好有一个user_role存储这些信息的表。它是规范化的，并且会更容易查询。
A table like:
像这样的表：
user_id | role
--------+-----------------
1 | Admin
2 | User
2 | Admin
3 | User
3 | Author
Will allow you to query for all users with a particular role, such as SELECT user_id, user.name FROM user_role JOIN user WHERE role='Admin'rather than having to use string parsing to get details out of a column.
将允许您查询具有特定角色的所有用户，例如SELECT user_id, user.name FROM user_role JOIN user WHERE role='Admin'不必使用字符串解析从列中获取详细信息。
Amongst other things this will be faster, as you can index the columns properly and will take marginally more space than any solution that puts multiple values into a single column - which is antithetical to what relational databases are designed for.
除其他外，这会更快，因为您可以正确索引列，并且比将多个值放入单个列的任何解决方案占用的空间略多 - 这与关系数据库的设计目的相反。
The reason this shouldn't be stored is that it is inefficient, for the reason DCoder states on the comment to this answer. To check if a user has a role, every row of the user table will need to be scanned, and then the "roles" column will have to be scanned using string matching - regardless of how this action is exposed, the RMDBS will need to perform string operations to parse the content. These are very expensive operations, and not at all good database design.
不应该存储它的原因是它效率低下，因为 DCoder 在对此答案的评论中指出。要检查用户是否具有角色，需要扫描用户表的每一行，然后必须使用字符串匹配扫描“角色”列 - 无论此操作如何公开，RMDBS 都需要执行字符串操作来解析内容。这些都是非常昂贵的操作，而且根本不是好的数据库设计。
If you needto have a single column, I would strongly suggest that you no longer have a technical problem, but a people management one. Adding additional tables to an existing database that is under development, should not be difficult. If this isn't something you are authorised to do, explain to why the extra table is needed to the right person - because mungingmultiple values into a single column is a bad, badidea.
如果你需要一个专栏，我强烈建议你不再是技术问题，而是人员管理问题。向正在开发的现有数据库中添加额外的表应该不难。如果这不是您有权执行的操作，请向合适的人解释为什么需要额外的表 - 因为将多个值转换为单个列是一个糟糕的主意。
回答by bfalsata
You can also use bitwise logic with MySQL. role_idmust be in BASE 2 (0, 1, 2, 4, 8, 16, 32...)
您还可以在 MySQL 中使用按位逻辑。role_id必须在 BASE 2 (0, 1, 2, 4, 8, 16, 32...)
role_id | label
--------+-----------------
1 | Admin
2 | User
4 | Author
user_id | name | role
--------+-----------------
1 | John | 1
2 | Steve | 3
3 | Hyman | 6
Bitwise logic allows you to select all user roles
按位逻辑允许您选择所有用户角色
SELECT * FROM users WHERE role & 1
-- returns all Admin users
SELECT * FROM users WHERE role & 5
-- returns all users who are admin or Author because 5 = 1 + 4
SELECT * FROM users WHERE role & 6
-- returns all users who are User or Author because 6 = 2 + 4
回答by JTC
You could do something like this
你可以做这样的事情
INSERT INTO table (id, roles) VALUES ('', '2,3,4');
Then to find it use FIND_IN_SET
然后找到它使用 FIND_IN_SET
回答by Md. Noor-A-Alam Siddique
From your question what I got,
从你的问题我得到了什么，
Suppose, you have to table. one is "meal" table and another one is "combo_meal" table. Now I think you want to store multiple meal_id inside one combo_meal_id without separating coma[,]. And you said that it'll make your DB to more standard.
假设，你必须表。一个是“膳食”表，另一个是“combo_meal”表。现在我想你想在一个组合_meal_id中存储多个meal_id而不分离coma[,]。你说它会让你的数据库更标准。
If I not getting wrong from your question then please read carefully my suggestion bellow. It may be help you.
如果我没有从你的问题中弄错，那么请仔细阅读我下面的建议。它可能会帮助你。
First think is your concept is right. Definitely it'll give you more standard DB.
首先认为是你的概念是对的。肯定会为您提供更标准的数据库。
For this you have to create one more table [ example table: combo_meal_relation ] for referencing those two table data. May be one visible example will clear it.
为此，您必须再创建一个表 [示例表：combo_meal_relation] 来引用这两个表数据。可能是一个可见的例子将清除它。
meal table
+------+--------+-----------+---------+
| id | name | serving | price |
+------+--------+-----------+---------+
| 1 | soup1 | 2 person | 12.50 |
+------+--------+-----------+---------+
| 2 | soup2 | 2 person | 15.50 |
+------+--------+-----------+---------+
| 3 | soup3 | 2 person | 23.00 |
+------+--------+-----------+---------+
| 4 | drink1 | 2 person | 4.50 |
+------+--------+-----------+---------+
| 5 | drink2 | 2 person | 3.50 |
+------+--------+-----------+---------+
| 6 | drink3 | 2 person | 5.50 |
+------+--------+-----------+---------+
| 7 | frui1 | 2 person | 3.00 |
+------+--------+-----------+---------+
| 8 | fruit2 | 2 person | 3.50 |
+------+--------+-----------+---------+
| 9 | fruit3 | 2 person | 4.50 |
+------+--------+-----------+---------+
combo_meal table
+------+--------------+-----------+
| id | combo_name | serving |
+------+--------------+-----------+
| 1 | combo1 | 2 person |
+------+--------------+-----------+
| 2 | combo2 | 2 person |
+------+--------------+-----------+
| 4 | combo3 | 2 person |
+------+--------------+-----------+
combo_meal_relation
+------+--------------+-----------+
| id | combo_meal_id| meal_id |
+------+--------------+-----------+
| 1 | 1 | 1 |
+------+--------------+-----------+
| 2 | 1 | 2 |
+------+--------------+-----------+
| 3 | 1 | 3 |
+------+--------------+-----------+
| 4 | 2 | 4 |
+------+--------------+-----------+
| 5 | 2 | 2 |
+------+--------------+-----------+
| 6 | 2 | 7 |
+------+--------------+-----------+
When you search inside table then it'll generate faster result.
当您在表内搜索时，它会生成更快的结果。
search query:
搜索查询：
SELECT m.*
FROM combo_meal cm
JOIN meal m
ON m.id = cm.meal_id
WHERE cm.combo_id = 1
Hopefully you understand :)
希望你明白:)
