SQL-Ex blog

Что такое генерируемый столбец в MySQL?

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

Другими словами, вы можете думать о генерируемом столбце как о неком представлении, которое ограничено столбцами. Заметим, что генерируемые столбцы отличаются от триггеров SQL, и вы можете определить их только с помощью операторов CREATE TABLE или ALTER TABLE, используя следующий синтаксис:

generate_column_name column_type [GENERATED ALWAYS] AS (generation_expression)

[VIRTUAL | STORED] [NOT NULL | NULL]

[UNIQUE [KEY]] [[PRIMARY] KEY][COMMENT 'string']

Предложение AS (выражение для генерируемого столбца) указывает, что столбец, который вы добавляете или обновляете, является генерируемым столбцом. generation_expression определяет выражение, которое MySQL будет использовать для вычисления значений столбца, и оно не может ссылаться на другой генерируемый столбец или на что-либо другое, кроме столбцов в текущей таблице. Заметим также, что выражение генерации может включать только неизменяемые функции. Например, вы не можете использовать в генерируемом столбце функцию, которая возвращает текущую дату, поскольку это изменяемая функция.

Вы можете также предварить AS ключевыми словами GENERATED ALWAYS, чтобы выделить генерируемую природу столбца более явно, но это не является обязательным. Затем вы можете указать, какого типа является генерируемый столбец - VIRTUAL или STORED. Мы изучим разницу между этими двумя типами в следующей части. По умолчанию, если это явно не указано в запросе, MySQL помечает генерируемый столбец как VIRTUAL.

Теперь давайте посмотрим на синтаксис генерируемого столбца в действии в запросе CREATE TABLE:

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY KEY,

email VARCHAR(100) NOT NULL,

first_name VARCHAR(60) NOT NULL,

last_name VARCHAR(60) NOT NULL,

full_name varchar(120) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name))

);

В этом примере столбец full_name будет автоматически выполнять конкатенацию столбцов first_name и last_name.

Какие типы генерируемых столбцов имеются в MySQL?

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

Сравнение виртуальных и сохраняемых генерируемых столбцов

Давайте оценим теперь за и против виртуальных и сохраняемых генерируемых столбцов.

Виртуальные генерируемые столбцы

За

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


• Они не требуют места на диске.


• Запросы INSERT и UPDATE не вносят дополнительную нагрузку, поскольку MySQL не требуется их генерировать.

Против

• MySQL должен вычислять их при чтении таблицы, что замедляет выполнение запросов SELECT, содержащих эти столбцы.

Сохраняемые генерируемые столбцы

За

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

Против

• При добавлении в новую таблицу, MySQL должен перестраивать всю таблицу.


• INSERT или UPDATE вызывают дополнительную нагрузку, поскольку MySQL должен генерировать значения.


• Они требуют места на диске.

Заметьте также, что вы можете смешивать столбцы VIRTUAL и STORED в пределах одной таблицы, и они оба поддерживают индексы и вторичные индексы MySQL. Но, как объясняется в официальной документации, вторичные индексы на виртуальных столбцах потребляют меньше места и памяти по сравнению с хранимыми генерируемым столбцами. Поэтому виртуальные генерируемые столбцы более эффективны в сочетании с вторичными индексами.

Зачем использовать генерируемые столбцы?

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

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


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


• Они позволяют определять функциональные индексы: MySQL реализует функциональные индексы как скрытые виртуальные генерируемые столбцы. Другими словами, генерируемые столбцы дают вам возможность определять эффективные и расширенные индексы, включающие функции MySQL.

Генерируемые столбцы MySQL в действии на реальных примерах

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

Использование генерируемого столбца для конкатенации столбцов с целью согласования

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

first_name [first_character_middle_name.] last_name [(jersey_number)]

Как видно, вы можете легко сгенерировать это поле данных агрегацией четырех столбцов в следующем генерируемом столбце:

string_identifier VARCHAR(255) GENERATED ALWAYS AS

(CONCAT(first_name, ' ', IF(middle_name IS NULL, '', CONCAT(LEFT(middle_name, 1), '. ')), last_name, IF(jersey_number IS NULL, '', CONCAT(' (', jersey_number, ')'))))

STORED

В результате получим:

Cristiano Ronaldo (7)

Lionel A. Messi (10)

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

Использование генерируемого столбца для автоматического создания хешированных идентификаторов

Обычно вы используете ID ресурсов в URL вашего сайта или REST API для извлечения необходимых данных. Но выставление напоказ ваших ID может спровоцировать проблемы безопасности. Это особенно справедливо, когда вы формируете ID с помощью автоинкремента, что легко предсказать и упростить парсинг или атаки ботов.

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

public_id VARCHAR(40) GENERATED ALWAYS AS  SHA1(CONCAT("PLAYER", id)) VIRTUAL

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

Определение генерируемого столбца для упрощения фильтрации данных

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

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

filter_string VARCHAR(255) GENERATED ALWAYS AS

(CONCAT(first_name, ' ', last_name, IF(birthdate IS NULL, '', CONCAT(' ', DATE_FORMAT(birthdate, "%m-%d-%Y"))))

STORED

Такой столбец производит:

LeBron James 12-30-1984

Stephen Curry 03-14-1988

Такие данные будут полезны для фильтрации и по дню рождения игрока в формате US.

Генерируемые столбцы по сравнению с триггерами

Как объяснялось ранее, вы можете использовать генерируемые столбцы только в пределах таблицы. Также они могут включать только неизменяемые функции, а MySQL генерирует их значения в ответ на запрос INSERT или UPDATE. С другой стороны, триггер является хранимой программой, которую MySQL автоматически выполняет, когда наступает событие INSERT, UPDATE или DELETE, связанное с конкретной таблицей. Другими словами, триггеры могут задействовать несколько таблиц и все функции MySQL. Это делает их более общим решением по сравнению с генерируемыми столбцами. В то же время триггеры MySQL по существу являются более сложными в использовании и определении, а также более медленными по сравнению с генерируемыми столбцами.

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