通过对分区知识的学习,终于展开实践,可是发现出师不利,对于别人提供的例子复制改改发现出现了问题。
别人的例子:
CREATE TABLE employee(id INT NOT NULL,
fname VARCHAR(30),Iname VARCHAR(30),job_code INT NOT NULL,
store_id INT NOT )PARTITION BY RANGE (store_id)(
PARTITION p0 VALUES LESS THAN (2),PARTITION p1 VALUES LESS THAN (3),
PARTITION p2 VALUES LESS THAN MAXVALUE);
我的sql语句:
CREATE TABLE `tb_famenshidu2` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`famenid` INT(11) ,
`shidu` VARCHAR(30) ,
`time` DATETIME ,
`flag` VARCHAR(2) ,
PRIMARY KEY (`id`)
)PARTITION BY RANGE (famenid)(
PARTITION p0 VALUES LESS THAN (2),PARTITION p1 VALUES LESS THAN (3),
PARTITION p2 VALUES LESS THAN MAXVALUE);
报错:A primary key must include all columns in the table's partitioning function
也就是报错说 你的分区字段不包括主键,查看mysql手册有了更深的认识:
解决一:发现别人的例子,没有主键的,所以我也尝试这样解决:
CREATE TABLE `tb_famenshidu1` (
`id` INT(40) NOT NULL AUTO_INCREMENT,
`famenid` INT(11) NOT NULL,
`shidu` VARCHAR(30) DEFAULT NULL,
`time` DATETIME DEFAULT NULL,
`flag` VARCHAR(2) DEFAULT NULL,
KEY `id` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=84389 DEFAULT CHARSET=gbk
PARTITION BY RANGE (famenid)
(PARTITION p0 VALUES LESS THAN (2) ENGINE = INNODB,
PARTITION p1 VALUES LESS THAN (3) ENGINE = INNODB,
PARTITION p2 VALUES LESS THAN (4) ENGINE = INNODB,
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = INNODB ) ;
我用jdbc向数据库插入数据发现正常,修改hibernate的配置,去除主键映射后发现,提取信息也是正确的,所以在不影响功能的前天下去掉主键为一种方法吧。
查看分区
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM information_schema.partitions WHERE
table_schema = SCHEMA()
AND table_name='tb_famenshidu1';
解决二:复合键