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 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 | | | count_past_partitions | decimal(23,0) | YES | | NULL | | | count_future_partitions | decimal(23,0) | YES | | NULL | | | has_maxvalue | decimal(23,0) | YES | | NULL | | | 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
- count_past_partitions: in the case partitions are recognized to be by some temporal representation, the number of partitions that are in the past.
- count_future_partitions: in the case partitions are recognized to be by some temporal representation, the number of partitions that are in the future (including NOW).
- has_maxvalue: 1 is the table has a LESS THAN MAXVALUE partition, 0 otherwise.
- 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
count_past_partitions: 6
count_future_partitions: 0
has_maxvalue: 1
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)
The above query was issued in the year 2013, and so all partitions are considered as in the past. The MAXVALUE partition is considered neither as past nor future (although it will contain any future rows). This is so that count_future_partitions makes for the number of partitions strictly specifying future dates.
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=utf8
/*!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) */