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

Rick James
80.7k5 gold badges52 silver badges119 bronze badges
asked May 9, 2020 at 15:17
1
  • 1
    Some hints. Commented May 10, 2020 at 4:06

1 Answer 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)
answered May 10, 2020 at 21:20
4
  • How would I find partition name from value? Commented May 11, 2020 at 15:32
  • @RajeshRanjan - I added an example Commented May 11, 2020 at 15:47
  • It works fine with range partition, but what if we have KEY partition? Commented Jun 24, 2020 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. Commented Jun 24, 2020 at 6:28

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.