MySQL Forums
Forum List  »  Partitioning

Re: Partitioning dynamically?
Posted by: Jonathan Stephens
Date: October 29, 2006 07:32AM

> 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
Orlando, Florida, USA

MySQL Dev Zone
MySQL Server Documentation
Oracle



Edited 1 time(s). Last edit at 10/29/2006 07:39AM by Jon Stephens.

Options: ReplyQuote


Subject
Views
Written By
Posted
3259
October 28, 2006 09:24AM
Re: Partitioning dynamically?
2288
October 29, 2006 07:32AM
2215
November 05, 2006 11:10PM
2136
November 06, 2006 03:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.