MySQL中的insert ignore into讲解

2023-12-06 04:24| 来源: 网络整理| 查看: 265

最近工作中，使用到了insert ignore into语法，感觉这个语法还是挺有用的，就记录下来做个总结。

insert ignore into : 忽略重复的记录，直接插入数据。

包括两种场景：

1、插入的数据是主键冲突时

insert ignore into会给出warnings，show warnings就可以看到提示主键冲突；并且本次插入无效。

[test]> create table tt(c1 int primary key, c2 varchar(50))engine = xx; Query OK, 0 rows affected (0.21 sec) [test]> insert into tt values(1, "aaa"), (2, "bbb"), (3, "ccc"); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 [test]> select * from tt; +----+------+ | c1 | c2 | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +----+------+ 3 rows in set (0.01 sec) [test]> [test]> insert ignore into tt values(1, "aaa"), (2, "bbb"), (3, "ccc"); Query OK, 0 rows affected, 3 warnings (0.01 sec) Records: 3 Duplicates: 3 Warnings: 3 [test]> [test]> select * from tt; +----+------+ | c1 | c2 | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +----+------+ 3 rows in set (0.00 sec)

使用insert ignore into语句时，如果主键冲突，只是提示"warnings"。

如果使用insert into语句时，如果主键冲突直接报错。

2、没有主键冲突时，直接插入数据

insert into 与 insert ignore into 都是直接插入数据

[test]> create table t2(c1 int, c2 varchar(50))engine = xxx; Query OK, 0 rows affected (0.05 sec) [test]> insert into t2 values(1, "aaa"), (2, "bbb"), (3, "ccc"); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 GreatDB Cluster[test]> select * from t2; +------+------+ | c1 | c2 | +------+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +------+------+ 3 rows in set (0.00 sec) [test]> insert into t2 values(1, "aaa"), (2, "bbb"), (3, "ccc"); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 [test]> insert into t2 values(1, "aaa"), (2, "bbb"), (3, "ccc"); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 [test]> select * from t2; +------+------+ | c1 | c2 | +------+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | | 1 | aaa | | 2 | bbb | | 3 | ccc | | 1 | aaa | | 2 | bbb | | 3 | ccc | +------+------+ 9 rows in set (0.00 sec) [test]> insert ignore into t2 values(1, "aaa"), (2, "bbb"), (3, "ccc"); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0

因此，insert ignore into主要是忽略重复的记录，直接插入数据。

【本文地址】

公司简介

联系我们