Mysql Partition Introduction (ii)--RANGE partition

Partition by scope, give each partition a certain range, the range must be contiguous and cannot be duplicated, use the values less than operator <br/>

Let's start by creating a range partition table

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT ‘1970-01-01‘,    separated DATE NOT NULL DEFAULT ‘9999-12-31‘,    job_code INT NOT NULL,    store_id INT NOT NULL)PARTITION BY RANGE (store_id) (    PARTITION p0 VALUES LESS THAN (6),    PARTITION p1 VALUES LESS THAN (11),    PARTITION p2 VALUES LESS THAN (16),    PARTITION p3 VALUES LESS THAN (21));

store_id less than 6, will be placed in the first partition, less than 11 will be placed in the second partition. <br>
如果我的store_id大于21怎么办呢？, so we have to change the way this partition is created.

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT ‘1970-01-01‘,    separated DATE NOT NULL DEFAULT ‘9999-12-31‘,    job_code INT NOT NULL,    store_id INT NOT NULL)PARTITION BY RANGE (store_id) (    PARTITION p0 VALUES LESS THAN (6),    PARTITION p1 VALUES LESS THAN (11),    PARTITION p2 VALUES LESS THAN (16),    PARTITION p3 VALUES LESS THAN (21),    PARTITION p4 VALUES LESS THAN (MAXVALUE));

Added a maxvalue, maxvalue means that more than 21 of the data will be put into this partition, of course, there is another way to avoid this problem, is to add the Ignore keyword in the insert. <br/>

分区键类型为时间转时间戳<br/>
You can use Unix-timestamp ()

 CREATE TABLE quarterly_report_status (report_id INT not NULL, Report_status VARCHAR () is not NULL, Report_u pdated TIMESTAMP not NULL DEFAULT current_timestamp on UPDATE current_timestamp) PARTITION by RANGE (unix_timestamp _updated)) (PARTITION p0 values less THAN (Unix_timestamp (' 2008-01-01 00:00:00 '), PARTITION p1 values less THAN (Unix_timestamp (' 2008-04-01 00:00:00 ')), PARTITION p2 VALUES less THAN (Unix_timestamp (' 2008-07-01 00:00:00 ')), PARTITION P3 Values less THAN (Unix_timestamp (' 2008-10-01 00:00:00 '), PARTITION P4 values less THAN (unix_timestam P (' 2009-01-01 00:00:00 ')), PARTITION P5 VALUES less THAN (Unix_timestamp (' 2009-04-01 00:00:00 ')), PARTITION P6 VA Lues less THAN (Unix_timestamp ("2009-07-01 00:00:00"), PARTITION P7 VALUES less THAN (Unix_timestamp (' 2009-10-01 00 : 00:00 '), PARTITION P8 values less THAN (Unix_timestamp (' 2010-01-01 00:00:00 '), PARTITION p9 values less THAN ( MAXVALUE)); 

In addition to Unix_timestamp, other expressions involving timestamps are not allowed

基于时间数字的分区<br/>

CREATE TABLE members (    firstname VARCHAR(25) NOT NULL,    lastname VARCHAR(25) NOT NULL,    username VARCHAR(16) NOT NULL,    email VARCHAR(35),    joined DATE NOT NULL)PARTITION BY RANGE(YEAR(joined) ) (    PARTITION p0 VALUES LESS THAN (1960),    PARTITION p1 VALUES LESS THAN (1970),    PARTITION p2 VALUES LESS THAN (1980),    PARTITION p3 VALUES LESS THAN (1990),    PARTITION p4 VALUES LESS THAN MAXVALUE);

Mysql Partition Introduction (ii)--RANGE partition