尝试insert ignore into

来源：互联网发布：网络播放系统编辑：程序博客网时间：2024/06/06 03:08

对于主键冲突的时候，自动忽略当前要插入的记录。违反唯一性约束的时候，同样丢弃当前要插入的数据。

可以用在数据同步的场景，这个场景数据源只有insert 的Action，如果数据源含有update就会失败，不能同步update的更新字段。这样做到最终一直和幂等。

mysql> select * from test_table;+----+------+| id | kk   |+----+------+|  1 | 1    ||  3 | 2    ||  4 | 3    ||  6 | 5    |+----+------+4 rows in set (0.01 sec)mysql> show create table test_table;+------------+---------------------------------------------------------------------------------------------------------------------------------------+| Table      | Create Table                                                              |+------------+---------------------------------------------------------------------------------------------------------------------------------------+| test_table | CREATE TABLE `test_table` (  `id` int(50) NOT NULL AUTO_INCREMENT,  `kk` varchar(30) DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `kk_uq` (`kk`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |+------------+---------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> insert ignore into test_table(id, kk) values(6,7);Query OK, 0 rows affected (0.01 sec)mysql> insert ignore into test_table(id, kk) values(6,5);Query OK, 0 rows affected (0.00 sec)mysql> insert ignore into test_table(id, kk) values(5,5);Query OK, 0 rows affected (0.01 sec)mysql> insert ignore into test_table(id, kk) values(5,4);Query OK, 1 row affected (0.02 sec)mysql> select * from test_table;+----+------+| id | kk   |+----+------+|  1 | 1    ||  3 | 2    ||  4 | 3    ||  5 | 4    ||  6 | 5    |+----+------+5 rows in set (0.00 sec)

0 0