mysql数据约束与触发器

1.概述

最近有个新需求，然后设计db的时候发现大部分字段都有取值范围限制，当然之前做法都是在后台服务器的接口请求时进行判断。但我现在想换种方式了，把部分功能前移给后台管理系统去实现，同时把约束后移到数据库层面，这样服务器就不需要增加接口了，如果功能复杂了再说。

我试了check约束和触发器约束

2.check约束

mysql创建：

然后发现check无效，异常数据还是能够插入成功：

原因：

因为mysql手册里写：所有的存储引擎均对check子句进行分析，但是忽略check子句所以check在mysql里是无效的；mysql 8.0支持check

参考资料：

https://dev.mysql.com/doc/refman/8.0/en/create-table.html

https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html

3.触发器约束

触发器是一种特殊的存储过程。触发器和存储过程一样，是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无语调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用。

在MySql中，只有执行insert，delete，update操作时才能触发触发器的执行

触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的，现在触发器只支持行级触发，不支持语句级触发

3.1.Mysql创建触发器约束

CREATE TABLE `out` (
  `db_sn` varchar(15) NOT NULL COMMENT '表头SN',
  `type` varchar(20) NOT NULL COMMENT '出库类型:integrated整车出库/db表头出库',
  `target` varchar(10) NOT NULL COMMENT '去向:customer客户/RD研发',
  `desc` varchar(64) DEFAULT NULL COMMENT '描述',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`db_sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配置表';

-- ----------------------------
--  Triggers structure for table out
-- ----------------------------
DROP TRIGGER IF EXISTS `before_insert`;
delimiter ;;
CREATE TRIGGER `before_insert` BEFORE INSERT ON `out` FOR EACH ROW BEGIN
  IF NEW.type not in ("integrated", "db") THEN
  SET @errorMsg = CONCAT("error type:", NEW.type, ",must in integrated/db");
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @errorMsg;
  END IF;
  IF NEW.target not in ("customer", "RD") THEN
  SET @errorMsg = CONCAT("error target:", NEW.target, ",must in customer/RD");
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @errorMsg;
  END IF;
  IF NEW.type = "db" and  NEW.db_sn not in (select m.sn from module as m left join  module_test as mt on m.sn = mt.sn where mt.sn = NEW.db_sn and mt.finished = 1)  THEN  
  SET @errorMsg = CONCAT('error out for type of db , db_sn:', NEW.db_sn, ", may be not exist or not finished, please check!!!");
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @errorMsg;
  END IF;
  IF NEW.type = "integrated" and  NEW.db_sn not in (select db_sn from device where db_sn = NEW.db_sn and flag=1 and status = "finish")  THEN  
  SET @errorMsg = CONCAT('error out for type of integrated, db_sn:', NEW.db_sn, ", may be not exist or flag is not 1 or status is not finish, please check!!!");
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @errorMsg;
  END IF;
END
 ;;
delimiter ;

这里面有对取值范围进行约束，比如约束type的取值范围("integrated", ''db")；

还有其他表格用的其他状态的约束

3.2.测试

插入异常数据测试，符合预期。