mysql 使用insert into select....on duplicate key update 语句时,返回多条结果，值被最后一条数据覆盖问题解决

最新推荐文章于 2023-11-08 15:07:56 发布
最新推荐文章于 2023-11-08 15:07:56 发布
阅读量4k 收藏 1
点赞数
CC 4.0 BY-SA版权
分类专栏： mysql 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/ljh_learn_from_base/article/details/115447230
在MySQL中遇到使用INSERT INTO SELECT...ON DUPLICATE KEY UPDATE时，发现多条数据被最后一条覆盖的问题。解决方案是确保在MySQL 8.0.20之前版本使用VALUES(列名)语法。此外，8.0.20之后版本需使用列别名，同时注意保持插入顺序一致以避免结果不一致的bug，并为涉及的where、orderby、groupby字段建立索引以优化性能。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

 问题：最近在工作中，使用insert into select....on duplicate  key update 语句时,返回多条结果，值被最后一条数据覆盖问题

 解决：直到最终查了官方文档才知道：mysql版本 在8.0.20之前要使用以下代码形式

ON DUPLICATE KEY UPDATE month_money=VALUES(month_money),my_commission=VALUES(my_commission) ; 
-- VALUES(列名)

#### 其它问题
#### ON DUPLICATE KEY UPDATE ：mysql版本 8.0.20时应该使用别名，但是当前的mysql版本是8.0.11 应该使用VALUES(列名)的形式，这样不会造成返回多条数据时，最后一条的值覆盖前面的值
#### 使用INSERT INTO SELECT....ON DUPLICATE KEY UPDATE 语句时，如果需要保证插入顺序与原表一样，要加入order by 语句,否则会产生结果不一致的莫名bug【mysq 8 已知bug】
#### 使用INSERT INTO SELECT....ON DUPLICATE KEY UPDATE 语句时，要注意创建相关where，order by,group by 的字段的索引，避免全表扫描造成锁表

 

