MySql 如何实现无则插入有则更新

2024-08-05 11:36:31

摘要

某些场景会有这样的需求：无记录则插入，有记录则更新。例如：新增用户，以身份证号码作为唯一身份标识，插入时若先查询是否存在记录再决定插入还是更新，在高并发情况下必然存在问题。本文提供三种解决方案。

方案一加锁

无论通过synchronized锁、ReentranLock锁还是分布式锁，都可以解决该问题。缺点是，加锁会影响性能。方法二和三都是数据库层面解决方案，个人感觉比方法一好一些。

方案二 Unique和Replace Into … SELECT …

首先对唯一性的字段添加唯一索引ALTER TABLE tb_name ADD UNIQUE (col1、col2...)，，通过唯一索引即可保证数据的唯一性。

加入唯一索引后，通过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，此时需要执行更新操作。

若不存在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内存，固态硬盘

\	:单条插入:	:单条预编译:	:单条插入多条:	:事务插入:
1000	4600毫秒	3334毫秒	8毫秒	704毫秒
10000	27204毫秒	26249毫秒	2959毫秒	2959毫秒
100000	240954毫秒	254716毫秒	17286毫秒	20539毫秒

总结

采用合并sql+事务插入组合，效率最高，乱序插入时候速度超过innodb_buffer的容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快；尽量采用非乱序方式即可。以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。

mysql 存在该记录则更新，不存在则插入记录的sql

复制代码代码如下: INSERT table (auto_id, auto_name) values (1, 'yourname') ON DUPLICATE KEY UPDATE auto_name='yourname' ON DUPLICATE KEY UPDATE的使用如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE.例如,如果列a被定义为UNIQUE,并且包含值1,则以下两
mysql 记录不存在时插入记录存在则更新的实现方法

mysql 记录不存在时插入在 MySQL 中,插入(insert)一条记录很简单,但是一些特殊应用,在插入记录前,需要检查这条记录是否已经存在,只有当记录不存在时才执行插入操作,本文介绍的就是这个问题的解决方案. 问题:我创建了一个表来存放客户信息,我知道可以用 insert 语句插入信息到表中,但是怎么样才能保证不会插入重复的记录呢? 答案:可以通过使用 EXISTS 条件句防止插入重复记录. 示例一:插入多条记录假设有一个主键为 client_id 的 clients 表,可以使用下面的
mysql如果数据不存在，则插入新数据，否则更新的实现方法

//如果不存在,则插入新数据 $sql = "INSERT INTO {$ecs->table('cat_lang')} (cat_id,lang_id,cat_name,keywords,cat_desc) VALUES({$cat_id},{$k},'{$val['cat_name']}','{$val['keywords']}','{$val['cat_desc']}') ON DUPLICATE KEY UPDATE cat_name='{$val['cat_name']}',ca
MySql 如何实现无则插入有则更新

摘要某些场景会有这样的需求:无记录则插入,有记录则更新.例如:新增用户,以身份证号码作为唯一身份标识,插入时若先查询是否存在记录再决定插入还是更新,在高并发情况下必然存在问题.本文提供三种解决方案. 方案一加锁无论通过synchronized锁.ReentranLock锁还是分布式锁,都可以解决该问题.缺点是,加锁会影响性能.方法二和三都是数据库层面解决方案,个人感觉比方法一好一些. 方案二 Unique和Replace Into - SELECT - 首先对唯一性的字段添加唯一索引ALT
postgresql无则插入,有则更新问题

目录准备工作 postgresql 9.5版本之前实现upsert功能 postgresql 9.5版本之后实现upsert功能总结准备工作我们只需要准备一张表 upsert是一个组合词,即当往表中插入记录,如果该记录已存在则更新,否则插入新记录. postgresql 9.5版本之前实现upsert功能语法 postgresql 9.5版本之前想实现upsert功能可以使用with语法来实现. 实例 SQL如下 WITH UPSERT AS ( UPDATE org SET org_
解决mysql服务器在无操作超时主动断开连接的情况

我们在使用mysql服务的时候,正常情况下,mysql的设置的timeout是8个小时(28800秒),也就是说,如果一个连接8个小时都没有操作,那么mysql会主动的断开连接,当这个连接再次尝试查询的时候就会报个"MySQL server has gone away"的误,但是有时候,由于mysql服务器那边做了一些设置,很多情况下会缩短这个连接timeout时长以保证更多的连接可用.有时候设置得比较变态,很短,30秒,这样就需要客户端这边做一些操作来保证不要让mysql主动来断开.
PHP+MySQL之Insert Into数据插入用法分析

本文实例讲述了PHP+MySQL之Insert Into数据插入用法.分享给大家供大家参考.具体如下: INSERT INTO 语句用于向数据库表中插入新纪录. 向数据库表插入数据 INSERT INTO 语句用于向数据库表添加新纪录. 语法: INSERT INTO table_name VALUES (value1, value2,....) 您还可以规定希望在其中插入数据的列: INSERT INTO table_name (column1, column2,...) VALUES (va
PHP使用PDO创建MySQL数据库、表及插入多条数据操作示例

本文实例讲述了PHP使用PDO创建MySQL数据库.表及插入多条数据操作.分享给大家供大家参考,具体如下: 创建 MySQL 数据库: <?php $servername = "localhost"; $username = "username"; $password = "password"; try { $conn = new PDO("mysql:host=$servername", $username, $pas
python的mysql数据库建立表与插入数据操作示例

本文实例讲述了python的mysql数据库建立表与插入数据操作.分享给大家供大家参考,具体如下: mysql数据库建立表一代码 import pymysql # 打开数据库连接 db = pymysql.connect("localhost","root","root","db_test01" ) # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # 使用 exec
MySql三种避免重复插入数据的方法

前言 MySql 在存在主键冲突或唯一键冲突的情况下,根据插入方式,一般有以下三种插入方式避免错误. insert ignore. replace into insert on duplicate key update insert ignore insert ignore 会忽视数据库中已经存在的数据,根据主键或者唯一索引判断,如果数据库没有数据,就会插入新的数据,如果有数据的话就跳过这条数据小case 表结构 root:test> show create table t3G *******
MySQL Innodb关键特性之插入缓冲(insert buffer)

什么是insert buffer? 插入缓冲,也称之为insert buffer,它是innodb存储引擎的关键特性之一,我们经常会理解插入缓冲时缓冲池的一个部分,这样的理解是片面的,insert buffer的信息一部分在内存中,另外一部分像数据页一样,存在于物理页中. 在innodb中,我们知道,如果一个表有自增主键,那么对于这个表的默认插入是非常快的,注意,这里的主键是自增的,如果不是自增的,那么这个插入将会变成随机的,就可能带来数据页分裂的开销,这样,插入就不是顺序的,就会变慢.还有一种
MySQL数据库10秒内插入百万条数据的实现

首先我们思考一个问题: 要插入如此庞大的数据到数据库,正常情况一定会频繁地进行访问,什么样的机器设备都吃不消.那么如何避免频繁访问数据库,能否做到一次访问,再执行呢? Java其实已经给了我们答案. 这里就要用到两个关键对象:Statement.PrepareStatement 我们来看一下二者的特性: 要用到的BaseDao工具类 (jar包 / Maven依赖) (Maven依赖代码附在文末)(封装以便于使用) 注:(重点)rewriteBatchedStatements=true,一次插入

MySql 如何实现无则插入有则更新

摘要

方案一 加锁