PARTITIONS

PARTITIONS 表提供有关分区表的信息。

1. USE INFORMATION_SCHEMA;
2. DESC PARTITIONS;

输出结果如下：

1. +-------------------------------+--------------+------+------+---------+-------+
2. | Field                         | Type         | Null | Key  | Default | Extra |
3. +-------------------------------+--------------+------+------+---------+-------+
4. | TABLE_CATALOG                 | varchar(512) | YES  |      | NULL    |       |
5. | TABLE_SCHEMA                  | varchar(64)  | YES  |      | NULL    |       |
6. | TABLE_NAME                    | varchar(64)  | YES  |      | NULL    |       |
7. | PARTITION_NAME                | varchar(64)  | YES  |      | NULL    |       |
8. | SUBPARTITION_NAME             | varchar(64)  | YES  |      | NULL    |       |
9. | PARTITION_ORDINAL_POSITION    | bigint(21)   | YES  |      | NULL    |       |
10. | SUBPARTITION_ORDINAL_POSITION | bigint(21)   | YES  |      | NULL    |       |
11. | PARTITION_METHOD              | varchar(18)  | YES  |      | NULL    |       |
12. | SUBPARTITION_METHOD           | varchar(12)  | YES  |      | NULL    |       |
13. | PARTITION_EXPRESSION          | longtext     | YES  |      | NULL    |       |
14. | SUBPARTITION_EXPRESSION       | longtext     | YES  |      | NULL    |       |
15. | PARTITION_DESCRIPTION         | longtext     | YES  |      | NULL    |       |
16. | TABLE_ROWS                    | bigint(21)   | YES  |      | NULL    |       |
17. | AVG_ROW_LENGTH                | bigint(21)   | YES  |      | NULL    |       |
18. | DATA_LENGTH                   | bigint(21)   | YES  |      | NULL    |       |
19. | MAX_DATA_LENGTH               | bigint(21)   | YES  |      | NULL    |       |
20. | INDEX_LENGTH                  | bigint(21)   | YES  |      | NULL    |       |
21. | DATA_FREE                     | bigint(21)   | YES  |      | NULL    |       |
22. | CREATE_TIME                   | datetime     | YES  |      | NULL    |       |
23. | UPDATE_TIME                   | datetime     | YES  |      | NULL    |       |
24. | CHECK_TIME                    | datetime     | YES  |      | NULL    |       |
25. | CHECKSUM                      | bigint(21)   | YES  |      | NULL    |       |
26. | PARTITION_COMMENT             | varchar(80)  | YES  |      | NULL    |       |
27. | NODEGROUP                     | varchar(12)  | YES  |      | NULL    |       |
28. | TABLESPACE_NAME               | varchar(64)  | YES  |      | NULL    |       |
29. | TIDB_PARTITION_ID             | bigint(21)   | YES  |      | NULL    |       |
30. | TIDB_PLACEMENT_POLICY_NAME    | varchar(64)  | YES  |      | NULL    |       |
31. +-------------------------------+--------------+------+------+---------+-------+
32. 27 rows in set (0.00 sec)

1. CREATE TABLE test.t1 (id INT NOT NULL PRIMARY KEY) PARTITION BY HASH (id) PARTITIONS 2;
2. SELECT * FROM PARTITIONS WHERE table_schema='test' AND table_name='t1'\G

输出结果如下：

1. *************************** 1. row ***************************
2. TABLE_CATALOG: def
3. TABLE_SCHEMA: test
4. TABLE_NAME: t1
5. PARTITION_NAME: p0
6. SUBPARTITION_NAME: NULL
7. PARTITION_ORDINAL_POSITION: 1
8. SUBPARTITION_ORDINAL_POSITION: NULL
9. PARTITION_METHOD: HASH
10. SUBPARTITION_METHOD: NULL
11. PARTITION_EXPRESSION: `id`
12. SUBPARTITION_EXPRESSION: NULL
13. PARTITION_DESCRIPTION:
14. TABLE_ROWS: 0
15. AVG_ROW_LENGTH: 0
16. DATA_LENGTH: 0
17. MAX_DATA_LENGTH: 0
18. INDEX_LENGTH: 0
19. DATA_FREE: 0
20. CREATE_TIME: 2022-12-14 06:09:33
21. UPDATE_TIME: NULL
22. CHECK_TIME: NULL
23. CHECKSUM: NULL
24. PARTITION_COMMENT:
25. NODEGROUP: NULL
26. TABLESPACE_NAME: NULL
27. TIDB_PARTITION_ID: 89
28. TIDB_PLACEMENT_POLICY_NAME: NULL
29. *************************** 2. row ***************************
30. TABLE_CATALOG: def
31. TABLE_SCHEMA: test
32. TABLE_NAME: t1
33. PARTITION_NAME: p1
34. SUBPARTITION_NAME: NULL
35. PARTITION_ORDINAL_POSITION: 2
36. SUBPARTITION_ORDINAL_POSITION: NULL
37. PARTITION_METHOD: HASH
38. SUBPARTITION_METHOD: NULL
39. PARTITION_EXPRESSION: `id`
40. SUBPARTITION_EXPRESSION: NULL
41. PARTITION_DESCRIPTION:
42. TABLE_ROWS: 0
43. AVG_ROW_LENGTH: 0
44. DATA_LENGTH: 0
45. MAX_DATA_LENGTH: 0
46. INDEX_LENGTH: 0
47. DATA_FREE: 0
48. CREATE_TIME: 2022-12-14 06:09:33
49. UPDATE_TIME: NULL
50. CHECK_TIME: NULL
51. CHECKSUM: NULL
52. PARTITION_COMMENT:
53. NODEGROUP: NULL
54. TABLESPACE_NAME: NULL
55. TIDB_PARTITION_ID: 90
56. TIDB_PLACEMENT_POLICY_NAME: NULL
57. 2 rows in set (0.00 sec)