Re: Partitioning problem

[Scott Noyes]

Resolving to day is not required; you can go clear down to the second if you like. But you do have to provide an integer to the expression and not a datetime, except in the case of partitioning by KEY.

Phil is right in that if you have a PRIMARY or UNIQUE key, it must include any columns that appear in the partitioning expression. But remember that auto_increment doesn't require a PRIMARY key, so you can do something like this:


CREATE TABLE mytable(
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
dt DATETIME NOT NULL,
INDEX(id)
)
PARTITION BY RANGE (TIME_TO_SEC(dt)) (
PARTITION p1 VALUES LESS THAN (TIME_TO_SEC('2008-08-08 00:00:00')),
PARTITION p2 VALUES LESS THAN (TIME_TO_SEC('2008-08-08 02:00:00')),
PARTITION p3 VALUES LESS THAN MAXVALUE
);