利用MySQL触发器实现check和assertion

最新推荐文章于 2021-11-20 09:00:00 发布

He11o_Liu

最新推荐文章于 2021-11-20 09:00:00 发布

阅读量4.9k

点赞数 8

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/He11o_Liu/article/details/69389997

版权

数据库专栏收录该内容

1 篇文章

订阅专栏

本文介绍了如何使用MySQL触发器模拟check约束和assertion功能。虽然MySQL本身不直接支持这些特性，但通过触发器可以达到类似效果。文中提供了一个年龄限制的例子，展示如何阻止插入年龄超过特定值的记录，并探讨了限制两个表元组总数不超过5的assertion实现。测试表明，这些触发器能有效执行预期的限制操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL虽然输入check语句不会报错，但是实际上并没有check的功能。但是MySQL 依然可以利用触发器来实现相应功能。
本文将根据两个例子简要阐述MySQL实现check和assertion的思路。

MySQL触发器官方文档 MySQL Signal 官方文档

注意

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 row
begin
    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.sql
Query 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_count
check(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 row
begin
    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 row
begin
    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.