Sharding分表配置素材

 pom依赖

        其中sharding版本高了会出现主键不生成现象。druid引用不可为druid-spring-boot-starter否则启动会冲突报错。

<!--        mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
<!--        druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.23</version>
        </dependency>
<!--        mybatis-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
<!--        sharding-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

application.yml

需要用到mysql8.0，其URL一定要有时区&serverTimezone=GMT%2B8

spring:
  main:
#    允许一个实体类对应多个表
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
#      数据源
      names: m1,m2
#      数据源配置
      m1:
#        连接池
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test02?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: 
      m2:
        #        连接池
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test03?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: 
#    分片
    sharding:
      tables:
#        分片表名
        course:
#          m1 m2数据库中的表1和表2
          actual-data-nodes: m${1..2}.course_${1..2}
#          主键成成策略
          key-generator:
#            绑定字段
            column: cid
#            算法
            type: SNOWFLAKE
#          表策略
          table-strategy:
            inline:
#              分片字段
              sharding-column: cid
#              分片策略表达式：保存到表_模2余1
              algorithm-expression: course_${cid % 2 + 1}
#          数据库策略
          database-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: m${user_id % 2 + 1}
#    打印sql语句
    props:
      sql:
        show: true

垂直切分，专库专表

        另外需要在实体类上加注解 @TableName("t_user") 来指定数据库中实际表名。

spring:
  shardingsphere:
    datasource:
#      数据源
      names: m1,m2,m3
      m3:
        #        连接池
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test04?serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: root
#        垂直切分，专库专表 m3 中的 t_user 表
    sharding:
      tables:
        t_user:
          actual-data-nodes: m${3}.t_user
          key-generator:
            column: user_id
            type: SNOWFLAKE
          table-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: t_user

公共表，多表同步

spring:
  shardingsphere:
    sharding:
      tables:
#      t_dict 公共表配置
        t_dict:
          key-generator:
            column: dict_id
            type: SNOWFLAKE
#     公共表列表
      broadcast-tables: t_dict