方便更快捷的说明问题,可以按需填写(可删除)
使用环境:
spring boot 2.5.5
sharding sphere 5.1.0
mybatis-plus 3.4
场景、问题:
依据id分片的,新增没问题,分页查询也没问题。但是涉及到id条件的查询和修改和删除,就报这样的错。
报错信息:
### Error querying database. Cause: org.apache.shardingsphere.infra.exception.ShardingSphereException: Inline sharding algorithms expression `sys_user_${id % 3}` and sharding column `id` not match.
### The error may exist in com/maxrocky/maxrocky/mapper/SysUserMapper.java (best guess)
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT id,name,phone FROM sys_user WHERE id=?
### Cause: org.apache.shardingsphere.infra.exception.ShardingSphereException: Inline sharding algorithms expression `sys_user_${id % 3}` and sharding column `id` not match.
已进行操作:
详细的配置如下:
spring:
shardingsphere:
mode:
type: Standalone
repository:
type: File
overwrite: true
datasource:
# 连接名称(下面要用这个名称来区分库)
names: ds0
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/shard_one?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root
# ds1:
# type: com.alibaba.druid.pool.DruidDataSource
# driver-class-name: com.mysql.jdbc.Driver
# url: jdbc:mysql://localhost:3306/shard_two?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
# username: root
# password: root
# ds2:
# type: com.alibaba.druid.pool.DruidDataSource
# driver-class-name: com.mysql.jdbc.Driver
# url: jdbc:mysql://localhost:3306/shard_three?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
# username: root
# password: root
# 是否开启 SQL 显示,默认值: false
props:
sql-show: true
# 分库策略
rules:
sharding:
# 未配置分片规则的表将通过默认数据源定位
default-data-source-name: ds0
# 按表来区分
tables:
# 表名
sys_user:
# 配置数据节点 由数据源名 + 表名组成,以小数点分隔。多个表以逗号分隔,支持 inline 表达式。
# 缺省表示使用已知数据源与逻辑表名称生成数据节点,
# 用于广播表(即每个库中都需要一个同样的表用于关联查询,多为字典表)或只分库不分表且所有库的表结构完全一致的情况
# actual-data-nodes: ds$->{0..2}.sys_user_$->{0..2}
actual-data-nodes: ds0.sys_user_$->{0..2}
# 分库策略
# databaseStrategy:
# 分库的规则 用id这个字段来分库 总共有三个库 ds0(sys_user)与ds1(sys_user)
# shardingColumn: id
# algorithmExpression: ds${id % 3}
# 分表策略
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: sys-user-inline
key-generate-strategy:
column: id
key-generator-name: snowflake
sharding-algorithms:
database-inline:
type: INLINE
props:
# algorithm-expression: ds$->{id % 3}
algorithm-expression: ds0
sys-user-inline:
type: INLINE
props:
algorithm-expression: sys_user_$->{id % 3}
default-database-strategy:
standard:
sharding-column: id
sharding-algorithm-name: database-inline
# 分布式序列算法配置
key-generators:
# 分布式序列列名称
snowflake:
# 分布式序列算法类型
type: SNOWFLAKE
# redis配置
redis:
database: 0
host: 127.0.0.1
port: 6379
timeout: 5000
#配置swagger
swagger:
basePackage: com.maxrocky.appserver
description: appserver-cornerstone
title: appserver-cornerstone
version: V1.0
url: http://127.0.0.1
#开关-swagger开关true/false 生产必须设置为false
enable: true