New partitioning SQL syntax added in next MySQL milestone release treee

This blogs gives some insights into the new SQL syntax added in WL#3352
and WL#4444 and WL#4571 which all have been included in the
mysql-next-mr tree which is the base for the next milestone release
codenamed Betony.

The purpose of these new changes is to enable improved partition pruning, and
also making it possible to partition on strings. Supporting TRUNCATE on
partitions will improve partition management and the ability to use separate
key caches for different partitions makes MyISAM partitioned tables more
useful. Finally we added a new function TO_SECONDS which makes it
possible to get more fine-grained dates for partitions and subpartitions.

This information will soon find its way into documentation but if you want
to get started right away here it comes.

There are 5 additions effectively:
1) The ability to RANGE partition by column list instead of by function
2) The ability to LIST partition by column list instead of by function
3) A new function TO_SECONDS which can be used in partition functions
and where partition pruning will be used also on ranges.
4) The ability to TRUNCATE a partition
5) The ability to use a keycache in MyISAM per partition

Here's a few examples of how to use these now additions:

1)
CREATE TABLE t1 (a varchar(5), b int)
PARTITION BY RANGE COLUMNS (a,b)
( PARTITION p0 VALUES LESS THAN ("abc", 1),
PARTITION p1 VALUES LESS THAN ("def", 2));

Some things noteworthy here:
The checks for the constants is fairly strict. Thus using "1" for a constant
to b isn't allowed, the constant must be of the same type as the field it
maps to. Also sql_mode will be ignored for those partition constants.
Thus e.g. even if sql_mode specifies that non-existing dates are allowed
they will not be allowed in the partition constants since these constants
will be a part of the table and need to live for longer than the current
session.

Character sets are allowed and the string constants will be interpreted
in the character set their field belongs to. Also character set strings
without mapping are allowed. If one tries to use SHOW CREATE TABLE
on the partition table and the mapping of the partition constants from
field charset to UTF8 fails or if mapping to client charset fails, then the
partition constants will be written in hex string format.

A partition constant can be MAXVALUE, NULL is however not allowed.
There were some considerations to also be able to use MINVALUE
which effectively would make it possible to create partitions where only
the NULL values can go in. However this is still possible if one knows
the minimum value of the field.

It's possible to partition on integer fields, string fields and date fields.
It's not possible to partition on BLOBs, SETs, ENUMs, GEOMETRY fields,
BIT fields.

2)
CREATE TABLE t1 (a varchar(1))
PARTITION BY LIST COLUMNS (a)
( PARTITION p0 VALUES IN ("a","b","c"));

CREATE TABLE t1 (a varchar(1), b int)
PARTITION BY LIST COLUMNS (a,b)
( PARTITION p0 VALUES IN (("a",1),("b",2),("c",3)));

Noteworthy here is that parenthesis are required when more than one
field is in the list of columns partitioned on. It's required to not use
parenthesis when there is only one field.

NULL values are allowed as in MySQL 5.1 but not MAXVALUE.

3)
CREATE TABLE t1 (a datetime)
PARTITION BY RANGE (TO_SECONDS(a))
( PARTITION p0 VALUES LESS THAN (TO_SECONDS("2009-11-11 08:00:00")),
PARTITION p1 VALUES LESS THAN (MAXVALUE));

Same syntax as in MySQL 5.1 but also possible to use TO_SECONDS as a
partition function.

4)
ALTER TABLE t1 TRUNCATE PARTITION p0;
ALTER TABLE t1 TRUNCATE PARTITION p1;
ALTER TABLE t1 TRUNCATE PARTITION ALL;

This delete all rows in the given partitions and resets the given partitions
auto_increment values (if exists) to 0.

The syntax works in the same manner as for ANALYZE, OPTIMIZE and other
commands that can be applied on partitions already in MySQL 5.1.

5)

CACHE INDEX t1 PARTITION p0 IN keycache_fast;
CACHE INDEX t1 PARTITION p1, p2 IN keycache_slow;
LOAD INDEX INTO CACHE t1 PARTITION p0;
LOAD INDEX INTO CACHE t1 PARTITION p0, p1;

This new syntax makes it possible to have separate key caches for different
partitions in a partitioned table using the MyISAM storage engine.