Статьи

Когда вы должны и не должны использовать тип данных ENUM

Октябрь 11, 2018

enum_data_type.png

ENUM — это новый перечислимый тип данных, представленный в CUBRID 9.0 . Как и во всех языках программирования, ENUMтип — это тип данных, состоящий из набора статических упорядоченных значений. Пользователи могут определять числовые и строковые значения для  ENUM столбцов.

Работа с типами ENUM

Создание ENUMстолбца выполняется путем указания статического списка возможных значений:

CREATE TABLE person(
    name VARCHAR(255),
    gender ENUM('Male', 'Female')
);

CUBRID понимает ENUMтип как упорядоченный набор констант, который в приведенном выше примере представляет собой набор {NULL: NULL, 1: 'Male', 2: 'Female”}. Для того, чтобы присвоить значение в genderстолбец, пользователи могут использовать либо индекс стоимости ( {NULL, 1, 2}) или фактического постоянная (литерал {NULL},  {'Male'},  {'Female'}). CUBRID ограничивает значения, которые могут быть назначены этому столбцу, только значениями из этого набора +  NULL. Кроме того,  ENUM столбец чувствителен к регистру , то есть он вызовет ошибку, если вы попытаетесь ввести  'female' строчные буквы Также допускается пустая строка, если она определена как один из элементов  ENUM столбца. В наших примерах это не разрешено.

csql> INSERT INTO person(name, gender) VALUES('Eugene', 'Male');
 
1 row affected.
1 command(s) successfully processed.
csql> INSERT INTO person(name, gender) VALUES('Anne', 2);
 
1 row affected.
1 command(s) successfully processed.
csql> SELECT * FROM person;
 
=== <Result of SELECT Command IN Line 1> ===
 
  name                  gender
============================================
  'Anne'                'Female'
  'Eugene'              'Male'
 
 
2 rows selected.

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

csql> INSERT INTO person(name, gender) VALUES('John', 'N/A');
 
IN line 1, COLUMN 44,
ERROR: before ' ); '
Cannot coerce 'N/A' TO type enum.
0 command(s) successfully processed.
 
csql> INSERT INTO person(name, gender) VALUES('John', 4);
 
IN line 1, COLUMN 45,
ERROR: before ' ); '
Cannot coerce 4 TO type enum.
0 command(s) successfully processed.
 
csql> INSERT INTO person(name, gender) VALUES('John', '');
 
IN line 1, COLUMN 44,
ERROR: before ' ); '
Cannot coerce '' TO type enum.
0 command(s) successfully processed.

Почему вы должны использовать тип ENUM

Есть три важные причины, по которым вы должны рассмотреть использование ENUMтипа:

  • Уменьшите место для хранения.
  • Уменьшите сложность соединения.
  • Создать дешевые значения ограничений.

Пространство для хранения

CUBRID использует только 1 байт на кортеж, если определено 255 ENUMэлементов или менее или 2 байта для 256 ~ 65535 элементов. Это потому, что вместо того, чтобы хранить константу литерала значения, CUBRID сохраняет индекс в упорядоченном наборе этого значения. Для очень больших таблиц это может значительно сэкономить место на диске.

Возьмем, к примеру, таблицу с 1 000 000 000 записей, ENUMстолбец которой определен как ( 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'). Если вы используете VARCHARтип вместо ENUMтипа для хранения этих значений, столбцу потребуется от 5 до 9 ГБ дискового пространства. Используя ENUMтип, вы можете уменьшить требуемое пространство до 2 байтов на кортеж, добавив всего до 2 ГБ.

Уменьшить сложность соединения

ПРИСОЕДИНЯЙТЕСЬ

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

CREATE TABLE days_of_week(
    id SHORT PRIMARY KEY,
    name VARCHAR(9)
);
CREATE TABLE opening_hours(
    week_day SHORT,
    opening_time TIME,
    closing_time TIME,
    FOREIGN KEY fk_dow (week_day) REFERENCES days_of_week(id)
);

Затем, когда вы хотите отобразить название дня недели, вы должны выполнить запрос вроде:

SELECT d.name day_name, o.opening_time, o.closing_time
FROM days_of_week d, opening_hours o
WHERE d.id = o.week_day
ORDER BY d.id;
 
=== <Result of SELECT Command IN Line 4> ===
  day_name              opening_time  closing_time
==================================================
  'Monday'              09:00:00 AM   06:00:00 PM
  'Tuesday'             09:00:00 AM   06:00:00 PM
  'Wednesday'           09:00:00 AM   06:00:00 PM
  ...

ENUM путь

Вы можете добиться того же эффекта, используя ENUMстолбец:

CREATE TABLE opening_hours(
    week_day ENUM ('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'),
    opening_time TIME,
    closing_time TIME
);

И не требуется JOIN, чтобы выбрать часы работы:

SELECT week_day, opening_time, closing_time
FROM opening_hours
ORDER BY week_day;
=== <Result of SELECT Command IN Line 1> ===
 
  week_day              opening_time  closing_time
==================================================
  'Monday'              09:00:00 AM   06:00:00 PM
  'Tuesday'             09:00:00 AM   06:00:00 PM
  'Wednesday'           09:00:00 AM   06:00:00 PM
  ...

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

Ценностные ограничения

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

Почему / Когда НЕ следует использовать тип ENUM

Хотя ENUMэто отличная функция, есть случаи, когда вам лучше ее не использовать. Например:

  • Когда ENUMтип не зафиксирован
  • Когда ENUMтип имеет длинный список значений
  • Когда ваше приложение не знает список ENUMзначений
  • ENUM тип не может быть использован повторно
  • Мобильность является проблемой

Когда тип ENUM не фиксирован

Если вы не уверены, что ENUMтип содержит все возможные значения для этого столбца, вам следует рассмотреть возможность использования отношения один ко многим. Единственный способ ENUMизменить столбец для обработки большего количества значений — использовать ALTERоператор. Это очень дорогая операция в любой СУБД и требует прав администратора. Кроме того, ALTERоператоры являются операциями обслуживания и должны, насколько это возможно, выполняться в автономном режиме.

Когда тип ENUM имеет длинный список значений

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

Когда ваше приложение не знает список значений ENUM

Есть только два способа получить список значений, которые вы определили для ENUMтипа:

  • парсинг вывода SHOW CREATE TABLEоператора:

    csql> SHOW CREATE TABLE opening_hours;
 
=== <Result of SELECT Command IN Line 1> ===
 
  TABLE                 CREATE TABLE
============================================
'opening_hours'       'CREATE TABLE [opening_hours] ([week_day] ENUM('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'), [opening_time] TIME, [closing_time] TIME)

  • выбор информации из системных таблиц CUBRID:

    csql> SELECT d.enumeration
FROM _db_domain d, _db_attribute a
WHERE a.attr_name = 'week_day'
AND a.class_of.class_name = 'opening_hours'
AND d IN a.domains;
 
=== <Result of SELECT Command IN Line 1> ===
enumeration
======================
{'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'}

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

Тип ENUM не может быть использован повторно

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

Мобильность является проблемой

ENUMТип поддерживается только несколько РСУБД и каждый из них имеет свое собственное представление о том , как ENUMтип должен работать. Ниже приведен список некоторых заметных различий между CUBRID, MySQL и PostgreSQL:

CUBRID PostgreSQL MySQL
Вставка вне диапазона значений Выдает ошибку Выдает ошибку Вставляет специальный индекс значения 0
По сравнению с символами литерала Сравнить как строки Сравнить как ENUMэлементы Сравнить как строки
Сравнение со значениями вне ENUMдомена Сравнить как строки Выдает ошибку Сравнить как строки

Эти тонкие различия, скорее всего, сломают ваше приложение интересным и трудным для понимания способом. Если вы, например, переходите с PostgreSQL на CUBRID и ожидаете, что сравнения с символами литералов будут выполняться как ENUMсравнения, вам будет сложно понять, почему ваш запрос возвращает странные результаты.

56
Share: