shardingsphere实操

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
      <version>2.7.12</version>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <version>2.7.12</version>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
      <version>2.7.8</version>
    </dependency>

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.31</version>
    </dependency>

    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.5.3.1</version>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.32</version>
    </dependency>

    <dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
      <version>5.2.0</version>
    </dependency>

2. 建表

2.1 建逻辑表

先建t_order和order_detail逻辑表

CREATE TABLE `t_order` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL COMMENT '名称',
  `type` varchar(255) DEFAULT NULL COMMENT '类型',
  `gmt_create` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `order_detail` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `order_id` bigint(20) NOT NULL COMMENT '订单ID',
  `description` varchar(255) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci
  COMMENT='订单详情表';

2.2 建物理子表

对这两个逻辑表分别建立3个同结构的物理子表

-- 建 3 张物理表，结构与原表完全一致（无数据）
CREATE TABLE t_order_0 LIKE t_order;
CREATE TABLE t_order_1 LIKE t_order;
CREATE TABLE t_order_2 LIKE t_order;

CREATE TABLE order_detail_0 LIKE order_detail;
CREATE TABLE order_detail_1 LIKE order_detail;
CREATE TABLE order_detail_2 LIKE order_detail;

2.3 分配数据

如果原来的逻辑表已经有了很多数据，这时候建立了子表需要分配数据

t_order表对3个子表根据id为3进行取模分配数据

INSERT INTO t_order_0 SELECT * FROM t_order WHERE id % 3 = 0;
INSERT INTO t_order_1 SELECT * FROM t_order WHERE id % 3 = 1;
INSERT INTO t_order_2 SELECT * FROM t_order WHERE id % 3 = 2;

同理, order_detail表对3个子表根据order_id对3进行取模分配数据

INSERT INTO order_detail_0 SELECT * FROM order_detail WHERE order_id % 3 = 0;
INSERT INTO order_detail_1 SELECT * FROM order_detail WHERE order_id % 3 = 1;
INSERT INTO order_detail_2 SELECT * FROM order_detail WHERE order_id % 3 = 2;

最低0.47元/天解锁文章