如何在H2数据库中替换枚举类型？

Question

MySQL方言：

CREATE TABLE My_Table ( my_column enum ('first', 'second', ... 'last'));

H2方言：

CREATE TABLE My_Table ( my_column ? ('first', 'second', ... 'last'));

H2中与MySQL的enum类型等效的类型是什么？

- Igor Kostenko

Answer 1

我不确定这是否符合您的要求，但您可以使用检查约束来实现：

CREATE TABLE My_Table(my_column varchar(255) 
    check (my_column in ('first', 'second', 'last')));

-- fails:
insert into My_Table values('x');

-- ok:
insert into My_Table values('first');

这将适用于H2、Apache Derby、PostgreSQL、HSQLDB甚至SQLite。我没有测试其它数据库。

- Thomas Mueller

---

顺便说一下，使用枚举和引用表可能更有效率。 - Thomas Mueller

问题在于这种解决方案没有办法枚举可能的值（尽管他可能不需要它）。 - Viruzzo

你的意思是你不知道可能的值是什么吗？那你怎么能使用枚举呢？你的例子正确吗？ - Thomas Mueller

我的意思是，除了查看约束条件外，你无法以任何方式获取接受值的列表。 - Viruzzo

Answer 2

目前没有最好的解决方案，不过无论如何，enum 都不是一个好的选择，最好使用一个参考表。

- Viruzzo

---

此处批评 "enum 不是任何情况下的好解决方案" 却没有给出理由或参考资料，应该扣分 1 分。 - Raphael

enum 类型可以因许多原因而被要求使用。以下是其中的两个例子: 1) 将测试 H2 数据库结构与现有的生产数据库匹配，该数据库不能更改并且需要此类型; 2) 当 enum 字段是强类型的，并且其选项不容易更改时，可以消除连接表和处理引用表中的插入/更新的负担。 - Uyric

Answer 3

看起来H2有枚举类型：http://www.h2database.com/html/datatypes.html#enum_type

所以应该可以使用完全相同的语法。（我不知道语义上是否完全匹配。）

- nafg

---

现在已经有支持了，截至于2017-04-23发布的1.4.195版本（详情见http://www.h2database.com/html/changelog.html），但在提问时还没有支持。也许你想在回答中澄清这一点。 - Cedric Reichenbach

Answer 4

升级h2 jar

H2 maven仓库: https://mvnrepository.com/artifact/com.h2database/h2

jar版本:

1.4.196 中央仓库 (2017年6月) - 支持枚举类型 (已测试)

1.4.192 中央仓库 (2016年5月) - 不支持枚举类型 (也已测试)

- blueberry0xff

Answer 5

我遇到了这个问题，通过创建一个单独的表和外键约束来解决它。

CREATE TABLE My_Enum_Table (
    my_column varchar(255) UNIQUE
);

INSERT INTO My_Enum_Table (my_column)
VALUES
    ('First'),
    ('Second'),
    ...
    ('Last');

CREATE TABLE My_Table (
   my_column varchar(255),
   FOREIGN KEY (my_column) REFERENCES My_Enum_Table (my_column)
);

这样，当你尝试向My_Table插入数据时，它将进行外键检查以确保你要插入的值在My_Enum_Table中。

不过这里有一些权衡：

PROS

- 你仍然可以像处理ENUM一样与之交互。 - 你还可以在不必更改表定义的情况下添加另一个值。

CONS

- 这可能比ENUM慢，因为它需要进行表查找。但实际上，由于该表应该只有相对较少的行，所以这可能是相当可忽略的。向My_Table.my_column添加索引可能有所帮助。 - 这样可以避免与参考表连接，但从数据库的角度来看，基本上具有相同的复杂性。虽然这可能不是什么大问题，除非你担心用另一个表混淆你的数据库。 - 这还要求你使用支持外键的引擎，例如INNODB。我不确定这是否真的是一个CONS，但我想对于有特殊需求的人来说可能是这样。

- thesquaregroot

Answer 6

现在在H2上已经支持这个功能，您可以创建一个带有枚举列的表，甚至在需要时还可以修改枚举。

CREATE TABLE example (
    "example" TEXT,
    "state" ENUM (
         'CREATED',
         'DELETED'
     )
);

ALTER TABLE example ALTER COLUMN "state" ENUM (
    'CREATED',
    'USED',
    'DELETED'
);

- loicmathieu