# 以下是分片规则配置
# 定义数据源
spring.shardingsphere.datasource.names = m1
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver‐class‐name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/order_db?useUnicode=true
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = root
# 指定t_order表的数据分布情况,配置数据节点
spring.shardingsphere.sharding.tables.t_order.actual‐data‐nodes = m1.t_order_$‐>{1..2}
# 指定t_order表的主键生成策略为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key‐generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key‐generator.type=SNOWFLAKE
# 指定t_order表的分片策略,分片策略包括分片键和分片算法
spring.shardingsphere.sharding.tables.t_order.table‐strategy.inline.sharding‐column = order_id
spring.shardingsphere.sharding.tables.t_order.table‐strategy.inline.algorithm‐expression = t_order_$‐>{order_id % 2 + 1}
# 打开sql输出日志
spring.shardingsphere.props.sql.show = true
多数据源配置
# 定义多个数据源 spring.shardingsphere.datasource.names = m1,m2 spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver‐class‐name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/order_db_1?useUnicode=true spring.shardingsphere.datasource.m1.username = root spring.shardingsphere.datasource.m1.password = root spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m2.driver‐class‐name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.m2.url = jdbc:mysql://localhost:3306/order_db_2?useUnicode=true spring.shardingsphere.datasource.m2.username = root spring.shardingsphere.datasource.m2.password = root ... # 分库策略,以user_id为分片键,分片策略为user_id % 2 + 1,user_id为偶数操作m1数据源,否则操作m2。 spring.shardingsphere.sharding.tables.t_order.database‐strategy.inline.sharding‐column = user_id spring.shardingsphere.sharding.tables.t_order.database‐strategy.inline.algorithm‐expression = m$‐>{user_id % 2 + 1} #分库策略,如何将一个逻辑表映射到多个数据源 spring.shardingsphere.sharding.tables.<逻辑表名称>.database‐strategy.<分片策略>.<分片策略属性名>= #分片策略属性值 #分表策略,如何将一个逻辑表映射为多个实际表 spring.shardingsphere.sharding.tables.<逻辑表名称>.table‐strategy.<分片策略>.<分片策略属性名>= #分片策略属性值
详细配置
# 真实数据源定义 m为主库 s为从库 spring.shardingsphere.datasource.names = m0,m1,m2,s0,s1,s2 spring.shardingsphere.datasource.m0.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m0.driver‐class‐name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.m0.url = jdbc:mysql://localhost:3306/store_db?useUnicode=true spring.shardingsphere.datasource.m0.username = root spring.shardingsphere.datasource.m0.password = root spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m1.driver‐class‐name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.m1.url = jdbc:mysql://localhost:3306/product_db_1? useUnicode=true spring.shardingsphere.datasource.m1.username = root spring.shardingsphere.datasource.m1.password = root spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.m2.driver‐class‐name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.m2.url = jdbc:mysql://localhost:3306/product_db_2? useUnicode=true spring.shardingsphere.datasource.m2.username = root spring.shardingsphere.datasource.m2.password = root spring.shardingsphere.datasource.s0.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.s0.driver‐class‐name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.s0.url = jdbc:mysql://localhost:3307/store_db?useUnicode=true spring.shardingsphere.datasource.s0.username = root spring.shardingsphere.datasource.s0.password = root spring.shardingsphere.datasource.s1.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.s1.driver‐class‐name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.s1.url = jdbc:mysql://localhost:3307/product_db_1? useUnicode=true spring.shardingsphere.datasource.s1.username = root spring.shardingsphere.datasource.s1.password = root spring.shardingsphere.datasource.s2.type = com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.s2.driver‐class‐name = com.mysql.jdbc.Driver spring.shardingsphere.datasource.s2.url = jdbc:mysql://localhost:3307/product_db_2? useUnicode=true spring.shardingsphere.datasource.s2.username = root spring.shardingsphere.datasource.s2.password = root # 主库从库逻辑数据源定义 ds0为store_db ds1为product_db_1 ds2为product_db_2 spring.shardingsphere.sharding.master‐slave‐rules.ds0.master‐data‐source‐name=m0 spring.shardingsphere.sharding.master‐slave‐rules.ds0.slave‐data‐source‐names=s0 spring.shardingsphere.sharding.master‐slave‐rules.ds1.master‐data‐source‐name=m1 spring.shardingsphere.sharding.master‐slave‐rules.ds1.slave‐data‐source‐names=s1 spring.shardingsphere.sharding.master‐slave‐rules.ds2.master‐data‐source‐name=m2 spring.shardingsphere.sharding.master‐slave‐rules.ds2.slave‐data‐source‐names=s2 # 默认分库策略,以store_info_id为分片键,分片策略为store_info_id % 2 + 1,也就是store_info_id为双数的 数据进入ds1,为单数的进入ds2 spring.shardingsphere.sharding.default‐database‐strategy.inline.sharding‐column = store_info_id spring.shardingsphere.sharding.default‐database‐strategy.inline.algorithm‐expression = ds$‐> {store_info_id % 2 + 1} # store_info分表策略,固定分配至ds0的store_info真实表, spring.shardingsphere.sharding.tables.store_info.actual‐data‐nodes = ds$‐>{0}.store_info spring.shardingsphere.sharding.tables.store_info.table‐strategy.inline.sharding‐column = id spring.shardingsphere.sharding.tables.store_info.table‐strategy.inline.algorithm‐expression = store_info # product_info分表策略,分布在ds1,ds2的product_info_1 product_info_2表 ,分片策略为product_info_id % 2 + 1,product_info_id生成为雪花算法,为双数的数据进入product_info_1表,为单数的进入product_info_2 表 spring.shardingsphere.sharding.tables.product_info.actual‐data‐nodes = ds$‐> {1..2}.product_info_$‐>{1..2} spring.shardingsphere.sharding.tables.product_info.table‐strategy.inline.sharding‐column = product_info_id spring.shardingsphere.sharding.tables.product_info.table‐strategy.inline.algorithm‐expression = product_info_$‐>{product_info_id % 2 + 1} spring.shardingsphere.sharding.tables.product_info.key‐generator.column=product_info_id spring.shardingsphere.sharding.tables.product_info.key‐generator.type=SNOWFLAKE # product_descript分表策略,分布在ds1,ds2的product_descript_1 product_descript_2表 ,分片策略为 product_info_id % 2 + 1,id生成为雪花算法,product_info_id为双数的数据进入product_descript_1表,为单 数的进入product_descript_2表 spring.shardingsphere.sharding.tables.product_descript.actual‐data‐nodes = ds$‐> {1..2}.product_descript_$‐>{1..2} spring.shardingsphere.sharding.tables.product_descript.table‐strategy.inline.sharding‐column = product_info_id spring.shardingsphere.sharding.tables.product_descript.table‐strategy.inline.algorithm‐ expression = product_descript_$‐>{product_info_id % 2 + 1} spring.shardingsphere.sharding.tables.product_descript.key‐generator.column=id spring.shardingsphere.sharding.tables.product_descript.key‐generator.type=SNOWFLAKE # 设置product_info,product_descript为绑定表 spring.shardingsphere.sharding.binding‐tables[0] = product_info,product_descript # 设置region为广播表(公共表),每次更新操作会发送至所有数据源 spring.shardingsphere.sharding.broadcast‐tables=region # 打开sql输出日志 spring.shardingsphere.props.sql.show = true