MySQL 如何使用 BEFORE INSERT 触发器来模拟 CHECK 约束以插入表中的值

在使用MySQL、Oracle 等数据库时，为了让表中只存储规定好的数据，我们可能需要使用 CHECK 约束来限定插入的值，但是在一些特殊情况下，某些数据库可能并不支持CHECK约束。这时，我们就可以使用 BEFORE INSERT 触发器来模拟实现 CHECK 约束。

阅读更多：MySQL 教程

什么是触发器

触发器是一类特殊的存储过程，它们能够自动在某个表的 INSERT、UPDATE 或 DELETE 操作触发时被服务器执行。在这个过程中，触发器可以加入业务逻辑来控制实际的修改操作行为，或者记录相关的审计日志，以提高数据的安全性和可靠性。

在 MySQL 中创建触发器的基本语法如下：

CREATE TRIGGER trigger_name [BEFORE| AFTER] operation_type ON table_name
FOR EACH ROW 
BEGIN
    --触发器逻辑
END;

trigger_name：自定义触发器名称。
operation_type ：触发的操作类型，比如 BEFORE INSERT、AFTER INSERT、BEFORE UPDATE 等。
table_name：定义触发器的表名。
FOR EACH ROW：指定该触发器针对每个数据行执行。
BEGIN...END：在此段代码中写入逻辑操作。

如何模拟 CHECK 约束

在 MySQL 中，虽然有 CHECK 约束的语法，但是并不支持直接将 CHECK 范围定义在列上，而且，即使用 ALTER TABLE 添加了 CHECK 约束，应用程序在插入数据时，也可以绕过这个约束。好在，我们可以通过使用 BEFORE INSERT 触发器模拟 CHECK 约束的行为：

例如，我们需要在 t_user 表的 age 列中插入的值必须在 18 和 99 之间，我们可以使用以下触发器代码模拟 CHECK 约束的行为：

CREATE TRIGGER check_age BEFORE INSERT ON t_user
FOR EACH ROW
BEGIN
    IF NEW.age<18 OR NEW.age >99 THEN
        SET NEW.age = NULL; --如果不符合条件，则将新插入的age列置为 NULL；
    END IF;
END;

在这个触发器中，我们使用了条件判断（IF..THEN) 来进行验证插入数据的合法性。当 age 列插入的值小于 18 或大于 99 时，我们将其设置为 NULL，这样就避免了插入非法数据的问题。

执行测试插入：

INSERT INTO t_user (name,age) VALUES ('fingal',99); --插入正常的数据
INSERT INTO t_user (name,age) VALUES ('wayne',100); --插入非法的数据
INSERT INTO t_user (name,age) VALUES ('bella',-10); --插入非法的数据

执行结果：

SELECT * FROM t_user;

输出：

+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | fingal |  99 |
|  2 | wayne | NULL|
|  3 | bella | NULL|
+----+-------+-----+

我们可以看到 ID=2 和 ID=3 的记录中，age列的值都为 NULL，因为这些值被触发器设置成了 NULL，而不是插入的非法值。

注意事项

如果 BEFORE INSERT 触发器中逻辑出现错误，会自动中断当前的 INSERT 操作，不会影响其他正常操作行为。
BEFORE INSERT 触发器可以用于修改插入的数据，可以将新数据修改为其他的值，也可以直接将插入操作取消。
对于较大的数据量，遍历每一行数据会增加操作的时间和资源消耗，因此需要慎重使用触发器。

结论

虽然 MySQL 和 Oracle 等数据库并不支持 CHECK 约束，但我们可以通过创建 BEFORE INSERT 触发器来模拟实现 CHECK 约束的行为。在触发器中，我们可以通过自定义条件判断来限制插入数据的合法性。虽然在特定情况下触发器存在一定的性能影响，但是它们可以提高数据的完整性和安全性，是数据库管理中不可或缺的重要工具。