问题描述
如何在不支持枚举的数据库中实现枚举字段? (即SQLite)
How would I implement a enumeration field in a database that doesn't support enumerations? (i.e. SQLite)
这些字段需要使用字段
=?轻松搜索所以使用任何类型的数据序列化是一个坏主意。
The fields need to be easily searchable with "field
= ?" so using any type of data serialization is a bad idea.
推荐答案
使用外键到查找表是我使用的方法。事实上,即使我使用支持ENUM的数据库(例如MySQL),我也使用这个。
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).
为了简单起见,我可以跳过永远存在的 id
,只需使用我在主表中需要的实际值作为查找表的主键。这样你不需要加入来获取这个值。
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.
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)
);
诚然,存储字符串比MySQL执行 ENUM
,但是除非表中有数百万行,否则几乎不重要。
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.
查找表的其他优点是可以添加或删除一个值从列表中,使用简单的 INSERT
或 DELETE
,而使用 ENUM
你必须使用 ALTER TABLE
来重新定义列表。
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.
还尝试查询当前的允许值列表在 ENUM
中,例如在用户界面中填充选择列表。这是一个很大的烦恼!使用查找表,很容易:来自BugStatus
的SELECT状态。
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
中,您不能注释条目;它们只是简单的值。
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.
查找表之外的另一个选项是使用 CHECK
约束(如果数据库支持 - MySQL不支持):
Another option besides a lookup table would be to use CHECK
constraints (provided the database supports them -- MySQL doesn't):
CREATE TABLE Bugs (
bug_id SERIAL PRIMARY KEY,
summary VARCHAR(80),
...
status VARCHAR(20) NOT NULL
CHECK (status IN ('NEW', 'OPEN', 'FIXED'))
);
但是使用 CHECK
与 ENUM
相同的缺点:难以更改不含 ALTER TABLE
的值列表,难以查询列表允许的值,很难注释值。
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.
PS:SQL中的等式比较运算符是单个 =
。双重 ==
在SQL中没有意义。
PS: the equality comparison operator in SQL is a single =
. The double ==
has no meaning in SQL.
这篇关于如何在数据库中处理没有枚举字段的枚举?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!