1

I have my table partitioned on day of month basis.

Now I need to archive data of the last 16th day date_add(now(), interval -16 day) to another table. For that I need to find out the partition name that contains data of the date, so that I can exchange the partition and export to another table.

But my problem is,I don't know how to find the partition name from value, "I need to exchange and archive.

Your help will be highly appreciated.

Thanks

1
2
information_schema.PARTITIONS.PARTITION_DESCRIPTION

Consider naming the partitions in an obvious way, such as

p20200419

to contain data for the 19th (and defined LESS THAN ('2020-04-20'))

More tips: http://mysql.rjweb.org/doc.php/partitionmaint and its companion: http://mysql.rjweb.org/demo_part_maint.pl.txt

Example

Given:

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)

This select:

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)
4
  • How would I find partition name from value? – Rajesh Ranjan May 11 '20 at 15:32
  • @RajeshRanjan - I added an example – Rick James May 11 '20 at 15:47
  • It works fine with range partition, but what if we have KEY partition? – Rajesh Ranjan Jun 24 '20 at 6:19
  • @RajeshRanjan - Name the partitions so that you can recognize what the partition key is. I have found that BY RANGE is the only useful partitioning method, so I have not looked into KEY. – Rick James Jun 24 '20 at 6:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.