文章目录
1. 需求分析
需要批量插入一些数据,数据来源可能是其他数据库的表,也可能是一个外部excel的导入。那么问题来了,是不是每次插入之前都要查一遍,看看重不重复,在代码里筛选一下数据,重复的就过滤掉呢?
向大数据数据库中插入值时,还要判断插入是否重复,然后插入。如何提高效率。
2. on duplicate key update
(1)说明
1:当primary或者unique重复时,则执行update语句,这里说的update语句指的是遇到重复时执行on duplicate key update后的语句
2:如果ON duplicate KEY UPDATE username= username则表示遇到重复错误时不报错,同时也不会更新数据,这个id指的的对比的字段
3::如果ON duplicate KEY UPDATE username = “bob”,则表示遇到冲突时将冲突的这条记录,执行UPDATE username = “bob”,即表示遇到冲突时会更新数据
(2)示例
当前有staff表,有一条测试数据,其中staff_id为主键
【1】测试示例一
针对staff_id=1的记录执行下面的插入语句
INSERT INTO staff (staff_id, username) VALUES (1,'bob') ON duplicate KEY UPDATE username = username;
【结果】
不会报错也不会执行执行,即忽略当前执行语句
【2】测试示例二
针对staff_id=1的记录执行下面的插入语句
INSERT INTO staff (staff_id, username) VALUES (1,'bob') ON duplicate KEY UPDATE username = "bob";
注意此时数据已被更新
2. insert … select … where not exist
(1)说明
上面描述的场景是必须是primary 和unique约束来判断,但是在很多的场景中我们不能去修改原表的约束,根据select的条件判断是否插入,可以不光通过primary 和unique来判断,也可通过其它条件。
这种方法其实就是使用了mysql的一个临时表的方式,但是里面使用到了子查询,效率也会有一点点影响,如果能使用上面的就不使用这个。
【总结】
1:不需要要求严格的约束表结构限制
2:可以设置多条件联合判断
(2)示例
【需求】
当前有一个staff表,在插入时需要不能重复插入username和last_name相同的用户信息,目前表结构没有二者的约束,重复则跳过不插入
INSERT INTO staff (username ,last_name ) SELECT 'Alice' ,'Charles' FROM dual WHERE NOT EXISTS (SELECT 1 FROM staff WHERE username='Alice' and last_name='Charles' )
3. replace into
(1)说明
如果存在primary or unique相同的记录,则先删除掉。再插入新记录。这种方法就是不管原来有没有相同的记录,都会先删除掉然后再插入。
当前情况比较适用于数据完整性要求不高的情况,比如日志数据,只要保证插入即可不要报错影响主干业务
(2)示例
原表有一个id为1的数据,当再次插入一条ID为1的数据时
REPLACE INTO staff (staff_id, username,last_name) SELECT 1, 'Alice' ,'Charles' FROM dual