MySQL ENUM 类型与连接表

2021-11-20 00:00:00 enums mysql database-design

我的要求

一个表需要维护一个状态列.

A table needs to maintain a status column.

此列代表 5 个州之一.

This column represents one of 5 states.


初步设计

我想我可以将其设为整数列并使用数值表示状态.

I figured I can just make it an integer column and represent the states using a numeric value.

  • 0 = 开始
  • 1 = 运行
  • 2 = 崩溃
  • 3 = 暂停
  • 4 = 停止

由于我不希望我的应用维护从整数到它们的字符串描述的映射，我计划将它们放在一个单独的状态描述表中(依赖于 FK 关系).

Since I don't want my app to maintain the mapping from the integers to their string description, I plan to place those in a separate state description table (relying on a FK relation).

然后我发现 MySQL 有一个 ENUM 类型，它完全符合我的要求.除了直接依赖 MySQL 之外，使用 ENUM 类型还有什么问题吗?

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?

推荐答案

  • 更改 ENUM 中的值集需要一个 ALTER TABLE，这可能会导致表重组——这是一项非常昂贵的操作(如果您只是添加一个表，则不会发生表重组新值添加到 ENUM 定义的末尾，但如果删除一个或更改顺序，它会进行表重组).而更改查找表中的值集就像 INSERT 或 DELETE 一样简单.

    • Changing the set of values in an ENUM requires an ALTER TABLE which might cause a table restructure -- an incredibly expensive operation (the table restructure doesn't happen if you simply add one new value to the end of the ENUM definition, but if you delete one, or change the order, it does a table restructure). Whereas Changing the set of values in a lookup table is as simple as INSERT or DELETE.

      无法将其他属性与 ENUM 中的值相关联，例如哪些已停用，哪些有资格放入用户界面的下拉列表中.但是，查找表可以包含此类属性的附加列.

      There's no way to associate other attributes with the values in an ENUM, like which ones are retired, and which ones are eligible to be put in a drop-down list in your user interface. However, a lookup table can include additional columns for such attributes.

      查询 ENUM 以获得不同值的列表非常困难，基本上需要您从 INFORMATION_SCHEMA 查询数据类型定义，并从返回的 BLOB 中解析列表.您可以从您的表中尝试 SELECT DISTINCT status，但这只会获取当前正在使用的状态值，这可能不是 ENUM 中的所有值.但是，如果您将值保留在查找表中，则查询、排序等很容易.

      It's very difficult to query an ENUM to get a list of distinct values, basically requiring you to query the data type definition from INFORMATION_SCHEMA, and parsing the list out of the BLOB returned. You could try SELECT DISTINCT status from your table, but that only gets status values currently in use, which might not be all values in the ENUM. However, if you keep values in a lookup table, it's easy to query, sort, etc.

      正如您所知，我不是 ENUM 的忠实粉丝.:-)

      I'm not a big fan of ENUM, as you can tell. :-)

      这同样适用于简单地将一列与一组固定值进行比较的 CHECK 约束.尽管 MySQL 无论如何都不支持 CHECK 约束.

      The same applies to CHECK constraints that simply compare a column to a fixed set of values. Though MySQL doesn't support CHECK constraints anyway.

      更新:MySQL 8.0.16 现在实现了 检查约束.

      Update: MySQL 8.0.16 now implements CHECK constraints.

相关文章