MySQL ENUM类型vs连接表 [英] MySQL ENUM type vs join tables

问题描述

我的要求

表需要维护状态列。

此列表示5个州之一。

初始设计

我想我可以使它成为整数列，并使用数值代表状态。

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

• 0 =开始


• 1 =正在运行


• 2 =崩溃


• 3 = li>
  
• 4 =停止

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

由于我不希望我的应用程序维护整数的映射对于他们的字符串描述，我打算将它们放在单独的状态描述表（依赖于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 ENUM类型vs连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！