如何处理数据库中没有枚举字段的枚举? [英] How to handle enumerations without enum fields in a database?

问题描述

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

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

需要使用field = ?"轻松搜索字段所以使用任何类型的数据序列化都是一个坏主意.

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 中查询当前的允许值列表，例如在您的用户界面中填充一个选择列表.这是一个主要的烦恼！使用查找表，很容易:SELECT status from BugStatus.

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 直到 8.0.16 版本才支持 CHECK):

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

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.

这篇关于如何处理数据库中没有枚举字段的枚举?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！