Sharding JDBC分庫分表怎么配置

這篇文章主要介紹“Sharding JDBC分庫分表怎么配置”，在日常操作中，相信很多人在Sharding JDBC分庫分表怎么配置問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Sharding JDBC分庫分表怎么配置”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

分庫分表配置

分庫需要兩個以上數據源，這里配置test0,test1兩個數據庫

spring.shardingsphere.datasource.names=test0,test1
#test0
spring.shardingsphere.datasource.test0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.test0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.test0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test0
spring.shardingsphere.datasource.test0.username=
spring.shardingsphere.datasource.test0.password=
#test1
spring.shardingsphere.datasource.test1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test1
spring.shardingsphere.datasource.test1.username=
spring.shardingsphere.datasource.test1.password=

配置分庫策略 按照user_id % 2 進行分庫

# 指定分片列名稱的 shardingColumn
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
# 指定分片算法行表達式的 algorithmExpression
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=test$->{user_id % 2}

配置綁定表和廣播表

# 設置綁定表
spring.shardingsphere.sharding.binding-tables[0]=health_record,health_task
# 設置廣播表
spring.shardingsphere.sharding.broadcast-tables[0]=health_level

設置分表策略，按照 record_id % 2 進行分表

# user 如果不加這個，數據會隨機插入數據庫中
spring.shardingsphere.sharding.tables.user.actual-data-nodes=test$->{[0,1]}.user
#路由到 test0 否則會隨意添加到兩個數據庫中
spring.shardingsphere.sharding.tables.other_table.actual-data-nodes=test$->{0}.other_table
# health_record
spring.shardingsphere.sharding.tables.health_record.actual-data-nodes=test$->{0..1}.health_record$->{0..1}
spring.shardingsphere.sharding.tables.health_record.table-strategy.inline.algorithm-expression=health_record$->{record_id % 2}
spring.shardingsphere.sharding.tables.health_record.table-strategy.inline.sharding-column=record_id
spring.shardingsphere.sharding.tables.health_record.key-generator.column=record_id
spring.shardingsphere.sharding.tables.health_record.key-generator.type=SNOWFLAKE
# health_task
spring.shardingsphere.sharding.tables.health_task.actual-data-nodes=test$->{0..1}.health_task$->{0..1}
spring.shardingsphere.sharding.tables.health_task.table-strategy.inline.algorithm-expression=health_task$->{record_id % 2}
spring.shardingsphere.sharding.tables.health_task.table-strategy.inline.sharding-column=record_id
spring.shardingsphere.sharding.tables.health_task.key-generator.column=task_id
spring.shardingsphere.sharding.tables.health_task.key-generator.type=SNOWFLAKE

完整配置

server.port=8080
#打印sql
spring.shardingsphere.props.sql.show=true
#配置數據源
spring.shardingsphere.datasource.names=test0,test1
#test0
spring.shardingsphere.datasource.test0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.test0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.test0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test0
spring.shardingsphere.datasource.test0.username=devadmin
spring.shardingsphere.datasource.test0.password=
#test1
spring.shardingsphere.datasource.test1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test1
spring.shardingsphere.datasource.test1.username=devadmin
spring.shardingsphere.datasource.test1.password=
# 指定分片列名稱的 shardingColumn
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
# 指定分片算法行表達式的 algorithmExpression
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=test$->{user_id % 2}
# 設置綁定表
spring.shardingsphere.sharding.binding-tables[0]=health_record,health_task
# 設置廣播表
spring.shardingsphere.sharding.broadcast-tables[0]=health_level
# user 如果不加這個，數據會隨機插入數據庫中
spring.shardingsphere.sharding.tables.user.actual-data-nodes=test$->{[0,1]}.user
#路由到 test0 否則會隨意添加到兩個數據庫中
spring.shardingsphere.sharding.tables.other_table.actual-data-nodes=test$->{0}.other_table
# health_record
spring.shardingsphere.sharding.tables.health_record.actual-data-nodes=test$->{0..1}.health_record$->{0..1}
spring.shardingsphere.sharding.tables.health_record.table-strategy.inline.algorithm-expression=health_record$->{record_id % 2}
spring.shardingsphere.sharding.tables.health_record.table-strategy.inline.sharding-column=record_id
spring.shardingsphere.sharding.tables.health_record.key-generator.column=record_id
spring.shardingsphere.sharding.tables.health_record.key-generator.type=SNOWFLAKE
# health_task
spring.shardingsphere.sharding.tables.health_task.actual-data-nodes=test$->{0..1}.health_task$->{0..1}
spring.shardingsphere.sharding.tables.health_task.table-strategy.inline.algorithm-expression=health_task$->{record_id % 2}
spring.shardingsphere.sharding.tables.health_task.table-strategy.inline.sharding-column=record_id
spring.shardingsphere.sharding.tables.health_task.key-generator.column=task_id
spring.shardingsphere.sharding.tables.health_task.key-generator.type=SNOWFLAKE

數據庫

test0 test1 兩個數據庫的結構如下:

執行測試方法之后，數據庫的數據如下：

health_level 是廣播表，所以test0、test1中的數據是一樣的

user 表中的數據分布。test0中user_id 都為偶數，test1中user_id都為奇數。

testx_health_record0 、testx_health_task0 中 record_id 都為偶數，testx_health_record1、testx_health_task11中record_ir都為奇數。（我們只截取healt_record表，health_task表的數據是一樣的）

test0 中 health_record0 和 health_record1

test1 中 health_record0 和 health_record1

到此，關于“Sharding JDBC分庫分表怎么配置”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！