哪些数据库系统支持枚举数据类型，哪些不支持?

Following up this question: "Database enums - pros and cons", I'd like to know which database systems support enumeration data types, and a bit of detail on how they do it (e.g. what is stored internally, what are the limits, query syntax implications, indexing implications, ...).

在回答这个问题之后:“数据库枚举-利弊”，我想知道哪个数据库系统支持枚举数据类型，以及它们如何进行索引的一些细节(例如，内部存储的内容、限制、查询语法含义、含义……)。

Discussion of use cases or the pros and cons should take place in the other questions.

关于用例的讨论或赞成和反对应该在其他问题中进行。

6 个解决方案

#1

I know that MySQL does support ENUM:

我知道MySQL支持ENUM:

the data type is implemented as integer value with associated strings
数据类型实现为具有关联字符串的整数值
you can have a maximum of 65.535 elements for a single enumeration
对于单个枚举，最多可以有65.535个元素
each string has a numerical equivalent, counting from 1, in the order of definition
每个字符串都有一个数值，从定义的顺序从1开始。
the numerical value of the field is accessible via "SELECT enum_col+0"
可以通过“选择enum_col+0”访问字段的数值
in non-strict SQL mode, assigning not-in-list values does not necessarily result in an error, but rather a special error value is assigned instead, having the numerical value 0
在非严格的SQL模式中，分配非列表值不一定会导致错误，而是分配一个特殊的错误值，数值为0
sorting occurs in numerical order (e.g. order of definition), not in alphabetical order of the string equivalents
排序是按照数字顺序(例如定义顺序)进行的，而不是按照字符串等价物的字母顺序进行的
assignment either works via the value string or the index number
赋值可以通过值字符串或索引号进行工作。
this: ENUM('0','1','2') should be avoided, because '0' would have integer value 1
应该避免这种情况:ENUM('0'、'1'、'2')，因为'0'将具有整数值1

#2

PostgreSQL supports ENUM from 8.3 onwards. For older versions, you can use :

PostgreSQL从8.3开始支持ENUM。对于旧版本，您可以使用:

You can simulate an ENUM by doing something like this :

您可以通过以下操作来模拟ENUM:

CREATE TABLE persons (
  person_id int not null primary key,
  favourite_colour varchar(255) NOT NULL,
  CHECK (favourite_colour IN ('red', 'blue', 'yellow', 'purple'))
);

You could also have :

你也可以:

CREATE TABLE colours (
  colour_id int not null primary key,
  colour varchar(255) not null
)
CREATE TABLE persons (
  person_id int not null primary key,
  favourite_colour_id integer NOT NULL references colours(colour_id),
);

which would have you add a join when you get to know the favorite colour, but has the advantage that you can add colours simply by adding an entry to the colour table, and not that you would not need to change the schema each time. You also could add attribute to the colour, like the HTML code, or the RVB values.

当你知道最喜欢的颜色时，你会添加一个连接，但是它的优点是你可以通过添加一个条目到颜色表来添加颜色，而不是每次都不需要改变模式。您还可以向颜色添加属性，如HTML代码或RVB值。

You also could create your own type which does an enum, but I don't think it would be any more faster than the varchar and the CHECK.

您也可以创建自己的类型来执行enum，但是我不认为它会比varchar和CHECK更快。

#3

Oracle doesn't support ENUM at all.

Oracle根本不支持ENUM。

#4

AFAIK, neither IBM DB2 nor IBM Informix Dynamic Server support ENUM types.

AFAIK，既不支持IBM DB2，也不支持IBM Informix Dynamic Server ENUM类型。

#5

Unlike what mat said, PostgreSQL does support ENUM (since version 8.3, the last one):

与mat所说的不同，PostgreSQL确实支持ENUM(因为8.3版本是最后一个版本):

essais=> CREATE TYPE rcount AS ENUM (
essais(>   'one',
essais(>   'two',
essais(>   'three'
essais(> );
CREATE TYPE
essais=> 
essais=> CREATE TABLE dummy (id SERIAL, num rcount);
NOTICE:  CREATE TABLE will create implicit sequence "dummy_id_seq" for serial column "dummy.id"
CREATE TABLE
essais=> INSERT INTO dummy (num) VALUES ('one');
INSERT 0 1
essais=> INSERT INTO dummy (num) VALUES ('three');
INSERT 0 1
essais=> INSERT INTO dummy (num) VALUES ('four');
ERROR:  invalid input value for enum rcount: "four"
essais=> 
essais=> SELECT * FROM dummy WHERE num='three';
 id |  num  
----+-------
  2 | three
  4 | three

There are functions which work specifically on enums.

有一些函数专门作用于枚举。

Indexing works fine on enum types.

索引在枚举类型上很好。

According to the manual, implementation is as follows:

根据手册，实施情况如下:

An enum value occupies four bytes on disk. The length of an enum value's textual label is limited by the NAMEDATALEN setting compiled into PostgreSQL; in standard builds this means at most 63 bytes.

枚举值在磁盘上占用四个字节。enum值的文本标签的长度受到编译为PostgreSQL的NAMEDATALEN设置的限制;在标准构建中，这意味着最多63字节。

Enum labels are case sensitive, so 'happy' is not the same as 'HAPPY'. Spaces in the labels are significant, too.

Enum标签是区分大小写的，所以“happy”与“happy”并不相同。标签中的空格也很重要。

#6

MSSQL doesn't support ENUM.

该软件不支持枚举。

When you use Entity Framework 5, you can use enums (look at: Enumeration Support in Entity Framework and EF5 Enum Types Walkthrough), but even then the values are stored as int in the database.

当您使用实体框架5时，您可以使用枚举(查看:实体框架中的枚举支持和EF5 Enum类型演练)，但是即使这样，这些值仍然以int形式存储在数据库中。

#1