ShardingSphere-JDBC垂直分片

docker run -d \
-p 3301:3306 \
-v /server/user/conf:/etc/mysql/conf.d \
-v /server/user/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-user \
mysql:8.0.29

#进入容器：
docker exec -it server-user env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码插件
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

CREATE DATABASE db_user;
USE db_user;
CREATE TABLE t_user (
 id BIGINT AUTO_INCREMENT,
 uname VARCHAR(30),
 PRIMARY KEY (id)
);

docker run -d \
-p 3302:3306 \
-v /server/order/conf:/etc/mysql/conf.d \
-v /server/order/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name server-order \
mysql:8.0.29

#进入容器：
docker exec -it server-order env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码插件
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

CREATE DATABASE db_order;
USE db_order;
CREATE TABLE t_order (
  id BIGINT AUTO_INCREMENT,
  order_no VARCHAR(30),
  user_id BIGINT,
  amount DECIMAL(10,2),
  PRIMARY KEY(id) 
);

@TableName("t_order")//逻辑表名
@Data
public class Order {
    //当没有配置shardingsphere-jdbc的分布式序列时，自动依赖数据库的主键自增策略
    @TableId(type = IdType.AUTO)
    private Long id;
    //自动映射数据库表中的order_no字段
    private String orderNo;
    //user_id
    private Long userId;
    private BigDecimal amount;
}

@TableName("t_user")
@Data
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String uname;
}

@Mapper
public interface OrderMapper extends BaseMapper<Order> {
}

@Mapper
public interface UserMapper extends BaseMapper<User> {
}

# 垂直分片
spring:
  shardingsphere:
    datasource:
      # 配置真实数据源
      names: server-order,server-user
      # 配置第 1 个数据源
      server-user:
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.1:3301/db_user
        password: 123456
        type: com.zaxxer.hikari.HikariDataSource
        username: root
      # 配置第 2 个数据源
      server-order:
        driver-class-name: com.mysql.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.1:3302/db_order
        password: 123456
        type: com.zaxxer.hikari.HikariDataSource
        username: root
    mode:
      # 内存模式
      type: Memory
    # 打印sql
    props:
      sql-show: true
    rules:
      # 标准分片表配置
      # 由数据源名 + 表名组成，以小数点分隔。多个表以逗号分隔，支持 inline 表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点，用于广播表（即每个库中都需要一个同样的表用于关联查询，多为字典表）或只分库不分表且所有库的表结构完全一致的情况
      sharding:
        tables:
          t_user:
            actual-data-nodes: server-user.t_user
          t_order:
            actual-data-nodes: server-order.t_order

@RestController
@RequestMapping("/order")
public class OrderController {
    @Resource
    private UserMapper userMapper;
    @Resource
    private OrderMapper orderMapper;

    /**
     * 垂直分片：插入数据测试
     * @author: yh
     * @date: 2022/9/18
     */
    @GetMapping(value = "/add")
    public void testInsertOrderAndUser(){
        User user = new User();
        user.setUname("小明");
        userMapper.insert(user);

        Order order = new Order();
        order.setOrderNo("ATGUIGU001");
        order.setUserId(user.getId());
        order.setAmount(new BigDecimal(100));
        orderMapper.insert(order);
    }

    /**
     * 垂直分片：查询数据测试
     * @author: yh
     * @date: 2022/9/18
     */
    @GetMapping("/get")
    public void testSelectFromOrderAndUser(){
        User user = userMapper.selectById(1L);
        Order order = orderMapper.selectById(1L);
    }
}

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';