关于Mysql 的on duplicate key update操作，导致主键不连续自增的问题
飞越蓝天的专栏
06-19 797
2、使用ON DUPLICATE KEY UPDATE时，如果将行作为新行插入，则每行的受影响行值为 1，如果更新现有行，则为 2，如果将现有行设置为其当前值，则为 0。（换句话说就是默认级别为1，在进行insert操作时此时会锁表所以只能影响一行，但是ON DUPLICATE KEY UPDATE本质是先删除后插入，遇到重复是会进行回滚所以它占了两个索引，一切是以主键索引自增为基础的）如果更新操作，受到影响行的值为2；简单说，就是数据库中存在某个记录时，执行这个语句会更新，而不存在这条记录时，就会插入。
MySQL ON DUPLICATE KEY UPDATE影响行数
CoderJia的学习之路
07-16 750
经常使用 `ON DUPLICATE KEY UPDATE` 来插入数据或者更新已存在的记录，使用到 MySQL 一个 `INSERT ... ON DUPLICATE KEY UPDATE` 的语法，明明只更新了两条记录，返回的影响行数竟然是 4
MySQL INSERT的4种形态
Enmotech的博客
08-07 740
墨墨导读：MySQL中常用的四种插入数据的语句: insert ，insert select，replace into，insert into on duplicate key upda...
INSERT ON DUPLICATE KEY UPDATE返回值引起的小乌龙
mxt51220的博客
01-03 1459
thanks for reading.有其它方案或想法可以一起交流!
mysql insert into ... select from on duplicate update
a785975139的博客
07-01 1万+
mysql提供了这样的语法，即当我们往表里插数据里，如果数据已经存在（通过主键或唯一索引确定），我们可以对已存在的记录做更新操作。如： INSERT USER(id,username) VALUES(1,'张三丰') ON DUPLICATE KEY UPDATE username='王老五' 上面的sql意思是，如果id为1的记录不存在，则insert一条id=1、username=张三丰...
mysql insert into select ON DUPLICATE KEY UPDATE 更新值的问题
任香980101
05-12 3730
我有一句话 要达到 INSERT INTO table1 (count) select count(b.c1) count   from table2 b   where date(b.date) =...   group by b.page_hash ON DUPLICATE KEY UPDATE count=count+b.count   ON DUPLICA...
MySql之INSERT INTO…ON DUPLICATE KEY UPDATE详解
MyDriverC
11-16 3371
http://www.myxzy.com/post-137.html 在我们的日常开发中，经常会遇到过这样的情景：查看某条记录是否存在，不存在的话创建一条新记录，存在的话更新某些字段。 你会采用怎么样的处理方式呢？ $result = mysql_query('select * from xxx where id = 1'); $row = mysql_fetch_assoc($
mysql 忽略主键冲突、避免重复插入的几种方式
cpongo11的博客
11-18 214
2015年11月18日 No comments Article http://my.oschina.net/leejun2005/blog/150510 其中博客的简介挺有意思 “...
Mysql中Insert into xxx on duplicate key update问题
12-15
ON DUPLICATE KEY UPDATE` 是一个非常实用的特性，它允许你在尝试插入新记录时，如果插入的数据违反了唯一性约束（如UNIQUE索引或PRIMARY KEY），则自动更新已经存在的匹配行，而不是抛出错误。这个特性是MySQL特有...
MySQL的Replace into 与Insert into on duplicate key update真正的不同之处
12-15
看下面的例子吧： 1 Replace into …1.1 录入原始数据mysql> use test;Database changedmysql> mysql> CREATE TABLE t1 SELECT 1 AS a, ‘c3’ AS b, ‘c2’ AS c;ALTER TABLE t1 CHANGE a a INT PRIMARY KEY AUTO_...
mysql inser into on_mysql insert into ... select from on duplicate update详解
weixin_33585909的博客
01-27 472
mysql提供了这样的语法，即当我们往表里插数据里，如果数据已经存在(通过主键或唯一索引确定)，我们可以对已存在的记录做更新操作。如：INSERT USER(id,username) VALUES(1,'张三丰') ON DUPLICATE KEY UPDATE username='王老五'上面的sql意思是，如果id为1的记录不存在，则insert一条id=1、username=张三丰的记录；否...
mysql重复插入insert时更新ON DUPLICATE KEY UPDATE
小平果的欢乐谷
06-25 1万+
mysql当插入重复时更新的方法： 第一种方法：示例一：插入多条记录 假设有一个主键为 client_id 的 clients 表，可以使用下面的语句：INSERT INTO clients (client_id,client_name,client_type) SELECT supplier_id,supplier_name,'advertising' FROM suppliers
MySQL_插入更新 ON DUPLICATE KEY UPDATE
热门推荐
迎难而上
01-12 8万+
平时我们在设计数据库表的时候总会设计 unique  或者 给表加上 primary key 的限制条件. 此时 插入数据的时候 ，经常会有这样的情况： 我们想向数据库插入一条记录：   若数据表中存在以相同主键的记录，我们就更新该条记录。   否则就插入一条新的记录。 逻辑上我们需要怎么写： $result = mysql_query('select * from x
mysql, insert into table select * from table2 On duplicate key update xxx.和 copy row from a table备忘
festone000的专栏
10-17 832
INSERT INTO product SELECT * FROM product WHERE Id = 4239 on DUPLICATE KEY UPDATE Id = 99999; 添加链接描述 添加链接描述 添加链接描述 添加链接描述
MySQL的INSERT ··· ON DUPLICATE KEY UPDATE使用的几种情况
javashareauthor的博客
02-19 898
在MySQL数据库中，如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句，而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果插入的行数据与现有表中记录的唯一索引或者主键不重复，则执行新纪录插入操作。另外，ON DUPLICATE KEY UPDATE不能写where条件。 示例： create table kid_score(...
MySQL INSERT ... ON DUPLICATE KEY UPDATE
只是甲的博客
05-25 8523
备注:测试数据库版本为MySQL 8.0 真实环境中会有一种应用场景，如存在更新，不存在则更新，Oracle中有merge语句，可以实现此功能。 MySQL中可以用INSERT … ON DUPLICATE KEY UPDATE来实现。 文章目录一.从一个简单的例子看INSERT ... ON DUPLICATE KEY UPDATE二.模拟一个同事存在insert+update的例子 一.从一个简单的例子看INSERT … ON DUPLICATE KEY UPDATE 代码: create table
MySQL语句：insert into …… on duplicate key update ……
老程的博客
08-17 1239
INSERT INTO…ON DUPLICATE KEY UPDATE 解释： 如果记录不存在就进行插入，否则进行修改        如果insert的数据会引起唯一索引（包括主键索引）的冲突，即这个唯一值重复了（已经存在），则不会执行insert操作，而执行后面的update操作。 好处： 原本需要三条操作数据库的语句，现在一句实现； 案例： 建立test表，a为主键（唯一不能重复）： create table test( a
【MySQL】INSERT INTO ... ON DUPLICATE KEY UPDATE 合并两张表
weixin_44168387的博客
11-08 697
如果目标表中已存在具有相同唯一键值的行，将执行更新操作以覆盖目标表中的数据。若条件不匹配任何现有行（也就是目标表中没有相同的唯一键值），那么将会执行插入操作，新的行将被插入到目标表中。通过 INSERT INTO …更新（Update）：如果条件匹配（目标表中已存在具有相同唯一键值的行），则执行更新操作，将源表的值更新到目标表中。INSERT INTO …插入（Insert）：如果条件不匹配，将执行插入操作，将新行插入目标表。: 是要合并的列，这些列的名称和顺序应该在两个表中一致。
使用insert into values on duplicate key update 语法使用sqlsession.insert()中用于批量插入或更新，报错unsupportedoperationexception
最新发布
03-21
### 使用 MyBatis 进行 `INSERT INTO ... ON DUPLICATE KEY UPDATE` 的解决方案 当使用 MyBatis 的 `SqlSession.insert()` 方法执行带有 `ON DUPLICATE KEY UPDATE` 语句的操作时，可能会遇到 `UnsupportedOperationException` 异常。这是因为 MyBatis 默认的 `insert()` 方法仅支持标准的插入操作而不直接支持复杂的 SQL 语法。 以下是解决问题的方法： #### 方法一：自定义 Mapper 接口并配置动态 SQL 可以通过在 XML 映射文件中编写 `<update>` 节点来实现复杂逻辑。MyBatis 支持通过 `<update>` 来完成 `INSERT INTO ... ON DUPLICATE KEY UPDATE` 操作。 ```xml <update id="batchInsertOrUpdate" parameterType="java.util.List"> INSERT INTO your_table (column1, column2, column3) VALUES <foreach collection="list" item="item" separator=","> (#{item.column1}, #{item.column2}, #{item.column3}) </foreach> ON DUPLICATE KEY UPDATE column1 = VALUES(column1), column2 = VALUES(column2), column3 = VALUES(column3); </update> ``` 上述代码片段展示了如何利用 MyBatis 的 `<foreach>` 动态标签构建批量插入语句，并结合 MySQL 提供的 `ON DUPLICATE KEY UPDATE` 实现更新功能[^1]。 调用此方法时需注意： - 参数应为列表对象。 - 数据库表结构中的唯一键约束必须已正确定义以便触发冲突检测。 #### 方法二：手动管理事务 如果需要更灵活地控制事务行为，则可以显式开启事务并通过 JDBC API 或者 Spring AOP 配合 MyBatis 完成操作。例如，在 Java 中可如下设置事务模式： ```java try { sqlSession.getConnection().setAutoCommit(false); // 设置自动提交关闭 sqlSession.update("namespace.batchInsertOrUpdate", dataList); // 执行批处理SQL sqlSession.commit(); // 提交事务 } catch (Exception e) { sqlSession.rollback(); // 出错则回滚 } ``` 这里的关键在于调整连接属性以允许程序级事务管理。 #### 方法三：借助存储过程或函数 对于某些场景下频繁使用的业务逻辑，考虑将其封装到数据库端作为存储过程或者触发器可能更为高效。比如之前提到的一个 PostgreSQL 自定义时间戳更新的例子就可以扩展应用于此处[^2]。 创建类似的触发器能够简化应用程序层面对重复记录更新的需求： ```sql CREATE OR REPLACE FUNCTION handle_duplicate_insert() RETURNS TRIGGER AS $$ BEGIN IF EXISTS(SELECT 1 FROM your_table WHERE unique_key_column = NEW.unique_key_column) THEN UPDATE your_table SET ... WHERE unique_key_column = NEW.unique_key_column; ELSE INSERT INTO your_table (...) VALUES (...); END IF; RETURN NULL; -- result is ignored since this is an AFTER trigger END; $$ LANGUAGE plpgsql; -- 创建触发器绑定至目标表上 CREATE TRIGGER before_your_table_update_or_insert BEFORE INSERT ON your_table FOR EACH ROW EXECUTE PROCEDURE handle_duplicate_insert(); ``` 这种方法虽然增加了数据库负载但减少了客户端编码工作量以及潜在错误风险。 --- ### 总结 针对 MyBatis 在执行带 `ON DUPLICATE KEY UPDATE` 的批量插入/更新过程中抛出 `UnsupportedOperationException` 的情况，推荐采用以上三种策略之一加以应对。具体选择取决于项目实际需求和技术栈偏好等因素综合考量决定。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值