MYSQL不存在插入 存在更新的解决方法和对比

这篇具有很好参考价值的文章主要介绍了MYSQL不存在插入 存在更新的解决方法和对比。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

mysql存在更新不存在新增,mysql,数据库,java
设置主键id自增,name为唯一索引

一、避免重复插入

insert ignore into(有唯一索引)

关键字/句: insert ignore into,如果插入的数据会导致 UNIQUE索引PRIMARY KEY 发生冲突/重复,则忽略此次操作/不插入数据,例:

INSERT IGNORE INTO `student`(`name`,`age`) VALUES(`Jack`18);

-- row(s) affected

这里已经存在 name='Jack”的数据,所以会忽略新插入的数据,受影响行数为 0,表数据不变

需要注意 主键会自增

当使用了insert ignore into 新增数据,即使没有插入,某些版本的mysql会自增主键。
比如原来有数据1(id为1),你又插入了数据1,但是重复了没插入,之后再插入数据2,此时的数据2的主键为3而非2.
mysql5.7.26不会自增,8.0会自增

如何避免自增?

在MySQL5.7中做INSERT IGNORE时发现, 即使INSERT未成功执行, 表的自增主键却自动加1了, 在某些情况下需要避免这种行为. 需要修改的变量是 innodb_autoinc_lock_mode, 将其设为0后, 在INSERT未成功执行时不会自增主键.

innodb_autoinc_lock_mode在MySQL各版本的默认值

根据MySQL官方手册的说明:

There are three possible settings for the innodb_autoinc_lock_mode configuration parameter. The settings are 0, 1, or 2, for “traditional”, “consecutive”, or “interleaved” lock mode, respectively. As of MySQL 8.0, interleaved lock mode (innodb_autoinc_lock_mode=2) is the default setting. Prior to MySQL 8.0, consecutive lock mode is the default (innodb_autoinc_lock_mode=1).

在MySQL8中, 默认值为 2 (interleaved, 交错), 在MySQL8以前, 准确地说在8之前, 5.1之后, 默认值为 1 (consecutive, 连续), 在更早的版本是 0

innodb_autoinc_lock_mode的说明

这个值主要用于平衡性能与安全(主从的数据一致性), insert主要有以下类型

simple insert 如insert into t(name) values(‘test’)
bulk insert 如load data | insert into … select … from …
mixed insert 如insert into t(id,name) values(1,‘a’),(null,‘b’),(5,‘c’);
innodb_autoinc_lock_mode = 0:

与更高版本的MySQL向后兼容
在这一模式下,所有的insert语句都要在语句开始的时候得到一个表级的auto_inc锁,在语句结束的时候才释放这把锁,一个事务可能包涵有一个或多个语句
它能保证值分配的可预见性,与连续性,可重复性,这个也就保证了insert语句在复制到slave的时候还能生成和master那边一样的值(它保证了基于语句复制的安全)
由于在这种模式下auto_inc锁一直要保持到语句的结束,所以这个就影响到了并发的插入
innodb_autoinc_lock_mode = 1:

这一模式对simple insert做了优化,由于simple insert一次性插入值的个数可以立即确定, 所以mysql可以一次生成几个连续的值用于这个insert语句, 总的来说这个对复制也是安全的(保证了基于语句复制的安全)
这一模式也是MySQL8.0之前的默认模式, 这个模式的好处是auto_inc锁不要一直保持到语句的结束, 只要语句得到了相应的值后就可以提前释放锁
innodb_autoinc_lock_mode = 2:

由于这个模式下已经没有了auto_inc锁, 所以这个模式下的性能是最好的, 但是它也有一个问题, 就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的
现在mysql已经推荐把二进制的格式设置成row, 所以在binlog_format不是statement的情况下这个模式可以达到最好的性能

insert if not exists(无唯一索引)

数据字段没有设置主键或唯一索引,当插入数据时,首先判断是否存在这条数据,不存在正常插入,存在则忽略。现在我把主键和唯一索引都去掉了。完整sql为

# 示例一:插入多条记录,假设有一个主键为 client_id 的 clients 表,可以使用下面的语句:

INSERT INTO clients (client_id,client_name,client_type)
SELECT supplier_id,supplier_name,’advertising’
FROM suppliers
WHERE not exists
(select * from clients
where clients.client_id = suppliers.supplier_id);

