mysql 触发器条件判断偶尔失效_MySQL触发器使用时遇到的坑

在使用MySQL的触发器的过程中遇到一个比较蛋疼的问题。

报错信息如下：


于是查看下数据库中的triggers触发器的定义语句：

~~~

mysql> SHOW TRIGGERS\G

*************************** 1. row ***************************

Trigger: items_comment_insert

Event: INSERT

Table: hz_items_comment

Statement: BEGIN

UPDATE hz_items SET comment_count = comment_count + 1 WHERE id = new.item_id;

END

Timing: BEFORE

Created: NULL

sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Definer: root@localhost

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

*************************** 2. row ***************************

Trigger: items_comment_delete

Event: DELETE

Table: hz_items_comment

Statement: BEGIN

UPDATE hz_items SET comment_count = comment_count - 1 WHERE id = old.item_id;

END

Timing: AFTER

Created: NULL

sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

Definer: root@localhost

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

2 rows in set (0.00 sec)

~~~

在上面的定义语句中我们看到了`Definer`触发器的定义者，那是因为在svn测试服务器的连接用户为luo_account用户。

**解决方案**

使用shell登录Linux终端，删除之前定义的触发器语句，并执行新的语句。

查看当前连接的用户：

~~~

mysql> \s

--------------

mysql Ver 14.14 Distrib 5.6.28, for Linux (x86_64) using EditLine wrapper

Connection id:1339

Current database:www_welltrend

Current user:root@localhost

SSL:Not in use

Current pager:stdout

Using outfile:''

Using delimiter:;

~~~

具体执行语句：

~~~

DROP TRIGGER items_comment_insert;

DROP TRIGGER items_comment_delete;

DELIMITER $

CREATE TRIGGER items_comment_insert BEFORE INSERT ON hz_items_comment

FOR EACH ROW BEGIN

UPDATE hz_items SET comment_count = comment_count + 1 WHERE id = new.item_id;

END;

$

DELIMITER ;

DELIMITER $

CREATE TRIGGER items_comment_delete AFTER DELETE ON hz_items_comment

FOR EACH ROW BEGIN

UPDATE hz_items SET comment_count = comment_count - 1 WHERE id = old.item_id;

END;

$

DELIMITER ;

~~~