MySQL分区唯一键

Question

我在看这篇文章:

http://www.chrismoos.com/2010/01/31/mysql-partitioning-tables-with-millions-of-rows

但是当我运行我的查询来划分我的产品表(包含500,000行)时，我得到了错误:

#1503 - A UNIQUE INDEX must include all columns in the table's partitioning function

我的查询是:

ALTER TABLE parts_library PARTITION by HASH(manufacturerId) PARTITIONS 200

我的主键是id和manufacturerId的复合键，这与本文中相同，所以我不明白为什么我得到这个错误。

下面是我的表的create语句:

CREATE TABLE IF NOT EXISTS `parts_library` (
  `id` int(11) NOT NULL,
  `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `typeId` int(3) NOT NULL COMMENT 'Reference to part_types',
  `manufacturerId` int(11) NOT NULL DEFAULT '0',
  `familyId` int(11) NOT NULL DEFAULT '454',
  `partNumber` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'e.g. 6ES5123B62',
  `idealForm` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'E.g. 6ES5-123-B6/2',
  `comCodeId` int(11) DEFAULT NULL,
  `countryOriginId` int(3) NOT NULL DEFAULT '258',
  `minStockLevel` int(11) DEFAULT NULL,
  `weight` decimal(11,2) DEFAULT NULL,
  `width` decimal(11,2) DEFAULT NULL,
  `height` decimal(11,2) DEFAULT NULL,
  `depth` decimal(11,2) DEFAULT NULL,
  `validated` tinyint(1) NOT NULL DEFAULT '0',
  `onWeb` tinyint(1) DEFAULT '0',
  `indexed` tinyint(1) NOT NULL DEFAULT '1',
  `averageMargin` decimal(11,2) NOT NULL,
  PRIMARY KEY (`id`,`manufacturerId`),
  UNIQUE KEY `partNumber` (`partNumber`),
  KEY `fk_parts_library_parts_categories1` (`typeId`),
  KEY `fk_parts_library_manufacturers1` (`manufacturerId`),
  KEY `fk_parts_library_geo_countries1` (`countryOriginId`),
  KEY `fk_parts_library_parts_families1` (`familyId`),
  KEY `indexed` (`indexed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

如何对表进行分区?

Answer 1

你的代码和文章的区别——你有一个

UNIQUE KEY 'partNumber' ('partNumber')

在文章中没有提到。DB抱怨你需要在键定义中包含用于分区的列，如:

UNIQUE KEY 'partNumber' ('manufacturerId','partNumber')