postgresql SQL：ENUM 与一对多关系的优势？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4293476/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: Advantages of an ENUM vs. a one-to-many relationship?
提问by jamieb
I very rarely see ENUM datatypes used in the wild; a developer almost always just uses a secondary table that looks like this:
我很少看到在野外使用 ENUM 数据类型；开发人员几乎总是只使用如下所示的辅助表：
CREATE TABLE officer_ranks (
id int PRIMARY KEY
,title varchar NOT NULL UNIQUE);
INSERT INTO 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:
但同样的事情也可以使用用户定义的类型 / 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)
（示例使用 PostgreSQL，但其他 RDBMS 具有类似的语法）
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.
我认为使用 ENUM 的最大缺点是从应用程序内部更新更困难。并且它也可能会使习惯于将 SQL 数据库用作一个位桶的没有经验的开发人员感到困惑。
Assuming that the information is mostly static (weekday names, month names, US Army ranks, etc) is there any advantage to using a ENUM?
假设信息大部分是静态的（工作日名称、月份名称、美国陆军军衔等），使用 ENUM 有什么好处吗？
采纳答案by Bob Jarvis - Reinstate Monica
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?
使用 ENUM 之类的东西的一个缺点是，如果数据表中不存在所有可用值，则您无法获得所有可用值的列表，除非您在某处对可用值列表进行了硬编码。例如，如果在您的 OFFICERS 表中您碰巧没有 MG 在职，则无法知道等级是否存在。因此，当 BG Blowhard 被 MG Marjorie-Banks 解除职务时，您将无法进入新军官的级别——这是一种耻辱，因为他是现代少将的典范。:-) 当陆军将军（五星级将军）出现时会发生什么？
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:
对于不会改变的简单类型，我已经成功地使用了域。例如，在我的一个数据库中，我有一个 yes_no_domain 定义如下：
CREATE DOMAIN yes_no_dom
AS character(1)
DEFAULT 'N'::bpchar
NOT NULL
CONSTRAINT yes_no_dom_check
CHECK ((VALUE = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])));
Share and enjoy.
分享和享受。
回答by PerformanceDBA
Example shown using PostgreSQL, but other RDBMS's have similar syntax
使用 PostgreSQL 显示的示例，但其他 RDBMS 具有类似的语法
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.
那是不正确的。它不是 ISO/IEC/ANSI SQL 要求，因此商业数据库不提供它（您应该提供查找表）。城镇的小端实施各种“附加”，但不执行城镇大端的更严格要求或咕噜声。
We do not have ENUMs as part of a DataType either, that is absurd.
我们也没有将 ENUM 作为 DataType 的一部分，这很荒谬。
The first disadvantage of ENUMs is that is it non-standard and therefore not portable.
ENUM 的第一个缺点是它是非标准的，因此不可移植。
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.
ENUM 的第二大缺点是数据库已关闭。可在数据库（独立于应用程序）上使用的数百个报告工具无法找到它们，因此无法预测名称/含义。如果您有一个普通的标准 SQL 查找表，则该问题就消除了。
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.
第三个是，当你改变值时，你必须改变DDL。在普通标准 SQL 数据库中，您只需在查找表中插入/更新/删除一行。
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.
最后，您无法轻松获得 ENUM 内容的列表；您可以使用查找表。更重要的是，您有一个向量来执行任何 Dimension-Fact 查询，无需从大型 Fact 表和 GROUP BY 中进行选择。
回答by a_horse_with_no_name
I don't see any advantage in using ENUMS.
我认为使用 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.
它们更难维护，并且不提供带有适当外键的常规查找表不允许您做的任何事情。
回答by Lukas Eder
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.
一个小优势可能在于这样一个事实，即在创建 ENUM 时您有一种 UDT。用户定义的类型可以在许多其他数据库对象中正式重用，例如在视图、其他表、其他类型、存储过程（在其他 RDBMS 中）等。
Another advantage is for documentation of the allowed values of a field. Examples:
另一个优点是用于记录字段的允许值。例子：
- A yes/no field
- A male/female field
- A mr/mrs/ms/dr field
- 是/否字段
- 男/女场
- 一个 mr/mrs/ms/dr 字段
Probably a matter of taste. I prefer ENUMs for these kinds of fields, rather than foreign keys to lookup tables for such simple concepts.
大概是口味问题。对于这些类型的字段，我更喜欢使用 ENUM，而不是使用外键来查找此类简单概念的表。
Yet another advantage may be that when you use code generation or ORMs like jOOQin 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
另一个优点可能是，当您在 Java 中使用代码生成或像 jOOQ 这样的ORM时，您可以使用该 ENUM 从中生成 Java 枚举类，而不是加入查找表，或使用 ENUM 文字的 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.
然而，事实上只有少数 RDBMS 支持正式的 ENUM 类型。我只知道 Postgres 和 MySQL。Oracle 或 DB2 没有它。
回答by nate c
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.
存储过程的类型安全：如果无法将参数强制转换为类型，则会引发类型错误。喜欢：
select court_martial('3LT')会自动引发类型错误。自定义联盟顺序：在您的示例中，可以在没有排名 ID 的情况下对军官进行排序。
回答by Scott Marlowe
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.
一般来说，枚举对于变化不大的事情更好，并且它使用的资源略少，因为没有 FK 检查或任何类似在插入时执行的操作等。
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.
使用查找表更优雅或更传统，并且比枚举更容易添加和删除选项。与枚举相比，批量更改值也更容易。
回答by Danubian Sailor
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.
嗯，你没有看到，因为通常开发人员在 Java 等编程语言中使用枚举，而在数据库设计中没有对应的枚举。
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.
在数据库中，此类枚举通常是文本或整数字段，没有限制。数据库枚举不会被翻译成 Java/C#/etc。枚举，因此开发人员认为这没有任何好处。
There are very many very good database features which are rarely used because most ORM tools are too primitive to support them.
有很多非常好的数据库功能很少使用，因为大多数 ORM 工具都太原始而无法支持它们。
