我想追踪表中每一条记录的创建者,把信息存放在$create_user列,打算使用触发器来实现。
但是,创建触发器后,当插入数据时,mysql会有一个warning。
在MS-DOS下,可以忽略它。但当我用java jdbc去操作数据时,系统会抛出异常,错误号:1592,不让我插入数据。请问高手,为什么会这样?有其他方法实现吗?mysql>
mysql>
mysql> create table t(a varchar(45),$create_user varchar(45));
Query OK, 0 rows affected (0.02 sec)mysql> insert into t(a) values ('Test1');
Query OK, 1 row affected (0.00 sec)mysql> delimiter //
mysql> create trigger trg001 before insert on t
-> for each row
-> begin
-> set NEW.$create_user=user();
-> end;
-> //
Query OK, 0 rows affected (0.02 sec)mysql> insert into t(a) values ('Test2');
-> //
Query OK, 1 row affected, 1 warning (0.01 sec) <<warning!!!!!mysql> select * from t;
-> //
+-------+----------------+
| a | $create_user |
+-------+----------------+
| Test1 | NULL |
| Test2 | root@localhost |
+-------+----------------+
2 rows in set (0.00 sec)mysql>
但是,创建触发器后,当插入数据时,mysql会有一个warning。
在MS-DOS下,可以忽略它。但当我用java jdbc去操作数据时,系统会抛出异常,错误号:1592,不让我插入数据。请问高手,为什么会这样?有其他方法实现吗?mysql>
mysql>
mysql> create table t(a varchar(45),$create_user varchar(45));
Query OK, 0 rows affected (0.02 sec)mysql> insert into t(a) values ('Test1');
Query OK, 1 row affected (0.00 sec)mysql> delimiter //
mysql> create trigger trg001 before insert on t
-> for each row
-> begin
-> set NEW.$create_user=user();
-> end;
-> //
Query OK, 0 rows affected (0.02 sec)mysql> insert into t(a) values ('Test2');
-> //
Query OK, 1 row affected, 1 warning (0.01 sec) <<warning!!!!!mysql> select * from t;
-> //
+-------+----------------+
| a | $create_user |
+-------+----------------+
| Test1 | NULL |
| Test2 | root@localhost |
+-------+----------------+
2 rows in set (0.00 sec)mysql>
C:\123>
C:\123>mysql -uroot -p
Enter password: *******
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: Y
ES)C:\123>mysql -uroot -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.22-community-logType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> use opsdb;
Database changed
mysql> create table t(a varchar(45),$create_user varchar(45));
Query OK, 0 rows affected (0.05 sec)mysql> insert into t(a) values ('Test1');
Query OK, 1 row affected (0.00 sec)mysql> delimiter //
mysql> create trigger trg001 before insert on t
-> for each row
-> begin
-> set NEW.$create_user=user();
-> end;
-> //
Query OK, 0 rows affected (0.01 sec)mysql> insert into t(a) values ('Test2');
-> //
Query OK, 1 row affected (0.00 sec) <--没有warning!mysql> select * from t;
-> //
+-------+----------------+
| a | $create_user |
+-------+----------------+
| Test1 | NULL |
| Test2 | root@localhost |
+-------+----------------+
2 rows in set (0.00 sec)mysql>
+---------+------+---------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------+
| Warning | 1592 | Statement is not safe to log in statement format. |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)mysql>今天看MYSQL官方文档,问题应该是在触发器中使用了USER()函数,因为这个函数是“NOT DETERMINISTIC”,所以会引发WARNING。但是这个WARNING会拒绝JAVA(JDBC)插入数据,如何解决呀??折腾了一天时间了!谢谢!!
1 MySQL 触发器其实还不是太稳定,在某些情况下会出现问题。
2 触发器会降低数据库系统的性能