Question

关于mysql创建表分区，让id自增长、加上主键的话就报错，取消就能创建成功的问题！求大神帮助下！！！

DROP TABLE IF EXISTS `t_meterorigvalue`;
CREATE TABLE `t_meterorigvalue` (
`ID` int(18) NOT NULL AUTO_INCREMENT ,
`F_OrigValueID` varchar(32) DEFAULT NULL,
`F_BuildID` varchar(16) DEFAULT NULL ,
`F_GateWayID` varchar(2) DEFAULT NULL ,
`F_MeterParamID` varchar(50) DEFAULT NULL ,
`F_MeterID` smallint(6) DEFAULT NULL ,
`F_Address` varchar(16) DEFAULT NULL ,
`F_FunctionID` smallint(6) DEFAULT NULL ,
`F_OrigValue` varchar(32) DEFAULT NULL ,
`F_CollectTime` datetime NOT NULL ,
`F_ReceiveTime` datetime DEFAULT NULL ,
`F_OrigStaCode` varchar(8) DEFAULT NULL,
`F_OrigErrCode` varchar(8) DEFAULT NULL ,
`F_MeterType` smallint(6) DEFAULT NULL,
`F_PackType` smallint(6) DEFAULT NULL ,
`F_ContinusHandle` smallint(6) DEFAULT NULL ,
`F_CalcTime` datetime DEFAULT NULL ,
`F_State` smallint(6) DEFAULT NULL ,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=115169 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (month(F_CollectTime)) (
PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (4) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (5) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (6) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (7) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (8) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (9) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (10)ENGINE = InnoDB,
PARTITION p10 VALUES LESS THAN (11) ENGINE = InnoDB,
PARTITION p11 VALUES LESS THAN MAXVALUE
)*/;

`ID` int(18) NOT NULL AUTO_INCREMENT
PRIMARY KEY (`ID`)
加上这个就会报错

Answer 1

MySQL主键的限制，每一个分区表中的公式中的列，必须在主键/unique key 中包括
MYSQL的官方文档里是这么说明的
18.5.1. Partitioning Keys, Primary Keys, and Unique Keys
This section discusses the relationship of partitioning keys with primary keys and unique keys. The rule governing this relationship can be expressed as follows: 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 columnin the table's partitioning expression. (This also includes the table's primary key, since it is by definition a unique key. This particular case is discussed later in this section.) For example, each of the following table creation statements is invalid:

解决办法是创建复合主键,将id和你的分区字段创建复合主键..