springboot集成mysql、mybatis-plus、shardingsphere-jdbc实现读写分离、水平分表：INTERVAL时间范围分片算法

详情请见上一篇读写分离

《springboot集成mysql、mybatis-plus、shardingsphere-jdbc实现读写分离》

警告：一定要先创建好表！！！

提供yml和properties2种配置

yml

mybatis-plus:
  mapper-locations: classpath:mapper/*.xml

spring:
  shardingsphere:
    props:
      sql-show: true
    datasource:
      names: master,slave1
      master:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:mysql://192.168.1.11:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:mysql://192.168.1.22:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
    rules:
      readwrite-splitting:
        data-sources:
          myds:
            static-strategy:
              write-data-source-name: master
              read-data-source-names:
                - slave1
            load-balancer-name: round-robin
        load-balancers:
          round-robin:
            type: ROUND_ROBIN
      sharding:
        tables:
          user:
            #分片数据库表要先创建好
            actual-data-nodes: myds.user_$->{202211..202212}
            table-strategy:
              standard:
                #分片时间字段
                sharding-column: create_time
                #分片时间算法别名
                sharding-algorithm-name: user-interval
            key-generate-strategy:
              column: id
              key-generator-name: snowflake
        key-generators:
          snowflake:
            type: SNOWFLAKE
        sharding-algorithms:
          user-interval:
            #时间范围分片算法和standard里的参数有关，有些算法是不支持某些字段的，所以会报错。例如：你是时间算法，那么分片的字段就不能为id类型为Bigint，必须是时间类型的字段。
            type: INTERVAL
            props:
              #分片键的时间戳格式，必须遵循 Java DateTimeFormatter 的格式。例如：yyyy-MM-dd HH:mm:ss，yyyy-MM-dd 或 HH:mm:ss 等。
              datetime-pattern: "yyyyMM"
              #时间分片下界值：起始时间
              datetime-lower: "202211"
              #时间分片上界值：结束时间
              datetime-upper: "202212"
              #对应数据库表后缀名202211
              sharding-suffix-pattern: "yyyyMM"
              #间隔类型：DAYS、MONTHS
              datetime-interval-unit: "MONTHS"
              #间隔一天/月分表，根据上面datetime-interval-unit的类型去分表
              datetime-interval-amount: 1

properties

#打印sql
spring.shardingsphere.props.sql-show=true
# 配置真实数据源
spring.shardingsphere.datasource.names=master,slave1
# 配置第 1 个数据源
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://192.168.56.11:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.56.22:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456

#写数据源名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.static-strategy.write-data-source-name=master
#读数据源名称，多个从数据源用逗号分隔。如：slave1,slave2
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.static-strategy.read-data-source-names=slave1
#负载均衡算法名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.load-balancer-name=round_robin
#负载均衡算法类型
spring.shardingsphere.rules.readwrite-splitting.load-balancers.round_robin.type=ROUND_ROBIN

#分表
spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=myds.user_$->{202211..202212}
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-column=create_time
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-algorithm-name=user_interval
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.column=id
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.key-generator-name=snowflake
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.sharding-algorithms.user_interval.type=INTERVAL
spring.shardingsphere.rules.sharding.sharding-algorithms.user_interval.props.datetime-pattern=yyyyMM
#不要加双引号不然会报：No active profile set, falling back to 1 default profile: "default"
spring.shardingsphere.rules.sharding.sharding-algorithms.user_interval.props.datetime-lower=202211
#不要加双引号不然会报：No active profile set, falling back to 1 default profile: "default"
spring.shardingsphere.rules.sharding.sharding-algorithms.user_interval.props.datetime-upper=202212
spring.shardingsphere.rules.sharding.sharding-algorithms.user_interval.props.sharding-suffix-pattern=yyyyMM
spring.shardingsphere.rules.sharding.sharding-algorithms.user_interval.props.datetime-interval-unit=MONTHS
spring.shardingsphere.rules.sharding.sharding-algorithms.user_interval.props.datetime-interval-amount=1

测试

数据库表：

数据库表结构：

写入master主库：localhost:88/user/insert

控制台打印如下sql：

2022-11-12 16:52:36.580  INFO 9236 --- [p-nio-88-exec-1] ShardingSphere-SQL                       : Logic SQL: INSERT INTO user  ( id,username,create_time )  VALUES  ( ?,?,? )
2022-11-12 16:52:36.580  INFO 9236 --- [p-nio-88-exec-1] ShardingSphere-SQL                       : SQLStatement: MySQLInsertStatement(super=InsertStatement(super=AbstractSQLStatement(parameterCount=3, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), table=SimpleTableSegment(tableName=TableNameSegment(startIndex=12, stopIndex=15, identifier=IdentifierValue(value=user, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), insertColumns=Optional[InsertColumnsSegment(startIndex=18, stopIndex=46, columns=[ColumnSegment(startIndex=20, stopIndex=21, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=24, stopIndex=31, identifier=IdentifierValue(value=username, quoteCharacter=NONE), owner=Optional.empty), ColumnSegment(startIndex=34, stopIndex=44, identifier=IdentifierValue(value=create_time, quoteCharacter=NONE), owner=Optional.empty)])], insertSelect=Optional.empty, values=[InsertValuesSegment(startIndex=57, stopIndex=67, values=[ParameterMarkerExpressionSegment(startIndex=59, stopIndex=59, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=62, stopIndex=62, parameterMarkerIndex=1, parameterMarkerType=QUESTION, alias=Optional.empty), ParameterMarkerExpressionSegment(startIndex=65, stopIndex=65, parameterMarkerIndex=2, parameterMarkerType=QUESTION, alias=Optional.empty)])]), setAssignment=Optional.empty, onDuplicateKeyColumns=Optional.empty)
2022-11-12 16:52:36.580  INFO 9236 --- [p-nio-88-exec-1] ShardingSphere-SQL                       : Actual SQL: master ::: INSERT INTO user_202211  ( id,username,create_time )  VALUES  (?, ?, ?) ::: [1591353252301623297, Meta39, 2022-11-12T16:52:35.898]

查询从库：
localhost:88/user/1591352421154082817

控制台打印如下sql：

2022-11-12 16:54:26.905  INFO 9236 --- [p-nio-88-exec-7] ShardingSphere-SQL                       : Logic SQL: SELECT id,username,create_time FROM user WHERE id=? 
2022-11-12 16:54:26.905  INFO 9236 --- [p-nio-88-exec-7] ShardingSphere-SQL                       : SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=1, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=50, stopIndex=50, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=29, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=7, stopIndex=8, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=10, stopIndex=17, identifier=IdentifierValue(value=username, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=19, stopIndex=29, identifier=IdentifierValue(value=create_time, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=36, stopIndex=39, identifier=IdentifierValue(value=user, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional[WhereSegment(startIndex=41, stopIndex=50, expr=BinaryOperationExpression(startIndex=47, stopIndex=50, left=ColumnSegment(startIndex=47, stopIndex=48, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), right=ParameterMarkerExpressionSegment(startIndex=50, stopIndex=50, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), operator==, text=id=?))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combine=Optional.empty), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-11-12 16:54:26.905  INFO 9236 --- [p-nio-88-exec-7] ShardingSphere-SQL                       : Actual SQL: slave1 ::: SELECT id,username,create_time FROM user_202211 WHERE id=?  UNION ALL SELECT id,username,create_time FROM user_202212 WHERE id=?  ::: [1591352421154082817, 1591352421154082817]