Добавьте автоматически генерируемые данные в свои таблицы

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

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

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

Что такое столбец, сгенерированный 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 (generated_column_expression) указывает, что столбец, который вы добавляете или обновляете в таблицу, является сгенерированным столбцом. 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?

Как объяснялось ранее, вы можете определить сгенерированный столбец как VIRTUAL или STORED. Теперь давайте подробнее рассмотрим эти два типа.

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

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

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

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

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

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

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

Плюсы

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

Минусы

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

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

Плюсы

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

Минусы

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

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

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

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

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

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

1. Использование сгенерированного столбца для объединения столбцов по соображениям согласованности.

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

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)

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

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

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

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

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

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

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

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

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

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

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

Сгенерированные столбцы и триггеры

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

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

Заключение

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

Спасибо за прочтение! Я надеюсь, что вы нашли эту статью полезной. Не стесняйтесь оставлять любые вопросы, комментарии или предложения.

Первоначально опубликовано на https://arctype.com 28 июня 2022 г.