ShardingSphere-JDBC 分库分表 - ToB企服应用市场:ToB评测及商务社交产业平台

复制代码

spring:
shardingsphere:
datasource:
names: db1,db2
db1:
url: jdbc:mysql://localhost:3306/db1?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
db2:
url: jdbc:mysql://localhost:3306/db2?useUnicode=true&useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
# masterslave:
# # 读写分离配置
# load-balance-algorithm-type: round_robin #负载均衡策略，round_robin是轮循
# # 最终的数据源名称
# name: dataSource #就是bean的名字
# # 主库数据源名称
# master-data-source-name: db1
# # 从库数据源名称列表，多个逗号分隔
# slave-data-source-names: db2
sharding:
default-database-strategy:
inline:
sharding-column: order_id #数据库分片策略字段
algorithm-expression: db${order_id % 2+1} # 配置t_order表分表策略
tables:
t_order:
actual-data-nodes: db$->{1..2}.t_order_$->{0..3} #配置t_order表分库策略（inline-基于行表达式的分片算法）
table-strategy:
inline: #只适合一个字段
sharding-column: order_id #分表策略字段
algorithm-expression: t_order_$->{order_id % 2} #根据order_id%2 找到对应表名
key-generator:
column: order_id
type: SNOWFLAKE
props:
sql:
show: true #打印sql

复制代码

tables: t_order: actual-data-nodes: db$->{1..2}.t_order_$->{0..3} #配置t_order表分库策略（inline-基于行表达式的分片算法） table-strategy: standard: sharding-column: order_id precise-algorithm-class-name: com.zjf.web.config.standard.MyTablePreciseShardingAlgorithm

复制代码

@Component
@Slf4j
public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
/**
* @param tableNames 对应分片库中所有分片表的集合
* @param shardingValue 为分片属性，logicTableName 为逻辑表，columnName 分片键，value 为从 SQL 中解析出来的分片键的值
* @return
*/
@Override
public String doSharding(Collection<String> tableNames, PreciseShardingValue<Integer> shardingValue) {
for (String tableName : tableNames) {
// 取模算法，分片键 % 表数量
String value = String.valueOf(shardingValue.getValue() % tableNames.size() );
log.info("tableName {} value============ {}",tableName,value);
if (tableName.endsWith(value)) {
return tableName;
}
}
throw new IllegalArgumentException("分片失败，tableNames：" + tableNames);
}
}

复制代码