IGNORE
IGNORE is an optional keyword used in SQL statements such as INSERT, UPDATE, and DELETE. It allows the execution of the operation to continue even if some errors occur, specifically those related to duplicate key violations. In essence, it tells MySQL to treat errors as warnings and go ahead with the command if possible.
Example
INSERT IGNORE INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date)VALUES ('9999', '1965-02-02', 'John', 'Doe', 'M', '2002-07-30');Output
Query OK, 0 rows affected (0.00 sec)Explanation
The INSERT IGNORE statement prevents an error from occurring and skipping the row that would cause the error when inserting data into a table. In the provided example, if ‘9999’ already exists in the ‘emp_no’ column in the ‘employees’ table, the database engine would omit this row and carry on processing any subsequent rows. The output displays ‘Query OK, 0 rows affected’ meaning no row was inserted due to the IGNORE clause.
Example
INSERT OR IGNORE INTO employees(id, name, dept_id)VALUES(1, 'John', 101);Output
If id 1 already exists, there will not be any output and no new entry will be recorded in the table.
Explanation
The INSERT OR IGNORE command in SQL checks during the insertion of new data. If the data already exists, it will simply ignore and not insert the new data into the table. The use of IGNORE prevents errors that occur due to duplications, or violations of unique constraints. This sample query attempts to insert record with id 1, name ‘John’, and dept_id 101 into the employees table. If a record with id 1 already exists, SQL ignores the insertion.