MySQL虽然输入check语句不会报错，但是实际上并没有check的功能。但是MySQL 依然可以利用触发器来实现相应功能。
MySQL实现check和assertion的思路。
注意
signal异常处理功能在MySQL5.5版本以后才出现。之前的版本可以选择对相应值进行操作而不是报错。
下文测试所用数据库版本为Server version: 10.1.21-MariaDB Source distribution
check实现
例子1是希望能够对插入表项有约束，例如年龄不能超过60，若大于60则报错，拒绝插入。
对于其他的支持check的数据库，可以用以下语句来实现：
alter table emp add constraint c_age check(age<60);
而利用触发器，则可以写如下语句
delimiter //create trigger age before insert on emp for each rowbegin declare msg varchar(200); if (new.age > 60) then set msg = "Age is above 60. Cannot insert."; signal sqlstate 'HY000' SET message_text = msg; end if;end //delimiter ;
(最后记得恢复;为结束标志) 将其保存至1.sql，测试其功能
MariaDB [book5]> source path/to/it/1.sqlQuery OK, 0 rows affected (0.03 sec)MariaDB [book5]> insert into emp values(3,'bobo',61,'softeng',10000,1);ERROR 1644 (HY000): Age is above 60. Cannot insert.
assertion实现
例子2 是希望限制两个表的元组总数不能超过5。
支持assertion的数据库可以用以下语句实现：
create assertion asse_countcheck(50>=select count(*) from (select * from male union select * from female) as guest);
利用触发器也可实现这个功能。
delimiter //create trigger count_check_male before insert on male for each rowbegin declare msg varchar(200); declare count int; set count = (select count(*) from male); set count = count + (select count(*) from female); if (count >= 5) then set msg = "The count of guest is above 5."; signal sqlstate 'HY000' SET message_text = msg; end if;end //create trigger count_check_female before insert on female for each rowbegin declare msg varchar(200); declare count int; set count = (select count(*) from male); set count = count + (select count(*) from female); if (count >= 5) then set msg = "The count of guest is above 5."; signal sqlstate 'HY000' SET message_text = msg; end if;end //delimiter ;
由于是插入之前进行处理，这里要注意为count >= 5
在利用聚集函数结果对变量进行赋值时记得加括号。
测试触发器功能：
MariaDB [book5]> select count(*) from female;+----------+| count(*) |+----------+| 3 |+----------+1 row in set (0.00 sec)MariaDB [book5]> select count(*) from male;+----------+| count(*) |+----------+| 2 |+----------+1 row in set (0.00 sec)MariaDB [book5]> insert into male values(3,"test");ERROR 1644 (HY000): The count of guest is above 5.