Understanding INSERT IGNORE in MySQL

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'		|+------------+----------+-------------------------------------------+

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist