Как обработать перечисления без перечислимых полей в базе данных?
Я решил эту проблему!
Вам нужно перейти в репозиторий maven, чтобы удалить пакет tomcat-embed-core.jar, а затем обновить репозиторий Maven.
5 ответов
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).
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)
);
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.
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.
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.
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.
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'))
);
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: the equality comparison operator in SQL is a single =. The double == has no meaning in SQL.
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.
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.
You basically have two options :
use an integer field
use a varchar field
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)
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.
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.)
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.
If I missed your question- I apologize.
Я бы использовал varchar. Разве это не вариант для вас?