MySQL：insert ignore


#MySQL 笔记

使用 insert ignore 插入数据时，若遇主键、唯一索引冲突，则不会插入数据，且不会报错。

示例1

create table `user_info` (
    `id` bigint unsigned not null auto_increment comment '自增ID',
    `name` varchar(45) not null default '' comment '用户名',
    primary key (`id`)
) engine = InnoDB character set = utf8mb4;

mysql> insert into user_info (id, name) values(1, 'letian');
Query OK, 1 row affected

mysql> insert into user_info (id, name) values(1, 'letian2');
(1062, u"Duplicate entry '1' for key 'PRIMARY'")

mysql > select * from user_info;
+----+--------+
| id | name   |
+----+--------+
| 1  | letian |
+----+--------+

mysql> insert ignore into user_info (id, name) values(1, 'letian2');
Query OK, 0 rows affected

mysql> select * from user_info;
+----+--------+
| id | name   |
+----+--------+
| 1  | letian |
+----+--------+

mysql> insert ignore into user_info (id, name) values(2, 'letian2');
Query OK, 1 row affected

mysql> select * from user_info;
+----+---------+
| id | name    |
+----+---------+
| 1  | letian  |
| 2  | letian2 |
+----+---------+

示例2

create table `user_info` (
    `id` bigint unsigned not null auto_increment comment '自增ID',
    `name` varchar(45) not null default '' comment '用户名',
    `note` varchar(1024) not null default '' comment '备注',
    primary key (`id`),
    unique key idx_name(name)
) engine = InnoDB character set = utf8mb4;

mysql> insert into user_info (name) values('letian');

mysql> insert into user_info (name) values('letian');
Query OK, 1 row affected

mysql> select * from user_info;
+----+--------+------+
| id | name   | note |
+----+--------+------+
| 1  | letian |      |
+----+--------+------+

mysql> insert into user_info (name, note) values('letian', '备注');
(1062, u"Duplicate entry 'letian' for key 'idx_name'")

mysql> insert ignore into user_info (name, note) values('letian', '备注');
Query OK, 0 rows affected


( 本文完 )