欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

mysql使用经验(十一)-按日期进行表分区

更新时间: 2014-01-05 02:08:30 责任编辑: Author_N1

 

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)
上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-01-05 02:08:30
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/6086/
本文WWW.DOC100.NET DOC100.NET版权所有。