分库分表demo - xinwu-yang/cube-java GitHub Wiki

功能描述

Demo描述：使用uid和sex作为分片键，db1存放男用户且uid取模运算是单数的放在user1表，偶数放在user2表，db2存放女用户且uid取模运算是单数的放在user1表，偶数放在user2表。

db1:

CREATE TABLE `user1` (
  `uid` bigint(32) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user2` (
  `uid` bigint(32) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

db2:

CREATE TABLE `user1` (
  `uid` bigint(32) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user2` (
  `uid` bigint(32) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

yml配置

  shardingsphere:
    datasource:
      record1:
        type: com.zaxxer.hikari.HikariDataSource
        jdbcUrl: jdbc:mysql://${cube.data.host}/record1?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: chengxun
      record2:
        type: com.zaxxer.hikari.HikariDataSource
        jdbcUrl: jdbc:mysql://${cube.data.host}/record2?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
        driver-class-name: com.mysql.cj.jdbc.Driver
        username: root
        password: chengxun
      names: record1,record2
    rules:
      sharding:
        default-database-strategy: # 默认数据库分片策略
          standard:
            sharding-column: sex #分片列（分片键）
            sharding-algorithm-name: database-inline  #分片算法名称
        tables:
          user: #逻辑表名
            #数据节点：格式（数据库+表名） 此处：record1.user1 ,record1.user2,record2.user1,record2.user2
            actual-data-nodes: record$->{1..2}.user$->{1..2}
            table-strategy: #表策略
              standard:
                sharding-column: uid #分片键
                sharding-algorithm-name: user-inline #分片算法名称
            key-generate-strategy: # 分布式序列策略
              column: uid
              key-generator-name: snowflake # 分布式序列算法配置
        sharding-algorithms: # 分布式序列算法配置
          database-inline: # 分片算法名称
            type: INLINE # 分片算法类型
            props: # 分片算法属性配置
              algorithm-expression: record$->{sex%2+1}
          user-inline:
            type: INLINE
            props:
              algorithm-expression: user$->{uid%2+1}
        # 分布式序列算法配置
        key-generators:
          snowflake:
            type: SNOWFLAKE #雪花算法

demo地址 http://125.71.201.11:9004/cube-demo/sharding-example