MySQL
ENUM

Почему ENUM?

ENUM предоставляет способ предоставления атрибута для строки. Атрибуты с небольшим количеством нечисловых опций работают лучше всего. Примеры:

reply ENUM('yes', 'no')
gender ENUM('male', 'female', 'other', 'decline-to-state')

Значения: строки:

INSERT ... VALUES ('yes', 'female')
SELECT ... --> yes female

TINYINT в качестве альтернативы

Допустим, у нас есть

type ENUM('fish','mammal','bird')

Альтернативой является

type TINYINT UNSIGNED

плюс

CREATE TABLE AnimalTypes (
    type TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL  COMMENT "('fish','mammal','bird')",
    PRIMARY KEY(type),
    INDEX(name)
) ENGINE=InnoDB

который очень похож на таблицу многих ко многим.

Сравнение и лучше или хуже ENUM:

• (хуже) INSERT: нужно искать type
• (хуже) SELECT: нужно подключиться, чтобы получить строку (ENUM дает вам строку без усилий)
• (лучше) Добавление новых типов: просто вставьте в эту таблицу. С ENUM вам нужно сделать ALTER TABLE.
• (тот же) Любой метод (до 255 значений) принимает только 1 байт.
• (смешанный). Также существует проблема целостности данных: TINYINT допустит недопустимые значения; тогда как ENUM им специальное значение пустой строки (если не включен режим строгого SQL, и в этом случае они отклоняются). Улучшение целостности данных может быть достигнуто с помощью TINYINT , сделав его внешним ключом в справочную таблицу: которая с соответствующими запросами / объединениями, но все еще небольшая стоимость достижения другой таблицы. ( FOREIGN KEYs не являются бесплатными.)

VARCHAR в качестве альтернативы

Допустим, у нас есть

type ENUM('fish','mammal','bird')

Альтернативой является

type VARCHAR(20)  COMENT "fish, bird, etc"

Это совершенно открыто, поскольку новые типы тривиально добавляются.

Сравнение и лучше или хуже ENUM:

• (то же самое) INSERT: просто укажите строку
• (хуже?) В INSERT опечатка останется незамеченной
• (тот же самый) SELECT: возвращается фактическая строка
• (хуже) Больше места потребляется

Добавление новой опции

ALTER TABLE tbl MODIFY COLUMN type ENUM('fish','mammal','bird','insect');

Заметки

• Как и во всех случаях MODIFY COLUMN, вы должны включить NOT NULL и любые другие квалификаторы, которые изначально существовали, иначе они будут потеряны.
• Если вы добавите в конец списка, а список будет содержать не более 256 элементов, ALTER будет выполняться путем простого изменения схемы. То есть не будет длинной копии таблицы. (У старых версий MySQL такой оптимизации нет).

NULL vs NOT NULL

Примеры того, что происходит, когда NULL и «bad-value» хранятся в столбцах с возможностью NULL, а не с NULL. Также показано использование литья в числовое значение с помощью +0 .

CREATE TABLE enum (
    e     ENUM('yes', 'no')   NOT NULL,
    enull ENUM('x', 'y', 'z')     NULL
        );
INSERT INTO enum (e, enull)
    VALUES
        ('yes', 'x'),
        ('no',  'y'),
        (NULL,  NULL),
        ('bad-value', 'bad-value');
Query OK, 4 rows affected, 3 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 3

mysql>SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1048 | Column 'e' cannot be null                  | 
| Warning | 1265 | Data truncated for column 'e' at row 4     |
| Warning | 1265 | Data truncated for column 'enull' at row 4 |
+---------+------+--------------------------------------------+
3 rows in set (0.00 sec)

Что находится в таблице после этих вставок. Это использует «+0» для приведения в числовое значение, чтобы увидеть, что хранится.

mysql>SELECT e, e+0 FROM enum;
+-----+-----+
| e   | e+0 |
+-----+-----+
| yes |   1 |
| no  |   2 |
|     |   0 |  -- NULL
|     |   0 |  -- 'bad-value'
+-----+-----+
4 rows in set (0.00 sec)

mysql>SELECT enull, enull+0 FROM enum;
+-------+---------+
| enull | enull+0 |
+-------+---------+
| x     |       1 |
| y     |       2 |
| NULL  |    NULL |
|       |       0 |  -- 'bad-value'
+-------+---------+
4 rows in set (0.00 sec)