文章目录
0. 项目demo如下
1. 引入依赖
<!--sharding-jdbc: 并不是做分库分表的,而是对分库分表之后数据进行操作-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
2. 编写application.yml文件
server:
port: 8080
spring:
application:
name: shardingDemo
mybatis:
mapper-locations: classpath:mapper/*.xml
type-aliases-package: sharding.mode #别名包
3. 编写application.properties文件
3.1 一主一从 读写分离
#一主一从 读写分离
spring.shardingsphere.datasource.names=m1,s1
#一个实体类对应两张表,覆盖
spring.main.allow-bean-definition-overriding=true
#第一个数据库:主服务器 包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/sharding_userdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=hmj050117
#第二个数据库:从服务器 包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.s1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s1.url=jdbc:mysql://localhost:3307/sharding_userdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=hmj050117
#主从库逻辑定义 定义ds0 为user_db
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s1
#配置表 如果不想指定单独表,就使用注掉的,更多可以参考官网
#https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/configuration/config-spring-boot/
#spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds0.t_user
#配置t_user内主键
spring.shardingsphere.sharding.tables.t_user.key-generator.column=userId
# 指定t_user表里面主键生成策略 SNOWFLAKE 雪花算法 所以数据库主键字段不能为int可以为bigint
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
#打开sql输出日志
spring.shardingsphere.props.sql.show=true
3.2 专库专表
#专库专表 m1,m2是专库专表
spring.shardingsphere.datasource.names=m1,m2
#一个实体类对应两张表,覆盖
spring.main.allow-bean-definition-overriding=true
#第三个数据库:包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/sharding_orderdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=hmj050117
#第四个数据库:包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/sharding_userdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=hmj050117
######################专库专表###################################
#设置数据库的库和表的分布情况
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m$->{2}.t_user
spring.shardingsphere.sharding.tables.t_user.key-generator.column=userId
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=userId
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user
#设置数据库的库和表的分布情况
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m$->{1}.t_order
spring.shardingsphere.sharding.tables.t_order.key-generator.column=orderId
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order
#########################################################
#######################公共表######################
#配置公共表名称
spring.shardingsphere.sharding.broadcast-tables=t_public
#公共表也有主键Id,也可以使用雪花算法
spring.shardingsphere.sharding.tables.t_public.key-generator.column=publicId
spring.shardingsphere.sharding.tables.t_public.key-generator.type=SNOWFLAKE
#########################################################
#打开sql输出日志
spring.shardingsphere.props.sql.show=true
3.3 单库水平分表
#单库水平分表
spring.shardingsphere.datasource.names=db1
#一个实体类对应两张表,覆盖
spring.main.allow-bean-definition-overriding=true
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=hmj050117
#指定apply_info表分布情况,配置表在哪个数据库里面,表名都是什么
# 第一个apply_info 是表规则,因为数据库现在里面有apply_info1 和apply_info2
# 第二个db1.apply_info 代表db1库里面apply_info表 $->是表达式 {1..2} 代表1到2号表 也就是表的分布情况
spring.shardingsphere.sharding.tables.apply_info.actual-data-nodes=db1.apply_info$->{1..2}
#指定apply_info表里面主键名字 因为我数据库里面主键叫id所以这里写id
spring.shardingsphere.sharding.tables.apply_info.key-generator.column=id
# 指定apply_info表里面主键生成策略 SNOWFLAKE 雪花算法
spring.shardingsphere.sharding.tables.apply_info.key-generator.type=SNOWFLAKE
#指定分片策略 约定 id的值为偶数 我就放到 apply_info1里面 如果为奇数我就放到 apply_info2里面
spring.shardingsphere.sharding.tables.apply_info.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.apply_info.table-strategy.inline.algorithm-expression=apply_info$->{id % 2+1}
#打开sql输出日志
spring.shardingsphere.props.sql.show=true
3.4 垂直拆分专库专用
#专库专表 db1 db2 是之前的水平分库分表 m1,m2是专库专表
spring.shardingsphere.datasource.names=db1,db2,m1,m2
#一个实体类对应两张表,覆盖
spring.main.allow-bean-definition-overriding=true
#第一个数据库:包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=hmj050117
#第二个数据库:包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.db2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://localhost:3306/sharding2?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=hmj050117
#第三个数据库:包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/sharding_orderdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=hmj050117
#第四个数据库:包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/sharding_userdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=hmj050117
######################专库专表###################################
#设置数据库的库和表的分布情况
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m$->{2}.t_user
spring.shardingsphere.sharding.tables.t_user.key-generator.column=userId
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=userId
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user
#设置数据库的库和表的分布情况
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m$->{1}.t_order
spring.shardingsphere.sharding.tables.t_order.key-generator.column=orderId
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=orderId
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order
#########################################################
#指定数据库分布情况和数据库里面表的分步情况
#现在里面有 db1,db2数据库 表有apply_info1 apply_info2表
spring.shardingsphere.sharding.tables.apply_info.actual-data-nodes=db$->{1..2}.apply_info$->{1..2}
#指定apply_info表里面主键名字 因为我数据库里面主键叫id所以这里写id
spring.shardingsphere.sharding.tables.apply_info.key-generator.column=id
# 指定apply_info表里面主键生成策略 SNOWFLAKE 雪花算法 所以数据库主键字段不能为int可以为bigint
spring.shardingsphere.sharding.tables.apply_info.key-generator.type=SNOWFLAKE
#指定表分片策略 约定 id的值为偶数 我就放到 apply_info1里面 如果为奇数我就放到 apply_info2里面
spring.shardingsphere.sharding.tables.apply_info.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.apply_info.table-strategy.inline.algorithm-expression=apply_info$->{id % 2+1}
#指定数据库的分片策略 约定 role_id为偶数 就放到 sharding1库,为奇数则放到 sharding2库当中
# 注意 default-databse 意思是针对所有表,这样不够精细,如果我们只想针对特定几个表呢,所以先注掉
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=role_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=db$->{role_id % 2+1}
#可以修改为下面这个
spring.shardingsphere.sharding.tables.apply_info.database-strategy.inline.sharding-column=role_id
spring.shardingsphere.sharding.tables.apply_info.database-strategy.inline.algorithm-expression=db$->{role_id % 2+1}
#打开sql输出日志
spring.shardingsphere.props.sql.show=true
3.5 多库分库分表
#水平分库分表
spring.shardingsphere.datasource.names=db1,db2
#一个实体类对应两张表,覆盖
spring.main.allow-bean-definition-overriding=true
#第一个数据库:包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.db1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db1.url=jdbc:mysql://localhost:3306/sharding1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db1.username=root
spring.shardingsphere.datasource.db1.password=hmj050117
#第二个数据库:包含连接池,驱动,地址,用户名和密码
spring.shardingsphere.datasource.db2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.db2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.db2.url=jdbc:mysql://localhost:3306/sharding2?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.db2.username=root
spring.shardingsphere.datasource.db2.password=hmj050117
#指定数据库分布情况和数据库里面表的分步情况
#现在里面有 db1,db2数据库 表有apply_info1 apply_info2表
spring.shardingsphere.sharding.tables.apply_info.actual-data-nodes=db$->{1..2}.apply_info$->{1..2}
#指定apply_info表里面主键名字 因为我数据库里面主键叫id所以这里写id
spring.shardingsphere.sharding.tables.apply_info.key-generator.column=id
# 指定apply_info表里面主键生成策略 SNOWFLAKE 雪花算法 所以数据库主键字段不能为int可以为bigint
spring.shardingsphere.sharding.tables.apply_info.key-generator.type=SNOWFLAKE
#指定表分片策略 约定 id的值为偶数 我就放到 apply_info1里面 如果为奇数我就放到 apply_info2里面
spring.shardingsphere.sharding.tables.apply_info.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.apply_info.table-strategy.inline.algorithm-expression=apply_info$->{id % 2+1}
#指定数据库的分片策略 约定 role_id为偶数 就放到 sharding1库,为奇数则放到 sharding2库当中
# 注意 default-databse 意思是针对所有表,这样不够精细,如果我们只想针对特定几个表呢,所以先注掉
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=role_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=db$->{role_id % 2+1}
#可以修改为下面这个
spring.shardingsphere.sharding.tables.apply_info.database-strategy.inline.sharding-column=role_id
spring.shardingsphere.sharding.tables.apply_info.database-strategy.inline.algorithm-expression=db$->{role_id % 2+1}
#打开sql输出日志
spring.shardingsphere.props.sql.show=true