这个MySQL触发器创建语句哪里错误了?

黑狗 发布于 2017/03/04 15:35
阅读 368
收藏 0

【DevOps必读】产品经理与程序员之间如何破局?>>>

CREATE TRIGGER triger_card_usage_insert_block 
BEFORE INSERT ON annual_card_usage_log 
FOR EACH ROW
BEGIN

DECLARE lasttime INT;

SELECT
	usage_time INTO lasttime
FROM
	annual_card_usage_log
WHERE
	NEW.card_id = card_id
AND NEW.mch_id = mch_id
AND NEW.store_id = store_id
ORDER BY
	usage_time DESC
LIMIT 1;


IF(
	lasttime != NULL
	AND(lasttime + 300 < new.usagetime)
) THEN
	INSERT INTO annual_card_usage_log(
		card_id ,
		mch_id ,
		store_id ,
		usage_time
	)
VALUES
	(
		NEW.card_id ,
		NEW.mch_id ,
		NEW.store_id ,
		NEW.usage_time
	);
END
IF;

END$

表结构:

CREATE TABLE `annual_card_usage_log` (
  `usage_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '使用记录ID',
  `card_id` int(10) unsigned NOT NULL COMMENT '卡ID',
  `mch_id` int(10) unsigned NOT NULL COMMENT '商户ID',
  `store_id` int(10) DEFAULT NULL,
  `usage_time` int(10) NOT NULL COMMENT '使用时间unix时间戳',
  PRIMARY KEY (`usage_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

触发器的作用是:

防止5分钟内连续插入2条相同的记录。

加载中
1
他城之途
他城之途

@黑狗 

我的实现思想:如果重复了,抛出异常,让当前insert回滚。代码如下:

DROP TRIGGER
IF EXISTS triger_card_usage_insert_block;

CREATE TRIGGER triger_card_usage_insert_block BEFORE INSERT ON annual_card_usage_log FOR EACH ROW
BEGIN
	DECLARE
		lasttime INT;

SELECT
	usage_time INTO lasttime
FROM
	annual_card_usage_log
WHERE
	NEW.card_id = card_id
AND NEW.mch_id = mch_id
AND NEW.store_id = store_id
ORDER BY
	usage_time DESC
LIMIT 1;


IF lasttime IS NOT NULL
AND lasttime + 300 > new.usage_time THEN
	SIGNAL SQLSTATE 'HY000'
SET MESSAGE_TEXT = '重复提交'; -- 抛出异常,插入会取消


END
IF;


END;

----------

测试结果如下图:第二条记录插不进去

 

 

----------

纠正你1个语法错误: lasttime != NULL 必须改成 IS NOT NULL 

还有个疑问:时间戳一般不是以秒为单位吧?5分钟就不是300了

 

----------

按照你之前的实现思想,判断超过5分钟了才让insert,怎么试都不行。

可能是判断之前,insert已经“即将”写到表里了,只不过没有commit,就是说后面的判断没有意义了,数据已经写进去了。

所以我采用抛出异常,让当前事务回滚的做法。

0
您的好友
您的好友

太复杂不看

这种需求你得写到代码里啊    而不是用sql语句来处理逻辑 

黑狗
黑狗
这不复杂吧。。。这逻辑很直接的啊...
黑狗
黑狗
请看楼下我对那个回复的反馈
0
他城之途
他城之途

支持楼上的说法,触发器一般也不建议使用,因为它是隐藏的,可控性不好,性能也低。

防止重复有很多策略,最简单粗暴的方式就是建立 unique index 唯一索引。

很好奇你的需求,为什么是5分钟内,如果第6分钟重复插入了,你就不管了?

他城之途
他城之途
回复 @黑狗 : 在下面3楼回复你了,欢迎讨论!
黑狗
黑狗
回复 @NO17 : 我可能知道了原因 因为我这个trigger会递归调用自己 导致这个trigger就endless loop了? 因为这个trigger也是insert操作的 然后trigger的内容也是insert
黑狗
黑狗
回复 @NO17 : 十分感谢amigo :)
他城之途
他城之途
回复 @黑狗 : 明白了,马上给你回复在下面。
黑狗
黑狗
那些参数是加密到请求里的,解不出来 所以在代码层面已经动不了了 所以 现在的问题依旧是“这个触发器应该怎么写才可以顺利执行”
下一页
0
黑狗
黑狗

引用来自“NO17”的评论

@黑狗 

我的实现思想:如果重复了,抛出异常,让当前insert回滚。代码如下:

DROP TRIGGER
IF EXISTS triger_card_usage_insert_block;

CREATE TRIGGER triger_card_usage_insert_block BEFORE INSERT ON annual_card_usage_log FOR EACH ROW
BEGIN
	DECLARE
		lasttime INT;

SELECT
	usage_time INTO lasttime
FROM
	annual_card_usage_log
WHERE
	NEW.card_id = card_id
AND NEW.mch_id = mch_id
AND NEW.store_id = store_id
ORDER BY
	usage_time DESC
LIMIT 1;


IF lasttime IS NOT NULL
AND lasttime + 300 > new.usage_time THEN
	SIGNAL SQLSTATE 'HY000'
SET MESSAGE_TEXT = '重复提交'; -- 抛出异常,插入会取消


END
IF;


END;

----------

测试结果如下图:第二条记录插不进去

 

 

----------

纠正你1个语法错误: lasttime != NULL 必须改成 IS NOT NULL 

还有个疑问:时间戳一般不是以秒为单位吧?5分钟就不是300了

 

----------

按照你之前的实现思想,判断超过5分钟了才让insert,我怎么试都不行。

可能是判断之前,insert已经“即将”写到表里了,只不过没有commit,就是说后面的判断没有意义了,数据已经写进去了。

所以我采用抛出异常,让当前事务回滚的做法。

明白 你说的几个问题确实是这样 现在那个字段采用的unix时间戳所以是秒 然后 我没有采用你这种异常的方式达到目的 因为前端调用请求后程序会报异常 不知道会不会造成其他的什么影响,前端也会受到error的alert响应 现在的处理方式是将他处理成没有感知的操作方式了

具体的思路是,before insert的时候,判断条件,满足条件则将值都设置为业务上没有任何意义的值,比如说把id全部设置为0,然后打一个flag标记。然后开一个定时任务,定时扫描数据清理数据。

实现的时候,只是 set new.valud = 0; 就可以了 现在已经完成了个工作啦 

十分感谢!

之前的错误的确是因为在trigger里insert操作了相同的表,导致的错误

他城之途
他城之途
不方便去改应用程序的代码,又不能让用户感知,那就只能把数据设置成无意义的,并用定时任务及时地去清理了。
返回顶部
顶部