MySql如何实现⽆则插⼊有则更新
摘要
某些场景会有这样的需求：⽆记录则插⼊，有记录则更新。例如：新增⽤户，以⾝份证号码作为唯⼀⾝份标识，插⼊时若先查询是否存在记录再决定插⼊还是更新，在⾼并发情况下必然存在问题。本⽂提供三种解决⽅案。
⽅案⼀加锁
⽆论通过synchronized锁、ReentranLock锁还是分布式锁，都可以解决该问题。缺点是，加锁会影响性能。⽅法⼆和三都是数据库层⾯解决⽅案，个⼈感觉⽐⽅法⼀好⼀些。
⽅案⼆ Unique和Replace Into … SELECT …
⾸先对唯⼀性的字段添加唯⼀索引ALTER TABLE tb_name ADD UNIQUE (col1、)，，通过唯⼀索引即可保证数据的唯⼀性。
加⼊唯⼀索引后，通过INSERT INTO插⼊相同数据就会报错，此时需要使⽤REPLACE INTO插⼊数据，⽤法是⼀样的。通过REPLACE INTO插⼊数据时，若存在相同数据，会将之前的记录删除，再重新插⼊
数据。缺点是，存在先删除再插⼊的过程，sql需要考虑全部数据列，不然会丢失部分列的数据。缺点是，建⽴唯⼀索引会影响插⼊效率。下⾯是具体的例⼦。
# 建⽴索引
ALTER TABLE user ADD UNIQUE (id_card);
# 假设user表只有id,name,id_card三个字段，且id字段⾃增。
# 现在需要插⼊name=ly，id_card=142733的记录。
# 但是，若之前存在id_card=142733的记录，修改name=ly即可。
REPLACE INTO user (id,name,id_card)
SELECT id,'ly',142733 FROM user RIGHT JOIN (SELECT 1) AS tab
ON user.id_card = 142733;
通过RIGHT JOIN (SELECT 1)，若存在id_card=142733的记录，执⾏sql后会将原始id保存在临时的结果集中，随name和
id_card⼀同插⼊。若不存在该记录，则将null作为id随name和id_card⼀同插⼊。最终实现
⽅案三通过预插⼊语句判断是否存在记录
通过预插⼊语句，尝试插⼊，判断修改的记录是否⼤于0，若⼤于0表⽰插⼊成功，若为0则表⽰记录已存在，需要执⾏更新操作。
# 预插⼊
INSERT INTO user (name,id_card)
SELECT 'ly',142733 FROM DUAL
WHERE NOT EXISTE (SELECT id_card FROM user WHERE id_card = 142733) ;
# 若预插⼊语句插⼊成功（修改记录数=1），则⽆需后续操作。否则执⾏更新操作。
UPDATE user SET name = 'ly' WHERE id_card = 142733;
通过NOT EXISTE条件，若存在id_card=142733的记录则伪表DUAL记录为空，预插⼊语句修改记录为0，此时需要执⾏更新操作。
批量更新sql语句
若不存在id_card=142733的记录，则伪表DUAL记录为⼀⾏且内容是'ly',142733，预插⼊语句修改记录为1，此时不必执⾏更新语句。
Mysql批量插⼊更新性能优化
对于数据量较⼤的插⼊和更新，因io/cpu等性能瓶颈，会产⽣⼤量的时间消耗，⽬前主流的优化主要包括预编译、单条sql插⼊多条数据、事务插⼊等，下⾯详细介绍⼀下：
单条插⼊（Mybatis）
INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
(${cityCode}, ${cityName}, ${provinceName}, ${alias}, ${abbrePy})
单条预编译插⼊（Mybatis）
采⽤预编译可以节约mysql服务的解析时间，mytatis中就是采⽤#变量
INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
(#{cityCode}, #{cityName}, #{provinceName}, #{alias}, #{abbrePy})
单条sql插⼊多条数据
即是拼接sql，在⼀个sql中插⼊多条或更新多条数据。
INSERT INTO SYS_CITY (CITY_CODE, CITY_NAME, PROVINCE_NAME, ALIAS, ABBRE_PY) VALUES
("cityCode1", "cityName1", "provinceName1" "alias1", "abbrePy1"),("cityCode2", "cityName2", "provinceName2" "alias2", "abbrePy2")
快的原因
1、合并后⽇志量（MySQL的binlog和innodb的事务让⽇志）减少了，降低⽇志刷盘的数据量和频率，从⽽提⾼效率；
2、通过合并SQL语句，减少⽹络传输的IO；
3、通过合并SQL语句，减少SQL语句解析的次数；
注意事项
1、数据库sql长度是有限制，sql长度别溢出，会报错；
2、乱序插⼊时候速度超过innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快；
事务插⼊
事务插⼊即在插⼊前开启事务，插⼊结束关闭事务进⾏提交即可。
快的原因
1、进⾏⼀个INSERT操作时，MySQL内部会建⽴⼀个事务，在事务内才进⾏真正插⼊处理操作。通过使⽤事务可以减少创建事务的消耗；
注意事项
1、事务不能过⼤，MySQL有innodb_log_buffer_size配置项，事务超出这个时，会刷磁盘，导致性能下降；
2、乱序插⼊时候速度超过innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快；
测试结果
环境：i5-4200U 1.6GHZ，12G内存，固态硬盘
\：单条插⼊:：单条预编译:：单条插⼊多条:：事务插⼊:
10004600毫秒3334毫秒8毫秒704毫秒
1000027204毫秒26249毫秒2959毫秒2959毫秒
100000240954毫秒254716毫秒17286毫秒20539毫秒
总结
采⽤合并sql+事务插⼊组合，效率最⾼，乱序插⼊时候速度超过innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快；尽量采⽤⾮乱序⽅式即可。以上为个⼈经验，希望能给⼤家⼀个参考，也希望⼤家多多⽀持。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。