## 模块 1：RANGE、RANGE COLUMN 和 RANGE INTERVAL 分区表

+ 在本练习中，你将创建和管理 Range 分区、Range COLUMNS 分区和 Range INTERVAL 分区表

### 步骤 1：打开终端，SSH 登录到 TiUP 控制机

+ 点击这里 - [终端 1](ssh://monitor1-ssh#1) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。

### 步骤 2. 连接到 TiDB 并创建非分区表 `emp`

   ```bash
   mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
   ```

   ```sql
   use test;
   ```

   ```output
   Database changed
   ```

   ```sql
   CREATE TABLE emp (
    id INT PRIMARY KEY,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '2018-01-01',
    dept_code INT NOT NULL,
    region_id INT NOT NULL
   );
   INSERT INTO emp VALUES(1, 'Chars', 'Smith', '2018-05-08', 1, 801);
   INSERT INTO emp VALUES(2, 'Mott', 'Johnson', '2018-06-01', 2, 401);
   INSERT INTO emp VALUES(3, 'Lundeen', 'Williams', '2018-06-22', 3, 201);
   INSERT INTO emp VALUES(4, 'Broe', 'Brown', '2018-09-22', 4, 101);
   INSERT INTO emp VALUES(5, 'Layla', 'Jones', '2018-10-12', 5, 301);
   INSERT INTO emp VALUES(6, 'Tom', 'Garcia', '2019-01-30', 2, 501);
   INSERT INTO emp VALUES(7, 'Donald', 'Miller', '2019-02-21', 2, 401);
   INSERT INTO emp VALUES(8, 'Kevin', 'Davis', '2019-03-09', 3, 301);
   INSERT INTO emp VALUES(9, 'Ken', 'Rodriguez', '2020-11-17', 1, 201);
   INSERT INTO emp VALUES(10, 'Bill', 'Martinez', '2020-11-19', 2, 201);
   INSERT INTO emp VALUES(11, 'Jack', 'Hernandez', '2020-12-01', 4, 201);
   INSERT INTO emp VALUES(12, 'Fran', 'Jones', '2020-12-01', 4, 201);
   INSERT INTO emp VALUES(13, 'Mark', 'Wilson', '2021-07-08', 4, 101);
   INSERT INTO emp VALUES(14, 'Bob', 'Thomas', '2021-07-08', 4, 101);
   INSERT INTO emp VALUES(15, 'Jeff', 'Taylor', '2022-04-03', 5, 101);
   INSERT INTO emp VALUES(16, 'Jim', 'Lee', '2022-04-04', 2, 201);
   INSERT INTO emp VALUES(17, 'Tim', 'Anderson', '2022-05-10', 2, 301);
   INSERT INTO emp VALUES(18, 'Mike', 'Jackson', '2022-05-10', 3, 401);
   INSERT INTO emp VALUES(19, 'Benjamin', 'Thompson', '2022-09-24', 1, 501);
   INSERT INTO emp VALUES(20, 'Charlotte', 'Clark', '2022-09-24', 1, 801);
   INSERT INTO emp VALUES(21, 'Mason', 'Jones', '2022-10-27', 1, 801);
   INSERT INTO emp VALUES(22, 'Emma', 'Robinson', '2023-06-15', 2, 301);
   INSERT INTO emp VALUES(23, 'Ava', 'Adams', '2023-07-03', 2, 301);
   INSERT INTO emp VALUES(24, 'Olivia', 'Lewis', '2023-08-01', 5, 701);
   INSERT INTO emp VALUES(25, 'Ava', 'Phillips', '2023-09-02', 3, 701);
   INSERT INTO emp VALUES(26, 'Isabella', 'Morgan', '2023-09-03', 2, 101);
   INSERT INTO emp VALUES(27, 'Donald', 'Edwards', '2023-09-03', 4, 101);
   INSERT INTO emp VALUES(28, 'Somes', 'Alexander', '2023-12-10', 1, 101);
   INSERT INTO emp VALUES(29, 'Crabb', 'Ford', '2024-06-30', 2, 401);
   INSERT INTO emp VALUES(30, 'Sisika', 'Williams', '2024-06-30', 2, 401);
   INSERT INTO emp VALUES(31, 'Noah', 'Anderson', '2024-07-02', 4, 101);
   INSERT INTO emp VALUES(32, 'Jacob', 'Martinez', '2025-01-11', 4, 501);
   INSERT INTO emp VALUES(33, 'Kim', 'Miller', '2025-01-30', 1, 401);
   INSERT INTO emp VALUES(34, 'Jim', 'Smith', '2025-04-24', 2, 501);
   INSERT INTO emp VALUES(35, 'Sam', 'Martinez', '2025-05-22', 3, 101);
   INSERT INTO emp VALUES(36, 'Candy', 'Morgan', '2025-08-03', 1, 301);
   SELECT COUNT(*) from emp;
   ```

   ```output
   +----------+
   | COUNT(*) |
   +----------+
   |       36 |
   +----------+
   1 row in set (0.00 sec)
   ```

### 步骤 3. 创建无主键的 Range 分区表 `employees1`

   ```sql
   CREATE TABLE employees1 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '2018-01-01',
    dept_code INT NOT NULL,
    region_id INT NOT NULL
   )
   PARTITION BY RANGE (hired) (
      PARTITION p0 VALUES LESS THAN ('2019-01-01'),
      PARTITION p1 VALUES LESS THAN ('2020-01-01'),
      PARTITION p2 VALUES LESS THAN ('2021-01-01'),
      PARTITION p3 VALUES LESS THAN ('2022-01-01'),
      PARTITION p4 VALUES LESS THAN ('2023-01-01'),
      PARTITION p5 VALUES LESS THAN ('2024-01-01'),
      PARTITION p6 VALUES LESS THAN ('2025-01-01'),
      PARTITION p7 VALUES LESS THAN ('2026-01-01'),
      PARTITION p8 VALUES LESS THAN MAXVALUE
   );
   ```

   ```output
   ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT
   ```

+ 出现错误 `ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT` 的原因是，`PARTITION BY RANGE` 后的键必须为 `INT` 类型。我们需要相应地修改 SQL 语句。

   ```sql
   CREATE TABLE employees1 (
      id INT NOT NULL,
      fname VARCHAR(30),
      lname VARCHAR(30),
      hired DATE NOT NULL DEFAULT '2018-01-01',
      dept_code INT NOT NULL,
      region_id INT NOT NULL
   )
   PARTITION BY RANGE (YEAR(hired)) (
      PARTITION p0 VALUES LESS THAN (2019),
      PARTITION p1 VALUES LESS THAN (2020),
      PARTITION p2 VALUES LESS THAN (2021),
      PARTITION p3 VALUES LESS THAN (2022),
      PARTITION p4 VALUES LESS THAN (2023),
      PARTITION p5 VALUES LESS THAN (2024),
      PARTITION p6 VALUES LESS THAN (2025),
      PARTITION p7 VALUES LESS THAN (2026),
      PARTITION p8 VALUES LESS THAN MAXVALUE
   );
   ```

   ```output
   Query OK, 0 rows affected (0.13 sec)
   ```

### 步骤 4. 创建无主键的 Range COLUMNS 分区表 `employees2`

   ```sql
   CREATE TABLE employees2 (
      id INT NOT NULL,
      fname VARCHAR(30),
      lname VARCHAR(30),
      hired DATE NOT NULL DEFAULT '2018-01-01',
      dept_code INT NOT NULL,
      region_id INT NOT NULL
   )
   PARTITION BY RANGE COLUMNS(hired) (
      PARTITION p0 VALUES LESS THAN ('2019-01-01'),
      PARTITION p1 VALUES LESS THAN ('2020-01-01'),
      PARTITION p2 VALUES LESS THAN ('2021-01-01'),
      PARTITION p3 VALUES LESS THAN ('2022-01-01'),
      PARTITION p4 VALUES LESS THAN ('2023-01-01'),
      PARTITION p5 VALUES LESS THAN ('2024-01-01'),
      PARTITION p6 VALUES LESS THAN ('2025-01-01'),
      PARTITION p7 VALUES LESS THAN ('2026-01-01'),
      PARTITION p8 VALUES LESS THAN MAXVALUE
   );
   ```

   ```output
   Query OK, 0 rows affected (0.13 sec)
   ```

+ 分区列的数据类型可以是整数、字符串 (CHAR 或 VARCHAR)、DATE 和 DATETIME。不支持 `YEAR()`等表达式。你可以使用一列或多列作为分区键。

### 步骤 5. 创建有主键的 Range 分区表 `employees3`

   ```sql
   CREATE TABLE employees3 (
      id INT PRIMARY KEY,
      fname VARCHAR(30),
      lname VARCHAR(30),
      hired DATE NOT NULL DEFAULT '2018-01-01',
      dept_code INT NOT NULL,
      region_id INT NOT NULL
   )
   PARTITION BY RANGE (YEAR(hired)) (
      PARTITION p0 VALUES LESS THAN (2019),
      PARTITION p1 VALUES LESS THAN (2020),
      PARTITION p2 VALUES LESS THAN (2021),
      PARTITION p3 VALUES LESS THAN (2022),
      PARTITION p4 VALUES LESS THAN (2023),
      PARTITION p5 VALUES LESS THAN (2024),
      PARTITION p6 VALUES LESS THAN (2025),
      PARTITION p7 VALUES LESS THAN (2026),
      PARTITION p8 VALUES LESS THAN MAXVALUE
   );
   ```

   ```output
   ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function
   ```

+ 出现错误 `ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function` 的原因是，对于含有唯一键的表，分区表达式中的所有列都必须包含在唯一键中。主键也是一种唯一键。

   ```sql
   CREATE TABLE employees3 (
      id INT NOT NULL,
      fname VARCHAR(30),
      lname VARCHAR(30),
      hired DATE NOT NULL DEFAULT '2018-01-01',
      dept_code INT NOT NULL,
      region_id INT NOT NULL,
      PRIMARY KEY idx_id_hired(id, hired)
   )
   PARTITION BY RANGE (YEAR(hired)) (
      PARTITION p0 VALUES LESS THAN (2019),
      PARTITION p1 VALUES LESS THAN (2020),
      PARTITION p2 VALUES LESS THAN (2021),
      PARTITION p3 VALUES LESS THAN (2022),
      PARTITION p4 VALUES LESS THAN (2023),
      PARTITION p5 VALUES LESS THAN (2024),
      PARTITION p6 VALUES LESS THAN (2025),
      PARTITION p7 VALUES LESS THAN (2026),
      PARTITION p8 VALUES LESS THAN MAXVALUE
   );
   ```

   ```output
   Query OK, 0 rows affected (0.13 sec)
   ```

### 步骤 6. 创建有主键的 COLUMNS Range 分区表 `employees4`

   ```sql
   CREATE TABLE employees4 (
      id INT PRIMARY KEY,
      fname VARCHAR(30),
      lname VARCHAR(30),
      hired DATE NOT NULL DEFAULT '2018-01-01',
      dept_code INT NOT NULL,
      region_id INT NOT NULL
   )
   PARTITION BY RANGE COLUMNS(hired) (
      PARTITION p0 VALUES LESS THAN ('2019-01-01'),
      PARTITION p1 VALUES LESS THAN ('2020-01-01'),
      PARTITION p2 VALUES LESS THAN ('2021-01-01'),
      PARTITION p3 VALUES LESS THAN ('2022-01-01'),
      PARTITION p4 VALUES LESS THAN ('2023-01-01'),
      PARTITION p5 VALUES LESS THAN ('2024-01-01'),
      PARTITION p6 VALUES LESS THAN ('2025-01-01'),
      PARTITION p7 VALUES LESS THAN ('2026-01-01'),
      PARTITION p8 VALUES LESS THAN MAXVALUE
   );
   ```

   ```output
   ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function
   ```

+ 出现错误 `ERROR 1503 (HY000): A CLUSTERED INDEX must include all columns in the table's partitioning function` 的原因是，对于有唯一键的表，分区表达式中的所有列都必须包含在唯一键中。主键也是唯一键。

   ```sql
   CREATE TABLE employees4 (
      id INT,
      fname VARCHAR(30),
      lname VARCHAR(30),
      hired DATE NOT NULL DEFAULT '2018-01-01',
      dept_code INT NOT NULL,
      region_id INT NOT NULL,
      PRIMARY KEY idx_id_hired(id, hired)
   )
   PARTITION BY RANGE COLUMNS(hired) (
      PARTITION p0 VALUES LESS THAN ('2019-01-01'),
      PARTITION p1 VALUES LESS THAN ('2020-01-01'),
      PARTITION p2 VALUES LESS THAN ('2021-01-01'),
      PARTITION p3 VALUES LESS THAN ('2022-01-01'),
      PARTITION p4 VALUES LESS THAN ('2023-01-01'),
      PARTITION p5 VALUES LESS THAN ('2024-01-01'),
      PARTITION p6 VALUES LESS THAN ('2025-01-01'),
      PARTITION p7 VALUES LESS THAN ('2026-01-01'),
      PARTITION p8 VALUES LESS THAN MAXVALUE
   );
   ```

   ```output
   Query OK, 0 rows affected (0.14 sec)
   ```

### 步骤 7. 向分区表导入数据

+ 将非分区表 `emp` 中的数据导入分区表 `employees1`、`employees2`、`employees3` 和 `employees4`，并检查分区信息。然后，使用 `reorganize partition` 命令将 `employees1`、`employees2`、`employees3` 和 `employees4` 中的两个分区合并为一个分区。
+ 注意，`ANALYZE TABLE` 命令可能需要几秒钟才能运行完成。

   ```sql
   INSERT INTO employees1 SELECT * FROM emp;
   INSERT INTO employees2 SELECT * FROM emp;
   INSERT INTO employees3 SELECT * FROM emp;
   INSERT INTO employees4 SELECT * FROM emp;
   ANALYZE TABLE employees1;
   ANALYZE TABLE employees2;
   ANALYZE TABLE employees3;
   ANALYZE TABLE employees4;
   SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees1', 'employees2', 'employees3', 'employees4') ORDER BY table_name, partition_name;
   ```

   ```output
   +------------+----------------+------------+
   | table_name | partition_name | table_rows |
   +------------+----------------+------------+
   | employees1 | p0             |          5 |
   | employees1 | p1             |          3 |
   | employees1 | p2             |          4 |
   | employees1 | p3             |          2 |
   | employees1 | p4             |          7 |
   | employees1 | p5             |          7 |
   | employees1 | p6             |          3 |
   | employees1 | p7             |          5 |
   | employees1 | p8             |          0 |
   | employees2 | p0             |          5 |
   | employees2 | p1             |          3 |
   | employees2 | p2             |          4 |
   | employees2 | p3             |          2 |
   | employees2 | p4             |          7 |
   | employees2 | p5             |          7 |
   | employees2 | p6             |          3 |
   | employees2 | p7             |          5 |
   | employees2 | p8             |          0 |
   | employees3 | p0             |          5 |
   | employees3 | p1             |          3 |
   | employees3 | p2             |          4 |
   | employees3 | p3             |          2 |
   | employees3 | p4             |          7 |
   | employees3 | p5             |          7 |
   | employees3 | p6             |          3 |
   | employees3 | p7             |          5 |
   | employees3 | p8             |          0 |
   | employees4 | p0             |          5 |
   | employees4 | p1             |          3 |
   | employees4 | p2             |          4 |
   | employees4 | p3             |          2 |
   | employees4 | p4             |          7 |
   | employees4 | p5             |          7 |
   | employees4 | p6             |          3 |
   | employees4 | p7             |          5 |
   | employees4 | p8             |          0 |
   +------------+----------------+------------+
   36 rows in set (0.01 sec)
   ```

+ 运行 `ANALYZE TABLE` 命令后，你可能会看到警告消息代码 `1105` 。警告消息指出，由于没有全局统计信息，`dynamic pruning mode` 已禁用。你可以查看 [此文档](https://docs.pingcap.com/zh/tidb/v7.5/partitioned-table#%E5%8A%A8%E6%80%81%E8%A3%81%E5%89%AA%E6%A8%A1%E5%BC%8F) 了解详细信息。

   ```sql
   ALTER TABLE employees1 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN (2026));
   ALTER TABLE employees2 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01'));
   ALTER TABLE employees3 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN (2026));
   ALTER TABLE employees4 REORGANIZE PARTITION p5,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01'));
   ALTER TABLE employees4 REORGANIZE PARTITION p6,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01'));
   ANALYZE TABLE employees1;
   ANALYZE TABLE employees2;
   ANALYZE TABLE employees3;
   ANALYZE TABLE employees4;
   SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees1', 'employees2', 'employees3', 'employees4') ORDER BY table_name, partition_name;
   ```

   ```output
   tidb>ALTER TABLE employees4 REORGANIZE PARTITION p5,p7 into (PARTITION pErr VALUES LESS THAN ('2026-01-01'));
   ERROR 8200 (HY000): Unsupported REORGANIZE PARTITION of RANGE; not adjacent partitions

   +------------+----------------+------------+
   | table_name | partition_name | table_rows |
   +------------+----------------+------------+
   | employees1 | p0             |          5 |
   | employees1 | p1             |          3 |
   | employees1 | p2             |          4 |
   | employees1 | p3             |          2 |
   | employees1 | p4             |          7 |
   | employees1 | p5             |          7 |
   | employees1 | p8             |          0 |
   | employees1 | pErr           |          8 |
   | employees2 | p0             |          5 |
   | employees2 | p1             |          3 |
   | employees2 | p2             |          4 |
   | employees2 | p3             |          2 |
   | employees2 | p4             |          7 |
   | employees2 | p5             |          7 |
   | employees2 | p8             |          0 |
   | employees2 | pErr           |          8 |
   | employees3 | p0             |          5 |
   | employees3 | p1             |          3 |
   | employees3 | p2             |          4 |
   | employees3 | p3             |          2 |
   | employees3 | p4             |          7 |
   | employees3 | p5             |          7 |
   | employees3 | p8             |          0 |
   | employees3 | pErr           |          8 |
   | employees4 | p0             |          5 |
   | employees4 | p1             |          3 |
   | employees4 | p2             |          4 |
   | employees4 | p3             |          2 |
   | employees4 | p4             |          7 |
   | employees4 | p5             |          7 |
   | employees4 | p8             |          0 |
   | employees4 | pErr           |          8 |
   +------------+----------------+------------+
   32 rows in set (0.01 sec)
   ```

+ 创建 `employees4` 表时出现错误 `ERROR 8200 (HY000): Unsupported REORGANIZE PARTITION of RANGE; not adjacent partitions` 的原因是，对于 Range 分区表，你只能合并相邻的分区。

### 步骤 8. 删除分区

+ 使用 `DROP PARTITION` 命令从 `employees1`、`employees2`、`employees3` 和 `employees4`  中删除不必要的分区 `pErr`。

   ```sql
   ALTER TABLE employees1 DROP PARTITION pErr;
   ALTER TABLE employees2 DROP PARTITION pErr;
   ALTER TABLE employees3 DROP PARTITION pErr;
   ALTER TABLE employees4 DROP PARTITION pErr;
   ANALYZE TABLE employees1;
   ANALYZE TABLE employees2;
   ANALYZE TABLE employees3;
   ANALYZE TABLE employees4;
   SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees1', 'employees2', 'employees3', 'employees4') ORDER BY table_name, partition_name;
   ```

   ```output
   +------------+----------------+------------+
   | table_name | partition_name | table_rows |
   +------------+----------------+------------+
   | employees1 | p0             |          5 |
   | employees1 | p1             |          3 |
   | employees1 | p2             |          4 |
   | employees1 | p3             |          2 |
   | employees1 | p4             |          7 |
   | employees1 | p5             |          7 |
   | employees1 | p8             |          0 |
   | employees2 | p0             |          5 |
   | employees2 | p1             |          3 |
   | employees2 | p2             |          4 |
   | employees2 | p3             |          2 |
   | employees2 | p4             |          7 |
   | employees2 | p5             |          7 |
   | employees2 | p8             |          0 |
   | employees3 | p0             |          5 |
   | employees3 | p1             |          3 |
   | employees3 | p2             |          4 |
   | employees3 | p3             |          2 |
   | employees3 | p4             |          7 |
   | employees3 | p5             |          7 |
   | employees3 | p8             |          0 |
   | employees4 | p0             |          5 |
   | employees4 | p1             |          3 |
   | employees4 | p2             |          4 |
   | employees4 | p3             |          2 |
   | employees4 | p4             |          7 |
   | employees4 | p5             |          7 |
   | employees4 | p8             |          0 |
   +------------+----------------+------------+
   28 rows in set (0.01 sec)
   ```

### 步骤 9. 创建 Range INTERVAL 和 Range COLUMNS 分区表

   ```sql
   CREATE TABLE employees5 (
      id INT NOT NULL,
      fname VARCHAR(30),
      lname VARCHAR(30),
      hired DATE NOT NULL DEFAULT '2018-01-01',
      dept_code INT NOT NULL,
      region_id INT NOT NULL
   )
   PARTITION BY RANGE (YEAR(hired))
   INTERVAL (1) FIRST PARTITION LESS THAN (2019) LAST PARTITION LESS THAN (2026) MAXVALUE PARTITION;

   CREATE TABLE employees6 (
      id INT NOT NULL,
      fname VARCHAR(30),
      lname VARCHAR(30),
      hired DATE NOT NULL DEFAULT '2018-01-01',
      dept_code INT NOT NULL,
      region_id INT NOT NULL
   )
   PARTITION BY RANGE COLUMNS (hired)
   INTERVAL (1 MONTH) FIRST PARTITION LESS THAN ('2019-01-01') LAST PARTITION LESS THAN ('2026-01-01') MAXVALUE PARTITION;

   INSERT INTO employees5 SELECT * FROM emp;
   INSERT INTO employees6 SELECT * FROM emp;

   ANALYZE TABLE employees5;
   ANALYZE TABLE employees6;

   SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE table_schema = 'test' AND table_name IN ('employees5', 'employees6') ORDER BY table_name, partition_name;
   ```

   ```output
   +------------+-----------------+------------+
   | table_name | partition_name  | table_rows |
   +------------+-----------------+------------+
   | employees5 | P_LT_2019       |          5 |
   | employees5 | P_LT_2020       |          3 |
   | employees5 | P_LT_2021       |          4 |
   | employees5 | P_LT_2022       |          2 |
   | employees5 | P_LT_2023       |          7 |
   | employees5 | P_LT_2024       |          7 |
   | employees5 | P_LT_2025       |          3 |
   | employees5 | P_LT_2026       |          5 |
   | employees5 | P_MAXVALUE      |          0 |
   | employees6 | P_LT_2019-01-01 |          5 |
   | employees6 | P_LT_2019-02-01 |          1 |
   | employees6 | P_LT_2019-03-01 |          1 |
   | employees6 | P_LT_2019-04-01 |          1 |
   | employees6 | P_LT_2019-05-01 |          0 |
   | employees6 | P_LT_2019-06-01 |          0 |
   | employees6 | P_LT_2019-07-01 |          0 |
   | employees6 | P_LT_2019-08-01 |          0 |
   | employees6 | P_LT_2019-09-01 |          0 |
   | employees6 | P_LT_2019-10-01 |          0 |
   | employees6 | P_LT_2019-11-01 |          0 |
   | employees6 | P_LT_2019-12-01 |          0 |
   | employees6 | P_LT_2020-01-01 |          0 |
   | employees6 | P_LT_2020-02-01 |          0 |
   | employees6 | P_LT_2020-03-01 |          0 |
   | employees6 | P_LT_2020-04-01 |          0 |
   | employees6 | P_LT_2020-05-01 |          0 |
   | employees6 | P_LT_2020-06-01 |          0 |
   | employees6 | P_LT_2020-07-01 |          0 |
   | employees6 | P_LT_2020-08-01 |          0 |
   | employees6 | P_LT_2020-09-01 |          0 |
   | employees6 | P_LT_2020-10-01 |          0 |
   | employees6 | P_LT_2020-11-01 |          0 |
   | employees6 | P_LT_2020-12-01 |          2 |
   | employees6 | P_LT_2021-01-01 |          2 |
   | employees6 | P_LT_2021-02-01 |          0 |
   | employees6 | P_LT_2021-03-01 |          0 |
   | employees6 | P_LT_2021-04-01 |          0 |
   | employees6 | P_LT_2021-05-01 |          0 |
   | employees6 | P_LT_2021-06-01 |          0 |
   | employees6 | P_LT_2021-07-01 |          0 |
   | employees6 | P_LT_2021-08-01 |          2 |
   | employees6 | P_LT_2021-09-01 |          0 |
   | employees6 | P_LT_2021-10-01 |          0 |
   | employees6 | P_LT_2021-11-01 |          0 |
   | employees6 | P_LT_2021-12-01 |          0 |
   | employees6 | P_LT_2022-01-01 |          0 |
   | employees6 | P_LT_2022-02-01 |          0 |
   | employees6 | P_LT_2022-03-01 |          0 |
   | employees6 | P_LT_2022-04-01 |          0 |
   | employees6 | P_LT_2022-05-01 |          2 |
   | employees6 | P_LT_2022-06-01 |          2 |
   | employees6 | P_LT_2022-07-01 |          0 |
   | employees6 | P_LT_2022-08-01 |          0 |
   | employees6 | P_LT_2022-09-01 |          0 |
   | employees6 | P_LT_2022-10-01 |          2 |
   | employees6 | P_LT_2022-11-01 |          1 |
   | employees6 | P_LT_2022-12-01 |          0 |
   | employees6 | P_LT_2023-01-01 |          0 |
   | employees6 | P_LT_2023-02-01 |          0 |
   | employees6 | P_LT_2023-03-01 |          0 |
   | employees6 | P_LT_2023-04-01 |          0 |
   | employees6 | P_LT_2023-05-01 |          0 |
   | employees6 | P_LT_2023-06-01 |          0 |
   | employees6 | P_LT_2023-07-01 |          1 |
   | employees6 | P_LT_2023-08-01 |          1 |
   | employees6 | P_LT_2023-09-01 |          1 |
   | employees6 | P_LT_2023-10-01 |          3 |
   | employees6 | P_LT_2023-11-01 |          0 |
   | employees6 | P_LT_2023-12-01 |          0 |
   | employees6 | P_LT_2024-01-01 |          1 |
   | employees6 | P_LT_2024-02-01 |          0 |
   | employees6 | P_LT_2024-03-01 |          0 |
   | employees6 | P_LT_2024-04-01 |          0 |
   | employees6 | P_LT_2024-05-01 |          0 |
   | employees6 | P_LT_2024-06-01 |          0 |
   | employees6 | P_LT_2024-07-01 |          2 |
   | employees6 | P_LT_2024-08-01 |          1 |
   | employees6 | P_LT_2024-09-01 |          0 |
   | employees6 | P_LT_2024-10-01 |          0 |
   | employees6 | P_LT_2024-11-01 |          0 |
   | employees6 | P_LT_2024-12-01 |          0 |
   | employees6 | P_LT_2025-01-01 |          0 |
   | employees6 | P_LT_2025-02-01 |          2 |
   | employees6 | P_LT_2025-03-01 |          0 |
   | employees6 | P_LT_2025-04-01 |          0 |
   | employees6 | P_LT_2025-05-01 |          1 |
   | employees6 | P_LT_2025-06-01 |          1 |
   | employees6 | P_LT_2025-07-01 |          0 |
   | employees6 | P_LT_2025-08-01 |          0 |
   | employees6 | P_LT_2025-09-01 |          1 |
   | employees6 | P_LT_2025-10-01 |          0 |
   | employees6 | P_LT_2025-11-01 |          0 |
   | employees6 | P_LT_2025-12-01 |          0 |
   | employees6 | P_LT_2026-01-01 |          0 |
   | employees6 | P_MAXVALUE      |          0 |
   +------------+-----------------+------------+
   95 rows in set (0.01 sec)
   ```

+ 你会发现 Range INTERVAL 分区支持两种方法：PARTITION BY RANGE 和 PARTITION BY RANGE COLUMNS。它还可以根据设置的间隔自动进行分区。

### 步骤 10. 清理表

   ```sql
   DROP TABLE emp;
   DROP TABLE employees1;
   DROP TABLE employees2;
   DROP TABLE employees3;
   DROP TABLE employees4;
   DROP TABLE employees5;
   DROP TABLE employees6;
   ```

   ```output
   Query OK, 0 rows affected (0.81 sec)
   ```