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要把分区字段包含在主键内才能创建分区,那么在创建表的时候,先不指定主键字段,测试通过