先引入依赖
<properties>
<druid.version>1.2.8</druid.version>
<mybatis.plus.version>3.5.1</mybatis.plus.version>
<mysql.connector.java.version>8.0.30</mysql.connector.java.version>
<shardingsphere.version>5.0.0</shardingsphere.version>
</properties>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
</dependency>
<!-- druid 注意这里不要使用springboot集成的druid,和shardingsphere不兼容-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis.plus.version}</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.connector.java.version}</version>
</dependency>
添加分表配置
#这里的table换成你的表名
spring.shardingsphere.rules.sharding.tables.bit_monitor_gather_history.actual-data-nodes=water-sharding.**table**_$->{2023..2024}_$->{1..12}
#使用表的insert_time进行分片
spring.shardingsphere.rules.sharding.tables.**table**.table-strategy.standard.sharding-column=insert_time
spring.shardingsphere.rules.sharding.tables.bit_monitor_gather_history.table-strategy.standard.sharding-algorithm-name=month-interval
spring.shardingsphere.rules.sharding.tables.bit_monitor_gather_history.key-generate-strategy.column=id
spring.shardingsphere.rules.sharding.tables.bit_monitor_gather_history.key-generate-strategy.key-generator-name=snowflake
#分片算法
spring.shardingsphere.rules.sharding.sharding-algorithms.month-interval.type=INTERVAL
#分片键的时间戳格式
spring.shardingsphere.rules.sharding.sharding-algorithms.month-interval.props.datetime-pattern=yyyy-MM-dd HH:mm:ss
#时间分片下界值
spring.shardingsphere.rules.sharding.sharding-algorithms.month-interval.props.datetime-lower=2023-01-01 00:00:00
#时间分片上界值,这里我没配置默认是当前时间(这里有坑下面说)
#spring.shardingsphere.rules.sharding.sharding-algorithms.month-interval.props.datetime-upper=
#分片键时间间隔,超过该时间间隔将进入下一分片
spring.shardingsphere.rules.sharding.sharding-algorithms.month-interval.props.datetime-interval-amount=1
#分片键时间间隔单位 默认为DAYS
spring.shardingsphere.rules.sharding.sharding-algorithms.month-interval.props.datetime-interval-unit=MONTHS
# 分片名后缀 我的后缀是 table_2023_1的格式
spring.shardingsphere.rules.sharding.sharding-algorithms.month-interval.props.sharding-suffix-pattern=yyyy_M
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.props.sql-show=false
说一说遇到的坑
1.datetime-upper 时间分片上界值
这个配置不设置默认是当前时间,我在测试的时候,用的测试数据大于当前时间,一直报错,最后跟进源码才发现,大于datetime-upper的数据是无法入库的,所以在测试的时候测试数据不要大于这个值。
2.spring.shardingsphere.rules.sharding.tables.bit_monitor_gather_history.actual-data-nodes=water-sharding.table_KaTeX parse error: Expected group after '_' at position 15: ->{2023..2024}_̲->{1…12}
注意这个配置,因为我开始是从10月开始分表,因此只创建了10-12月份的表,在做查询时,单表查询没问题,跨表查询就会报空指针。原因是shardingsphere在初始化数据源的时候会从table_2023_1开始加载逻辑表,但是我没有创建1的这个表,因此就没有加载到逻辑表。