Home > Database > Mysql Tutorial > MYSQL使用经验(十一)-按日期进行表分区

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Release: 2016-06-07 16:15:54
Original
2590 people have browsed it

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 ('198

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
输出
*************************** 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)
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Issues
MySQL stops process
From 1970-01-01 08:00:00
0
0
0
Error when installing mysql on linux
From 1970-01-01 08:00:00
0
0
0
phpstudy cannot start mysql?
From 1970-01-01 08:00:00
0
0
0
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template