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!
No, it is not possible in SQL Server to insert records that have duplicate values in the Primary Key.
Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
When you insert a new row into a table if the row causes a duplicate in UNIQUE index or PRIMARY KEY , MySQL will issue an error. However, if you specify the ON DUPLICATE KEY UPDATE option in the INSERT statement, MySQL will update the existing row with the new values instead.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With