如何從 MySQL/MariaDB 中的值獲取分區名稱

我的表按月分區。

現在我需要將最後 16 天的數據歸檔date_add(now(), interval -16 day)到另一個表中。為此，我需要找出包含日期數據的分區名稱，以便我可以交換分區並導出到另一個表。

但我的問題是，我不知道如何從值中找到分區名稱，“我需要交換和歸檔。

您的幫助將不勝感激。

謝謝

information_schema.PARTITIONS.PARTITION_DESCRIPTION

考慮以明顯的方式命名分區，例如

p20200419

包含 19 日（和已定義LESS THAN ('2020-04-20')）的數據

更多提示： http: //mysql.rjweb.org/doc.php/partitionmaint及其伴侶：http: //mysql.rjweb.org/demo_part_maint.pl.txt

例子

鑑於：

SHOW CREATE TABLE pmdemo.pmdemo\G
*************************** 1. row ***************************
      Table: pmdemo
Create Table: CREATE TABLE `pmdemo` (
 `dt` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(dt))
(PARTITION `start` VALUES LESS THAN (0) ENGINE = MyISAM,
PARTITION from20121008 VALUES LESS THAN (735150) ENGINE = MyISAM,
PARTITION from20121009 VALUES LESS THAN (735151) ENGINE = MyISAM,
PARTITION from20121010 VALUES LESS THAN (735152) ENGINE = MyISAM,
PARTITION from20121011 VALUES LESS THAN (735153) ENGINE = MyISAM,
PARTITION future VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.01 sec)

本次選擇：

mysql> SELECT * FROM information_schema.`PARTITIONS`
     WHERE table_name = 'pmdemo'
       AND partition_description = TO_DAYS('2012-10-10')\G

*************************** 1. row ***************************
               TABLE_CATALOG: def
                TABLE_SCHEMA: pmdemo
                  TABLE_NAME: pmdemo
              PARTITION_NAME: from20121009  -- the partition name
           SUBPARTITION_NAME: NULL
  PARTITION_ORDINAL_POSITION: 3
SUBPARTITION_ORDINAL_POSITION: NULL
            PARTITION_METHOD: RANGE
         SUBPARTITION_METHOD: NULL
        PARTITION_EXPRESSION: TO_DAYS(ft) -- a convenient formula for time-series
     SUBPARTITION_EXPRESSION: NULL
       PARTITION_DESCRIPTION: 735151  -- the TO_DAYS() in the query
                  TABLE_ROWS: 0
              AVG_ROW_LENGTH: 0
                 DATA_LENGTH: 0
             MAX_DATA_LENGTH: 1970324836974591
                INDEX_LENGTH: 1024
                   DATA_FREE: 0
                 CREATE_TIME: 2017-01-09 13:49:01
                 UPDATE_TIME: 2017-01-09 13:49:01
                  CHECK_TIME: NULL
                    CHECKSUM: NULL
           PARTITION_COMMENT: 
                   NODEGROUP: default
             TABLESPACE_NAME: NULL
1 row in set (0.00 sec)

引用自：https://dba.stackexchange.com/questions/266828