SQLinfo.ru - Все о MySQL

Старые и новые способы эмуляции ограничения CHECK и DOMAIN

Дата: 12.03.2019

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

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

  • триггеры
  • представления
  • генерируемые колонки (добавлены в MySQL 5.7)

Сказанное выше справедливо и для другой возможности SQL - DOMAIN. В двух словах, это пользовательский тип данных, состоящий из базового типа (INT, CHAR, …), значения по умолчанию и ограничений на допустимые значения - последнее является стандартным ограничением CHECK. Если мы сможем эмулировать ограничения CHECK, то сможем реализовать некое подобие функциональности DOMAIN.

В качестве примера рассмотрим таблицу автомобилей с одной колонкой, в которой предполагается хранить регистрационные номера машин во Франции. Эти номера имеют определенный синтаксис: две буквы, тире, три цифры, тире, две буквы. Конечно, в качестве примера можно было рассмотреть и хранение UUID, телефонных номеров, IP-адресов, почтовых индексов... чего угодно с определенным синтаксисом.

create table cars (number char(9));
insert into cars values('AS-229-ZT'); # Valid

Мы хотим, чтобы MySQL предотвращал ввод недопустимых значений, таких как:

mysql> insert into cars values('AS-2X9-ZT'); # "X" in digit zone!
Query OK, 1 row affected (0,01 sec) # Ouch! Should not be accepted!

Использование триггера

delimiter $
create trigger cars_number_validate before insert on cars
for each row
begin
    if new.number not rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
    then
        signal sqlstate '45000' set message_text = 'bad number';
    end if;
end$
delimiter ;

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

mysql> delete from cars;
mysql> insert into cars values('AS-2X9-ZT'); # "X" in digit zone!
ERROR 1644 (45000): bad number
mysql> insert into cars values('AS-229-ZT'); # Valid
Query OK, 1 row affected (0,01 sec)

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

mysql> update cars set number='AS-2X9-ZT';
Query OK, 1 rows affected (0,00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Для решения этой проблемы необходимо добавить ещё один триггер, действующий перед обновлением (BEFORE UPDATE):

delimiter $
create trigger cars_number_validate_2 before update on cars
for each row
begin
    if new.number not rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
    then
        signal sqlstate '45000' set message_text = 'bad number';
    end if;
end$
delimiter ;

Теперь UPDATE не позволяет испортить данные:

mysql> update cars set number='AS-2X9-ZT';
ERROR 1644 (45000): bad number

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

  • дважды написать 10 строк процедурного кода
  • иметь привилегию TRIGGER.

Использование представления с параметром WITH CHECK OPTION

Давайте удалим триггеры, очистим таблицу и рассмотрим другое решение. Создадим представление cars_checked на основе таблицы cars; условию WHERE в определении представления будут удовлетворять только строки с корректными номерами машин. Из-за наличия параметра WITH CHECK OPTION все добавляемые или изменяемые через представление строки будут проверяться на соответствие определению представления. В случае несоответствия данное изменение не будет выполнено. Иными словами, внести можно будет только корректные номера:

mysql> drop trigger cars_number_validate_2;
mysql> drop trigger cars_number_validate;
mysql> delete from cars;

mysql> create view cars_checked as
select * from cars where
number rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
with check option;

mysql> insert into cars_checked values('AS-2X9-ZT'); # "X" in digit zone!
ERROR 1369 (HY000): CHECK OPTION failed 'test.cars_checked'
mysql> insert into cars_checked values('AS-229-ZT'); # Valid
Query OK, 1 row affected (0,00 sec)
mysql> update cars_checked set number='AS-2X9-ZT';
ERROR 1369 (HY000): CHECK OPTION failed 'test.cars_checked'

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

Как и триггеры это решение отлично работает и является достаточно гибким (за счет возможности использовать подзапросы в определении представления). Однако, необходимо:

  • иметь привилегию CREATE VIEW
  • менять права доступа у пользователей
  • возможно потребуется изменение приложений, чтобы вместо таблицы cars использовалось представление cars_checked.

Дополнение: можно переименовать таблицу, например, в cars_data, а представлению дать имя cars (т.е. имя исходной таблицы). Тогда не потребуется: ни изменять права у пользователей, ни вносить изменения в код приложений. Возможно нужно будет изменить некоторые служебные команды, которые не работают с представлениями (такие как ANALYZE TABLE или CREATE TABLE .. LIKE ..). Спасибо Jörg Brühe за предложенную идею. Более подробную информацию смотрите в комментариях к оригинальной статье.

Использование генерируемых колонок

Давайте попробуем новый метод, ставший доступным в MySQL 5.7:

mysql> drop view cars_checked;                                                  
mysql> alter table cars add column
number_validate char(0) as
(case when number rlike '^[[:alpha:]]{2}-[[:digit:]]{3}-[[:alpha:]]{2}$'
 then '' end)
virtual not null;

mysql> insert into cars values('AS-229-ZT'); # Valid
ERROR 1136 (21S01): Column count doesnot match value count at row 1
mysql> insert into cars (number) values('AS-229-ZT'); # Valid
Query OK, 1 row affected (0,00 sec)
mysql> insert into cars (number) values('AS-2X9-ZT'); # "X" in digit zone!
ERROR 1048 (23000): Column 'number_validate' cannot be null
mysql> update cars set number='AS-2X9-ZT';
ERROR 1048 (23000): Column 'number_validate' cannot be null

Мы добавили генерируемую колонку number_validate, имеющую тип CHAR(0) NOT NULL, поэтому она может быть только пустой строкой и не занимает места в записи. Кроме того, колонка определена как VIRTUAL, т.е. её значение не хранится в таблице, а вычисляется каждый раз при обращении - это является ещё одной причиной почему она не занимает место. Если добавляемый номер машины является допустимым, то значение, вычисляемое для number_validate, равно пустой строке (''), что соответствует типу колонки и, следовательно, insert/update происходят успешно; если же добавляется недопустимый номер, то значение нового поля вычисляется как NULL (в CASE присутствует неявная часть ELSE NULL), что недопустимо, т.к. поле number_validate определено как NOT NULL.

Обратите внимание, команда insert, которую мы использовали ранее (insert into cars values(‘AS-229-ZT’)) вернула ошибку несмотря на корректное значение. Причина в том, что теперь строка имеет 2 колонки и нужно или явно указывать в какое поле мы добавляем данные (insert into cars (number) как в примере выше), или использовать default для генерируемой колонки:

mysql> insert into cars values('AS-229-ZT', default); # Valid
Query OK, 1 row affected (0,00 sec)

Давайте сравним 3 использованных решения для эмуляции ограничения CHECK:

  • Решения с помощью триггеров и представлений являются универсальными, но более сложными при развертывании.
  • Решение с помощью генерируемых колонок менее универсально, так как выражение, вычисляющее значение генерируемой колонки, не может содержать подзапросов. Но его легче развернуть, так как оно привязано непосредственно к таблице и для создания требуется 1 строка SQL кода. На мой взгляд, это лучший выбор для выполнения простых проверок (как в использованном примере).

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

Дата публикации: 12.03.2019

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

Статьи :
 Установка и настройка MySQL
 Коды ошибок в MySQL
>Программирование в MySQL
 Оптимизация производительности
 Кодировка символов в MySQL
 Хранение данных в MySQL
 MySQL Cluster
См. также:
 Оптимизация производительности MySQL
 Услуги по оптимизации MySQL