Mysql --分区(3)range分区_only range partitioning can use values less than i

3.分区类型

RANGE分区

按照range分区的表是利用取值范围将数据分成分区，区间要连续并且不能互相重叠，使用values less than操作符进行分区定义

CREATE TABLE tnp (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY pk (id)
)
partition by range (id) (
    partition p0 values less than(10),
    partition p1 values less than(20),
    partition p2 values less than(30)
);

1
2
3
4
5
6
7
8
9
10
11
12

当插入ID大于30的值是，会出现错误
可以在设置分区时使用values less than maxvalue子句

alter table tnp add partition(partition p3 values less than maxvalue);
1

MySQL支持在values less than子句中使用表达式，比如，以日期作为range分区的分区列:

CREATE TABLE emp_date (
    id INT NOT NULL auto_increment,
    name VARCHAR(255),
    hiredate date not null default '1970-01-01',
    key (id)
)
partition by range (year(hiredate)) (
    partition p0 values less than(1995),
    partition p1 values less than(2000),
    partition p2 values less than(2005)
);
1
2
3
4
5
6
7
8
9
10
11

MySQL也支持对TIMESTAMP列进行range分区

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_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_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES 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)
);

实际是是将TMESTAMP类型通过UNIX_TIMESTAMP函数转换成INT型
mysql> select  UNIX_TIMESTAMP('2008-01-01 00:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2008-01-01 00:00:00') |
+---------------------------------------+
|                            1199116800 |
+---------------------------------------+
1 row in set (0.00 sec)

delimiter $$
drop procedure if exists pr_insertdate_2$$
create procedure pr_insertdate_2(in begindate date,in enddate date,in tabname varchar(40))
begin
    set begindate = timestamp(begindate);
    while begindate<enddate
    do
        set @s=concat_ws(' ','insert into',tabname,'values(1,''true'',''',begindate,''')');
        prepare stmt from @s;
        execute stmt;
        drop prepare stmt;
        set begindate = date_add(begindate,interval 1 day);
    end while;
end$$

delimiter ;

mysql> call pr_insertdate_2('2007-01-01','2010-12-31','quarterly_report_status');
Query OK, 0 rows affected (0.32 sec)

mysql> select 
    ->   partition_name part,  
    ->   partition_expression expr,  
    ->   partition_description descr,  
    ->   table_rows  
    -> from information_schema.partitions  where 
    ->   table_schema = schema()  
    ->   and table_name='quarterly_report_status';
+------+---------------------------------+------------+------------+
| part | expr                            | descr      | table_rows |
+------+---------------------------------+------------+------------+
| p0   |  UNIX_TIMESTAMP(report_updated) | 1199116800 |        365 |
| p1   |  UNIX_TIMESTAMP(report_updated) | 1206979200 |         91 |
| p2   |  UNIX_TIMESTAMP(report_updated) | 1214841600 |         91 |
| p3   |  UNIX_TIMESTAMP(report_updated) | 1222790400 |         92 |
| p4   |  UNIX_TIMESTAMP(report_updated) | 1230739200 |         92 |
| p5   |  UNIX_TIMESTAMP(report_updated) | 1238515200 |         90 |
| p6   |  UNIX_TIMESTAMP(report_updated) | 1246377600 |         91 |
| p7   |  UNIX_TIMESTAMP(report_updated) | 1254326400 |         92 |
| p8   |  UNIX_TIMESTAMP(report_updated) | 1262275200 |         92 |
| p9   |  UNIX_TIMESTAMP(report_updated) | MAXVALUE   |        364 |
+------+---------------------------------+------------+------------+
10 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

涉及TIMESTAMP值的任何其他表达式不允许 (See Bug #42849.)

当一个或多个下列条件为真时，范围分区是特别有用的：
1.需要卸载旧数据
此时你就可以使用 ALTER TABLE employees DROP PARTITION p0;卸载旧数据，效率要比delete高很多

2.你想用一列包含日期或时间值，或包含从其他系列所产生的价值

You want to use a column containing date or time values, or containing values arising from some other series

3.where条件列经常为分区列。这个不用多说了，利用分区裁剪特性