我应该使用 ENUM 作为主键和外键吗？
同事创建了一个架构，该架构使用 ENUM() 列作为查找表上的主键。 该表将产品代码“FB”转换为名称“Foo Bar”。
然后，该主键在其他地方用作外键。 目前，FK 也是一个 ENUM()。
我认为这不是一个好主意。 这意味着要连接这两个表，我们最终需要进行四次查找。 两个表，加上两个 ENUM()。 我对么？
我希望 FK 为 CHAR(2) 以减少查找次数。 我还希望 PK 也是 CHAR(2) 以完全减少它。
ENUM() 的好处是获得对值的约束。 我希望有类似的东西： CHAR(2) ALLOW('FB', 'AB', 'CD') 我们可以将其用于 PK 和 FK 列。
什么是：
- 最佳实践
- 您的偏好
这个概念也用在其他地方。 如果 ENUM() 的值更长怎么办？ ENUM('叮、咚、戴尔', '咩咩黑羊'). 现在，从空间的角度来看，ENUM() 非常有用。 如果有几百万行使用这些值，我是否应该只关心这个？ 在这种情况下，ENUM() 可以节省存储空间。
An associate has created a schema that uses an ENUM() column for the primary key on a lookup table. The table turns a product code "FB" into it's name "Foo Bar".
This primary key is then used as a foreign key elsewhere. And at the moment, the FK is also an ENUM().
I think this is not a good idea. This means that to join these two tables, we end up with four lookups. The two tables, plus the two ENUM(). Am I correct?
I'd prefer to have the FKs be CHAR(2) to reduce the lookups. I'd also prefer that the PKs were also CHAR(2) to reduce it completely.
The benefit of the ENUM()s is to get constraints on the values. I wish there was something like: CHAR(2) ALLOW('FB', 'AB', 'CD') that we could use for both the PK and FK columns.
What is:
- Best Practice
- Your preference
This concept is used elsewhere too. What if the ENUM()'s values are longer? ENUM('Ding, dong, dell', 'Baa baa black sheep'). Now the ENUM() is useful from a space point-of-view. Should I only care about this if there are several million rows using the values? In which case, the ENUM() saves storage space.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
ENUM 应用于定义给定字段的可能值范围。 这也意味着您可能有多行该特定字段具有相同的值。
我不建议使用 ENUM 作为外键类型的主键类型。
使用 ENUM 作为主键意味着添加新键将涉及修改表，因为必须先修改 ENUM，然后才能插入新键。
我猜测您的同事正在尝试限制谁可以插入新行，并且行数是有限的。 我认为这应该通过在数据库级别或应用程序上进行适当的权限设置来实现，而不是通过使用 ENUM 作为主键来实现。
恕我直言，使用 ENUM 作为主键类型违反了 KISS 原则。
ENUM should be used to define a possible range of values for a given field. This also implies that you may have multiple rows which have the same value for this perticular field.
I would not recommend using an ENUM for a primary key type of foreign key type.
Using an ENUM for a primary key means that adding a new key would involve modifying the table since the ENUM has to be modified before you can insert a new key.
I am guessing that your associate is trying to limit who can insert a new row and that number of rows is limited. I think that this should be achieved through proper permission settings either at the database level or at the application and not through using an ENUM for the primary key.
IMHO, using an ENUM for the primary key type violates the KISS principle.
但是，当您只捕获不同的 10 行或更少的行时，这不会成为问题
，例如
此表，获得 DML 非常困难
but when you only trapped with differently 10 or less rows that wont be a problem
e.g's
This table it is more than diffecult to get a DML
我们对此进行了更多讨论，得出的结论如下：
到处使用 CHAR(2)。 无论是PK还是FK。 然后使用 mysql 的外键约束来禁止为查找表中不存在的行创建 FK。
这样，假设查找表为
L，并且有两个引用表X和Y，我们可以连接X到Y，无需任何查找ENUM()或表L，并且可以确定地知道存在如果（当）我们需要它时，在L中行。我仍然对评论和其他想法感兴趣。
We've had more discussion about it and here's what we've come up with:
Use CHAR(2) everywhere. For both the PK and FK. Then use mysql's foreign key constraints to disallow creating an FK to a row that doesn't exist in the lookup table.
That way, given the lookup table is
L, and two referring tablesXandY, we can joinXtoYwithout any looking up ofENUM()s or tableLand can know with certainty that there's a row inLif (when) we need it.I'm still interested in comments and other thoughts.
拥有查找表和枚举意味着您始终在两个位置更改值。 有趣的是……我们花了很多年时间使用枚举，导致我们需要重新编译以添加值的问题。 近年来，我们在许多情况下已经不再使用枚举，而是使用查找表中的值。 我喜欢查找表的最大价值是您无需编译即可添加或更改值。 即使有数百万行，我也会坚持使用查找表，并且在数据库设计中保持智能
Having a lookup table and a enum means you are changing values in two places all the time. Funny... We spent to many years using enums causing issues where we need to recompile to add values. In recent years, we have moved away from enums in many situations an using the values in our lookup tables. The biggest value I like about lookup tables is that you add or change values without needing to compile. Even with millions of rows I would stick to the lookup tables and just be intelligent in your database design