mysql insert or update与UQ索引

兜兜毛毛

发布于 2020-10-26 17:07:12

1.7K0

发布于 2020-10-26 17:07:12

在项目过程中因需要大批量数据的insert or update操作，为了减少应用程序的校验逻辑，所以使用了mysql 的特殊语法insert into … on duplicate key update。用于解决出现相同的主键执行update ，不同主键执行新增。

在测试过程中发现一个小坑，在网上的技术文档中都写的是（当记录中有PrimaryKey或者unique索引的话，如果数据库已经存在数据，则用新数据更新）当时理解错误，理解成会按其中一种进行处理。

实际情况是mysql会先使用UQ更新，如果没有UQ然后才用PK更新。所以我遇到了因为UQ相同，PK不同导致触发了数据的update导致数据错误。

下边增加一个测试说明：

# 创建测试表
drop table if EXISTS `insert_or_update`;
CREATE TABLE `insert_or_update` (
  `id` int(5) NOT NULL auto_increment,
  `code` varchar(40) not null ,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  unique index (`code`) comment '唯一编码'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;


# 初始化数据
insert into `insert_or_update` (`id`,`code`,`name`,`age`) values(1,'code1','名字1',10);
insert into `insert_or_update` (`id`,`code`,`name`,`age`) values(2,'code2','名字2',10);
insert into `insert_or_update` (`id`,`code`,`name`,`age`) values(3,'code3','名字3',10);
insert into `insert_or_update` (`id`,`code`,`name`,`age`) values(4,'code4','名字4',10);

mysql insert or update 共有4重方式

# 1.  insert ignore into（重复则忽略，不重复则插入）

# 主键重复
insert ignore into `insert_or_update` (`id`,`code`,`name`,`age`) values(4,'code4','名字4',10);
# UQ重复
insert ignore into `insert_or_update` (`id`,`code`,`name`,`age`) values(5,'code4','名字4',10);
# 不重复直接插入
insert ignore into `insert_or_update` (`id`,`code`,`name`,`age`) values(5,'code5','名字4',10);

# 2.  replace into  （重复则先删除再插入新记录，不重复则插入）

# 主键重复
replace into `insert_or_update` (`id`,`code`,`name`,`age`) values(4,'code4','名字4 replace',10);
# UQ重复
replace into `insert_or_update` (`id`,`code`,`name`,`age`) values(5,'code5','名字5 replace',10);

# 3.  insert into … on duplicate key update（重复则更新指定字段，不重复则插入）

# 主键重复
insert into `insert_or_update` (`id`,`code`,`name`,`age`) values(4,'code4','名字4 duplicate key update',10)
    on duplicate key update name = values(`name`);

# UQ重复
insert into `insert_or_update` (`id`,`code`,`name`,`age`) values(5,'code5','名字5 duplicate key update',10)
    on duplicate key update name = values(`name`);