CODEWITHSUNDEEP
mysqlsyntaxpartitioning
Griff
Griff

Reputation: 1747

Partition syntax mysql contains error?

I am trying to create the following table below,

CREATE TABLE IF NOT EXISTS `hashes` (
    `hash` binary(20) NOT NULL,
    `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `category` tinyint(1) unsigned NOT NULL DEFAULT '0',
    `indexed` tinyint(1) unsigned NOT NULL DEFAULT '0',
    `dcma` tinyint(1) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`hash`),
    KEY `category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci,
PARTITION BY LIST(`category`)( 
    PARTITION p0 VALUES IN(0), 
    PARTITION p1 VALUES IN(1), 
    PARTITION p2 VALUES IN(2), 
    PARTITION p3 VALUES IN(3),
    PARTITION p4 VALUES IN(4),
    PARTITION p5 VALUES IN(5),
    PARTITION p6 VALUES IN(6),
);

The partition is for the column category it is a tinyint(1) and each number corresponds to a category. As far as I understand I cannot partition by enum but I thought this way would work?

Where am I going wrong?

EDIT

The error is as follows,

#1064 - 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 'PARTITION BY LIST(`category`)( PARTITION p0 VALUES IN(0), PARTITION ' at line 10

Upvotes: 1

Views: 2655

Answers (2)

Devart
Devart

Reputation: 121902

There were two syntax errors. Try this code -

    CREATE TABLE IF NOT EXISTS `hashes`(
      `hash` BINARY(20) NOT NULL,
      `name` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `category` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
      `indexed` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
      `dcma` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
--      PRIMARY KEY (`hash`),
      KEY `category` (`category`)
    )
    ENGINE = INNODB
    DEFAULT CHARSET = utf8
    COLLATE = utf8_unicode_ci
    PARTITION BY LIST (`category`) (
      PARTITION p0 VALUES IN (0),
      PARTITION p1 VALUES IN (1),
      PARTITION p2 VALUES IN (2),
      PARTITION p3 VALUES IN (3),
      PARTITION p4 VALUES IN (4),
      PARTITION p5 VALUES IN (5),
      PARTITION p6 VALUES IN (6)
    );

Note, that I have commented out primary key definition. Partitions have some limitations, one of them - All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have. In other words, every unique key on the table must use every column in the tables partitioning expression.

More information here - Partitioning Keys, Primary Keys, and Unique Keys.

Upvotes: 2

Girish Rao
Girish Rao

Reputation: 2669

I think you have an extra comma if this is an exact copy of your code.

PARTITION BY LIST(`category`)( 
  PARTITION p0 VALUES IN(0), 
  PARTITION p1 VALUES IN(1), 
  PARTITION p2 VALUES IN(2), 
  PARTITION p3 VALUES IN(3),
  PARTITION p4 VALUES IN(4),
  PARTITION p5 VALUES IN(5),
  PARTITION p6 VALUES IN(6),
);

Remove the last comma

Upvotes: 0

Related Questions