Mysql – way to partition a thesql table, by range columns, where the 2 columns in question are of datetime and bigint

MySQLmysql-5.6partitioning

I need to partition a table into 4 partitions, p0 to p3: with p0 having all rows with "id"=0, and p1 having all rows with "id"=1 and "doj" before february, and p2 having "id"=1 and "doj" before march, and partition "future" with others. The value for id, is guaranteed to be either 0 or 1 always.

I've tried to come up with a solution and the following is what i arrived at:

create table temp
(id int not null primary key,
 doj datetime not null primary key)
partition by range columns(id,month(doj))
(partition p0 values less than (1, 13),
 partition p1 values less than (2, 2),
 partition p2 values less than (2, 3),
 partition p3 values less than(maxvalue, maxvalue));

I get the following error when i try and execute the above sql command:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(doj)) (partition p0 values less than (1, maxvalue), partition p1 values less t' at line 4

Any insights as to how to partition the table in a manner described above would be much appreciated.

Version of MySql used is:
5.6.19-67.0-log

Best Answer

The solution can be:

create table temp
(id int not null primary key,
 doj datetime not null primary key,
 doj_month tinyint) -- add field for partitioning
partition by range columns(id,doj_month)
(partition p0 values less than (1, 13),
 partition p1 values less than (2, 2),
 partition p2 values less than (2, 3),
 partition p3 values less than(maxvalue, maxvalue));

-- create triggers for additional field calculations
create trigger tr_i_temp
for insert on temp
for each row
set NEW.doj_month = month(NEW.doj);

create trigger tr_u_temp
for update on temp
for each row
set NEW.doj_month = month(NEW.doj);

But you must to remember that there exists a lot of cases when triggers are not fired (cascade operations, LOAD DATA, etc.). If you use such operations you MUST update this additional field "by hands" (for example, update query which reassigns doj field).

Related Question