CODEWITHSUNDEEP
mysql
Frieda
Frieda

Reputation: 163

MySQL : Create Partition

I have table like this, I want to make partition based on Category ID.

CREATE TABLE film_category (
   film_id SMALLINT UNSIGNED NOT NULL,
   category_id TINYINT UNSIGNED NOT NULL,
   last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
   CURRENT_TIMESTAMP,
   PRIMARY KEY (film_id, category_id),
   CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES 
   film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE,
   CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) 
   REFERENCES category (category_id) ON DELETE RESTRICT ON UPDATE 
   CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8;

So I wrote in mysql shell

ALTER TABLE film_category

PARTITION BY RANGE(category_id) (

PARTITION p1 VALUES ('1'),
PARTITION p2 VALUES ('2'), 
PARTITION p3 VALUES ('3'),  
PARTITION p4 VALUES ('4'),
PARTITION p5 VALUES ('5'),
PARTITION p6 VALUES ('6'),
PARTITION p7 VALUES ('7'),
PARTITION p8 VALUES ('8'),
PARTITION p9 VALUES ('9'),
PARTITION p10 VALUES ('10'),
PARTITION p11 VALUES ('11'),
PARTITION p12 VALUES ('12'),
PARTITION p13 VALUES ('13'),
PARTITION p14 VALUES ('14'),
PARTITION p15 VALUES ('15'),
PARTITION p16 VALUES ('16')

); But I got error message 

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 '('1'),
PARTITION p2 VALUES ('2'), 
PARTITION p3 VALUES ('3'),  
PARTITION ' at line 5

Any clue on what syntax error I made?

Upvotes: 0

Views: 30

Answers (1)

O. Jones
O. Jones

Reputation: 108839

You need ...

PARTITION p1 VALUES LESS THAN (2),    
PARTITION p2 VALUES LESS THAN (3),

etc. Two things.

  1. VALUES LESS THAN is the syntax.

  2. Don't put numbers in quotes ('2') when you mean actual numbers (2)

Upvotes: 1

Related Questions