shardingsphere实现分库分表-蒲公英云

shardingsphere实现分库分表

傷城~ 2023-06-27 05:43 143阅读 0赞

实验环境

springboot版本：2.2.2.RELEASE

shardingsphere版本：4.0.0-RC3

实验说明：

数据库：demo_ds_0，demo_ds_1

表user,分别在demo_ds_0，demo_ds_1各自建两张user表，user_1和user_2，两个库共四个表

user表结构如下，自行改表名在两个库各执行一次即可。

CREATE TABLE `user` (
  `id` bigint(255) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `uid` varchar(255) DEFAULT NULL,
  `school` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

第一步：引入shardingsphere依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC3</version>
</dependency>
        <!-- for spring namespace -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>4.0.0-RC3</version>
</dependency>

第二步：application.properties配置

#读写分离配置，即一主多从方式配置
debug=false
server.port=8081
server.servlet.context-path=
spring.jackson.serialization.INDENT_OUTPUT=true
spring.profiles.active=
spring.shardingsphere.props.sql.show=true
spring.shardingsphere.datasource.names=ds0,ds1
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://youraddress:3306/demo_ds_0
spring.shardingsphere.datasource.ds0.username=
spring.shardingsphere.datasource.ds0.password=
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://youraddress:3306/demo_ds_1
spring.shardingsphere.datasource.ds1.username=
spring.shardingsphere.datasource.ds1.password=
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user_$->{1..2},ds1.user_$->{1..2}
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=age
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{age % 2+1}
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.user.key-generator.props.worker.id=123