枚举数据类型与MySQL中的数据表？ [英] Enum datatype versus table of data in MySQL?

问题描述

我有一个MySQL表， users ，包含以下列：

1. user_id（PK）


2. 电子邮件


3. 姓名


4. 密码

要管理角色系统，是否会有以下任一选项的缺点？

选项1：

创建名为 roles 列： role_id （主键），名称和描述，然后在的第三个表中将 users.user_id 与 roles.role_id > users_roles ？

或...

选项2：
$ b

使用两个列创建名为 roles 的第二个表： user_id users.user_id ）和 role （ENUM）？ ENUM数据类型列允许插入一个允许的角色的短列表作为值。

我从来没有使用MySQL中的ENUM数据类型，所以我只是好奇，因为选项2将意味着少一个表。我希望这是有道理的，这是我第一次尝试在论坛中描述MySQL表。

解决方案

一般来说， ENUM 类型不能用于这些情况。如果您打算满足将来添加或删除角色的灵活性，这是特别的情况。更改 ENUM 的值的唯一方法是使用 ALTER TABLE ，同时在自己的表中定义角色 $

此外，使用 roles 表允许您添加其他列以更好地定义角色，如在选项1中建议的 description 字段。在选项2中使用 ENUM 类型。

我个人不会选择 ENUM 。也许我可以看到它们用于具有绝对有限的值集合的列，例如 {Spades，Hearts，Diamonds，Clubs} 来定义卡的套装，不是在有问题的情况下，对于前面提到的缺点。

I have one MySQL table, users, with the following columns:

1. user_id (PK)
2. email
3. name
4. password

To manage a roles system, would there be a downside to either of the following options?

Option 1:

Create a second table called roles with three columns: role_id (Primary key), name, and description, then associate users.user_id with roles.role_id as foreign keys in a third table called users_roles?

Or...

Option 2:

Create a second table called roles with two columns: user_id (Foreign key from users.user_id) and role (ENUM)? The ENUM datatype column would allow for a short list of allowable roles to be inserted as values.

I've never used the ENUM datatype in MySQL before, so I'm just curious, as option 2 would mean one less table. I hope that makes sense, this is the first time I've attempted to describe MySQL tables in a forum.

解决方案

In general, ENUM types are not meant to be used in these situations. This is especially the case if you intend to cater for the flexibility of adding or removing roles in the future. The only way to change the values of an ENUM is with an ALTER TABLE, while defining the roles in their own table will simply require a new row in the roles table.

In addition, using the roles table allows you to add additional columns to better define the role, like the description field you suggested in Option 1. This is not possible if you were to use an ENUM type as in Option 2.

Personally I would not opt for an ENUM in these scenarios. Maybe I can see them being used for columns with an absolutely finite set of values, such as {Spades, Hearts, Diamonds, Clubs} to define the suit of a card, but not in cases such as the one in question, for the disadvantages mentioned earlier.

这篇关于枚举数据类型与MySQL中的数据表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！