Shulou(Shulou.com)06/01 Report--

这篇文章主要介绍"Sharding JDBC分库分表怎么配置"，在日常操作中，相信很多人在Sharding JDBC分库分表怎么配置问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答"Sharding JDBC分库分表怎么配置"的疑惑有所帮助!接下来，请跟着小编一起来学习吧!

分库分表配置

分库需要两个以上数据源，这里配置test0,test1两个数据库

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

配置分库策略 按照user_id % 2 进行分库

# 指定分片列名称的 shardingColumnspring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id# 指定分片算法行表达式的 algorithmExpressionspring.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_recordspring.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_idspring.shardingsphere.sharding.tables.health_record.key-generator.column=record_idspring.shardingsphere.sharding.tables.health_record.key-generator.type=SNOWFLAKE# health_taskspring.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_idspring.shardingsphere.sharding.tables.health_task.key-generator.column=task_idspring.shardingsphere.sharding.tables.health_task.key-generator.type=SNOWFLAKE

完整配置

server.port=8080#打印sqlspring.shardingsphere.props.sql.show=true#配置数据源spring.shardingsphere.datasource.names=test0,test1#test0spring.shardingsphere.datasource.test0.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.test0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.test0.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test0spring.shardingsphere.datasource.test0.username=devadminspring.shardingsphere.datasource.test0.password=#test1spring.shardingsphere.datasource.test1.type=com.zaxxer.hikari.HikariDataSourcespring.shardingsphere.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3306/test1spring.shardingsphere.datasource.test1.username=devadminspring.shardingsphere.datasource.test1.password=# 指定分片列名称的 shardingColumnspring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id# 指定分片算法行表达式的 algorithmExpressionspring.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_recordspring.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_idspring.shardingsphere.sharding.tables.health_record.key-generator.column=record_idspring.shardingsphere.sharding.tables.health_record.key-generator.type=SNOWFLAKE# health_taskspring.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_idspring.shardingsphere.sharding.tables.health_task.key-generator.column=task_idspring.shardingsphere.sharding.tables.health_task.key-generator.type=SNOWFLAKE数据库

test0 test1 两个数据库的结构如下:

After executing the test method, the data in the database is as follows:

health_level is a broadcast table, so the data in test0 and test1 are the same

Distribution of data in user table. User_id is even in test0 and odd in test1.

record_id in testx_health_record0 and testx_health_task0 is even, record_ir in testx_health_record1 and testx_health_task11 is odd. (We only intercept the health alt_record table, the data in the health_task table is the same)

health_record0 and health_record1 in test0

health_record0 and health_record1 in test1

At this point, on the "Sharding JDBC sub-table how to configure" the study is over, I hope to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.