MySQL INSERT IGNORE忽略无效行

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

现在，让我们看看如果在上面的查询中使用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或UPDATE语句添加到表中的无效或缺失值。如果严格模式为ON，并且我们尝试使用INSERT语句将无效值添加到表中，则该语句将中止，并且将收到错误消息。

但是，如果我们使用INSERT IGNORE命令，MySQL会生成警告消息，而不是抛出错误。另外，此语句尝试将值截断以使其有效，然后再将其插入表中。

让我们借助一个示例来理解它。首先，我们将使用以下语句创建一个名为“ Test”的表：

CREATE TABLE Test (  

    ID int AUTO_INCREMENT PRIMARY KEY,  

    Name varchar(5) NOT NULL  

);  

在上表中，名称列仅接受长度小于或等于五个字符的字符串。现在，执行以下语句将记录插入表中。

INSERT INTO Test(Name)  

VALUES ('Peter'), ('John');  

我们可以看到指定的名称验证了名称列约束，因此它将成功添加。执行SELECT语句以验证结果。它将给出如下输出：

接下来，插入长度大于五的名称：

INSERT INTO Test(Name) VALUES ('Stephen');  

由于严格模式为ON，MySQL不添加值并给出错误消息。但是，如果我们使用INSERT IGNORE语句插入相同的字符串，它将发出警告消息，而不是引发错误。

INSERT IGNORE INTO Test(Name) VALUES ('Stephen');  

最后，我们执行了SHOW WARNINGS命令来检查警告消息。下面的输出更清楚地说明了它，它显示了MySQL在将数据插入表之前试图截断数据。