MySQL 中的 Insert Ignore 语句有一个特殊的功能,当我们将单行或多行插入到表中时,它会忽略无效行。我们可以通过下面的解释来理解它,其中一个表包含一个主键列。
主键列不能将重复值存储到表中。例如, student_roll_number对于每个学生应该始终是唯一的。同样,公司中的employee_id应该始终与employee 表不同。当我们尝试将重复记录插入具有主键列的表中时,它会产生一条错误消息。但是,如果我们使用 INSERT IGNORE 语句将重复行添加到具有主键列的表中,MySQL 不会产生任何错误。当我们尝试批量插入记录时,此语句是首选,并且由此产生的错误可能会中断执行过程。因此,它不会将任何记录存储到表中。在这种情况下,INSERT IGNORE 语句仅生成警告。
以下是 INSERT IGNORE 语句避免错误的情况:
- 当我们尝试在表的列具有 PRIMARY 或 UNIQUE KEY 约束的情况下插入重复键时。
- 当我们尝试在表的列具有 NOT NULL 约束的情况下添加 NULL 值时。
- 当我们尝试将记录插入到分区表中时,输入的值与列出的分区的格式不匹配。
句法
以下是在 MySQL 中使用 INSERT IGNORE 语句的语法:
INSERT IGNORE INTO table_name (column_names)
VALUES (value_list), (value_list) .....;
MySQL INSERT IGNORE 示例
让我们通过一个例子来了解 INSERT IGNORE 语句在MySQL中是如何工作的。首先,我们需要使用以下语句创建一个名为“Student”的表:
CREATE TABLE Student (
Stud_ID int AUTO_INCREMENT PRIMARY KEY,
Name varchar(45) DEFAULT NULL,
Email varchar(45) NOT NULL UNIQUE,
City varchar(25) DEFAULT NULL
);
该UNIQUE约束确保我们不能插入重复的值到电子邮件列。接下来,需要将记录插入表中。我们可以执行以下语句将数据添加到表中:
INSERT INTO Student(Stud_ID, Name, Email, City)
VALUES (1,'Stephen', 'stephen@javatpoint.com', 'Texax'),
(2, 'Joseph', 'Joseph@javatpoint.com', 'Alaska'),
(3, 'Peter', 'Peter@javatpoint.com', 'california');
最后,执行SELECT语句验证插入操作:
SELECT * FROM Student;
我们可以看到下面的输出,我们有3行到表:
让我们执行以下语句,尝试将两条记录添加到表中:
INSERT INTO Student(Stud_ID, Name, Email, City)
VALUES (4,'Donald', 'donald@javatpoint.com', 'New York'),
(5, 'Joseph', 'Joseph@javatpoint.com', 'Chicago');
它将产生一个错误:ERROR 1062 (23000): Duplicate entry ' Joseph@javatpoint.com ' for key 'student.Email'\因为电子邮件紫罗兰色 UNIQUE 约束。
现在,让我们看看如果我们在上面的查询中使用 INSERT IGNORE 语句会发生什么:
INSERT IGNORE INTO Student(Stud_ID, Name, Email, City)
VALUES (4,'Donald', 'donald@javatpoint.com', 'New York'),
(5, 'Joseph', 'Joseph@javatpoint.com', 'Chicago');
MySQL 将产生一条消息:添加了一行,而忽略了另一行。
1 row affected, 1 warning(s): 1062 Duplicate entry for key email.
Records: 2 Duplicates: 1 Warning: 1
我们可以使用SHOW WARNINGS命令查看详细警告:
因此,我们可以说,如果我们使用 INSERT IGNORE 语句,MySQL 会给出警告而不是发出错误。
MySQL INSERT IGNORE 和 STRICT 模式
在 MySQL 中,STRICT MODE 处理将使用 INSERT OR UPDATE 语句添加到表中的无效或缺失值\。如果严格模式为ON,并且我们尝试使用INSERT 语句将无效值添加到表中,则该语句将被中止,我们将收到一条错误消息。
但是,如果我们使用 INSERT IGNORE 命令,MySQL 会生成警告消息而不是抛出错误。此外,此语句尝试在将值插入表之前截断值以使其有效。
让我们通过一个例子来理解它。首先,我们将使用以下语句创建一个名为“Test”的表:
CREATE TABLE Test (
ID int AUTO_INCREMENT PRIMARY KEY,
Name varchar(5) NOT NULL
);
上表中,name列只接受长度小于等于5个字符的字符串。现在,执行以下语句将记录插入到表中。
INSERT INTO Test(Name)
VALUES ('Peter'), ('John');
我们可以看到指定的名称验证了名称列约束,因此它会被成功添加。执行 SELECT 语句以验证结果。它将给出如下输出:
接下来,插入长度大于 5 的名称:
INSERT INTO Test(Name) VALUES ('Stephen');
MySQL 不会添加值并给出错误消息,因为严格模式为 ON。但是,如果我们使用 INSERT IGNORE 语句插入相同的字符串,它将给出警告消息而不是抛出错误。
INSERT IGNORE INTO Test(Name) VALUES ('Stephen');
最后,我们执行了 SHOW WARNINGS 命令来检查警告消息。下面的输出更清楚地解释了它,显示 MySQL 在将数据插入表之前尝试截断数据。