SQL：ENUM 与一对多关系的优点？
我很少看到 ENUM 数据类型在野外使用；开发人员几乎总是只使用看起来像这样的辅助表：
CREATE TABLE officer_ranks (
id int PRIMARY KEY
,title varchar NOT NULL UNIQUE);
INSERT INTO officer_ranks VALUES (1,'2LT'),(2,'1LT'),(3,'CPT'),(4,'MAJ'),(5,'LTC'),(6,'COL'),(7,'BG'),(8,'MG'),(9,'LTG'),(10,'GEN');
CREATE TABLE officers (
solider_name varchar NOT NULL
,rank int NOT NULL REFERENCES officer_ranks(id) ON DELETE RESTRICT
,serial_num varchar PRIMARY KEY);
但是使用用户定义类型/ENUM也可以显示相同的内容：（
CREATE TYPE officer_rank AS ENUM ('2LT', '1LT','CPT','MAJ','LTC','COL','BG','MG','LTG','GEN');
CREATE TABLE officers (
solider_name varchar NOT NULL
,rank officer_rank NOT NULL
,serial_num varchar PRIMARY KEY);
使用PostgreSQL显示的示例，但其他RDBMS具有类似的语法）
我看到的最大缺点使用 ENUM 的缺点是从应用程序内部更新更加困难。它也可能会让习惯于将 SQL DB 简单地用作位存储桶的缺乏经验的开发人员感到困惑。
假设信息大部分是静态的（工作日名称、月份名称、美国陆军军衔等），使用 ENUM 有什么优势吗？
I very rarely see ENUM datatypes used in the wild; a developer almost always just uses a secondary table that looks like this:
CREATE TABLE officer_ranks (
id int PRIMARY KEY
,title varchar NOT NULL UNIQUE);
INSERT INTO officer_ranks VALUES (1,'2LT'),(2,'1LT'),(3,'CPT'),(4,'MAJ'),(5,'LTC'),(6,'COL'),(7,'BG'),(8,'MG'),(9,'LTG'),(10,'GEN');
CREATE TABLE officers (
solider_name varchar NOT NULL
,rank int NOT NULL REFERENCES officer_ranks(id) ON DELETE RESTRICT
,serial_num varchar PRIMARY KEY);
But the same thing can also be shown using a user-defined type / ENUM:
CREATE TYPE officer_rank AS ENUM ('2LT', '1LT','CPT','MAJ','LTC','COL','BG','MG','LTG','GEN');
CREATE TABLE officers (
solider_name varchar NOT NULL
,rank officer_rank NOT NULL
,serial_num varchar PRIMARY KEY);
(Example shown using PostgreSQL, but other RDBMS's have similar syntax)
The biggest disadvantage I see to using an ENUM is that it's more difficult to update from within an application. And it might also confuse an inexperienced developer who's used to using a SQL DB simply as a bit bucket.
Assuming that the information is mostly static (weekday names, month names, US Army ranks, etc) is there any advantage to using a ENUM?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
一般来说，枚举更适合那些变化不大的东西，并且它使用的资源稍微少一些，因为没有 FK 检查或类似在插入时执行的东西等。
使用查找表更优雅和/或传统，而且更容易添加和删除选项而不是枚举。批量更改值也比枚举更容易。
Generally speaking, enum is better for things that don't change much, and it uses slightly fewer resources, since there's no FK checks or anything like to execute on insert etc.
Using a lookup table is more elegant and or traditional and it's much easier to add and remove options than an enum. It's also easier to mass change the values than an enum.
嗯，您看不到，因为通常开发人员在 Java 等编程语言中使用枚举，而在数据库设计中没有对应的枚举。
在数据库中，此类枚举通常是文本或整数字段，没有任何限制。数据库枚举不会被转换为 Java/C#/等。枚举，因此开发人员认为这没有任何好处。
有很多非常好的数据库功能很少被使用，因为大多数 ORM 工具太原始而无法支持它们。
Well, you don't see, because usually developers are using enums in programming languages such as Java, and the don't have their counterparts in database design.
In database such enums are usually text or integer fields, with no constraints. Database enums will not be translated into Java/C#/etc. enums, so the developers see no gain in this.
There are very many very good database features which are rarely used because most ORM tools are too primitive to support them.
枚举相对于查找表的另一个好处是，当您编写 SQL 函数时，您可以进行类型检查。
Another benefit of enums over a lookup table is that when you write SQL functions you get type checking.
使用 PostgreSQL 显示示例，但其他 RDBMS 具有类似的语法
这是不正确的。它不是 ISO/IEC/ANSI SQL 要求，因此商业数据库不提供它（您应该提供查找表）。城镇的小端实施各种“额外”，但不执行城镇大端的更严格要求或咕噜声。
我们也没有将 ENUM 作为数据类型的一部分，这是荒谬的。
ENUM 的第一个缺点是它是非标准的，因此不可移植。
ENUM 的第二大缺点是数据库是封闭的。可在数据库上使用的数百个报告工具（独立于应用程序）无法找到它们，因此无法预测名称/含义。如果您有一个普通的标准 SQL 查找表，那么这个问题就可以消除。
第三个是，当你改变值时，你必须改变DDL。在普通标准 SQL 数据库中，您只需在查找表中插入/更新/删除一行即可。
最后，您无法轻松获得 ENUM 的内容列表；你可以使用查找表。更重要的是，您有一个向量可以用来执行任何维度事实查询，从而无需从大型事实表和 GROUP BY 中进行选择。
Example shown using PostgreSQL, but other RDBMS's have similar syntax
That's incorrect. It is not an ISO/IEC/ANSI SQL requirement, so the commercial databases do not provide it (you are supposed to provide Lookup tables). The small end of town implement various "extras", but do not implement the stricter requirements, or the grunt, of the big end of town.
We do not have ENUMs as part of a DataType either, that is absurd.
The first disadvantage of ENUMs is that is it non-standard and therefore not portable.
The second big disadvantage of ENUMs is, that the database is Closed. The hundreds of Report Tools that can be used on a database (independent of the app), cannot find them, and therefore cannot project the names/meanings. If you had a normal Standard SQL Lookup table, that problem is eliminated.
The third is, when you change the values, you have to change DDL. In a Normal Standard SQL database, you simply Insert/Update/Delete a row in the Lookup table.
Last, you cannot easily get a list of the content of the ENUM; you can with a Lookup table. More important, you have a vector to perform any Dimension-Fact queries with, eliminating the need for selecting from the large Fact table and GROUP BY.
ENUMS 非常非常非常有用！您只需要知道如何使用它们：
因此，如果您有要使用的固定字符串值列表，那么与查找表相比，ENUM 是更好的解决方案。假设您需要列出产品中的氨基酸及其各自的重量。今天有大约 20 种氨基酸。如果您要存储他们的全名，则每次需要的空间将多于 2 个字节。另一种选择是使用人工键并链接到外部表。但是外国表会是什么样子呢？它有两列：ID 和氨基酸名称吗？你每次都会加入那张桌子吗？如果您的主表有超过 40 个这样的字段怎么办？查询该表将涉及 > 40 个连接。
如果您的数据库托管 1600 个表，其中 400 个是仅替换 ENUM 的查找表，那么您的开发人员将浪费大量时间浏览它们（除了 JOIN 之外）。是的，您可以使用前缀、模式等......但为什么不直接将这些表踢出呢？
ENUMS 是枚举列表/有序列表。这意味着，如果您有有序的值，那么您实际上省去了维护 3 列查找表的麻烦。
问题是：为什么我需要查找表？
嗯，答案很简单：
现在有趣的是：
查找表和 ENUMS 不能完全替代！！！！
如果你有一个列表，其中PK是单列自然键。列表可以增长，或者值可以更改名称（出于某种原因），然后您可以定义一个 ENUM 并将其用于以下两者：查找中的 PK 和主表中的 FK！
好处示例：
您必须更改查找键的名称。如果不使用 ENUM，DBMS 必须将更改级联到所有表，您在其中使用此值而不仅仅是查找表。如果您使用ENUM，那么您只需更改ENUM的值，而不会更改数据。
ENUMS are very-very-very useful! You just have to know how to use them:
Thus if you have a fixed list of string values, which you want to use, an ENUM is a better solution compared to a lookup table. Let's say you need to List Amino-Acids in your products, with their respective weight. Today there are ~20 Amino Acids. If you would store their full names, you'd need much more space each time then 2 Bytes. The other option is to use artificial keys and to link to a foreign table. But how would the foreign Table look like? Would it have 2 columns: ID and Amino Acid Name? And you would join that table every time? What if your main table has >40 such fields? Querying that table would involve >40 Joins.
If your database hosts 1600 Tables, 400 of which are lookup tables which just replace ENUMs, your devs will waste lots of time navigating through them (in addition to the JOINs). Yes, you can work with prefixes, schemas and such.... but why not just kick those tables out?
ENUMS are Enumerated lists / ordered. That means that if you have values which are ordered, you are actually saving the hassle of maintaining a 3 columns lookup table.
The question is rather: why do I need lookup tables then?
Well, the answer is easy:
And now the funny thing:
Lookup Tables and ENUMS are not complete replacements for each other!!!!
If you have a list, where the PK is single-column natural key. The list can grow or the values can change their names (for some reason), then you could define an ENUM and use it for both: PK in lookup and FK in main tables!
Example benefit:
you have to change the name of a lookup key. Without using the ENUM the DBMS will have to cascade the changes to all tables, where you use this value and not just your lookup table. If you are using ENUM, then you just change the value of ENUM, and there are no changes to the data.
我没有看到使用 ENUMS 的任何优势。
它们更难维护，并且不提供任何具有适当外键的常规查找表不允许您做的事情。
I don't see any advantage in using ENUMS.
They are harder to maintain and don't offer anything that a regular lookup table with proper foreign keys wouldn't allow you to do.
使用 ENUM 之类的缺点是，如果数据表中不存在所有可用值，则无法获取所有可用值的列表，除非您在某处对可用值列表进行硬编码。例如，如果在您的 OFFICERS 表中您碰巧没有 MG 职位，则无法知道该军衔是否存在。因此，当布洛哈德准将被马乔里-班克斯少将接替时，你将无法进入新军官军衔——这是一种耻辱，因为他是现代少将的典范。 :-) 当陆军将军（五星级将军）出现时会发生什么？
对于不会改变的简单类型，我已成功使用域。例如，在我的一个数据库中，我有一个 yes_no_domain 定义如下：
共享和享受。
A disadvantage of using something like an ENUM is that you can't get a list of all the available values if they don't happen to exist in your data table, unless you hard-code the list of available values somewhere. For example, if in your OFFICERS table you don't happen to have an MG on post there's no way to know the rank exists. Thus, when BG Blowhard is relieved by MG Marjorie-Banks you'll have no way to enter the new officer's rank - which is a shame, as he is the very model of a modern Major General. :-) And what happens when a General of the Army (five-star general) shows up?
For simple types which will not change I've used domains successfully. For example, in one of my databases I've got a yes_no_domain defined as follows:
Share and enjoy.
一个小小的优势可能在于，在创建 ENUM 时您可以使用某种 UDT。用户定义的类型可以在许多其他数据库对象中正式重用，例如在视图、其他表、其他类型、存储过程（在其他RDBMS 中）等中。
另一个优点是可以记录字段的允许值。示例：
可能是品味问题。对于此类字段，我更喜欢使用 ENUM，而不是用于查找此类简单概念的表的外键。
另一个优点可能是，当您在 Java 中使用代码生成或 ORM（例如 jOOQ）时，您可以使用该 ENUM 从中生成 Java 枚举类，而不是加入查找表或使用 ENUM 文字的 ID。
事实上，只有少数 RDBMS 支持正式的 ENUM 类型。我只知道Postgres和MySQL。 Oracle 或 DB2 没有它。
A small advantage may lie in the fact, that you have a sort of UDT when creating an ENUM. A user defined type can be reused formally in many other database objects, e.g. in views, other tables, other types, stored procedures (in other RDBMS), etc.
Another advantage is for documentation of the allowed values of a field. Examples:
Probably a matter of taste. I prefer ENUMs for these kinds of fields, rather than foreign keys to lookup tables for such simple concepts.
Yet another advantage may be that when you use code generation or ORMs like jOOQ in Java, you can use that ENUM to generate a Java enum class from it, instead of joining the lookup table, or working with the ENUM literal's ID
It's a fact, though, that only few RDBMS support a formal ENUM type. I only know of Postgres and MySQL. Oracle or DB2 don't have it.
优点：
存储过程的类型安全：如果参数无法强制转换为类型，则会引发类型错误。例如：
select Court_martial('3LT')会自动引发类型错误。自定义联盟顺序：在您的示例中，可以在没有排名 ID 的情况下对军官进行排序。
Advantages:
Type safety for stored procedures: will raise a type error if argument can not be coerced into the type. Like:
select court_martial('3LT')would raise a type error automatically.Custom coalition order: In your example, officers could be sorted without a ranking id.