MySQL ON DUPLICATE KEY insert into an audit or log table
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')
I really don't want to use PHP for this :(
Thanks!
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');
Result:
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)
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.
ON DUPLICATE KEY is used to update the row, not to insert in another table, you have to use two queries according the first's result.
EDIT : you can use a trigger too
Not sure if this would work but look into IF statement for MySQL
Pseudo-code
IF(SELECT id_key_index FROM tbl WHERE id_key_index = $index) THEN (UPDATE SECOND TBL);
ELSE
INSERT ...
END IF;
精彩评论