MySQL 主必须包含表的分区位置错误中的所有列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11896067/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
A primary must include all columns in the table's partitioning location error?
提问by Dhileepan
I tried to create a table with range partitioning. But it shows the following error:
我试图创建一个带有范围分区的表。但它显示以下错误：
A primary must include all columns in the table's partitioning location.
主节点必须包括表分区位置中的所有列。
This is my SQL statement:
这是我的 SQL 语句：
CREATE TABLE `tbl_emp_confirmation` (
`fld_id` int(11) NOT NULL AUTO_INCREMENT,
`fldemp_id` varchar(100) DEFAULT NULL,
`fldempname` varchar(100) DEFAULT NULL,
`fldjoindate` varchar(100) DEFAULT NULL,
`fldconfirmdate` Date NOT NULL,
`fldresigndate` varchar(100) DEFAULT NULL,
`fldstatus` varchar(50) DEFAULT NULL,
`fldcon_status` varchar(100) DEFAULT NULL,
UNIQUE KEY `fld_id` (`fld_id`),
KEY `in_empconfirmation` (`fldemp_id`,`fldempname`,`fldjoindate`,`fldconfirmdate`)
) PARTITION BY RANGE ( Month(fldconfirmdate))
(PARTITION p_JAN VALUES LESS THAN (TO_DAYS('2011-01-01')),
PARTITION p_FEB VALUES LESS THAN (TO_DAYS('2011-02-01')),
PARTITION p_MAR VALUES LESS THAN (TO_DAYS('2011-03-01')),
PARTITION p_APR VALUES LESS THAN (TO_DAYS('2011-04-01')),
PARTITION p_MAY VALUES LESS THAN (TO_DAYS('2011-05-01')),
PARTITION p_MAX VALUES LESS THAN MAXVALUE );
回答by jmoro
You are partitioning data using fldconfirmdate, which is part of your PK, but not a part of your UNIQUE KEY fld_id.
您正在使用 fldconfirmdate 对数据进行分区，这是您的 PK 的一部分，但不是您的 UNIQUE KEY fld_id 的一部分。
This is extracted from the MySQL manual:
这是从MySQL 手册中提取的：
In other words, every unique key on the table must use every column in the table's partitioning expression.
换句话说，表上的每个唯一键都必须使用表分区表达式中的每一列。
Which means that, making fldconfirmdate to be a part of your UNIQUE KEY 'fld_id′ will solve the problem.
这意味着，使 fldconfirmdate 成为您的 UNIQUE KEY 'fld_id' 的一部分将解决问题。
CREATE TABLE `tbl_emp_confirmation` (
`fld_id` int(11) NOT NULL AUTO_INCREMENT,
`fldemp_id` varchar(100) DEFAULT NULL,
`fldempname` varchar(100) DEFAULT NULL,
`fldjoindate` varchar(100) DEFAULT NULL,
`fldconfirmdate` Date NOT NULL,
`fldresigndate` varchar(100) DEFAULT NULL,
`fldstatus` varchar(50) DEFAULT NULL,
`fldcon_status` varchar(100) DEFAULT NULL,
UNIQUE KEY `fld_id` (`fld_id`, `fldconfirmdate`),
KEY `in_empconfirmation` (`fldemp_id`,`fldempname`,`fldjoindate`,`fldconfirmdate`)
) PARTITION BY RANGE ( Month(fldconfirmdate))
(PARTITION p_JAN VALUES LESS THAN (TO_DAYS('2011-01-01')),
PARTITION p_FEB VALUES LESS THAN (TO_DAYS('2011-02-01')),
PARTITION p_MAR VALUES LESS THAN (TO_DAYS('2011-03-01')),
PARTITION p_APR VALUES LESS THAN (TO_DAYS('2011-04-01')),
PARTITION p_MAY VALUES LESS THAN (TO_DAYS('2011-05-01')),
PARTITION p_MAX VALUES LESS THAN MAXVALUE );
