JIYIK CN >

Traditional SQL INSERTstatements do not perform input validation of their parameters/values ​​against existing database tables, which sometimes results in errors when duplicate keys are found during the insert process.

This is handled in MySQL through INSERTthe extension of the AND ON DUPLICATE KEY UPDATE, REPLACEand statements.IGNORE

To illustrate these methods, let's create a sample database called programming_languages.

-- Here goes the definition of the database
CREATE DATABASE programming_languages;
USE programming_languages;

-- Creating a table
CREATE TABLE Details(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(25) UNIQUE,    -- Making name column unique 
    year_released VARCHAR (5),
    PRIMARY KEY(id)
    );

Output:

20:22:03	CREATE DATABASE programming_languages	1 row(s) affected	0.219 sec
20:22:03	USE programming_languages	0 row(s) affected	0.000 sec
20:22:03	CREATE TABLE Details(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(25) UNIQUE,     year_released VARCHAR (5),PRIMARY KEY(id)) 0 row(s) affected	0.625 sec

Now the details table will be populated with values ​​like below.

-- Names of popular programming languages and their release year
INSERT INTO Details (name, year_released) VALUES ('python', 1991),('c++', 1985),('Java', 1995);

SELECT * FROM Details ORDER BY id;    -- Previewing table

Output:

id	name	year_released
1	python	1991
2	c++		1985
3	Java	1995

-----------------------------------------------------------------------------------------
20:23:24	INSERT INTO Details (name, year_released) VALUES ('python', 1991),('c++', 	     1985),('Java',1995) 3 row(s) affected Records: 3 Duplicates: 0 Warnings: 0	0.109 sec

20:23:24	SELECT * FROM Details ORDER BY id LIMIT 0, 1000	3 row(s) returned 0.000 sec /     0.000 sec

We try to insert a row called into the table python. As expected, this operation results in an error.

-- Trying to insert a new value
INSERT INTO Details (name, year_released) VALUES ('python', 1992)

Output:

20:27:31	INSERT INTO Details (name, year_released) VALUES ('python',1992) Error Code:     1062. Duplicate entry 'python' for key 'details.name' 0.046 sec

ON DUPLICATE KEYInserting values ​​in MySQL table with duplicate key validation using update method

This method either inserts new values ​​(if they don't exist in the table) or updates an existing row. So if a row contains the same data as the new insert, MySQL doesn't make any changes. However, if its data is different from the insert query, it updates the row. If the row doesn't exist, it inserts it.

INSERT INTO Details (name, year_released) VALUES ('python', 1992) as V ON DUPLICATE KEY UPDATE name = V.name, year_released = V.year_released;

SELECT * FROM Details ORDER BY id;   -- Checking the output

Output:

id	name	year_released
1	python	1992
2	c++		1985
3	Java	1995

-----------------------------------------------------------------------------------------
20:47:35	INSERT INTO Details (name, year_released) VALUES ('python', 1992) as V ON         DUPLICATE KEY UPDATE name = V.name, year_released = V.year_released 2 row(s) affected     0.172 sec

20:49:23	SELECT * FROM Details ORDER BY id LIMIT 0, 1000	3 row(s) returned	0.000 sec     / 0.000 sec

From the output, observe that year_releasedthe column has pythonbeen updated for and the logs indicate that two (2) rows were affected (a typical row update operation). You can refer to the official reference of this extension for more options.

REPLACEInsert values ​​in MySQL table with duplicate key validation using

Use this method with caution! Unlike the previous extension, it deletes the row and inserts a new row containing the desired data. While this may appear benign, it can be problematic if a row's unique relationship is deleted during the operation.

REPLACE INTO Details (name, year_released) VALUES ('python', 1993);
SELECT * FROM Details ORDER BY id;

Output:

id	name	year_released
2	c++		1985
3	Java	1995
7	python	1993
-----------------------------------------------------------------------------------------
20:56:48	REPLACE INTO Details (name, year_released) VALUES ('python', 1993) 2 row(s)       affected 0.093 sec

Now, observe Insertthat the operation is performed. However, idit has been changed (due to deletions and insertions). It is recommended to review the official reference of this extension to determine safe usage and extra options.

IGNOREInsert values ​​in MySQL table with duplicate key validation using

IGNOREMethod duplicate keyshas no effect on rows with . However, it does not INSERTthrow an error for the operation. This method handles situations where the update is not allowed and does not require an exception/error to be thrown.

INSERT IGNORE INTO Details (name, year_released) VALUES ('python', 1991);
SELECT * FROM Details ORDER BY id;

Output:

id	name	year_released
2	c++		1985
3	Java	1995
7	python	1993
-----------------------------------------------------------------------------------------
21:29:44	INSERT IGNORE INTO Details (name, year_released) VALUES ('python', 1991) 0 	     row(s) affected, 1 warning(s): 1062 Duplicate entry 'python' for key 'details.name'	0.157 sec

As expected, MySQL does not throw any errors. However, the table remains unchanged.