如何在数据库中处理没有枚举字段的枚举？

How would I implement a enumeration field in a database that doesn't support enumerations? (i.e. SQLite)

如何在不支持枚举的数据库中实现枚举字段? (即SQLite)

The fields need to be easily searchable with "field = ?" so using any type of data serialization is a bad idea.

这些字段需要使用“field =?”轻松搜索所以使用任何类型的数据序列化是一个坏主意。

5 个解决方案

#1

Using a foreign key to a lookup table is the approach I use. In fact, I use this even when I do use a database that supports ENUM (e.g. MySQL).

使用外键到查找表是我使用的方法。实际上,即使我使用支持ENUM的数据库(例如MySQL),我也会使用它。

For simplicity, I may skip the ever-present "id" for the lookup table, and just use the actual value I need in my main table as the primary key of the lookup table. That way you don't need to do a join to get the value.

为简单起见,我可能会跳过查找表中永远存在的“id”,只使用我主表中需要的实际值作为查找表的主键。这样您就不需要进行连接来获取值。

CREATE TABLE BugStatus (
  status            VARCHAR(20) PRIMARY KEY
);

INSERT INTO BugStatus (status) VALUES ('NEW'), ('OPEN'), ('FIXED');

CREATE TABLE Bugs (
  bug_id            SERIAL PRIMARY KEY,
  summary           VARCHAR(80),
  ...
  status            VARCHAR(20) NOT NULL DEFAULT 'NEW',
  FOREIGN KEY (status) REFERENCES BugStatus(status)
);

Admittedly, storing strings takes more space than MySQL's implementation of ENUM, but unless the table in question has millions of rows, it hardly matters.

不可否认,存储字符串比MySQL的ENUM实现占用更多空间,但除非有问题的表有数百万行,否则它几乎不重要。

Other advantages of the lookup table are that you can add or remove a value from the list with a simple INSERT or DELETE, whereas with ENUM you have to use ALTER TABLE to redefine the list.

查找表的其他优点是您可以使用简单的INSERT或DELETE从列表中添加或删除值,而使用ENUM,您必须使用ALTER TABLE重新定义列表。

Also try querying the current list of permitted values in an ENUM, for instance to populate a pick-list in your user interface. It's a major annoyance! With a lookup table, it's easy: SELECT status from BugStatus.

还可以尝试查询ENUM中当前允许值的列表,例如填充用户界面中的选择列表。这是一个很大的烦恼!使用查找表,很容易:从BugStatus中选择SELECT状态。

Also you can add other attribute columns to the lookup table if you need to (e.g. to mark choices available only to administrators). In an ENUM, you can't annotate the entries; they're just simple values.

如果需要,您还可以将其他属性列添加到查找表中(例如,标记仅供管理员使用的选项)。在ENUM中,您无法注释条目;他们只是简单的价值观。

Another option besides a lookup table would be to use CHECK constraints (provided the database supports them -- MySQL doesn't):

除了查找表之外的另一个选择是使用CHECK约束(假设数据库支持它们 - MySQL不支持):

CREATE TABLE Bugs (
  bug_id            SERIAL PRIMARY KEY,
  summary           VARCHAR(80),
  ...
  status            VARCHAR(20) NOT NULL
    CHECK (status IN ('NEW', 'OPEN', 'FIXED'))
);

But this use of a CHECK constraint suffers from the same disadvantages as the ENUM: hard to change the list of values without ALTER TABLE, hard to query the list of permitted values, hard to annotate values.

但是这种使用CHECK约束的缺点与ENUM相同:如果没有ALTER TABLE,很难更改值列表,很难查询允许值列表,难以注释值。

PS: the equality comparison operator in SQL is a single =. The double == has no meaning in SQL.

PS:SQL中的等式比较运算符是单个=。双= =在SQL中没有意义。

#2

To restrict the possible values I would use a foreign key to a table that holds the enumeration items.

为了限制可能的值,我将使用外键来保存枚举项。

If you don't want to JOIN to do your searches then make the key a varchar if JOINS are not a problem then make the key an INT and don't join unless you need to search on that field.

如果您不想JOIN进行搜索,那么如果JOINS不是问题,则将密钥设为varchar,然后将密钥设为INT并且不加入,除非您需要搜索该字段。

Note that putting your enumerations in the DB precludes compile time checking of the values in your code (unless you duplicate the enumeration in code.) I have found this to be a large down side.

请注意,将枚举放在数据库中会阻止编译时检查代码中的值(除非您在代码中复制枚举。)我发现这是一个很大的缺点。

#3

You basically have two options :

你基本上有两个选择:

use an integer field

使用整数字段
use a varchar field

使用varchar字段

I would personally advocate the use of varchars, because you won't break anything if you change your enum + the fields are human-readable, but ints have some pro aswell, namely performance (the size of the data is an obvious example)

我个人会提倡使用varchars,因为如果你改变你的enum +你不会破坏任何东西+字段是人类可读的,但是int有一些pro,即性能(数据的大小是一个明显的例子)

#4

This is what I did recently

这就是我最近所做的

In my hibernate mapped POJO- I kept the type of the member as String and it is VARCHAR in the database.

在我的hibernate映射的POJO中 - 我将成员的类型保留为String,并且它在数据库中是VARCHAR。

The setter for this takes an enum There is another setter which takes String- but this is private (or you can map the field directly- if that's what you prefer.)

这个setter用于枚举有另一个setter,它接受String-但这是私有的(或者你可以直接映射字段 - 如果这是你喜欢的。)

Now the fact I am using String is encapsulated from all. For the rest of the application- my domain objects use enum. And as far as the database is concerned- I am using String.

现在我使用String的事实是从所有人封装的。对于应用程序的其余部分 - 我的域对象使用枚举。就数据库而言 - 我正在使用String。

If I missed your question- I apologize.

如果我错过了你的问题 - 我道歉。

#5

I would use a varchar. Is this not an option for you?

我会使用varchar。这不适合你吗?

#1