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
-
1Some hints.danblack– danblack2020年05月10日 04:06:02 +00:00Commented May 10, 2020 at 4:06
1 Answer 1
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)
-
How would I find partition name from value?Rajesh Ranjan– Rajesh Ranjan2020年05月11日 15:32:35 +00:00Commented May 11, 2020 at 15:32
-
@RajeshRanjan - I added an exampleRick James– Rick James2020年05月11日 15:47:22 +00:00Commented May 11, 2020 at 15:47
-
It works fine with range partition, but what if we have KEY partition?Rajesh Ranjan– Rajesh Ranjan2020年06月24日 06:19:08 +00:00Commented 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 intoKEY
.Rick James– Rick James2020年06月24日 06:28:27 +00:00Commented Jun 24, 2020 at 6:28