mysql 进行批量修改（包括 on duplicate key update）
1.
说明：mybatis 做批量修改的时候，需要配置文件中（数据库URL后面加上 &allowMultiQueries=true），允许通过代码进行 批量修改
之前遇到问题：扒出来的批量修改的SQL在datagrip可以运行，在代码中运行就报语法问题（check right synax？） 就是少了&allowMultiQueries=true 这个
作用：
MySQL连接数据库时，添加语句：“allowMultiQueries=true”的作用：
1.可以在sql语句后携带分号，实现多语句执行。
2.可以执行批处理，同时发出多个SQL语句。
1. mysql 批量修改的思路：
① 在mybatis 的xml 文件中拼接update语句，每个语句后面 都追加 “;” ,一个会话执行多个sql（数据量不超过1000的话，可以用这种方法，1000条数据耗时15s，太多的话易堵塞，而且语法简单，方便扩展，增加一个字段，减少一个字段，很方便，数据量不大的情况推荐使用此法）
② case id when 某个id值 then else end 语法（注意：else 必须要加，非常重要，如果不指定else的话，其他不符合条件的字段数据会被置为null，所以这个语法有风险，不熟悉的话，一般用id 限制的话就没问题），主要表的字段名不要取 order，name 这些，用在 case when then else end 语法里会报错
设置为null的情况：（没有限制条件）
UPDATE graduates
SET income =
CASEWHEN income = 20000 THENincome * 0.5WHEN income = 15000 THENincome + 500
# 像这种场景，where 后面没有条件，也没有加 else ，就会把库里 不是 20000 和 15000 的数据 设置为 null
# ELSE income
END ;
# where name = '怀特'之前所有的income都有值：
+----+------+
|name|income|
+----+------+
|桑普森 |NULL |
|迈克 |NULL |
|怀特 |NULL |
|阿诺德 |10000 |
|史密斯 |10000 |
|劳伦斯 |15500 |
|哈德逊 |15500 |
|肯特 |NULL |
|贝克 |NULL |
|斯科特 |NULL |
+----+------+
case when then else end的正确sql如下：（不要在网上copy 示例，要尽量自己写，网上有的示例自己copy过来，运行时候，可能有特殊字符，肉眼无法发现，最终报错：排查了一个上午最终决定自己写一下子就出来了，原因在最底下，告诫自己以后不要犯类似的错误）
# 3 rows affected in 7 ms
update girls set
name = case when id = 1 then '1111111' else name end ,
name = case when id = 2 then '2222222' else name end ,
name = case when id = 3 then '3333333' else name end ,sex = case when id = 1 then 'W' else sex end ,
sex = case when id = 2 then 'W' else sex end ,
sex = case when id = 3 then 'W' else sex end
where id in (1,2,3)③ on duplicate key on
④ 修改多表的sql演变：语法如下：
UPDATE A as a
INNER JOIN B as b ON b.aid = a.id
AND a.is_deleted = 0SET a.字段1=b.value1,a.字段2=b.value2,a.字段3=b.value3WHERE a.id=b.aid
# 其他限制条件AND a.字段4=? ;
demo:
# 3 rows affected in 8 ms
update (
# 操作的数据集start(select '1' as id , '黄蓉1' as name, 'W' as sexunionselect '2' as id , '黄蓉1' as name, 'W' as sexunionselect '3' as id , '陆无双1' as name, 'W' as sex) as tmp inner join girl on girl.id = tmp.id
# 只对物理表的女生进行操作：限制条件 是为了获取更少的数据集and girl.sex = '女'
# 操作的数据集end)
# 将 前段传过来的 tem 里的 字段值 赋值给 物理表
setgirl.name = tmp.name,girl.sex = tmp.sex
# girl.sex = '女' 加到这里也行 做个限制
where girl.sex = '女' ;
1.
on duplicate key update 是 mysql的 特有功能（必须得设定主键，否则，会不停的进行插入操作）
有就进行插入，没有就根据主键进行更新
1. 没有设定主键
create table sql_test.on_duplicate_key_update_employee
(id int not null,name varchar(20) null,password varchar(20) null,sex varchar(10) default 'M' null
);
查询表结果为空：
select * from on_duplicate_key_update_employee;
# +--+----+--------+---+
# |id|name|password|sex|
# +--+----+--------+---+
连续执行两次以下sql：
INSERT INTO on_duplicate_key_update_employee (id, name, password)
VALUES (1, '郭一光','111111'),(2, '慕容皝','2222222'),(3, '慕容垂','333333')
ON DUPLICATE KEY UPDATEpassword = 'upPassword';
查询表：发现每执行一个插入sql就会 执行插入（因为现在没有建主键）
select * from on_duplicate_key_update_employee;
# +--+----+--------+---+
# |id|name|password|sex|
# +--+----+--------+---+
# |1 |郭一光 |111111 |M |
# |2 |慕容皝 |2222222 |M |
# |3 |慕容垂 |333333 |M |
# |1 |郭一光 |111111 |M |
# |2 |慕容皝 |2222222 |M |
# |3 |慕容垂 |333333 |M |
# +--+----+--------+---+
2. 清空上表的数据，（不清空数据，因为表里有重复的 id 所以建立不了主键，所以必须清空数据）并建立主键后
create table sql_test.on_duplicate_key_update_employee
(id int not nullprimary key,name varchar(20) null,password varchar(20) null,sex varchar(10) default 'M' null
);
执行两次sql，并查询结果
# 执行 第一次
select * from on_duplicate_key_update_employee;
# +--+----+------------------------------+---+
# |id|name|password |sex|
# +--+----+------------------------------+---+
# |1 |郭一光 |建立主键后的 on_duplicate_key_update|M |
# |2 |慕容皝 |建立主键后的 on_duplicate_key_update|M |
# |3 |慕容垂 |建立主键后的 on_duplicate_key_update|M |
# +--+----+------------------------------+---+# 执行 第二次
select * from on_duplicate_key_update_employee;
# +--+----+----------+---+
# |id|name|password |sex|
# +--+----+----------+---+
# |1 |郭一光 |upPassword|M |
# |2 |慕容皝 |upPassword|M |
# |3 |慕容垂 |upPassword|M |
# +--+----+----------+---+
2. 批量更新（根据主键，给不同的记录进行更新）说明：关键是 用 values（） 函数
方便测试：还是清空表里的数据：
# 第一次执行的sql
INSERT INTO on_duplicate_key_update_employee (id, name, password)
VALUES (1, '郭一光','111111'),(2, '慕容皝','2222222'),(3, '慕容垂','3333333')
ON DUPLICATE KEY UPDATE
password = VALUES(password);select * from on_duplicate_key_update_employee;
# +--+----+--------+---+
# |id|name|password|sex|
# +--+----+--------+---+
# |1 |郭一光 |111111 |M |
# |2 |慕容皝 |2222222 |M |
# |3 |慕容垂 |3333333 |M |
# +--+----+--------+---+# 第二次执行的sql
INSERT INTO on_duplicate_key_update_employee (id, name, password)
VALUES (1, '郭一光','update_111111'),(2, '慕容皝','update_2222222'),(3, '慕容垂','3333333')
ON DUPLICATE KEY UPDATE
password = VALUES(password);select * from on_duplicate_key_update_employee;
# +--+----+--------------+---+
# |id|name|password |sex|
# +--+----+--------------+---+
# |1 |郭一光 |update_111111 |M |
# |2 |慕容皝 |update_2222222|M |
# |3 |慕容垂 |3333333 |M |
# +--+----+--------------+---+# 第三次执行的sql
INSERT INTO on_duplicate_key_update_employee (id, name, password)
VALUES (1, '郭一光','update_111111'),(2, '慕容皝','update_2222222'),(3, '慕容垂','3333333'),(4, '慕容雪','insert_test')
ON DUPLICATE KEY UPDATE
password = VALUES(password);select * from on_duplicate_key_update_employee;
# +--+----+--------------+---+
# |id|name|password |sex|
# +--+----+--------------+---+
# |1 |郭一光 |update_111111 |M |
# |2 |慕容皝 |update_2222222|M |
# |3 |慕容垂 |3333333 |M |
# |4 |慕容雪 |insert_test |M |
# +--+----+--------------+---+# 4 rows affected in 7 ms
3. 批量更新多个字段
# 增加多个字段# 先插入测试数据
INSERT INTO on_duplicate_key_update_employee (id, name, password,sex)
VALUES (1, '郭一光','update_111111','M'),(2, '慕容皝','update_2222222','M'),(3, '慕容垂','3333333','M'),(4, '慕容雪','insert_test','M')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
password = VALUES(password),
sex = VALUES(sex) ;
# 4 rows affected in 7 msselect * from on_duplicate_key_update_employee ;
# +--+----+--------------+---+
# |id|name|password |sex|
# +--+----+--------------+---+
# |1 |郭一光 |update_111111 |M |
# |2 |慕容皝 |update_2222222|M |
# |3 |慕容垂 |3333333 |M |
# |4 |慕容雪 |insert_test |M |
# +--+----+--------------+---+# 进行批量更新多个字段INSERT INTO on_duplicate_key_update_employee (id, name, password,sex)
VALUES (1, '郭一光','update_111111','M'),(2, '慕容皝','update_2222222','M'),(3, '慕容垂','3333333','M'),(4, '慕容雪','insert_test','W')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
password = VALUES(password),
sex = VALUES(sex) ;# 5 rows affected in 8 ms# 查看
select * from on_duplicate_key_update_employee ;
# +--+----+--------------+---+
# |id|name|password |sex|
# +--+----+--------------+---+
# |1 |郭一光 |update_111111 |M |
# |2 |慕容皝 |update_2222222|M |
# |3 |慕容垂 |3333333 |M |
# |4 |慕容雪 |insert_test |W |
# +--+----+--------------+---+
如果给 created_time 一个非空 约束，则操作的时候必须给 created_time 一个默认值 ,否则报错，所以如果是批量更新就把创建日期给 改了，不合适，所以可以考虑不加created_time
### SQL: INSERT INTO on_duplicate_key_update_employee (id, name, password,sex) VALUES ( ? , ? , ? , ? ) , ( ? , ? , ? , ? ) , ( ? , ? , ? , ? ) , ( ? , ? , ? , ? ) ON DUPLICATE KEY UPDATE name = VALUES(name), password = VALUES(password), sex = VALUES(sex)
### Cause: java.sql.SQLException: Field 'created_time' doesn't have a default value
; Field 'created_time' doesn't have a default value; nested exception is java.sql.SQLException: Field 'created_time' doesn't have a default value但是呢，onDuplicate key update 一般是用来批量操作的，可以考虑将 created_time 的 非空约束去掉
mybatis 的用法：
List a = new ArrayList();OnDuplicateKeyUpdateUser user1 = new OnDuplicateKeyUpdateUser();user1.setId(1);user1.setName("王五");user1.setSex("M");user1.setPassword("******");OnDuplicateKeyUpdateUser user2 = new OnDuplicateKeyUpdateUser();user2.setId(2);user2.setName("李四2222222222");user2.setSex("M");user2.setPassword("******");OnDuplicateKeyUpdateUser para = new OnDuplicateKeyUpdateUser();para.setId(1);para.setName("h");para.setSex("h");para.setPassword("p");para.setList(a);int i = onDupicateKeyUpdateMapper.updateBatch3(para); package com.springboot.demo.model;import lombok.Data;import java.time.LocalDateTime;
import java.util.List;@Data
public class OnDuplicateKeyUpdateUser {private Integer id;private String name;private String password;private String sex;private LocalDateTime createdTime;List list;
}
package com.springboot.demo.mapper;import com.springboot.demo.model.Region;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import com.springboot.demo.model.OnDuplicateKeyUpdateUser;
import java.util.List;@Repository
public interface OnDupicateKeyUpdateMapper {public int updateBatch1(@Param("list") List list);public int updateBatch2(@Param("list") List list);public int updateBatch3(OnDuplicateKeyUpdateUser para);}
UPDATE on_duplicate_key_update_employeename = #{item.name}, password = #{item.password}, sex = #{item.sex}, created_time = #{item.createdTime}, id = #{item.id} update on_duplicate_key_update_employeewhen id=#{item.id} then #{item.name} when id=#{item.id} then on_duplicate_key_update_employee.name when id=#{item.id} then #{item.password} when id=#{item.id} then #{item.sex} when id=#{item.id} then #{item.createdTime} #{item.id}
INSERT INTO on_duplicate_key_update_employee(id, name, password, sex )VALUES(#{item.id} , #{item.name} , #{item.password} , #{item.sex} ) ON DUPLICATE KEY UPDATEname = VALUES(name),password = VALUES(password),sex = VALUES(sex)
二 case when then end 做批量修改：（一个update语句就把批量数据处理了，但是有风险，如果 不加 else的话，很容易将全库的某列字段 设置为 null，如果是金额话）
update on_duplicate_key_update_employee
SET name=casewhen id = 1 then '1111111111111'when id = 2 then '2222222222'when id = 3 then '3333333333'when id = 4 then '44444444444'
# ELSE name 这个条件非常重要，没有的话，会把所有不满足条件的数据都清理成nullELSE nameend
WHERE id in (1, 2, 3, 4);
update s_order
set name = case when id = 16 then '测试' else name END,description = CASE WHEN id = 16 THEN '测试批量修改' else description END,updatedGUID = CASE WHEN id = 16 THEN '12345234563456' else updatedGUID END,updatedTime = CASE WHEN id = 16 THEN '2021-03-04 19:08:59.157' else updatedTime END,name = case when id = 17 then '测试' else name END,description = CASE WHEN id = 17 THEN '测试批量修改' else description END,updatedGUID = CASE WHEN id = 17 THEN '12345234563456' else updatedGUID END,updatedTime = CASE WHEN id = 17 THEN '2021-03-04 19:08:59.157' else updatedTime END,name = case when id = 18 then '测试' else name END,description = CASE WHEN id = 18 THEN '测试批量修改' else description END,updatedGUID = CASE WHEN id = 18 THEN '12345234563456' else updatedGUID END,updatedTime = CASE WHEN id = 18 THEN '2021-03-04 19:08:59.157' else updatedTime END,name = case when id = 19 then '测试' else name END,description = CASE WHEN id = 19 THEN '测试批量修改' else description END,updatedGUID = CASE WHEN id = 19 THEN '12345234563456' else updatedGUID END,updatedTime = CASE WHEN id = 19 THEN '2021-03-04 19:08:59.157' else updatedTime END,name = case when id = 20 then '测试' else name END,description = CASE WHEN id = 20 THEN '测试批量修改' else description END,updatedGUID = CASE WHEN id = 20 THEN '12345234563456' else updatedGUID END,updatedTime = CASE WHEN id = 20 THEN '2021-03-04 19:08:59.158' else updatedTime END
WHERE id in (16, 17, 18, 19, 20);参考xml：
update s_ordername = case when id = #{item.id} then #{item.name} else name END,description = CASE WHEN id = #{item.id} THEN #{item.description} else description END,updatedGUID = CASE WHEN id = #{item.id} THEN #{item.updatedGUID} else updatedGUID END,updatedTime = CASE WHEN id = #{item.id} THEN #{item.updatedTime} else updatedTime END, #{item.id} mapper 接口：
int updateBatchOrders(@Param("list") List list);
注意一定要 加这个配置：否则 批量修改不生效（在可视化工具执行没问题，在Java代码中就有问题）
spring.datasource.url=jdbc:mysql://localhost:3306/sql_test?serverTimezone=UTC&characterEncoding=utf-8&allowMultiQueries=true1. 之前从网上copy的代码模板，一直运行不了，报语法问题，检查版本
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！