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

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

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

Сказанное выше справедливо и для другой возможности 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 ;

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

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

Использование представления с параметром 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.

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

Дополнение: можно переименовать таблицу, например, в 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:

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

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