如果您考虑使用存储过程,可以使用 DECLARE CONTINUE HANDLER
。下面是一个例子:
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 ;
让我们添加一些数据,试图插入重复的键:
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
权限,以便您的数据库用户只能调用存储过程。