在整合SpringBoot+shardingSphere实现分库分表时,参考官网和其他文章的配置application.yml,出现过各种报错,这里记录一下其中最大的一个问题,研究了好几个小时才解决的。
原有的配置如下:
spring:
shardingsphere:
# 配置真实数据源
dataSource:
names: ds0,ds1
# 配置第 1 个数据源
ds0:
jdbc-url: jdbc:mysql://192.168.87.112:3308/ds0?useSSL=false&serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
password: root
type: com.zaxxer.hikari.HikariDataSource
username: root
# 配置第 2 个数据源
ds1:
jdbc-url: jdbc:mysql://192.168.87.112:3309/ds1?useSSL=false&serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
password: root
type: com.zaxxer.hikari.HikariDataSource
username: root
rules:
sharding:
tables:
# 配置 t_order 表规则
t_order:
actual-data-nodes: ds${0..1}.t_order_${0..15}
# 配置分库策略
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: database-inline
# 配置分表策略
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: table-inline
# 配置分片算法
sharding-algorithms:
database-inline:
type: INLINE
props:
algorithm-expression: ds$->{user_id % 2}
table-inline:
type: INLINE
props:
algorithm-expression: t_order_$->{order_id % 16}
# t_order_item:
# 省略配置 t_order_item 表规则...
# ...
enabled: true
mybatis:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations: classpath:mapper/*.xml
type-aliases-package: com.zym.week07.mutlids2.entity
启动Application,报错信息如下:
百度一波,网上有些文章说要配置key-generators,于是加上后的配置为:
spring:
shardingsphere:
# 配置真实数据源
dataSource:
names: ds0,ds1
# 配置第 1 个数据源
ds0:
jdbc-url: jdbc:mysql://192.168.87.112:3308/ds0?useSSL=false&serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
password: root
type: com.zaxxer.hikari.HikariDataSource
username: root
# 配置第 2 个数据源
ds1:
jdbc-url: jdbc:mysql://192.168.87.112:3309/ds1?useSSL=false&serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
password: root
type: com.zaxxer.hikari.HikariDataSource
username: root
rules:
sharding:
# 序列配置,这个必须配置,否则报错
key-generators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
tables:
# 配置 t_order 表规则
t_order:
actual-data-nodes: ds${0..1}.t_order_${0..15}
# 配置分库策略
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: database-inline
# 配置分表策略
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: table-inline
# 配置分片算法
sharding-algorithms:
database-inline:
type: INLINE
props:
algorithm-expression: ds$->{user_id % 2}
table-inline:
type: INLINE
props:
algorithm-expression: t_order_$->{order_id % 16}
# t_order_item:
# 省略配置 t_order_item 表规则...
# ...
enabled: true
mybatis:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations: classpath:mapper/*.xml
type-aliases-package: com.zym.week07.mutlids2.entity
启动后,仍然报错,报错信息还是java.util.NoSuchElementException: No value bound,简直吐血:
但仔细观察日志,发现报错的调用栈有点不太一样,这次的报错主要发生在DataSourceMapSetter#getDataSourceMap这个方法引起,进入方法看下源码:
发现里面要取配置spring.shardingsphere.datasource.common.,也就是说必须要配置公共数据源,且前缀必须是spring.shardingsphere.datasource.common,于是加上后的配置为:
spring:
shardingsphere:
# 配置真实数据源
dataSource:
names: ds0,ds1
#配置公共数据源,这个必须配置否则报错
common:
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.zaxxer.hikari.HikariDataSource
password: root
username: root
# 配置第 1 个数据源
ds0:
jdbc-url: jdbc:mysql://192.168.87.112:3308/ds0?useSSL=false&serverTimezone=UTC
# 配置第 2 个数据源
ds1:
jdbc-url: jdbc:mysql://192.168.87.112:3309/ds1?useSSL=false&serverTimezone=UTC
rules:
sharding:
# 序列配置,这个必须配置,否则报错
key-generators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
tables:
# 配置 t_order 表规则
t_order:
actual-data-nodes: ds${0..1}.t_order_${0..15}
# 配置分库策略
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: database-inline
# 配置分表策略
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: table-inline
# 配置分片算法
sharding-algorithms:
database-inline:
type: INLINE
props:
algorithm-expression: ds$->{user_id % 2}
table-inline:
type: INLINE
props:
algorithm-expression: t_order_$->{order_id % 16}
# t_order_item:
# 省略配置 t_order_item 表规则...
# ...
enabled: true
mybatis:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations: classpath:mapper/*.xml
type-aliases-package: com.zym.week07.mutlids2.entity
启动后,终于成功。