MySQL中使用触发器、字典表、存储过程、定时任务及避免1442错误

需求：在插入信息之前查询表总记录数大于十则按照时间字段排序删除第一条

首先需要规避的坑：触发器在本表中插入数据不允许操作本表的新增、删除、更新操作会报1442错误

mysql -- error code [1442]; Can't update table 'table_name' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

所以只能新建中间表、存储过程及定时任务来完成这个工作
首先第一步在新建主表eshop_msg_title

CREATE TABLE `eshop_msg_title` (
  `ID` varchar(36) NOT NULL COMMENT 'uuid',
  `STATE` bit(1) DEFAULT NULL COMMENT '返回是否成功true,false',
  `MSG_MESSAGE` varchar(32) DEFAULT NULL COMMENT '返回信息',
  `MSG_CODE` varchar(16) DEFAULT NULL COMMENT '返回码',
  `MSG_TYPE` varchar(16) DEFAULT NULL COMMENT '返回类型',
  `MSG_CONTENT_REQ` varchar(1024) DEFAULT NULL COMMENT '请求报文内容',
  `MSG_CONTENT_RSB` longtext COMMENT '响应报文内容',
  `MSG_PROVIDER` varchar(36) DEFAULT NULL COMMENT '供应商编号',
  `SAVE_TIME` datetime DEFAULT NULL COMMENT '数据库存储时间',
  PRIMARY KEY (`ID`),
  KEY `MSG_PROVIDER` (`MSG_PROVIDER`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后再建中间表eshop_msg_img

CREATE TABLE `eshop_msg_img` (
  `ID` varchar(36) NOT NULL COMMENT 'uuid',
  `STATE` bit(1) DEFAULT NULL COMMENT '返回是否成功true,false',
  `MSG_MESSAGE` varchar(32) DEFAULT NULL COMMENT '返回信息',
  `MSG_CODE` varchar(16) DEFAULT NULL COMMENT '返回码',
  `MSG_TYPE` varchar(16) DEFAULT NULL COMMENT '返回类型',
  `MSG_CONTENT_REQ` varchar(1024) DEFAULT NULL COMMENT '请求报文内容',
  `MSG_CONTENT_RSB` longtext COMMENT '响应报文内容',
  `MSG_PROVIDER` varchar(36) DEFAULT NULL COMMENT '供应商编号',
  `SAVE_TIME` datetime DEFAULT NULL COMMENT '数据库存储时间',
  PRIMARY KEY (`ID`),
  KEY `MSG_PROVIDER` (`MSG_PROVIDER`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TRIGGER `MSG_DEL_IMG` BEFORE INSERT ON `eshop_msg_img` FOR EACH ROW begin
DECLARE del_fig int(16);
select ITEMVALUE into del_fig from msg_dict where ITEMKEY='MSG_IMG_DEL';
if((select count(*) from eshop_msg_title) > (select ITEMVALUE from msg_dict where ITEMKEY='MSG_IMG_DEL')) then
delete from eshop_msg_title  order by 'SAVE_TIME' ASC limit del_fig;
end if;
end;

使用navicat创建触发器截图

我还自己建立了一个字典表msg_dict来存储变量方便后期配置维护

CREATE TABLE `msg_dict` (
  `ID` varchar(36) NOT NULL,
  `ITEMTYPE` varchar(36) DEFAULT NULL COMMENT '类型',
  `ITEMNAME` varchar(36) DEFAULT NULL COMMENT '名称',
  `ITEMKEY` varchar(36) DEFAULT NULL,
  `ITEMVALUE` int(36) DEFAULT NULL,
  `REMARK` varchar(128) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入的语句
INSERT INTO `msg_dict` VALUES ('0001', 'MSG_IMG', 'MSG_IMG_MAX', 'MSG_IMG_MAX', '10', 'ESHOP_MSG_TITLE表中触发器的配置判断条件允许最大值');
INSERT INTO `msg_dict` VALUES ('0002', 'MSG_IMG', 'MSG_IMG_DEL', 'MSG_IMG_DEL', '1', 'ESHOP_MSG_TITLE表中触发器删除条件中的条数');

之后新建存储过程

BEGIN
	#Routine body goes here...
	TRUNCATE TABLE eshop_msg_img;
	COMMIT;
  
	INSERT INTO eshop_msg_img (
	 	id,
        state,
        msg_message,
        msg_code,
        msg_type,
        msg_content_req,
        msg_content_rsb,
        msg_provider,
        save_time
) 
SELECT
	IFNULL( ID, '' ) ID,
	IFNULL( STATE, '' ) STATE,
	IFNULL( MSG_MESSAGE, '' ) MSG_MESSAGE,
	IFNULL(MSG_CODE, '' ) MSG_CODE, 
	IFNULL( MSG_TYPE, '' ) MSG_TYPE,
	IFNULL( MSG_CONTENT_REQ, '' ) MSG_CONTENT_REQ,
	IFNULL( MSG_CONTENT_RSB, '' ) MSG_CONTENT_RSB,
	IFNULL( MSG_PROVIDER, '' ) MSG_PROVIDER,
	IFNULL( date_format( SAVE_TIME, '%Y-%m-%d %H:%i:%s' ), '' ) SAVE_TIME
FROM
	eshop_msg_title
  COMMIT;
END

最后新建定时事件

CREATE DEFINER=`eshop`@`%` EVENT `eshop_msg_title_send_eshop_msg_img_start` ON SCHEDULE EVERY 1 DAY STARTS '2020-12-03 01:30:00' ON COMPLETION PRESERVE ENABLE DO CALL eshop_msg_title_send_eshop_msg_img()

通过测试，以上代码只是锻炼SQL编程能力，真正调用时存储过程不能调用触发器导致错误，实现这个功能最终使用存储过程及定时事件实现定时删除功能。