Глубокое погружение в перечисления MySQL: хорошие, плохие и ужасные операции
Перечисляемые типы, широко известные как Enums, — интересная тема в MySQL, которой часто уделяется ограниченное внимание. Перечисляемые типы позволяют определить набор допустимых строковых значений для столбца, обеспечивая целостность данных и удобочитаемость. Однако использование Enums имеет свои особенности и сложности, особенно при выполнении над ними арифметических или статистических операций.
Эта запись в блоге призвана пролить свет на эту тонкую тему, предоставив вам полное понимание Enums в MySQL, уделяя особое внимание тому, что происходит, когда вы выполняете определенные операции, такие как +0, MAX(), MIN(), и AVG() в столбце Enum.
Отказ от ответственности
Информация, представленная в этом сообщении блога, основана на MySQL на момент публикации. Обратите внимание, что поведение и функции, связанные с Enums или любыми другими функциями MySQL, могут измениться в будущих версиях. Упомянутые примеры и SQL-запросы предназначены для образовательных целей, и их следует использовать с осторожностью в производственной среде. Всегда проверяйте тщательное тестирование перед внедрением любого кода или запроса в действующую систему. Ни автор, ни платформа, на которой размещена эта публикация, не несут ответственности за любые проблемы, возникающие в результате использования представленной здесь информации.
Что такое перечисления?
В MySQL Enum — это строковый объект, значение которого выбрано из списка разрешенных значений, определенных во время создания таблицы.
CREATE TABLE fruits (
id INT PRIMARY KEY,
name ENUM('Apple', 'Banana', 'Cherry')
);Здесь name столбец может принимать только одно из трех значений: «Apple», «Banana» или «Cherry».
Подбрюшье Enum: его числовой индекс
Когда вы определяете Enum, MySQL автоматически присваивает числовой индекс каждому из его элементов, начиная с 1. Итак, в нашем fruitsпримере таблицы «Apple» будет иметь индекс 1, «Banana» — индекс 2 и т. д. Этот числовой индекс имеет решающее значение для понимания того, как Enums ведут себя во время арифметических и статистических операций.
Операция +0: преобразование Enum в целое число
Когда вы выполняете +0операцию над столбцом Enum, MySQL автоматически преобразует значение Enum в его числовой индекс.
SQL-запрос:
SELECT name, name+0 FROM fruits;Таблица результатов:
| name | name+0 |
|--------|--------|
| Apple | 1 |
| Banana | 2 |
| Cherry | 3 |Почему это важно?
Знание числового индекса Enum может быть полезно в различных сценариях:
- Преобразование данных: При переносе данных или их преобразовании для аналитики вам может потребоваться работать с числовыми значениями.
- Оптимизация: Числовые операции обычно выполняются быстрее, чем манипуляции со строками, что дает выигрыш в производительности.
Агрегационные операции: MAX(), MIN(), AVG()
Функции MAX()_MIN()
Когда вы используете функцию MAX()или MIN()в столбце Enum, MySQL учитывает числовой индекс значений Enum для операции.
SQL-запрос:
SELECT MAX(name), MIN(name) FROM fruits;Таблица результатов:
| MAX(name) | MIN(name) |
|-----------|-----------|
| Cherry | Apple |Функция AVG()_
Усредненные операции AVG() над столбцом Enum могут вводить в заблуждение. MySQL сначала преобразует Enum в его числовой индекс, а затем вычисляет среднее значение. Это может не дать вам значимого результата, поскольку числовой индекс не представляет семантическое значение данных.
SQL-запрос:
SELECT AVG(name) FROM fruits;Таблица результатов:
| AVG(name) |
|-----------|
| 2 |Непонятное поведение: пример размеров футболок
Фон
Чтобы изучить некоторые из наиболее сложных аспектов Enums, давайте рассмотрим таблицу с именем tshirt_sizes. В этой таблице есть столбец size, который включает такие значения, как «x-большой», «большой», «средний», «маленький» и «x-маленький».
SQL-запрос:
CREATE TABLE tshirt_sizes (
id INT PRIMARY KEY,
size ENUM('x-large', 'large', 'medium', 'small', 'x-small')
);Причудливость ORDER BY: смысловой обход
На первый взгляд может показаться логичным ожидать, что ORDER BYзапрос к этому sizeстолбцу будет сортировать размеры от наименьшего к наибольшему или наоборот. Однако это далеко не то, что происходит на самом деле.
SQL-запрос:
SELECT size FROM tshirt_sizes ORDER BY size ASC;Таблица результатов:
| size |
|---------|
| large |
| medium |
| small |
| x-large |
| x-small |Демистификация с помощью +0: числовая реальность
Чтобы раскрыть метод, стоящий за этим безумием, мы можем добавить значение +0к столбцу Enum в нашем SQL-запросе. Это действие преобразует значения Enum в соответствующие внутренние числовые индексы.
SQL-запрос:
SELECT size, size+0 FROM tshirt_sizes ORDER BY size+0 ASC;Таблица результатов:
| size | size+0 |
|---------|--------|
| x-large | 1 |
| large | 2 |
| medium | 3 |
| small | 4 |
| x-small | 5 |Странность MAX(): лексическое превосходит числовое
Когда вы обращаетесь MAX()к этому столбцу, результат столь же озадачивает. В отличие от других операций, MAX()не использует внутренний числовой индекс. Вместо этого он возвращается к лексическому (алфавитному) упорядочению.
SQL-запрос:
SELECT MAX(size) FROM tshirt_sizes;Таблица результатов:
| MAX(size) |
|-----------|
| x-small |Краткое содержание: История о раздвоении личностей
Перечисления в MySQL подобны Янусу, двуликому римскому богу; у них две личности. Одно из них — строковое значение, которое вы видите, а другое — внутренний числовой индекс, который видит MySQL. Понимание этой двойственности имеет решающее значение для эффективного использования Enums и предотвращения ошибок.
Предостережения и рекомендации
- Читабельность и гибкость: Перечисления читабельны, но не очень гибки. Добавление нового значения требует изменения схемы таблицы.
- Целостность данных: Перечисления обеспечивают целостность данных, но могут иметь ограничения.
- Производительность: Операции перечисления обычно выполняются быстрее, но могут привести к неожиданным результатам из-за их числовой индексации.
Заключение
Перечисления в MySQL предлагают удобный способ обеспечить целостность и читаемость данных. Однако когда дело доходит до таких операций, как +0, MAX(), MIN()и AVG(), понимание основного числового индекса имеет решающее значение. Это может быть как преимуществом, так и ловушкой, в зависимости от вашего конкретного варианта использования. Как и в случае с любой другой функцией, для эффективного использования Enums требуется вдумчивое рассмотрение.