#示例二:插入单条记录
INSERT INTO clients(client_id,client_name,client_type)
SELECT 10345,’IBM’,’advertising’
FROM dual
WHERE not exists
(select*from clients
where clients.client_id=10345
);

二、不存在则插入,存在则更新

on duplicate key update

如果插入的数据会导致UNIQUE 索引或PRIMARY KEY发生冲突/重复,则执行UPDATE语句,例:

#name 有唯一索引
INSERT INTO `student`(`name`, `age`) VALUES('Jack', 19)
  ON DUPLICATE KEY  UPDATE `age`=19; 
  -- If will happen conflict, the update statement is executed

-- 2 row(s) affected

这里受影响的行数是2,因为数据库中存在name='Jack’的数据,如果不存在此条数据,则受影响的行数为1

可能遇到死锁

bug在5.7.26以及8.0.15版本上已经修复了,当插入数据时,不会在形成间隙锁

但是此方法也有坑,如果表中不止一个唯一索引的话,在特定版本的mysql中容易产生dead lock(死锁)

当mysql执行INSERT ON DUPLICATE KEY的 INSERT时,存储引擎会检查插入的行是否会产生重复键错误。如果是的话,它会将现有的
行返回给mysql,mysql会更新它并将其发送回存储引擎。当表具有多个唯一或主键时,此语句对存储引擎检查密钥的顺序非常敏感。根据这个顺序,
存储引擎可以确定不同的行数据给到mysql,因此mysql可以更新不同的行。存储引擎检查key的顺序不是确定性的。例如,InnoDB按照索引添加到
表的顺序检查键。

insert … on duplicate key 在执行时,innodb引擎会先判断插入的行是否产生重复key错误,如果存在,在对该现有的行加上S(共享锁)锁,如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作,然后对该记录加上X(排他锁),最后进行update写入。
如果有两个事务并发的执行同样的语句,那么就会产生death lock

mysql官方描述很简单

An INSERT … ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)

insert on duplicate key update 如果命中主键或者唯一键索引,加行锁,未命中加gap锁,即会阻塞插入数据
mysql存在更新不存在新增,mysql,数据库,java
过程分析
insert … on duplicate key 在执行时,innodb引擎会先判断插入的行是否产生重复key错误,如果存在,在对该现有的行加上S(共享锁)锁,如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作,然后对该记录加上X(排他锁),最后进行update写入。
如果有两个事务并发的执行同样的语句,那么就会产生death lock,如:
mysql存在更新不存在新增,mysql,数据库,java
低版本的解决方法

  1. 尽量不对存在多个唯一键的table使用该语句
  2. 在有可能有并发事务执行的insert 的内容一样情况下不使用该语句。将批量insert on duplicate key update,拆分成多个语句。保证一次事务中不要插入过多值,将多个数据,变成多个sql,执行插入。可以有效的减少死锁命中的发生。
  3. 重试:死锁不可怕,当出现死锁发生时,多执行重试操作可以有效保证插入成功,更新不丢失。
  4. 线程池多线程并发执行改为单线程排队处理。

replace into(先删除再插入)

mysql存在更新不存在新增,mysql,数据库,java
replace into 会根据唯一索引或主键进行判断,如果存在则覆盖写入字段,如果不存在则新增。
此方法有坑,如果主键是自增的,且通过唯一索引来进行操作时,主键会变更,该方法底层是先进性delete,在insert
如果有子表依赖的话不建议使用。文章来源地址https://www.toymoban.com/news/detail-786925.html

