shardingJDBC分库和分表

application.yml

spring:
  shardingsphere:
    props:
      sql-show: true
    datasource:
      names: db0,db1
      db0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/order?characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      db1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/booking?characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
    rules:
      sharding:
        key-generators:
          alg-snowflake:
            type: SNOWFLAKE
        sharding-algorithms:
          database-inline:
            type: INLINE
            props:
              algorithm-expression: db$->{id % 2}
          table-inline:
            type: CLASS_BASED
            props:
              strategy: STANDARD
              algorithmClassName: com.common.log.sharding.TableShardingAlgorithm
        tables:
          t_log:
            # 主键：分布式序列
            key-generate-strategy:
              column: id
              key-generator-name: alg-snowflake
            # 节点
            actual-data-nodes: db$->{0..1}.t_log_$->{2025..2026}
            # 分库策略
            database-strategy:
              standard:
                  sharding-column: id
                  sharding-algorithm-name: database-inline
            # 分表策略
            table-strategy:
              standard:
                sharding-column: create_time
                sharding-algorithm-name: table-inline


mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

 

 

application.properties

# 1)===============数据源配置

# 定义多个数据源
spring.shardingsphere.datasource.names = db0,db1

#数据源1
spring.shardingsphere.datasource.db0.type = com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db0.url = jdbc:mysql://127.0.0.1:3306/order?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db0.username = root
spring.shardingsphere.datasource.db0.password = 123456

#数据源2
spring.shardingsphere.datasource.db1.type = com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.db1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.db1.url = jdbc:mysql://127.0.0.1:3306/base?characterEncoding=UTF-8&useSSL=false
spring.shardingsphere.datasource.db1.username = root
spring.shardingsphere.datasource.db1.password = 123456

# 2)===============数据节点配置

# 分库分表规则 : 由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持 inline 表达式。    `$->{begin..end}` 表示范围区间
spring.shardingsphere.rules.sharding.tables.t_log.actual-data-nodes=db$->{0..1}.t_log_$->{2025..2026}

#(3)===============分片策略
# 分库策略：使用 id 字段 路由到 db0/db1
spring.shardingsphere.rules.sharding.tables.t_log.database-strategy.standard.sharding-column=id
spring.shardingsphere.rules.sharding.tables.t_log.database-strategy.standard.sharding-algorithm-name=database-inline
# 分库算法：按 id % 2 分库
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=INLINE
spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithm-expression=db$->{id % 2}
#自定义
#spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.type=CLASS_BASED
#spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.strategy=STANDARD
#spring.shardingsphere.rules.sharding.sharding-algorithms.database-inline.props.algorithmClassName=com.common.log.sharding.DatabaseShardingAlgorithm



# 分表策略：使用 create_time 字段 路由到 t_log_2025/t_log_2026
spring.shardingsphere.rules.sharding.tables.t_log.table-strategy.standard.sharding-column=create_time
spring.shardingsphere.rules.sharding.tables.t_log.table-strategy.standard.sharding-algorithm-name=table-inline
# 分表算法：按年份取模分表  INLINE / CLASS_BASED
#spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.type=INLINE
#spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithm-expression=t_log_$->{String.valueOf(create_time).substring(0,4)}
#自定义
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.type=CLASS_BASED
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.strategy=STANDARD
spring.shardingsphere.rules.sharding.sharding-algorithms.table-inline.props.algorithmClassName=com.common.log.sharding.TableShardingAlgorithm


#4.分布式序列配置
spring.shardingsphere.rules.sharding.tables.t_log.key-generate-strategy.column=id
spring.shardingsphere.rules.sharding.tables.t_log.key-generate-strategy.key-generator-name=alg-snowflake
spring.shardingsphere.rules.sharding.key-generators.alg-snowflake.type=SNOWFLAKE

spring.shardingsphere.props.sql-show=true