Mysql分表springBoot使用shardingsphere -- 分库分表

yaml配置文件

spring:
  shardingsphere:
    datasource:
      names: xz-market,xz-farm
      xz-market:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1/xz-market?useAffectedRows=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
        username: 数据库用户名
        password: 数据库用户密码
      xz-farm:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1/xz-farm?useAffectedRows=true&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
        username: 数据库用户名
        password: 数据库用户密码
    sharding:
      default-data-source-name: xz-market --库名
      tables:
        reward_task_record:  --表名
          actual-data-nodes: xz-market.reward_task_record_$->{0..15} --表名分为16张表
          table-strategy:
            inline:
              sharding-column: userId  --根据表中字段userId分表
              algorithm-expression: reward_task_record_$->{Math.abs(userId.hashCode().intdiv(2) % 16)}  --userId hash
          key-generator:
            column: id  --主键ID生成
            type: SNOWFLAKE
            props:
              worker.id: ${workerId} --主键ID生成会重复所以生成时加参数，下方代码获取
        invite_friends_record:
          actual-data-nodes: xz-market.invite_friends_record_$->{0..15}
          table-strategy:
            inline:
              sharding-column: userId
              algorithm-expression: invite_friends_record_$->{Math.abs(userId.hashCode().intdiv(2) % 16)}
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker.id: ${workerId}
        follow_record:
          actual-data-nodes: xz-market.follow_record_$->{0..15}
          table-strategy:
            inline:
              sharding-column: userId
              algorithm-expression: follow_record_$->{Math.abs(userId.hashCode().intdiv(2) % 16)}
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker.id: ${workerId}
        user_animal_stat:
          actual-data-nodes: xz-farm.user_animal_stat_$->{0..15}
          table-strategy:
            inline:
              sharding-column: userId
              algorithm-expression: user_animal_stat_$->{Math.abs(userId.hashCode().intdiv(2) % 16)}
          key-generator:
            column: id
            type: SNOWFLAKE
            props:
              worker.id: ${workerId}
    props:
      sql:
        show: true

生成workerId

import cn.hutool.core.net.NetUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Configuration;

@Configuration
@Slf4j
public class WorkerIdConfiguration {

    private static String WORKER_ID = "workerId";

    static {
        long workerId = 0;
        try {
            String hostAddress = NetUtil.getLocalhostStr();
            log.info("============= 当前机器 hostAddress: {} =============", hostAddress);
            int[] ints = org.apache.commons.lang3.StringUtils.toCodePoints(hostAddress);
            int sums = 0;
            int[] var3 = ints;
            int var4 = ints.length;

            for(int var5 = 0; var5 < var4; ++var5) {
                int b = var3[var5];
                sums += b;
            }

            workerId = (long)(sums % 32);
            log.info("============== 当前机器 workerId：{} =============", workerId);
        } catch (Exception e) {
            log.warn("获取机器 workerId 失败", e);
        }
        System.setProperty(WORKER_ID, String.valueOf(workerId));
    }
}

使用 sql语句依然按照未分表的表名插入 注意所有curd操作必须带有分表的字段为参数

 	    public static final String TABLE = "invite_friends_record";

        public static String insert() {
            return "INSERT INTO " + TABLE + " (`userId`, `friendId`, `appName`)"
                    + " VALUES (#{userId}, #{friendId}, #{appName})"
                    + " ON DUPLICATE KEY UPDATE"
                    + " `id` = `id`";
        }