mysql jdbc 分库_ShardingJDBC分库分表配置(示例代码)

依赖包

org.apache.shardingsphere

sharding‐jdbc‐spring‐boot‐starter

4.0.0‐RC1

ShardingJDBC分库分表配置

垂直分表：user->user_info(常用字段)和user_detail(不常用字段)

垂直分库：user_db和product_db

水平分表：user_${1..2}(单表数据最好不超过1000w)

水平分库：db_${1..2}

#端口

server.port = 56082

#应用名

spring.application.name = shopping

#该配置运行环境为本地，dev开发，qas测试，prd生产

spring.profiles.active = local

#项目路径localhost:56082/shopping，thymeleaf无需添加/shopping

server.servlet.context-path = /shopping

#http编码配置UTF-8

spring.http.encoding.enabled = true

spring.http.encoding.charset = UTF-8

spring.http.encoding.force = true

#允许bean配置覆盖原有的bean

spring.main.allow-bean-definition-overriding = true

#mybatis驼峰，a_b -> aB

mybatis.configuration.map-underscore-to-camel-case = true

#sharding-jdbc分片规则

#配置数据源 m0,m1,m2,s0,s1,s2

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?characterEncoding=utf8

spring.shardingsphere.datasource.m0.username = root

spring.shardingsphere.datasource.m0.password = 123456

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?characterEncoding=utf8

spring.shardingsphere.datasource.m1.username = root

spring.shardingsphere.datasource.m1.password = 123456

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?characterEncoding=utf8

spring.shardingsphere.datasource.m2.username = root

spring.shardingsphere.datasource.m2.password = 123456

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?characterEncoding=utf8

spring.shardingsphere.datasource.s0.username = root

spring.shardingsphere.datasource.s0.password = 123456

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?characterEncoding=utf8

spring.shardingsphere.datasource.s1.username = root

spring.shardingsphere.datasource.s1.password = 123456

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?characterEncoding=utf8

spring.shardingsphere.datasource.s2.username = root

spring.shardingsphere.datasource.s2.password = 123456

#主从关系，ds为逻辑名

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

#分库策略(水平)

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分表策略

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分表策略

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分表策略

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为绑定表，绑定表的配置从[0]开始

spring.shardingsphere.sharding.binding-tables[0] = product_info, product_descript

#设置region为广播表(公共表)，每次更新操作会发送至所有数据源

spring.shardingsphere.sharding.broadcast-tables = region

#打开sql输出日志

spring.shardingsphere.props.sql.show = true

#swagger

swagger.enable = true

#日志配置

logging.level.root = info

logging.level.org.springframework.web = info

logging.level.com.itheima.dbsharding = debug