Почему ENUM зло

В MySQL вокруг типа ENUM возникают горячие споры. На первый взгляд кажется, что ENUM - эффективное решение для набора допустимых значений. Например, в ENUM возможно объявить «континент»: всего их семь, и скорее всего, это число в ближайшее время не изменится.

В большинстве случаев есть альтернатива ENUM:

Так почему ENUM - зло?

В случае ENUM данные технически перемещены из места, отведенного под их хранение (поля базы данных) в другое место - метаданные определения столбца.

Есть различие между тем, что тип столбца ограничивает данные (числовой столбец может содержать только целое число) и тем, что в ENUM на самом деле хранится часть данных.

Короче говоря, колонка ENUM нарушает правила нормализации. Это может показаться академическим подходом, но на самом деле есть еще масса других причин.

Вначале вы думаете, что не при каких обстоятельствах не придется менять список значений. Но люди ошибаются. Кто-то выпускает новый вид продукта, ваша компания предлагает еще один способ доставки, а Северная Америка врезается в Азию.

Проблема в том, что изменение списка ENUM подразумевает реструктуризацию всей таблицы с помощью ALTER TABLE, что очень дорого по ресурсам и времени.

Допустим, список содержит «красный», «синий», «черный», а нужно «красный», «синий», «белый». MySQL потребуется пересоздать всю таблицу, чтобы проверить все записи на наличие недопустимого «черного». Еще MySQL тупит и делает то же самое даже при добавлении нового значения в конец списка! Хотя ходят слухи, что этот момент в будущем будет обрабатываться лучше, но вот в приоритете разработки этой задачи есть сомнения.

На маленькой таблице эта операция безболезненна, но на большой потребует значительных ресурсов. А если использовать справочную таблицу вместо ENUM, то изменение этого списка будет также просто, как INSERT, UPDATE и DELETE.

Важно отметить, что при изменении списка ENUM MySQL преобразует записи, которые не включены в новое определение ENUM, в пустую строку. При работе же со справочной таблицей есть большая гибкость в переименовании или удалении значений (подробности ниже).

Нет ни одного правильного способа добавить связанную со списком ENUM дополнительную информацию. Что нужно сделать, если в дополнение к названию континента требуется сохранить его площадь? В справочной таблице такой проблемы нет. ENUM? Забудьте об этом.

Также зачастую требуется флаг, обозначающий прекращение использования данного варианта. Например, когда компания прекращает продажу черных гаджетов, в справочную таблицу достаточно добавить столбец is_discontinued. При этом все еще возможно сохранять информацию о всех старых черных гаджетах! Попробуйте-ка проделать это с ENUM!

Зачастую требуется заполнить выпадающий список возможными значениями из базы данных, например «Выберите цвет».

Если цвет хранятся в справочной таблице, это довольно просто: SELECT * FROM colors .... Достаточно изменить справочную таблицу, чтобы в выпадающем списке были нужные значения.

Злой ENUM: как же извлечь список цветов? Конечно, можно запросить различные значения ENUM из таблицы, но этот список будет не полным - в нем не будет всех возможных значений.

Вы можете запросить данные из INFORMATION_SCHEMA и разобрать полученные результаты, но это слишком сложно. На самом деле, нет ни одного элегантного способа извлечения список значений колонки ENUM чисто на SQL.

Обычные причины использования ENUM: увеличение производительности, иногда - упрощение сложной модели.

Давайте разберемся с производительностью.

Это важно помнить, поскольку разработчики должны стремиться к полностью нормализованной базе данных, и прибегать к денормализации только при серьезных проблемах с производительностью.

Если вы боитесь, что справочная таблица будет тормозить, вначале протестируйте приложение на реальном наборе данных (или довольно реалистичном тестовом). И не надо автоматически предполагать, объединение таблиц будет узким местом. ENUM не всегда заметно быстрее, чем альтернативы.

Второй аргумент за ENUM - это уменьшение количества таблиц и внешних ключей в том плане, что в больших системах сторонний эффект нормализации - сложность понимания человеком схемы данных и усложнение запросов.

Что же, идите вперед и составляйте другое визуальное представление вашей модели данных или ER диаграмму, избавляйтесь от лишней детальности.

Нет простого способа повторно список ENUM в других таблицах. При изменении этого списка потребуется обновить каждую таблицу, использующую его. Со справочной таблицей таких проблем нет.

Предположим, у вас есть ENUM («синий», «черный», «красный»), и вы попытаетесь вставить «фиолетовый». При этом MySQL фактически заменит неправильное значение пустой строкой. Это правильно, но если бы мы использовали справочную таблицу с внешним ключом, то имели более надежный механизм контроля целостности данных.

MySQL хранит значения ENUM как целые числа-ссылки на перечисление. Рассмотрим следующий момент:

CREATE TABLE test (foobar ENUM('0', '1', '2'));
 
mysql> INSERT INTO test VALUES ('1'), (1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM test;
+--------+
| foobar |
+--------+
| 1      |
| 0      |
+--------+
2 rows in set (0.00 sec)

Мы вставили '1' (как строку), и случайно - 1 (как число). MySQL правильно использовал номер как внутреннюю ссылку на первый пункта списка, который на самом деле имеет значение '0'.

Перечисление данных ENUM не входит в стандарт SQL, и встроенная поддержка имеется только у MySQL и нескольких других СУБД: PostgreSQL, MariaDB и Drizzle (последние две - форки MySQL).

Миграция данных в любую другую систему потребует дополнительных шагов для решения проблемы ваших «умных» перечислений. Если этим займетесь вы, то несомненно почувствуете себя менее умным чем раньше, а если вас будет несколько - вы можете им не понравиться.

Как правило, переход на другую систему и так не является простым делом, поэтому не следует усложнять этот процесс.

Хорошие примеры:

• наш список континентов
• обращения (мистер, миссис, мисс)
• карточные масти

Не стоит забывать, что к обращениям может добавиться «доктор», а в карточной игре - появиться джокер.

Что если в дополнение к карточным мастям потребуется хранить их цвет?

Если всего два значения, то следует использовать TINYINT (1) или даже BIT (1) - тип доступен, начиная с MySQL 5.0.3. Например, пол ENUM («мужской», «женский») может быть заменен на is_male BIT (1). То есть, два варианта всегда могут быть выражены логически через отрицание друг друга.

Что касается «менее 20»: да, ENUM способен хранить до 65535 значений. Однако более 20 - громоздко, более 50 - безумие с точки зрения управления и работы.

• никогда не используйте числа как значения списка (см. пункт 7)
• рассмотрите вариант использования строгого режима - вставка «неправильного» значения по крайней мере приведет к ошибке (за исключением использования IGNORE). В противном случае будет лишь предупреждение (warning)

Оригинал статьи