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          | longblob     | YES  |      | NULL    |       |
  14. | SUBPARTITION_EXPRESSION       | longblob     | YES  |      | NULL    |       |
  15. | PARTITION_DESCRIPTION         | longblob     | 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. +-------------------------------+--------------+------+------+---------+-------+
  30. 25 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: 2020-07-06 16:35:28
  21.                   UPDATE_TIME: NULL
  22.                    CHECK_TIME: NULL
  23.                      CHECKSUM: NULL
  24.             PARTITION_COMMENT: 
  25.                     NODEGROUP: NULL
  26.               TABLESPACE_NAME: NULL
  27. *************************** 2. row ***************************
  28.                 TABLE_CATALOG: def
  29.                  TABLE_SCHEMA: test
  30.                    TABLE_NAME: t1
  31.                PARTITION_NAME: p1
  32.             SUBPARTITION_NAME: NULL
  33.    PARTITION_ORDINAL_POSITION: 2
  34. SUBPARTITION_ORDINAL_POSITION: NULL
  35.              PARTITION_METHOD: HASH
  36.           SUBPARTITION_METHOD: NULL
  37.          PARTITION_EXPRESSION: `id`
  38.       SUBPARTITION_EXPRESSION: NULL
  39.         PARTITION_DESCRIPTION: 
  40.                    TABLE_ROWS: 0
  41.                AVG_ROW_LENGTH: 0
  42.                   DATA_LENGTH: 0
  43.               MAX_DATA_LENGTH: 0
  44.                  INDEX_LENGTH: 0
  45.                     DATA_FREE: 0
  46.                   CREATE_TIME: 2020-07-06 16:35:28
  47.                   UPDATE_TIME: NULL
  48.                    CHECK_TIME: NULL
  49.                      CHECKSUM: NULL
  50.             PARTITION_COMMENT: 
  51.                     NODEGROUP: NULL
  52.               TABLESPACE_NAME: NULL
  53. 2 rows in set (0.00 sec)