主页 / dba / 问题 / 266828
Accepted
Rajesh Ranjan
Asked: 2020-05-10 07:17:46 +0800 CST

如何从 MySQL/MariaDB 中的值获取分区名称

  • 772

我的表按月分区。

现在我需要将最后 16 天的数据归档date_add(now(), interval -16 day)到另一个表中。为此，我需要找出包含日期数据的分区名称，以便我可以交换分区并导出到另一个表。

但我的问题是，我不知道如何从值中找到分区名称，“我需要交换和归档。

您的帮助将不胜感激。

谢谢

mysql mariadb

1 个回答

  1. Best Answer
    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)
    • 2

相关问题