当前位置: 代码网 > it编程>数据库>Mysql > mysql如何防止插入相同数据

mysql如何防止插入相同数据

2025年06月24日 Mysql 我要评论
一、建唯一索引的方式mysql在存在主键冲突或者唯一键冲突的情况下,根据插入策略不同,一般有以下三种避免方法insert ignorereplace intoinsert on duplicate k

一、建唯一索引的方式

mysql在存在主键冲突或者唯一键冲突的情况下,根据插入策略不同,一般有以下三种避免方法

  • insert ignore
  • replace into
  • insert on duplicate key update

注意:除非表有一个primary key或unique索引,否则,使用以上三个语句没有意义,与使用单纯的insert into相同

表结构

create table `ups_lower_electricity_data` (
  `id` int(11) not null auto_increment,
  `componentinstanceid` int(11) default null,
  `starttime` bigint(20) default null,
  `endtime` bigint(20) default null,
  `type` int(4) default null,
  `electricity` decimal(8,2) default null,
  `year` int(20) default null,
  `month` int(20) default null,
  `day` int(20) default null,
  primary key (`id`) using btree
) engine=innodb auto_increment=164 default charset=utf8mb4 row_format=dynamic;

insert ignore

insert ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断),如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据

没有建索引插入

insert into ups_lower_electricity_data (componentinstanceid,starttime,endtime,type,electricity,year,month,day) values (499,1664121600000,1664146800000,3,18.00,2022,9,26)

建立索引进行插入

insert ignore into ups_lower_electricity_data (componentinstanceid,starttime,endtime,type,electricity,year,month,day) values (499,1664121600000,1664146800000,3,18.00,2022,9,26)

replace into

replace into 首先尝试插入数据到表中。 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据,否则,直接插入新数据

使用replace into,你必须具有delete和insert权限

replace into  ups_lower_electricity_data (componentinstanceid,starttime,endtime,type,electricity,year,month,day) values (499,1664294400000,1664319600000,3,22.00,2022,9,28)

insert on duplicate key update

如果在insert into 语句末尾指定了on duplicate key update,并且插入行后会导致在一个unique索引或primary key中出现重复值,则在出现重复值的行执行update;如果不会导致重复的问题,则插入新行,跟普通的insert into一样

使用insert into,你必须具有insert和update权限

如果有新记录被插入,则受影响行的值显示1;如果原有的记录被更新,则受影响行的值显示2;如果记录被更新前后值是一样的,则受影响行数的值显示0

insert into ups_lower_electricity_data (componentinstanceid,starttime,endtime,type,electricity,year,month,day) values (499,1664294400000,1664319600000,3,22.00,2022,9,28) on duplicate key update day = day+1  

insert…on duplicate key update产生死锁

insert … on duplicate key 在执行时,innodb引擎会先判断插入的行是否产生重复key错误, 如果存在,在对该现有的行加上s(共享锁)锁,如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作, 然后对该记录加上x(排他锁),最后进行update写入。

如果有两个事务并发的执行同样的语句, 那么就会产生death lock,如:

解决办法:

  • 1、尽量对存在多个唯一键的table使用该语句
  • 2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

结论:

  • 这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。
  • insert ignore能忽略重复数据,只插入不重复的数据。
  • replace into和insert … on duplicate key update,都是替换原有的重复数据,区别在于replace into是删除原有的行后,在插入新行,如有自增id,这个会造成自增id的改变;insert … on duplicate key update在遇到重复行时,会直接更新原有的行,具体更新哪些字段怎么更新,取决于update后的语句

二、insert into if exists

语法

insert into table (field1, field2, fieldn) select
	'field1',
	'field2',
	'fieldn'
from
	dual
where
	not exists (
		select
			field
		from
			table
		where
			field = ?
	)

第一次执行:affected rows: 1,之后执行都是affected rows: 0,说明实现了单条记录的防重插入

总结

以上为个人经验,希望能给大家一个参考,也希望大家多多支持代码网。

(0)

相关文章:

  • MySQL中的索引结构和分类实战案例详解

    MySQL中的索引结构和分类实战案例详解

    索引是提升数据库查询性能的核心技术,合理的索引设计能够大幅减少数据检索时间,提升系统响应速度;反之,不当的索引使用则可能导致性能下降、资源浪费。本文我将深入剖析... [阅读全文]
  • MySQL索引优化之回表

    MySQL索引优化之回表

    在mysql数据库中,回表是一个与索引查询相关的重要概念,通常指当使用索引查询数据时,仅通过索引无法获取所需的全部字段信息,需要再次访问数据表(聚簇索引)以获取... [阅读全文]
  • MySQL联合索引的使用解读

    MySQL联合索引的使用解读

    1、背景联合索引就是给多个列建一个索引,使用联合索引时要满足最左匹配原则,不然会索引失效,本篇文章就通过explain执行计划研究一下联合索引,能让我们避免使用... [阅读全文]
  • Mysql 数据库中设备实时状态表水平分表

    Mysql 数据库中设备实时状态表水平分表

    一、 需求概述在使用 mysql 数据库存储设备上报日志时,存在一张设备实时状态表,随着时间推移,数据量变得十分庞大。为了更好地管理和查询数据,提高数据库性能,... [阅读全文]
  • MySQL性能指标解读与监控的具体方法

    MySQL性能指标解读与监控的具体方法

    引言在数据库的世界里,mysql凭借其开源、高效、稳定的特性,成为众多企业和开发者的首选。然而,随着数据量的增长和业务复杂度的提升,mysql的性能问题逐渐凸显... [阅读全文]
  • 解决CentOS7下yum失效问题

    解决CentOS7下yum失效问题

    背景centos7已经停止支持,yum源也失效了 ,但是我们服务用的都是centos7 ,也不会轻易升级。so 我们只能找找替代方案,很幸运发现阿里云的yum源... [阅读全文]

版权声明:本文内容由互联网用户贡献,该文观点仅代表作者本人。本站仅提供信息存储服务,不拥有所有权,不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 2386932994@qq.com 举报,一经查实将立刻删除。

发表评论

验证码:
Copyright © 2017-2025  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱:2386932994@qq.com