如何在使用较少内存的单列中存储多个值?

问题描述：

我有一个 users 表，其中1列存储用户的角色". 我们可以为特定用户分配多个角色.

I have a table of users where 1 column stores user's "roles". We can assign multiple roles to particular user.

然后，我要将角色ID存储在角色"列中.

Then I want to store role IDs in the "roles" column.

但是如何将多个值存储到单个列中，以易于使用的方式节省内存?例如，使用逗号分隔的字段进行存储并不容易，并且会占用内存.

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.

有什么想法吗?

答

如果用户可以担任多个角色，最好使用一个user_role表来存储此信息.它已标准化，查询起来会容易得多.

If a user can have multiple roles, it is probably better to have a user_role table that stores this information. It is normalised, and will be much easier to query.

像这样的表

user_id | role
--------+-----------------
      1 | Admin
      2 | User
      2 | Admin
      3 | User
      3 | Author

将允许您查询具有特定角色(例如SELECT user_id, user.name FROM user_role JOIN user WHERE role='Admin')的所有用户，而不必使用字符串解析来获取列中的详细信息.

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.

这将更快，因为您可以正确索引列，并且比将多个值放入单个列的任何解决方案都要占用更多的空间-这与关系数据库的设计宗旨相反.

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.

不应存储此内容的原因是效率低下，原因是DCoder在对此答案的评论中指出一个>.要检查用户是否具有角色，将需要扫描用户表的每一行，然后必须使用字符串匹配来扫描角色"列-不管暴露此操作的方式如何，RMDBS都需要执行字符串操作以解析内容.这些操作非常昂贵，而且数据库设计也不是很好.

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.

如果您需要只有一栏，我强烈建议您不再遇到技术问题，而是人员管理问题.向正在开发的现有数据库中添加其他表应该不难.如果这不是您有权执行的操作，请向正确的人解释为什么需要额外的表-因为

If you need to 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 munging multiple values into a single column is a bad, bad idea.

如何在使用较少内存的单列中存储多个值?

相关推荐