本文共 3555 字，大约阅读时间需要 11 分钟。

1 实现垂直分库，专库专表

实现目标，用户查询用户信息的时候，只操作user_db中t_user表

2 创建数据库

CREATE SCHEMA `userdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

3 创建数据表

CREATE TABLE `userdb`.`t_user` (  `user_id` BIGINT NOT NULL,  `user_name` VARCHAR(45) NOT NULL,  `u_status` VARCHAR(45) NOT NULL,  PRIMARY KEY (`user_id`));

4 创建po

package com.ccb.sharding.po;import com.baomidou.mybatisplus.annotation.TableName;@TableName("t_user")public class User {       private Long userId;    private String userName;    private String uStatus;    public Long getUserId() {           return userId;    }    public void setUserId(Long userId) {           this.userId = userId;    }    public String getUserName() {           return userName;    }    public void setUserName(String userName) {           this.userName = userName;    }    public String getuStatus() {           return uStatus;    }    public void setuStatus(String uStatus) {           this.uStatus = uStatus;    }    @Override    public String toString() {           return "User{" +                "userId=" + userId +                ", userName='" + userName + '\'' +                ", uStatus='" + uStatus + '\'' +                '}';    }}

5 创建 UserMapper 接口

package com.ccb.sharding.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.ccb.sharding.po.User;import org.springframework.stereotype.Repository;@Repositorypublic interface UserMapper extends BaseMapper<User> {   }

6 创建application.properties配置分片策略 专库专表

# sharding-JDBC分片策略（垂直分库）# 配置数据源，给数据源命名# 垂直分库，配置数据源spring.shardingsphere.datasource.names=ds0# 配置数据源具体内容，连接池、驱动、地址、用户名和密码spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/userdb?serverTimezone=GMT%2B8spring.shardingsphere.datasource.ds0.username=rootspring.shardingsphere.datasource.ds0.password=chengwen# 一个实体类对应两张表，覆盖spring.main.allow-bean-definition-overriding=true# 配置user_db数据库里面t_user 专库专表spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=ds$->{0}.t_user# 配置user_db数据库里面 t_user 表里面主键 user_id 生成策略 SNOWFLAKE 雪花算法spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_idspring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE# 指定表分片策略 约定user_id的值添加到t_user表spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_idspring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user# 打印sql输出日志spring.shardingsphere.props.sql.show=true

7 Test

package com.ccb.sharding;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.ccb.sharding.mapper.UserMapper;import com.ccb.sharding.po.User;import org.junit.jupiter.api.Test;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;@SpringBootTestclass ShardingApplicationTests {       @Autowired    CourseMapper courseMapper;    @Autowired    UserMapper userMapper;    // ================= 测试水平分表 ======================    @Test    public void addUser() {           User user = new User();        user.setUserName("Tom");        user.setuStatus("A");        userMapper.insert(user);    }    @Test    public void getUser(){           QueryWrapper queryWrapper = new QueryWrapper();        queryWrapper.eq("user_id",482828647158251521L);        User cuser = userMapper.selectOne(queryWrapper);        System.out.println(cuser);    }}

8 Test result

专库专表 新增
专库专表 查询