> All the examples I have seen seem to reference some sort of context for splitting
> data into partitions, which I don't really need.
I don't understand what you mean by 'context'.
This doesn't appear particularly difficult to accomplish.
Suppose your table is defined like this:
CREATE TABLE t (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50),
added INT NOT NULL,
PRIMARY KEY pk (id, added)
) PARTITION BY RANGE ( YEARWEEK(added) ) (
PARTITION p200644 VALUES LESS THAN (200644)
);
You can add a new partition each week by running an ALTER TABLE statement in which the name and limiting value derive from the YEARWEEK() value for that week. In order to run this statement without hard-coding those values, you'll need to use a prepared statement:
SET @yw = YEARWEEK( NOW() );
SET @s = 'ALTER TABLE t ADD PARTITION (PARTITION p';
SET @s = CONCAT(@s, @yw, ' VALUES LESS THAN (', @yw, '))');
PREPARE p FROM @s;
EXECUTE p;
DEALLOCATE p;
DROP p;
Just put that into the DO block of a scheduled event(or wrap it into a SP and call the SP from the scheduled event), and (unless I'm missing something) you should be good to go.
Jon Stephens
MySQL Documentation Team @ Oracle
MySQL Dev Zone
MySQL Server Documentation
Oracle
Edited 1 time(s). Last edit at 10/29/2006 07:39AM by Jon Stephens.