mysql中sql使用 replace into与duplica

在使用mysql数据库时候经常会遇到需要插入数据但是如果存在了就更新的情况。那么针对mysql数据库就可以使用 replace into和insert into on duplicate key update进行操作；

准备

第一次建表 表1

CREATE TABLE `husers` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `cad_id` varchar(255) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `sex` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

第二次建表 表2

CREATE TABLE `husers` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `cad_id` varchar(255) DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `sex` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `uq_index` (`cad_id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

在使用replace into和insert into on duplicate key update时需要注意  一个表中必须存在 唯一索引或者主键id

语句实例

replace into husers(name,cad_id) value('aqa11','1234567890')

insert into husers(id,name,cad_id,sex) value(2,'aqa11211','1234567890',0) on duplicate key update name='1122asdasdasd',sex=0

工作机制 

replace into 在使用的时候 如果主键（或者唯一索引）已经存在那么该条语句将会删除 当前记录并将新的记录插入 ，这种会导致其他字段为空 只有主键字段和插入的属性有数据

insert into on duplicate key update则会存在时候更新不存在时候插入

以上两种操作 其中如果主键Id或唯一索引的列必须出现在 表名()括号里

例如在表2中使用时候

insert into husers(name,cad_id,sex) value('aqa11211','1234567890',0) on duplicate key update name='1122asdasdasd',sex=0

cad_id必须出现 表1中id必须出现

如果都出现在括号中  取或（or ||）的结果 也就是说id和唯一索引都没有时候才会插入 否则更新

在数据量比较小的时候 两种命令效率都可以；当数据量比较大时候（1000W+）时候效率差别就会比较大；

显而易见 第一种方式replace into会低很多 因为删除 插入要维护索引，第二种效率要好更新时候索引不变减少维护成本

如果遇到  存在了一条数据（根据主键或者唯一索引）那么就不再插入 的需求 可以使用insert ignoer into 语句

INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('thce@163.com', '11', '99199');