Mysql分库分表--sharding-jdbc

Alan0517

已于 2023-06-06 11:35:21 修改

阅读量386

点赞数

文章标签： mysql

于 2021-04-27 22:57:53 首次发布

本文链接：https://blog.csdn.net/Hmj050117/article/details/116212136

版权

Others其他-基础篇专栏收录该内容

22 篇文章 0 订阅

订阅专栏

文章目录

0. 项目demo如下
1. 引入依赖
2. 编写application.yml文件
3. 编写application.properties文件

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