MYSQL使用心得(十一) 按日期进行表分区 - MySQL教程
创建
create table user (id int(11) NOT NULL AUTO_INCREMENT, name varchar(255), birthday date default NULL, PRIMARY KEY (id,birthday)) partition by range (birthday) (partition p0 values less than ('1985-12-26'), partition p1 VALUES LESS THAN ('2013-12-01')); 报错 ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT 正常创建 create table user (id int(11) NOT NULL AUTO_INCREMENT, name varchar(255), birthday date default NULL, PRIMARY KEY (id,birthday)) engine=innodb partition by range (to_days(birthday)) (PARTITION p0 VALUES LESS THAN (to_days('1985-01-01')), PARTITION p1 VALUES LESS THAN (to_days('2004- 01-01')), PARTITION p2 VALUES LESS THAN (to_days('2005-01-01')), PARTITION p3 VALUES LESS THAN (to_days('2006-01-01')), PARTITION p4 VALUES LESS THAN (to_days('2007-01-01')), PARTITION p5 VALUES LESS THAN (to_days('2010-01-01')), PARTITION p6 VALUES LESS THAN MAXVALUE); 执行解释扫描 explain partitions select birthday from user where birthday>'2006-06-01' and birthday<'2006-12- 12'G 输出 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: p0,p4 type: index possible_keys: NULL key: PRIMARY key_len: 7 ref: NULL rows: 2 Extra: Using where; Using index 1 row in set (0.00 sec) 查看本栏目更多精彩内容:http://www.bianceng.cn/database/MySQL/ (编辑:应用网_镇江站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |