数据库分库分表（六）shardingjdbc垂直分库+水平分库+水平分表demo

最新推荐文章于 2024-04-19 19:02:51 发布

w_t_y_y

最新推荐文章于 2024-04-19 19:02:51 发布

阅读量965

点赞数

分类专栏： # ORM框架Mybatis 文章标签：数据库 java database

本文链接：https://blog.youkuaiyun.com/w_t_y_y/article/details/122106739

版权

ORM框架Mybatis 专栏收录该内容

38 篇文章

订阅专栏

本文介绍了一种基于年份和月份进行数据库和表水平拆分的策略，并详细配置了ShardingSphere来实现这一目标。通过具体示例展示了如何设置多个数据库、使用不同的分片策略，以及如何配置MyBatis映射文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

即有多个数据库，每个数据库有不同的表，如在前面订单按年分库按月分表的基建上，还有一个user库存放t_user表：

1、代码：

server.context-path=/sharding
server.port=6666
mybatis.mapper-locations=classpath*:Mapper/*Mapper.xml


# 配置数据源
spring.shardingsphere.datasource.names=sharding_demo_2020,sharding_demo_2021,dbu0
#配置sharding_demo_2020
spring.shardingsphere.datasource.sharding_demo_2020.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding_demo_2020.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding_demo_2020.url=jdbc:mysql://localhost:3306/sharding_demo_2020?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.sharding_demo_2020.username=root
spring.shardingsphere.datasource.sharding_demo_2020.password=******
#配置sharding_demo_2021
spring.shardingsphere.datasource.sharding_demo_2021.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.sharding_demo_2021.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.sharding_demo_2021.url=jdbc:mysql://localhost:3306/sharding_demo_2021?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.sharding_demo_2021.username=root
spring.shardingsphere.datasource.sharding_demo_2021.password=******
#配置dbu0
spring.shardingsphere.datasource.dbu0.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.dbu0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.dbu0.url=jdbc:mysql://localhost:3306/sharding_demo_user?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.dbu0.username=root
spring.shardingsphere.datasource.dbu0.password=******
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

#配置t_order库、表的分布，配置主键id为雪花算法自增长
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=sharding_demo_$->{2020..2021}.t_order_$->{1..12}
spring.shardingsphere.sharding.tables.t_order.key-generator.column=id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
#配置t_order表的分库策略,按年分库
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.preciseAlgorithmClassName=com.demo.config.OrderDbPreciseShardingConfig
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.rangeAlgorithmClassName=com.demo.config.OrderDbRangeShardingConfig
spring.shardingsphere.sharding.tables.t_order.database-strategy.standard.sharding-column=order_date
#配置t_order表的分表策略,按月分表
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.preciseAlgorithmClassName=com.demo.config.OrderTablePreciseShardingConfig
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.rangeAlgorithmClassName=com.demo.config.OrderTableRangeShardingConfig
spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=order_date

#配置垂直分库t_user的策略
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=dbu0.t_user
spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=id
#由于只有一张表，因此在此直接写表明，不需要像水平分多个表那样写策略
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user

2、测试：

（1）新增用户：

<insert id="add" parameterType="com.demo.model.UserDTO">
        insert into t_user(user_name,age) values (#{userName},#{age})
    </insert>

2021-12-23 14:31:33.405  INFO 10476 --- [nio-6666-exec-1] ShardingSphere-SQL                       : Actual SQL: dbu0 ::: insert into t_user (user_name, age, id) VALUES (?, ?, ?) ::: [张三1, 1, 680783745912930305]