sql_range_partitions
NAME
sql_range_partitions: Generate SQL statements for managing range partitionsTYPE
ViewDESCRIPTION
sql_range_partitions provides with SQL statements to create/drop partitions in a RANGE or RANGE COLUMNS partitioned table
It generates the DROP PARTITION statement required to drop the oldest partition, and the ADD PARTITION or REORGANIZE PARTITION statement for generating the next partition in sequence.
This view auto-deduces the "next in sequence" partition value. It handles consistent partitioning schemes, where the interval of values between partitions makes some sense. Such an interval can be a constant value, but can also be a time-based interval.
The view supports MySQL 5.1 as well as 5.5. 5.1 requires an integer partitioning key, thereby forcing users to convert such values as timestamps to integers via UNIX_TIMESTAMP(), TO_DAYS() etc. sql_range_partitions reverse engineers this conversion so as to compute the next in sequence LESS THAN value.
It handles views with a LESS THAN MAXVALUE partition by reorganizing such partition into a "normal" partition followed by a new LESS THAN MAXVALUE one.
STRUCTURE
mysql> DESC common_schema.sql_range_partitions; +--------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+--------------+------+-----+---------+-------+ | table_schema | varchar(64) | NO | | | | | table_name | varchar(64) | NO | | | | | count_partitions | bigint(21) | NO | | 0 | | | sql_drop_first_partition | varchar(284) | YES | | NULL | | | sql_add_next_partition | longblob | YES | | NULL | | +--------------------------+--------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- table_schema: schema of partitioned table table
- table_name: table partitioned by RANGE or RANGE COLUMNS
- count_partitions: number of partitions in table
- sql_drop_first_partition:
A SQL statement which drops the first partition.
Use with eval() to apply query. - sql_add_next_partition:
A SQL statement which adds the "next in sequence" partition.
Use with eval() to apply query.
The SQL statements are not terminated by ';'.
EXAMPLES
Show drop/reorganize statements for a partitioned table with MAXVALUE partition:
mysql> CREATE TABLE test.quarterly_report_status (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL
)
PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated)) (
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2008-01-01 00:00:00')),
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2008-04-01 00:00:00')),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2008-07-01 00:00:00')),
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2008-10-01 00:00:00')),
PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2009-01-01 00:00:00')),
PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2009-04-01 00:00:00')),
PARTITION p6 VALUES LESS THAN (MAXVALUE)
);
mysql> SELECT * FROM sql_range_partitions WHERE table_name='quarterly_report_status' \G
*************************** 1. row ***************************
table_schema: test
table_name: quarterly_report_status
count_partitions: 7
sql_drop_first_partition: alter table `test`.`quarterly_report_status` drop partition `p0`
sql_add_next_partition: alter table `test`.`quarterly_report_status` reorganize partition `p6` into (partition `p_20090701000000` values less than (1246395600) /* 2009-07-01 00:00:00 */ , partition p_maxvalue values less than MAXVALUE)
Add next partition:
mysql> call eval("SELECT sql_add_next_partition FROM sql_range_partitions WHERE table_name='quarterly_report_status'");
mysql> SHOW CREATE TABLE test.quarterly_report_status \G
Create Table: CREATE TABLE `quarterly_report_status` (
`report_id` int(11) NOT NULL,
`report_status` varchar(20) NOT NULL,
`report_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(report_updated))
(PARTITION p0 VALUES LESS THAN (1199138400) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (1206997200) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (1214859600) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (1222808400) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN (1230760800) ENGINE = MyISAM,
PARTITION p5 VALUES LESS THAN (1238533200) ENGINE = MyISAM,
PARTITION p_20090701000000 VALUES LESS THAN (1246395600) ENGINE = MyISAM,
PARTITION p_maxvalue VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */