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!

Comments

I guess "no", unless you use a stored procedure to perform this logic.

Written by dma_k

Accepted Answer

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.

Written by Daniel Vassallo
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki