Question

我的要求

一张表需要维护一个“状态”列。

这一列代表了五个州之一。

initial design

我想我可以将其设置为整数列，并使用数字值表示状态。

0 = start
1 = running
2 = crashed
3 = paused
4 = stopped

因为我不想让我的应用程序维护整数到其字符串描述的映射，所以我计划将它们放在单独的状态描述表中（依赖于外键关系）。

Then I discovered that MySQL has an ENUM type which matches my requirement exactly. Other than a direct dependency on MySQL, are there any pitfalls with using the ENUM type?

Answer 1

修改 ENUM 中的值集需要使用 ALTER TABLE，这可能会导致表重构——这是一项非常昂贵的操作（如果您仅在 ENUM 定义的末尾添加一个新值，则不会发生表重构，但如果删除一个或更改顺序，则会进行表重构）。而在查找表中更改值集只需要使用 INSERT 或 DELETE 。
没有办法将其他属性与ENUM中的值关联起来，比如哪些被退役，哪些有资格放入您的用户界面中的下拉列表中。然而，查找表可以包含用于这些属性的附加列。
查询ENUM以获取唯一值列表非常困难，基本上要求您从INFORMATION_SCHEMA查询数据类型定义，并从返回的BLOB中解析列表。您可以尝试从表中选择SELECT DISTINCT status，但这仅获取当前使用的状态值，可能并不是ENUM中的所有值。但是，如果您将值保留在查找表中，则很容易查询，排序等。

我不是ENUM的大粉丝，你可以看出来。 :-)

同样的道理适用于仅将列与固定值集合进行比较的CHECK约束。虽然MySQL无论如何都不支持CHECK约束。

更新：MySQL 8.0.16 现在实现 CHECK 约束。

Answer 2

Here is article about speed comparison of enum. Maybe it gives some hints. IMHO it should be limited to a use in fixed list of strings ("Yes/No", "Child/Adult") that with 99% probability doesn t change in the future.

Answer 3

Enums in MySQL are bad for the already explained reasons.
I can add the following fact: Enum does not ensure any kind of validation on the server side. If you insert a row with a value which does not exist in the enum definition, you ll get a nice <empty> or NULL value in the DB, depending on NULL-ability of the enum field declaration.

我的观点是关于 TinyInt 类型的：

enums are limited to 65535 values
if you don t need more than 256 values, tinyint will take less space for each row, and its behavior is much more "predictable".

Answer 4

If you have lots of data in your DB ( more data then you have RAM ) and you ENUM values are NEVER going to change, I would go with ENUM, rather than the join. It should be faster.
Think about it, in the join case, you need an index on your foreign key and index on your primary key in the other table. As Riho said, see the benchmarks.

Answer 5

A table would be easier to internationalize. But so would a class outside the database entirely. This kind of check can be hard to debug when it s not in the business logic, and would typically not be the responsibility of database people.

作为一种优化，现在可能为时过早；但是OP主要是提议把它作为一项便利功能。

另外请查看 http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/。

友情链接