Mysql分区表

mysql> create table stu(Id int(9) not null auto_increment, Name varchar(100) not null default '',Time datetime default null,primary key (Id,Time)) partition by range(to_days(Time))(partition p0 values less than (to_days('2015-03-21')),partition p1 values less than (to_days('2015-03-22')));

Query OK, 0 rows affected

mysql> show create table stu;

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                               |

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| stu   | CREATE TABLE `stu` (

  `Id` int(9) NOT NULL AUTO_INCREMENT,

  `Name` varchar(100) NOT NULL DEFAULT '',

  `Time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  PRIMARY KEY (`Id`,`Time`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE (to_days(Time))

(PARTITION p0 VALUES LESS THAN (736043) ENGINE = InnoDB,

 PARTITION p1 VALUES LESS THAN (736044) ENGINE = InnoDB) */ |

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set

mysql> alter table stu add partition (partition p2 values less than (to_days('2015-3-23')));

Query OK, 0 rows affected

Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table stu;

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                       |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| stu   | CREATE TABLE `stu` (

  `Id` int(9) NOT NULL AUTO_INCREMENT,

  `Name` varchar(100) NOT NULL DEFAULT '',

  `Time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  PRIMARY KEY (`Id`,`Time`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE (to_days(Time))

(PARTITION p0 VALUES LESS THAN (736043) ENGINE = InnoDB,

 PARTITION p1 VALUES LESS THAN (736044) ENGINE = InnoDB,

 PARTITION p2 VALUES LESS THAN (736045) ENGINE = InnoDB) */ |

+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set