MySQL ON DUPLICATE KEY插入审计或日志表 [英] MySQL ON DUPLICATE KEY insert into an audit or log table
本文介绍了MySQL ON DUPLICATE KEY插入审计或日志表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
有没有办法做到这一点?
Is there a way to accomplish this?
INSERT IGNORE INTO some_table (one,two,three) VALUES(1,2,3)
ON DUPLICATE KEY (INSERT INTO audit_table VALUES(NOW(),'Duplicate key ignored')
我真的不想为此使用PHP:(
I really don't want to use PHP for this :(
谢谢!
推荐答案
If you want to consider using a stored procedure, you can use a DECLARE CONTINUE HANDLER
. Here's an example:
CREATE TABLE users (
username VARCHAR(30),
first_name VARCHAR(30),
last_name VARCHAR(30),
PRIMARY KEY (username)
);
CREATE TABLE audit_table (timestamp datetime, description varchar(255));
DELIMITER $$
CREATE PROCEDURE add_user
(in_username VARCHAR(30),
in_first_name VARCHAR(30),
in_last_name VARCHAR(30))
MODIFIES SQL DATA
BEGIN
DECLARE duplicate_key INT DEFAULT 0;
BEGIN
DECLARE EXIT HANDLER FOR 1062 SET duplicate_key = 1;
INSERT INTO users (username, first_name, last_name)
VALUES (in_username, in_first_name, in_last_name);
END;
IF duplicate_key = 1 THEN
INSERT INTO audit_table VALUES(NOW(), 'Duplicate key ignored');
END IF;
END$$
DELIMITER ;
让我们添加一些数据,尝试插入重复的密钥:
Let's add some data, trying to insert a duplicate key:
CALL add_user('userA', 'Bob', 'Smith');
CALL add_user('userB', 'Paul', 'Green');
CALL add_user('userA', 'Jack', 'Brown');
结果:
SELECT * FROM users;
+----------+------------+-----------+
| username | first_name | last_name |
+----------+------------+-----------+
| userA | Bob | Smith |
| userB | Paul | Green |
+----------+------------+-----------+
2 rows in set (0.00 sec)
SELECT * FROM audit_table;
+---------------------+-----------------------+
| timestamp | description |
+---------------------+-----------------------+
| 2010-10-07 20:17:35 | Duplicate key ignored |
+---------------------+-----------------------+
1 row in set (0.00 sec)
如果审核在数据库级别上很重要,则您可能只想授予EXECUTE
权限,以便数据库用户只能调用存储过程.
If auditing is important on a database level, you may want to grant EXECUTE
permissions only so that your database users can only call stored procedures.
这篇关于MySQL ON DUPLICATE KEY插入审计或日志表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文