ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

mysql 中创建分区表的时候报 ：ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

mysql>  CREATE TABLE `lsxhdddmx0499` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `order_sn` varchar(20) NOT NULL DEFAULT '' COMMENT 'order_sn of order_info',
    ->   `lylx_name` varchar(20) NOT NULL DEFAULT '' COMMENT '来源类型的名称',
    ->   `shop_name` varchar(64) NOT NULL DEFAULT '' COMMENT '店铺简称',
    ->   `pay_time` datetime NOT NULL DEFAULT '2017-01-01 00:00:00' COMMENT '支付时间',
    ->   `sku` varchar(100) NOT NULL DEFAULT '' COMMENT 'sku of goods_sku',
    ->   `goods_sn` varchar(60) NOT NULL DEFAULT '' COMMENT 'goods_sn of goods',
    ->   `market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '标准价',
    ->   `discount` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '折扣',
    ->   `goods_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '单价',
    ->   `goods_number` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'sku级别的商品数量',
    ->   `amount` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '金额',
    ->   `receiver_name` varchar(32) NOT NULL DEFAULT '' COMMENT '收货人',
    ->   `receiver_mobile` varchar(32) NOT NULL DEFAULT '' COMMENT '联系电话',
    ->   `receiver_address` varchar(1024) NOT NULL DEFAULT '' COMMENT '收货地址',
    ->   `ckrq` date NOT NULL DEFAULT '2017-01-01' COMMENT '出库日期',
    ->   `receiver_province` varchar(64) NOT NULL DEFAULT '' COMMENT '省',
    ->   `receiver_city` varchar(64) NOT NULL DEFAULT '' COMMENT '市',
    ->   `user_name` varchar(64) NOT NULL DEFAULT '' COMMENT '会员昵称',
    ->   `deal_code` varchar(64) NOT NULL DEFAULT '' COMMENT '交易号',
    ->   `is_hh` tinyint(1) NOT NULL DEFAULT '0' COMMENT '换货单{0:否;1:是}',
    ->   `shipping_name` varchar(120) NOT NULL DEFAULT '' COMMENT '物流公司名称',
    ->   `fhck_name` varchar(32) NOT NULL DEFAULT '' COMMENT '发货仓库',
    ->   PRIMARY KEY (`id`),
    ->   KEY `ckrq` (`ckrq`),
    ->   KEY `order_sn` (`order_sn`),
    ->   KEY `goods_sn` (`goods_sn`),
    ->   KEY `sku` (`sku`)
    -> )ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='零售销货单订单商品明细'
    -> partition by RANGE COLUMNS(ckrq)
    -> (
    ->     PARTITION p201900 VALUES LESS THAN ('2019-01-01'),
    ->     PARTITION p201901 VALUES LESS THAN ('2019-02-01'),
    ->     PARTITION p201902 VALUES LESS THAN ('2019-03-01'),
    ->     PARTITION p201903 VALUES LESS THAN ('2019-04-01'),
    ->     PARTITION p201904 VALUES LESS THAN ('2019-05-01'),
    ->     PARTITION p201905 VALUES LESS THAN ('2019-06-01'),
    ->     PARTITION pnow VALUES LESS THAN MAXVALUE
    -> );
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

MySQL主键的限制，每一个分区表中的公式中的列，必须在主键/unique key 中包括

方法1： 

顺应MYSQL的要求，就把分区字段加入到主键中，组成复合主键，测试通过，分区成功。

mysql>  CREATE TABLE `lsxhdddmx0499` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `order_sn` varchar(20) NOT NULL DEFAULT '' COMMENT 'order_sn of order_info',
    ->   `lylx_name` varchar(20) NOT NULL DEFAULT '' COMMENT '来源类型的名称',
    ->   `shop_name` varchar(64) NOT NULL DEFAULT '' COMMENT '店铺简称',
    ->   `pay_time` datetime NOT NULL DEFAULT '2017-01-01 00:00:00' COMMENT '支付时间',
    ->   `sku` varchar(100) NOT NULL DEFAULT '' COMMENT 'sku of goods_sku',
    ->   `goods_sn` varchar(60) NOT NULL DEFAULT '' COMMENT 'goods_sn of goods',
    ->   `market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '标准价',
    ->   `discount` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '折扣',
    ->   `goods_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '单价',
    ->   `goods_number` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'sku级别的商品数量',
    ->   `amount` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '金额',
    ->   `receiver_name` varchar(32) NOT NULL DEFAULT '' COMMENT '收货人',
    ->   `receiver_mobile` varchar(32) NOT NULL DEFAULT '' COMMENT '联系电话',
    ->   `receiver_address` varchar(1024) NOT NULL DEFAULT '' COMMENT '收货地址',
    ->   `ckrq` date NOT NULL DEFAULT '2017-01-01' COMMENT '出库日期',
    ->   `receiver_province` varchar(64) NOT NULL DEFAULT '' COMMENT '省',
    ->   `receiver_city` varchar(64) NOT NULL DEFAULT '' COMMENT '市',
    ->   `user_name` varchar(64) NOT NULL DEFAULT '' COMMENT '会员昵称',
    ->   `deal_code` varchar(64) NOT NULL DEFAULT '' COMMENT '交易号',
    ->   `is_hh` tinyint(1) NOT NULL DEFAULT '0' COMMENT '换货单{0:否;1:是}',
    ->   `shipping_name` varchar(120) NOT NULL DEFAULT '' COMMENT '物流公司名称',
    ->   `fhck_name` varchar(32) NOT NULL DEFAULT '' COMMENT '发货仓库',
    ->   PRIMARY KEY (`id`,`ckrq`),
    ->   KEY `ckrq` (`ckrq`),
    ->   KEY `order_sn` (`order_sn`),
    ->   KEY `goods_sn` (`goods_sn`),
    ->   KEY `sku` (`sku`)
    -> )ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='零售销货单订单商品明细'
    -> partition by RANGE COLUMNS(ckrq)
    -> (
    ->     PARTITION p201900 VALUES LESS THAN ('2019-01-01'),
    ->     PARTITION p201901 VALUES LESS THAN ('2019-02-01'),
    ->     PARTITION p201902 VALUES LESS THAN ('2019-03-01'),
    ->     PARTITION p201903 VALUES LESS THAN ('2019-04-01'),
    ->     PARTITION p201904 VALUES LESS THAN ('2019-05-01'),
    ->     PARTITION p201905 VALUES LESS THAN ('2019-06-01'),
    ->     PARTITION pnow VALUES LESS THAN MAXVALUE
    -> );

方法2： 

既然MYSQL要把分区字段包含在主键内才能创建分区，那么在创建表的时候，先不指定主键字段，测试通过