到了这里,关于MYSQL不存在插入 存在更新的解决方法和对比的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • mysql插入数据如果存在则忽略

    经常有一种场景,某类数据只能在一张表中存在一条,我们会创建唯一索引进行约束,但是如果重复插入就报错了,所以开发上一版都会先查一次然后再insert。 mysql有个简单的写法,基于已经创建的索引,如果存在的话,那么就忽略这条记录的插入。 不废话了,上代码吧。

    2023年04月12日
    浏览(33)
  • 宝塔 检测到系统已存在mysql 请使用纯净安装 【有效的解决方法】

    advance2016 于 2023-03-21 14:47:50 发布 1)以管理员的身份运行CMD 执行命令: sc delete mysql 1 sc delete mysql 2)清除注册表: 在文件资源管理器中输入“C:Windowsregedit.exe\\\"弹出注册表 删除HKEY_LOCAL_MACHINESYSTEMControlSet001ServicesEventlogApplicationMySQL文件夹 如果能找到如下内容,删除 HKEY_L

    2024年02月07日
    浏览(29)
  • MySQL不同插入方式性能对比实验

    最近负责的项目需要数据同步入库MySQL,为了测速那种入库方式效率比较高,为此进行了以下的对比实验,在此记录一下 共三种方法对比 一条一条插入,最后一次提交 用for循环拼接好插入sql,一次执行 使用MySQL提供的方法executeBatch(),同样使用一次提交 mutiSqlInsert singleSqlIn

    2024年01月22日
    浏览(36)
  • 云原生数据仓库AnalyticDB Mysql(ADB分析型数据库)-DML语法之新增插入数据详解

    ADB分析型数据库,是阿里云平台上资源,原名叫云原生数据仓库AnalyticDB Mysql,俗称ADB分析型数据库,那么ADB的语法与mysql关系型数据库语法存在一定的差异。 本文为笔记,介绍ADB的DML语法的应用。 1. INSERT INTO INSERT INTO用于向表中插入数据,主键重复时会自动忽略当前写入数据

    2024年02月15日
    浏览(39)
  • Springboot Mybatis 不存在插入数据,存在则更新数据

    是不是经常看到代码, 查一下数据库,如果存在数据,就做更新语句调用; 如果不存在,就插入。 今天该篇介绍的 是使用     INSERT INTO        ON DUPLICATE KEY UPDATE        来实现我们上述的场景, 不需要自己再判断来判断去的。 ON DUPLICATE key update 是根据主键索引或者唯一

    2024年02月13日
    浏览(31)
  • 记录线上一次mysql只能查询,不能插入或更新的bug

    错误复现 突然有一天产品通知xx服务不可用,想着最近也没有服务更新,就先排查一下服务日志 使用postman测试的时候请求明显超时,查看日志显示是一个锁的问题 使用工具连接到mysql,查看information_schema.INNODB_TRX,发现有一个事务处于Running的状态,查看id后不能删除。 查看

    2024年02月13日
    浏览(38)
  • JAVA实现存在更新不存在插入与及多余的进行删除(三)

    这个版本,主要是迭代重载了下save方法,不废话,直接上代码: 具体实现类对应的重载方法如下: 然后就是头部加多了 implements ICudDataServiceT, ApplicationContextAware。 通过这个ApplicationContextAware获取到所有bean服务,肤浅地以实体类拼凑一下服务名,找到bean服务就作为这个调用的

    2024年02月13日
    浏览(44)
  • PostgreSQL 分区表插入数据及报错:子表明明存在却报不存在以及column “xxx“ does not exist 解决方法

    记录分区表插入遇到的俩个问题: 这个问题可真的是,太难查了,俩个sql看着一模一样,但就是一个报错,一个不报错;只有导入的文件有差别,猜测是文件的原因; 但具体是什么呢?来实锤下 错误的文件前面有非法字符:uFEFF ; 怎么解决呢?代码兼容, 1. 每次替换下非法

    2024年01月16日
    浏览(67)
  • Mysql大数据批量插入方法

    MySQL是当前最流行的关系型数据库之一,大数据批量插入是MySQL中常用的操作之一。在处理大量数据时,如果一条一条地插入会极大地影响效率,因此批量插入是一个更好的选择,可以大大提高数据的处理速度。下面介绍几种MySQL大数据批量插入的方法。 使用LOAD DATA INFILE语句

    2024年02月10日
    浏览(39)
  • MybatisPlus 使用 saveOrUpdate 详解(慎用),及问题解决方法&mysql保存或更新 ON DUPLICATE KEY UPDATE

    今天的想法是,要在插入数据库时,如果有某某一个主要字段的值重复,则不插入,否则则插入! 看了一下mybatis-Plus是有这个saveOrUpdate 方法! 原本使用save时是没有问题了,改成saveOrUpdate 用了一下就报错了。 com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: error: can not execut

    2024年02月11日
    浏览(38)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包