文章介绍了解决ShardingSphere中ON DUPLICATE KEY UPDATE语义失效的问题，通过升级ShardingSphere版本并调整数据源配置，成功实现了批量插入数据时已存在记录的更新。
Shardingsphere 中 ON DUPLICATE KEY UPDATE 失效问题处理
问题描述
ON DUPLICATE KEY UPDATE 是 MySQL 特有的语义，支持根据唯一约束，对记录进行插入或更新操作；已存在则更新，不存在则插入；
本地环境 MySQL + Mybatis + shardingsphere 4.X.X，对于业务表 loc_rollcall_region_history，根据区域ID、标签ID、统计日期设计了聚合唯一索引 uk_region_tag_statistics_date，实现批量插入表数据，实现数据表已存在的记录更新操作，不存在则插入数据；
- 表结构
CREATE TABLE `loc_rollcall_region_history` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`state` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '数据状态 0-删除 1-正常',
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`region_id` BIGINT(20) NOT NULL COMMENT '点名区域主键',
`region_name` VARCHAR(20) NOT NULL COMMENT '电子点名区域名称' COLLATE 'utf8_general_ci',
`map_name` VARCHAR(20) NOT NULL COMMENT '地图名称' COLLATE 'utf8_general_ci',
`floor` INT(1) NULL DEFAULT NULL COMMENT '楼层',
`tag_id` VARCHAR(20) NULL DEFAULT NULL COMMENT '标签ID' COLLATE 'utf8_general_ci',
`user_name` VARCHAR(20) NULL DEFAULT NULL COMMENT '标签用户姓名' COLLATE 'utf8_general_ci',
`role_name` VARCHAR(20) NULL DEFAULT NULL COMMENT '角色名称' COLLATE 'utf8_general_ci',
`dept_name` VARCHAR(20) NULL DEFAULT NULL COMMENT '部门名称' COLLATE 'utf8_general_ci',
`sign_time` VARCHAR(20) NULL DEFAULT NULL COMMENT '点名时间 缺勤点名时间不存在' COLLATE 'utf8_general_ci',
`statistics_date` VARCHAR(20) NULL DEFAULT NULL COMMENT '统计日期 2022-05-26' COLLATE 'utf8_general_ci',
`status` TINYINT(4) NULL DEFAULT '1' COMMENT '状态 0-正常 1-缺勤',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `uk_region_tag_statistics_date` (`region_id`, `tag_id`, `statistics_date`) USING BTREE
) COMMENT='电子点名历史统计表' COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=217;
- mapper.xml
<!-- 批量入库 -->
<insert id="batchInsert" >
insert into loc_rollcall_region_history
(region_id,region_name,map_name,floor,tag_id,user_name,role_name,dept_name,sign_time,statistics_date,status)
values
<foreach collection="list" item="item" separator="," >
(#{item.regionId},#{item.regionName},#{item.mapName},#{item.floor},#{item.tagId},#{item.userName}
,#{item.roleName},#{item.deptName},#{item.signTime},#{item.statisticsDate},#{item.status})
</foreach>
ON DUPLICATE KEY UPDATE region_name = values(region_name)
</insert>
sql 语句本身没问题，在 mysql 可视化管理工具直接执行没有问题，然后项目运行报错，对于已存在的 2-10000209-2022-05-27 记录提示存在唯一约束，不支持再插入记录。检查 mapper.xml 方法 batchInsert 也没有问题，并且打印日志中 ### SQL 语句直接执行也是没有问题的。
然后开始怀疑人生，有人在代码里面下毒，快去请如来佛祖~
- 异常信息
Caused by: org.springframework.dao.DuplicateKeyException:
### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '2-10000209-2022-05-27' for key 'uk_region_tag_statistics_date'
### The error may exist in file [E:\aworkspace\aproject\xizangkanshousuo\loc-webapp\loc-system\target\classes\mapper\location\LocRollCallRegionHistoryMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: insert into loc_rollcall_region_history (region_id,region_name,map_name,floor,tag_id,user_name,role_name,dept_name,sign_time,statistics_date,status) values (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) , (?,?,?,?,?,? ,?,?,?,?,?) ON DUPLICATE KEY UPDATE region_name = values(region_name)
### Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '2-10000209-2022-05-27' for key 'uk_region_tag_statistics_date'
; Duplicate entry '2-10000209-2022-05-27' for key 'uk_region_tag_statistics_date'; nested exception is java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '2-10000209-2022-05-27' for key 'uk_region_tag_statistics_date'
问题解决
本身语义 ON DUPLICATE KEY UPDATE 是 MySQL 独有的，还是建议考虑综合解决方案应对不存在则插入，存在则更新的场景。
语义使用没问题，语句拿到 MySQL 管理工具这里单独执行也正常运行，项目抛异常 SQLIntegrityConstraintViolationException，还是项目本身有问题。
异常信息是 java.sql.SQLIntegrityConstraintViolationException: Duplicate entry ‘2-10000209-2022-05-27’ for key ‘uk_region_tag_statistics_date’，因为唯一约束无法插入约束为 2-10000209-2022-05-27 的纪录，我们都知道 ON DUPLICATE KEY UPDATE 就是解决这个问题的，这里应该是 ON DUPLICATE KEY UPDATE 语义失效了。Mybatis 是支持语义的，项目中还用到了 shardingsphere，经排查果然是 shardingsphere 不支持导致的。
查到新版本的 shardingsphere 支持该语义，然后就升级到 shardingsphere 4.1.1，结果抛异常 MyBatisConfig required a single bean，存在两个数据源 bean，需要设置一个 @Primary 主 bean，使用另一个 bean 需要单独使用注解 @Qualifier 去引用即可。这里配置 masterDataSource 为主 bean。重启项目，问题解决，批量插入语句运行正常。
- 异常信息
***************************
APPLICATION FAILED TO START
***************************
Description:
Parameter 0 of method sqlSessionFactory in com.cn.loc.framework.config.MyBatisConfig required a single bean, but 2 were found:
- masterDataSource: defined by method 'masterDataSource' in class path resource [com/cn/loc/framework/config/DruidConfig.class]
- shardingDataSource: defined by method 'shardingDataSource' in class path resource [org/apache/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class]
Action:
Consider marking one of the beans as @Primary, updating the consumer to accept multiple beans, or using @Qualifier to identify the bean that should be consumed
- 数据源配置指定主数据源
@Configuration
public class DruidConfig
{
@Primary
@Bean
@ConfigurationProperties("spring.shardingsphere.datasource.master")
public DataSource masterDataSource(DruidProperties druidProperties)
{
DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
return druidProperties.dataSource(dataSource);
}
}
扫一扫
3358
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
