Sharding-JDBC 实现垂直分库水平分表

1、需求分析

​

2、创建数据库和表

CREATE TABLE `user_db`.`t_user_0`  (
  `user_id` bigint(20) NOT NULL,
  `username` varchar(50) NULL,
  `ustatus` varchar(50) NULL,
  PRIMARY KEY (`user_id`)
);

CREATE TABLE `user_db`.`t_user_1`  (
  `user_id` bigint(20) NOT NULL,
  `username` varchar(50) NULL,
  `ustatus` varchar(50) NULL,
  PRIMARY KEY (`user_id`)
);

3、编写操作代码
（1）创建 user 实体类和 mapper

package com.weianlai.shardingjdbc.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName(value = "t_user")
public class User {
    private Long userId;
    private String username;
    private String ustatus;
}

package com.weianlai.shardingjdbc.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.weianlai.shardingjdbc.entity.User;

public interface UserMapper extends BaseMapper<User> {
}

（2）配置垂直分库策略

 

# 水平分库，配置两个数据源
spring.shardingsphere.datasource.names=m0
# 一个实体类对应两张表，覆盖
spring.main.allow-bean-definition-overriding=true

#配置数据源
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root

# 配置 user_db 数据库里面 t_user 专库专表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m$->{0}.t_user_$->{0..1}

# 指定 course 表里面主键 cid 生成策略 SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

# 指定表分片策略 约定 cid 值偶数添加到 course_1 表，如果 cid 是奇数添加到course_2 表
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user_$->{user_id % 2}

# 打开 sql 输出日志
spring.shardingsphere.props.sql.show=true

（3）编写测试代码

    //添加操作
    @Test
    public void addUserDb() {
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setUsername("lucy");
            user.setUstatus("a");
            userMapper.insert(user);
        }
    }