0

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

4
  • RANGE COLUMNS does not accept expressions, only names of columns.. RANGE COLUMNS partitioning. Update your server version to 5.7 at least and use generated column.
    – Akina
    Commented Mar 26, 2019 at 6:58
  • Oh, alright. Thanks. Are there any other recommended ways to compare a date field in range columns partitioning? Commented Mar 26, 2019 at 7:01
  • Where or not there is a solution to the question asked, I need to ask "What advantage do you expect to get from such partitioning?" Nearly all PARTITION questions on this forum come from users who fail to realize that partitioning does not intrinsically provide any performance gain.
    – Rick James
    Commented Apr 18, 2019 at 6:13
  • I was just using partitioning by timestamp to segregate data and drop it once it reaches a certain age. Commented Apr 22, 2019 at 7:27

1 Answer 1

0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.