In SQL, there are mainly four categories of commands, DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and DQL (Data Query Language).
MySQL INSERT command comes under DML commands. DML also includes commands like UPDATE and DELETE.
INSERT command is used for inserting new records into a table. There are two types of syntax for the INSERT command, one when we are inserting all the values for a record (or a row) and another one when we will be skipping a few values from the record.
Syntax:
1. INSERT command Syntax for specific values:
INTERT INTO table (field1, field2, field4,..)
VALUES (value1, value2, value4..);Code language: SQL (Structured Query Language) (sql)2. INSERT command Syntax when inserting all the values:
INSERT INTO table
VALUES (value1, value2, value3,..);Code language: SQL (Structured Query Language) (sql)INSERT IGNORE Command
Whenever we are inserting data into a table, it might be possible that we are putting duplicate values, it happens when we are inserting multiple records together. Assume that we have an employee table with empid as the primary key, and we know that empid cannot be duplicated, but when we try to insert a duplicate value of that primary key into the table, it will generate an error.
This error can be efficiently removed using the INSERT IGNORE statement, which will prevent the error statement, it is useful when inserting multiple records together, while INSERT IGNORE will not process the duplicate statement it will return a warning, this helps in continuous inserting of records without interrupting the flow.
Instances where INSERT IGNORE should be used as they avoid errors:
- When a column contains a PRIMARY or UNIQUE constraint while inserting a duplicate value
- When inserting NULL value where the column has a NOT NULL constraint
- While inserting duplicate values in a partitioned table
The syntax for INSERT IGNORE:
INSERT IGNORE INTO table (field1, field2, field3,..)
VALUES (value1, value2, value3,..);Code language: SQL (Structured Query Language) (sql)Understanding INSERT IGNORE with examples
1. When inserting a single record, that contains a duplicate value of the primary key:
In this example, we’ll have a student’s table, with the following columns, with roll number as PRIMARY KEY.
Let’s see the records of the student table as well.
we’ll be inserting a new record in this table, but the roll number will be duplicated, as we know roll number is a primary key, therefore, it will throw an error. Let’s see if SQL throws an error when we used INSERT IGNORE or not.
INSERT IGNORE INTO student
VALUES
(5, 'Mini', 'Mathur', 2, 'A');Code language: SQL (Structured Query Language) (sql)
As you can see in the output, there is no error but we received a warning. As we used INSERT IGNORE, no changes occurred in the table.
2. When inserting Multiple records, and it contains duplicate values:
In the student table, let’s try and insert multiple records at the same time, with some duplicate values. As we’ll be using INSERT IGNORE, the insert operation will continue without any interruption in the flow.
INSERT IGNORE INTO student
VALUES
(1, 'Ram', 'Masi', 3, 'A'),
(2, 'Rita', 'Ani', 4, 'A'),
(3, 'Kartik', 'bima', 3, 'B'),
(6, 'Mona', 'Lisa', 4, 'C'),
(7, 'Kris', 'Lee', 3, 'B');Code language: SQL (Structured Query Language) (sql)
In the above output, you can we that the records are inserted but the records which were duplicated, which means, the roll numbers which were duplicated were actually ignored by SQL because of the INSERT IGNORE command.
Conclusion
In this tutorial, we learned about the INSERT IGNORE command, and the use of this command, we saw the syntax of the insert and insert ignore commands, we also saw where we must use the insert ignore command to avoid errors, and lastly, we saw a few examples of MySQL insert ignore command.