Search code examples
mysqlsqlcreate-tablepartition

Mysql Partitioning similar to an if statement

I want to partition my table let`s say "mytable" using a column named "column_value". This column is an integer with many values. I want to have 3 partitions: p1 where column_value is 433, p2 where column_value is 21 and a p3 where all other values go. How can I do this? Could I use a function in the partition definition? Can I do this without a function? Many thanks.

I`ve tried searching the documentation of mysql and other websites for things like this but I have not found any solution

Solution

  • If you can add a generated column then a list partition may do

    DROP TABLE IF EXISTS T;

CREATE TABLE T(ID INT,VAL INT,LID INT AS (CASE WHEN ID = 21 THEN ID WHEN ID = 433 THEN ID ELSE 999 END)
)
PARTITION BY LIST(LID)
(
PARTITION P438 VALUES IN(433),
PARTITION P21  VALUES IN(21),
PARTITION PREST VALUES IN(999)
);

    https://dbfiddle.uk/lquwhdnP