springboot2 shardingjdbc4.x 分表

一、shardingjdbc3.x

1. 依赖

  <!--分表库分表中间件-->
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-namespace</artifactId>
            <version>3.1.0</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11

2. yml

spring:
  main:
    allow-bean-definition-overriding: true
  application:
    name: mayikt-admin
  # 数据源 mayiktdb
#  按照范围分片
sharding:
  jdbc:
    datasource:
      names: sys-admin
      # 第一个数据库
      sys-admin:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/sys-admin?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: 123456
    # 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略
    config:
      sharding:
        tables:
          sys_user:
            actual-data-nodes: sys-admin.sys_user_$->{1..2}
            table-strategy:
              standard:
                ### where userId
                precise-algorithm-class-name: com.mayikt.main.api.impl.config.MayiktPreciseShardingAlgorithm
                sharding-column: id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

3. 自定义的算法

package com.mayikt.main.api.impl.config;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import lombok.extern.slf4j.Slf4j;

import java.util.Collection;

@Slf4j
public class MayiktPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
    /**
     * 真实表容量建议是为500万，测试暂未为一张表存储5条数据
     */
    private Long tableSize = 5l;
    // 逻辑表名称
    public static final String TABLE_NAME = "sys_user_";

    /**
     * 插入数据 改写表的名称
     * 查询 改写表的名称
     *
     * @param collection
     * @param preciseShardingValue
     * @return
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Integer> preciseShardingValue) {
        Double temp = Double.valueOf(preciseShardingValue.getValue())/ tableSize;
        String tableName = TABLE_NAME + (int) Math.ceil(temp);
        // 分表分库 userid====mysql自带的自增 序列 雪花算法
        log.info("{}>", tableName);
        return tableName;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

二、shardingjdbc4.x

2.1. 依赖

  <!--分表库分表中间件-->
 <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
1
2
3
4
5
6

2.2. yml

spring:
  main:
    allow-bean-definition-overriding: true
  application:
    name: mayikt-admin
  # 数据源 mayiktdb
#  按照范围分片
  shardingsphere:
    datasource:
      names: sys-admin
      sys-admin:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/sys-admin?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
        username: root
        password: 123456
    # 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略
    sharding:
      tables:
        sys_user:
          actual-data-nodes: sys-admin.sys_user_$->{1..2}
          table-strategy:
            standard:
              ### where userId
              precise-algorithm-class-name: com.mayikt.main.api.impl.config.MayiktPreciseShardingAlgorithm
              sharding-column: id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

2.3. 自定义算法

package com.mayikt.main.api.impl.config;

import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;

import java.util.Collection;

@Slf4j
public class MayiktPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
    /**
     * 真实表容量建议是为500万，测试暂未为一张表存储5条数据
     */
    private Long tableSize = 5l;
    // 逻辑表名称
    public static final String TABLE_NAME = "sys_user_";

    /**
     * 插入数据 改写表的名称
     * 查询 改写表的名称
     *
     * @param collection
     * @param preciseShardingValue
     * @return
     */
    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Integer> preciseShardingValue) {
        Double temp = Double.valueOf(preciseShardingValue.getValue())/ tableSize;
        String tableName = TABLE_NAME + (int) Math.ceil(temp);
        // 分表分库 userid====mysql自带的自增 序列 雪花算法
        log.info("{}>", tableName);
        return tableName;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35