Tip: Understanding INSERT IGNORE in MySQL

Usually, if there is an error during the INSERT command execution, the error is thrown and the execution stopped.

MySQL supports something named IGNORE along with INSERT, which captures the error as a WARNING.

Consider the following

CREATE TABLE 'EmployeeDetails' (
	'Id' INT(5) NOT NULL PRIMARY KEY,
	'Name' VARCHAR(50) NOT NULL COLLATE 'utf8_bin' 
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;

Here, the Id is a PRIMARY KEY and hence duplicates will not be allowed.

The query below will be successful:

INSERT IGNORE INTO EmployeeDetails (Id, Name) VALUES (1,"John")

Let us execute the below query which will result in warning:

INSERT IGNORE INTO EmployeeDetails (Id, Name) VALUES (1,"Steve") 

Warning: Duplicate entry '1' for key 'PRIMARY'

Now, the execution of the query below will display the warnings:

Query:
SHOW WARNINGS;

+------------+----------+-------------------------------------------+
| Level      | Code     | Message									|
+------------+----------+-------------------------------------------+
| Warning    | 1062     | Duplicate entry '1' for key 'PRIMARY'		|
+------------+----------+-------------------------------------------+

Visit the DevX Tip Bank