Mysql 简明教程
MySQL - Insert Ignore
在 MySQL 中，可以 INSERT INTO 语句来将一个或多个记录插入到表中。
In MySQL, the INSERT INTO statement can be used to insert one or more records into a table.
在某些情况下，如果某个特定列有唯一约束，并且我们尝试使用 INSERT INTO 语句将重复记录添加到该特定列，则 MySQL 将终止该语句并返回错误。结果，没有行被插入到表中。
In some scenarios, if a particular column has a UNIQUE constraint and if we are trying to add duplicates records into that particular column using the INSERT INTO statement, MySQL will terminate the statement and returns an error. As the result, no rows are inserted into the table.
MySQL Insert Ignore Statement
然而，如果我们使用 MySQL INSERT IGNORE INTO 语句，它将不会显示错误。相反，它允许我们将有效数据插入到表中，并忽略会导致错误的包含无效数据的行。
However, if we use the MySQL INSERT IGNORE INTO statement, it will not display an error. Instead, it allows us to insert valid data into a table and ignores the rows with invalid data that would cause errors.
以下是 INSERT IGNORE INTO 语句避免错误的一些情况：
Following are some scenarios where the INSERT IGNORE INTO statement avoid errors:
-
When we insert a duplicate value in the column of a table that has UNIQUE key or PRIMARY key constraints.
-
When we try to add NULL values to a column where it has NOT NULL constraint on it.
Syntax
以下是 MySQL 中 INSERT IGNORE 语句的语法 −
Following is the syntax of the INSERT IGNORE statement in MySQL −
INSERT IGNORE INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);Example
首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表 −
First of all, let us create a table named CUSTOMERS using the following query below −
Note: UNIQUE 约束确保没有重复值可以存储或插入到 NAME 列中。
Note: The UNIQUE constraint ensures that no duplicate value can be stored or inserted in the NAME column.
CREATE TABLE CUSTOMERS (
ID int NOT NULL,
NAME varchar(20) NOT NULL UNIQUE,
PRIMARY KEY (ID)
);以下查询将三条记录插入到 CUSTOMERS 表中 −
The following query inserts three records into the CUSTOMERS table −
INSERT INTO CUSTOMERS (ID, NAME)
VALUES (1, "Ajay"), (2, "Vinay"), (3, "Arjun");执行以下查询以显示 CUSTOMERS 表中存在的记录 −
Execute the following query to display the records present in the CUSTOMERS table −
SELECT * FROM CUSTOMERS;以下是 CUSTOMERS 表的记录：
Following are the records of CUSTOMERS table −
现在，让我们使用以下查询向 CUSTOMERS 表的 NAME 列中插入一条重复记录 −
Now, let us insert a duplicate record into the NAME column of CUSTOMERS table using the below query −
INSERT INTO CUSTOMERS (NAME) VALUES (2, "Arjun");它返回了一个错误，因为 NAME “Arjun” 已存在于该列中，因此它违反了 UNIQUE 约束。
It returns an error because the NAME "Arjun" is already present in the column and hence it violates the UNIQUE constraint.
ERROR 1062 (23000): Duplicate entry 'Arjun' for key 'customers.NAME'现在，让我们使用 INSERT IGNORE 语句，如下所示 −
Now, let us use the INSERT IGNORE statement as shown below −
INSERT IGNORE INTO CUSTOMERS (NAME) VALUES (2, "Arjun");Output
虽然我们插入了一个重复值，但它不会显示任何错误，而是会发出警告。
Though we are inserting a duplicate value, it do not display any error, instead it gives a warning.
Query OK, 0 rows affected, 1 warning (0.00 sec)我们可以使用以下查询查找以上警告的详细信息 −
We can find the details of the above warning using the following query −
SHOW WARNINGS;以下是警告表 −
Following is the warnings table −
MySQL INSERT IGNORE and STRICT mode
严格模式控制 MySQL 如何处理通过诸如 INSERT 或 UPDATE 数据更改语句将添加到表中的无效、缺失或超出范围的值。
The strict mode controls how MySQL handles the invalid, missing, or out of range values that are going to be added into a table through data-change statements such as INSERT or UPDATE.
因此，如果严格模式是 ON ，并且我们尝试使用 INSERT 语句将一些无效值插入表中，那么 MySQL 终止语句并返回一条错误消息。
So, if the strict mode is ON, and we are trying to insert some invalid values into a table using the INSERT statement, MySQL terminates the statement returns an error message.
但是，如果我们使用 INSERT IGNORE INTO 语句，MySQL 将调整这些值以使其有效，而不是返回错误。
However, if we use the INSERT IGNORE INTO statement, instead of returning an error, MySQL will adjust those values to make them valid before adding the value to the table.
Example
让我们使用以下查询创建一个名为 CUSTOMERS 的表 −
Let us create a table named CUSTOMERS using the following query −
Note: NAME 列仅接受长度小于或等于五的字符串。
Note: The NAME column accepts only strings whose length is less than or equal to five.
CREATE TABLE CUSTOMERS (
ID int NOT NULL,
NAME varchar(5),
PRIMARY KEY (ID)
);在此尝试插入值到 NAME 列中，其长度大于 5。
Here, we are trying to insert a value into NAME column whose length is greater than 5.
INSERT INTO CUSTOMERS (NAME) VALUES (1, "Malinga");它返回了下述错误 −
It returns an error as shown below −
ERROR 1406 (22001): Data too long for column 'NAME' at row 1现在尝试使用 INSERT IGNORE 语句插入同一字符串 −
Now, we are trying to use the INSERT IGNORE statement to insert the same string −
INSERT IGNORE INTO CUSTOMERS (NAME) VALUES (1, "Malinga");Output
就像可以在输出中看到的一样，它没有返回错误而是显示一个警告 −
As we can see in the output, instead of returning an error, it displays an warning −
Query OK, 1 row affected, 1 warning (0.01 sec)让我们通过使用以下命令找到有关上述警告的详细信息 −
Let us find the details of the above warning using the following command −
SHOW WARNINGS;就像可以在下述输出中看到的一样，MySQL 在将其插入 CUSTOMERS 表之前截断了数据。
As we can see in the output below, MySQL truncated the data before inserting it into the CUSTOMERS table.
Verification
执行以下查询验证 CUSTOMERS 表的记录 −
Execute the following query to verify the records of the CUSTOMERS table −
Select * from CUSTOMERS;就像可以在下述 CUSTOMERS 表中看到的一样，该值已被截断至 5 个字符并插入到该表中。
As we can see in the CUSTOMERS table below, the value has been truncated to 5 characters and inserted into the table.
Insert Ignore Query Using a Client Program
除了使用 MySQL 查询执行忽略插入操作外，还可以使用 Node.js、PHP、Java 和 Python 等客户端程序来获得相同的结果。
Besides using MySQL queries to perform the Insert Ignore operation, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.