CODEWITHSUNDEEP
mysqlpartitioning
user1040876
user1040876

Reputation: 31

MySQL alter table partitioning syntax

I tried to do table partitioning in MySQL by altering the table with the following code:

ALTER TABLE tt DROP PRIMARY KEY, ADD PRIMARY KEY(id, st);
ALTER TABLE tt ADD PARTITION BY LIST(st) (
    PARTITION p0 VALUES IN (20,10),
    PARTITION p1 VALUES IN (0,-10)
);

but got the following error:

Mysql::Error: 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 'ALTER TABLE tt ADD PARTITION (PARTITION p0 VALUES IN' at line 2:

Can someone please let me what's wrong with the syntax?

Upvotes: 0

Views: 21875

Answers (1)

Mat
Mat

Reputation: 206659

ALTER TABLE orders PARTITION BY LIST(st) (
          PARTITION p0 VALUES IN (20,10),
          PARTITION p1 VALUES IN (0,-10)
);

The ADD is extraneous - the syntax is essentially identical to the CREATE TABLE statement.

Make sure you have a good, restorable backup before doing this.

Log:

mysql> create table orders (id int, st int, whatever varchar(10), primary key (id));
Query OK, 0 rows affected (0.06 sec)

mysql> ALTER TABLE orders DROP PRIMARY KEY, ADD PRIMARY KEY(id, st);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE orders PARTITION BY LIST(st) (
    ->           PARTITION p0 VALUES IN (20,10),
    ->           PARTITION p1 VALUES IN (0,-10)
    -> );
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

Upvotes: 2

Related Questions