MySQL分区介绍

我就是我 2024-04-18 01:59 47阅读 0赞

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分  
mysql> create table t1(  
    -> col1 int not null,col2 date not null,col3 int not null,col4 int not null,unique key(col1,col2)) partition by hash(col3) partitions 4;  
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function  
  
 mysql> create table t1(  
    -> col1 int not null,col2 date not null,col3 int not null,col4 int not null,unique key(col1,col2,col3)) partition by hash(col3) partitions 4;  
Query OK, 0 rows affected (0.49 sec)  
  
 mysql> create table t2(  
    ->    col1 int null,  
    -> col2 date null,  
    -> col3 int null,  
    -> col4 int null  
    -> ) engine=innodb  
    -> partition by hash(col3)  
    -> partitions 4;  
Query OK, 0 rows affected (0.40 sec)  
mysql> create table t3(  
    ->    col1 int null,  
    -> col2 date null,  
    -> col3 int null,  
    -> col4 int null,  
    -> key (col4)  
    -> ) engine=innodb  
    -> partition by hash(col3)  
    -> partitions 4;  
Query OK, 0 rows affected (0.23 sec)   
  
**\--查看数据库是否支持分区**  
 MariaDB \[test\]> show plugins;  
 \+-------------------------------+----------+--------------------+---------+---------+  
 | Name                          | Status   | Type               | Library | License |  
 \+-------------------------------+----------+--------------------+---------+---------+  
 | binlog                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |  
 | mysql\_native\_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |  
 .....  
 | partition                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |  
 \+-------------------------------+----------+--------------------+---------+---------+  
  
 MariaDB \[test\]> select \* from INFORMATION\_SCHEMA.plugins where plugin\_name='partition'\\G  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
            PLUGIN\_NAME: partition  
         PLUGIN\_VERSION: 1.0  
          PLUGIN\_STATUS: ACTIVE  
            PLUGIN\_TYPE: STORAGE ENGINE  
    PLUGIN\_TYPE\_VERSION: 100114.0  
         PLUGIN\_LIBRARY: NULL  
 PLUGIN\_LIBRARY\_VERSION: NULL  
          PLUGIN\_AUTHOR: Mikael Ronstrom, MySQL AB  
     PLUGIN\_DESCRIPTION: Partition Storage Engine Helper  
         PLUGIN\_LICENSE: GPL  
            LOAD\_OPTION: ON  
        PLUGIN\_MATURITY: Stable  
    PLUGIN\_AUTH\_VERSION: 1.0  
 1 row in set (0.00 sec)  
  
**--范围分区**  
 MariaDB \[test\]> 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 COLUMNS(joined) (  
     ->     PARTITION p0 VALUES LESS THAN ('1960-01-01'),  
     ->     PARTITION p1 VALUES LESS THAN ('1970-01-01'),  
     ->     PARTITION p2 VALUES LESS THAN ('1980-01-01'),  
     ->     PARTITION p3 VALUES LESS THAN ('1990-01-01'),  
     ->     PARTITION p4 VALUES LESS THAN MAXVALUE  
     -> );  
 Query OK, 0 rows affected (0.45 sec)  
  
 MariaDB \[test\]> 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 MAXVALUE  
     -> );  
 Query OK, 0 rows affected (0.49 sec)  
  
 MariaDB \[test\]> insert into employees(id,fname,lname,job\_code,store\_id) values(1,'John','Terry',10,100);  
 Query OK, 1 row affected (0.06 sec)  
 MariaDB \[test\]> insert into employees(id,fname,lname,job\_code,store\_id) values(2,'Tom','Carl',10,1);  
 Query OK, 1 row affected (0.06 sec)  
 MariaDB \[test\]> insert into employees(id,fname,lname,job\_code,store\_id) values(2,'Lily','Berg',20,7);  
 Query OK, 1 row affected (0.03 sec)  
 MariaDB \[test\]> insert into employees(id,fname,lname,job\_code,store\_id) values(3,'Lucy','Phynix',20,10);  
 Query OK, 1 row affected (0.13 sec)  
 MariaDB \[test\]> insert into employees(id,fname,lname,job\_code,store\_id) values(4,'Bill','Jones',20,15);  
 Query OK, 1 row affected (0.02 sec)  
 MariaDB \[test\]> insert into employees(id,fname,lname,job\_code,store\_id) values(5,'Jill','Deco',30,12);  
 Query OK, 1 row affected (0.08 sec)  
 MariaDB \[test\]> insert into employees(id,fname,lname,job\_code,store\_id) values(6,'Emily','Aaron',30,20);  
 Query OK, 1 row affected (0.02 sec)  
  
 MariaDB \[test\]> select \* from employees;  
 \+----+-------+--------+------------+------------+----------+----------+  
 | id | fname | lname  | hired      | separated  | job\_code | store\_id |  
 \+----+-------+--------+------------+------------+----------+----------+  
 |  2 | Tom   | Carl   | 1970-01-01 | 9999-12-31 |       10 |        1 |  
 |  2 | Lily  | Berg   | 1970-01-01 | 9999-12-31 |       20 |        7 |  
 |  3 | Lucy  | Phynix | 1970-01-01 | 9999-12-31 |       20 |       10 |  
 |  4 | Bill  | Jones  | 1970-01-01 | 9999-12-31 |       20 |       15 |  
 |  5 | Jill  | Deco   | 1970-01-01 | 9999-12-31 |       30 |       12 |  
 |  1 | John  | Terry  | 1970-01-01 | 9999-12-31 |       10 |      100 |  
 |  6 | Emily | Aaron  | 1970-01-01 | 9999-12-31 |       30 |       20 |  
 \+----+-------+--------+------------+------------+----------+----------+  
 7 rows in set (0.00 sec)  
  
 MariaDB \[test\]> show create table employees\\G  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
        Table: employees  
 Create Table: CREATE TABLE \`employees\` (  
   \`id\` int(11) NOT NULL,  
   \`fname\` varchar(30) DEFAULT NULL,  
   \`lname\` varchar(30) DEFAULT NULL,  
   \`hired\` date NOT NULL DEFAULT '1970-01-01',  
   \`separated\` date NOT NULL DEFAULT '9999-12-31',  
   \`job\_code\` int(11) NOT NULL,  
   \`store\_id\` int(11) NOT NULL  
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
 /\*!50100 PARTITION BY RANGE (store\_id)  
 (PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,  
  PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB,  
  PARTITION p2 VALUES LESS THAN (16) ENGINE = InnoDB,  
  PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) \*/  
 1 row in set (0.00 sec)  
  
按照年进行分区  
mysql> create table sales(  
    -> money int unsigned not null,  
    -> date datetime  
    -> ) engine=innodb  
    -> partition by range (year(date)) (  
    -> partition p2008 values less than (2009),  
    -> partition p2009 values less than (2010),  
    -> partition p2010 values less than (2011)  
    -> );  
Query OK, 0 rows affected (0.31 sec)  
  
mysql> insert into sales values (100, '2008-01-01'),(100, '2008-02-01'),(200, '2008-01-02'), (100, '2009-03-01'), (200, '2010-03-01');  
Query OK, 5 rows affected (0.13 sec)  
Records: 5  Duplicates: 0  Warnings: 0  
  
mysql> commit;  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> alter table sales drop partition p2008;  
Query OK, 0 rows affected (0.12 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> explain partitions  
    -> select \* from sales  
    -> where date>='2009-01-01' and date<='2009-12-31'\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
           id: 1  
  select\_type: SIMPLE  
        table: sales  
   partitions: p2009  
         type: ALL  
possible\_keys: NULL  
          key: NULL  
      key\_len: NULL  
          ref: NULL  
         rows: 2  
        Extra: Using where  
1 row in set (0.00 sec)  
  
**优化器只能对YEAR()、TO\_DAYS()、TO\_SECONDS()和UNIX\_TIMESTAMP()这类函数进行优化选择**  
 **下面这个例子中的分区创建有问题，在分区扫描的时候会扫描多个分区**  
按照每年每月来进行分区  
mysql> create table sales2(  
    -> money int unsigned not null,  
    -> date datetime  
    -> ) engine=innodb  
    -> partition by range (year(date)\*100+month(date)) (  
    -> partition p201001 values less than (201002),  
    -> partition p201002 values less than (201003),  
    -> partition p201003 values less than (201004)  
    -> );  
Query OK, 0 rows affected (0.20 sec)  
  
mysql> explain partitions select \* from sales2 where date>='2010-01-01' and date <= '2010-01-31';  
\+----+-------------+--------+-------------------------+------+---------------+------+---------+------+------+-------------+  
| id | select\_type | table  | partitions              | type | possible\_keys | key  | key\_len | ref  | rows | Extra       |  
\+----+-------------+--------+-------------------------+------+---------------+------+---------+------+------+-------------+  
|  1 | SIMPLE      | sales2 | p201001,p201002,p201003 | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |  
\+----+-------------+--------+-------------------------+------+---------------+------+---------+------+------+-------------+  
1 row in set (0.01 sec)  
  
下面例子为上面例子的正确创建方法  
mysql> create table sales1(  
    -> money int unsigned not null,  
    -> date datetime) engine=innodb  
    -> partition by range(to\_days(date)) (  
    -> partition p201001  
    -> values less than(to\_days('2010-02-01')),  
    -> partition p201002  
    -> values less than(to\_days('2010-03-01')),  
    -> partition p201003  
    -> values less than (to\_days('2010-04-01'))  
    -> );  
Query OK, 0 rows affected (0.20 sec)  
  
mysql> explain partitions select \* from sales1 where date>='2010-01-01' and date<='2010-01-31';  
\+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+  
| id | select\_type | table  | partitions | type | possible\_keys | key  | key\_len | ref  | rows | Extra       |  
\+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+  
|  1 | SIMPLE      | sales1 | p201001    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |  
\+----+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+  
1 row in set (0.00 sec)  
  
mysql> create table t(  
    -> id int  
    -> ) engine=innodb  
    -> partition by range (id) (  
    -> partition p0 values less than (10),  
    -> partition p1 values less than (20));  
Query OK, 0 rows affected (0.55 sec)  
  
mysql> system ls -lrt /var/lib/mysql/test  
\-rw-rw----. 1 mysql mysql     8556 Nov  3 14:22 t.frm  
\-rw-rw----. 1 mysql mysql       28 Nov  3 14:22 t.par  
\-rw-rw----. 1 mysql mysql    98304 Nov  3 14:22 t\#P\#p0.ibd  
\-rw-rw----. 1 mysql mysql    98304 Nov  3 14:22 t\#P\#p1.ibd  
  
mysql> select \* from information\_schema.partitions  
    -> where table\_schema=database() and table\_name='t'\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                TABLE\_CATALOG: def  
                 TABLE\_SCHEMA: test  
                   TABLE\_NAME: t  
               PARTITION\_NAME: p0  
            SUBPARTITION\_NAME: NULL  
   PARTITION\_ORDINAL\_POSITION: 1  
SUBPARTITION\_ORDINAL\_POSITION: NULL  
             PARTITION\_METHOD: RANGE  
          SUBPARTITION\_METHOD: NULL  
         PARTITION\_EXPRESSION: id  
      SUBPARTITION\_EXPRESSION: NULL  
        PARTITION\_DESCRIPTION: 10  
                   TABLE\_ROWS: 1  
               AVG\_ROW\_LENGTH: 16384  
                  DATA\_LENGTH: 16384  
              MAX\_DATA\_LENGTH: NULL  
                 INDEX\_LENGTH: 0  
                    DATA\_FREE: 0  
                  CREATE\_TIME: 2016-11-03 14:22:00  
                  UPDATE\_TIME: NULL  
                   CHECK\_TIME: NULL  
                     CHECKSUM: NULL  
            PARTITION\_COMMENT:  
                    NODEGROUP: default  
              TABLESPACE\_NAME: NULL  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                TABLE\_CATALOG: def  
                 TABLE\_SCHEMA: test  
                   TABLE\_NAME: t  
               PARTITION\_NAME: p1  
            SUBPARTITION\_NAME: NULL  
   PARTITION\_ORDINAL\_POSITION: 2  
SUBPARTITION\_ORDINAL\_POSITION: NULL  
             PARTITION\_METHOD: RANGE  
          SUBPARTITION\_METHOD: NULL  
         PARTITION\_EXPRESSION: id  
      SUBPARTITION\_EXPRESSION: NULL  
        PARTITION\_DESCRIPTION: 20  
                   TABLE\_ROWS: 2  
               AVG\_ROW\_LENGTH: 8192  
                  DATA\_LENGTH: 16384  
              MAX\_DATA\_LENGTH: NULL  
                 INDEX\_LENGTH: 0  
                    DATA\_FREE: 0  
                  CREATE\_TIME: 2016-11-03 14:22:00  
                  UPDATE\_TIME: NULL  
                   CHECK\_TIME: NULL  
                     CHECKSUM: NULL  
            PARTITION\_COMMENT:  
                    NODEGROUP: default  
              TABLESPACE\_NAME: NULL  
2 rows in set (0.00 sec)  
  
mysql> insert into t values(50);  
ERROR 1526 (HY000): Table has no partition for value 50  
mysql> alter table t  
    -> add partition(  
    -> partition p2 values less than maxvalue );  
Query OK, 0 rows affected (0.49 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> insert into t values(50);  
Query OK, 1 row affected (0.00 sec)  
  
mysql> commit;  
Query OK, 0 rows affected (0.01 sec)  
  
**--LIST分区**  
 MariaDB \[test\]> CREATE TABLE employees5 (  
     ->     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,  
     ->     store\_id INT  
     -> )  
     -> PARTITION BY LIST(store\_id) (  
     ->     PARTITION pNorth VALUES IN (3,5,6,9,17),  
     ->     PARTITION pEast VALUES IN (1,2,10,11,19,20),  
     ->     PARTITION pWest VALUES IN (4,12,13,14,18),  
     ->     PARTITION pCentral VALUES IN (7,8,15,16)  
     -> );  
 Query OK, 0 rows affected (5.13 sec)  
  
**--COLUMN分区**  
 字段分区是范围分区和列表分区的一种变体，字段分区可以使用多个字段作为分区键。  
 范围字段分区和列表字段分区支持非整数字段，支持的数据类型如下：  
  
 所有整数类型：TINYINT, SMALLINT, MEDIUMINT, INT，BIGINT。  
 DATE，DATETIME。  
 CHAR, VARCHAR, BINARY,VARBINARY。  
  
 MariaDB \[test\]> CREATE TABLE rc2 (  
     ->     a INT,  
     ->     b INT  
     -> )  
     -> PARTITION BY RANGE COLUMNS(a,b) (  
     ->     PARTITION p0 VALUES LESS THAN (0,10),  
     ->     PARTITION p1 VALUES LESS THAN (10,20),  
     ->     PARTITION p2 VALUES LESS THAN (10,30),  
     ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)  
     ->  );  
 Query OK, 0 rows affected (0.27 sec)  
  
mysql> create table t\_columns\_range(  
    -> a int,  
    -> b datetime  
    -> )engine=innodb  
    -> partition by range columns (b) (  
    -> partition p0 values less than ('2009-01-01'),  
    -> partition p1 values less than ('2010-01-01')  
    -> );  
Query OK, 0 rows affected (0.20 sec)  
  
mysql> create table customers\_1 (  
    -> first\_name varchar(25),  
    -> last\_name varchar(25),  
    -> street\_1 varchar(30),  
    -> street\_2 varchar(30),  
    -> city varchar(15),  
    -> renewal date  
    -> )  
    -> partition by list columns(city) (  
    -> partition pRegion\_1  
    -> values in ('Oskarshamn', 'Hogsby', 'Monsters'),  
    -> partition pRegion\_2  
    -> values in ('Vimmerby', 'Hultsfred', 'Vastervik'),  
    -> partition pRegion\_3  
    -> values in ('Nassjo', 'Eksjo', 'Vetlanda'),  
    -> partition pRegion\_4  
    -> values in ('Uppvidinge', 'Alvesta', 'Vaxjo')  
    -> );  
Query OK, 0 rows affected (0.23 sec)  
  
mysql> create table rcx(  
    -> a int,  
    -> b int,  
    -> c char(3),  
    -> d int  
    -> )engine=innodb  
    -> partition by range columns(a,d,c) (  
    -> partition p0 values less than (5,10,'ggg'),  
    -> partition p1 values less than (10,20,'mmmm'),  
    -> partition p2 values less than (15,30,'sss'),  
    -> partition p3 values less than (MAXVALUE,MAXVALUE,MAXVALUE)  
    -> );  
Query OK, 0 rows affected (0.32 sec)  
  
**--哈希分区**  
 哈希分区主要确保分区表中的数据均匀分布在各个分区之中。  
mysql> create table t\_hash(a int,b datetime)engine=innodb  
    -> partition by hash(year(b))  
    -> partitions 4;  
Query OK, 0 rows affected (7.81 sec)  
  
 MariaDB \[test\]> CREATE TABLE employees7 (  
     ->     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,  
     ->     store\_id INT  
     -> )  
     -> PARTITION BY HASH(store\_id)  
     -> PARTITIONS 4;  
 Query OK, 0 rows affected (0.22 sec)  
  
MySQL数据库还支持一种称为LINEAR HASH的分区，它使用一个更加复杂的算法来确定新行插入到已经分区的表中的位置  
LINEAR HASH分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，这有利于处理含有大量数据的表。LINEAR HASH分区的缺点在于，  
与使用HASH分区得到的数据分布相比，各个分区间数据的分布可能不大均衡  
 mysql> create table t\_linear\_hash(  
    -> a int,  
    -> b datetime  
    -> )engine=innodb  
    -> partition by linear hash(year(b))  
    -> partitions 4;  
Query OK, 0 rows affected (0.23 sec)  
  
**--KEY分区**  
 KEY分区类似哈希分区，除了哈希分区使用用户自定义的表达式。分区键列必须包含部分或所有的表的主键。  
MariaDB \[test\]> CREATE TABLE k1 (  
     ->     id INT NOT NULL,  
     ->     name VARCHAR(20),  
     ->     UNIQUE KEY (id)  
     -> )  
     -> PARTITION BY KEY()  
     -> PARTITIONS 2;  
 Query OK, 0 rows affected (0.11 sec)  
  
**--复合分区  
MySQL数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区**  
 MariaDB \[test\]> CREATE TABLE ts (id INT, purchased DATE)  
     ->     PARTITION BY RANGE( YEAR(purchased) )  
     ->     SUBPARTITION BY HASH( TO\_DAYS(purchased) ) (  
     ->         PARTITION p0 VALUES LESS THAN (1990) (  
     ->             SUBPARTITION s0,  
     ->             SUBPARTITION s1  
     ->         ),  
     ->         PARTITION p1 VALUES LESS THAN (2000) (  
     ->             SUBPARTITION s2,  
     ->             SUBPARTITION s3  
     ->         ),  
     ->         PARTITION p2 VALUES LESS THAN MAXVALUE (  
     ->             SUBPARTITION s4,  
     ->             SUBPARTITION s5  
     ->         )  
     ->     );  
 Query OK, 0 rows affected (0.51 sec)  
  
mysql> create table ts(a int,b date) engine=innodb  
    -> partition by range(year(b))  
    -> subpartition by hash(to\_days(b))  
    -> subpartitions 2 (  
    -> partition p0 values less than (1990),  
    -> partition p1 values less than (2000),  
    -> partition p2 values less than MAXVALUE  
    -> );  
Query OK, 0 rows affected (0.24 sec)  
mysql> system ls -lh /var/lib/mysql/test/ts\*  
\-rw-rw----. 1 mysql mysql 8.4K Nov  4 15:44 /var/lib/mysql/test/ts.frm  
\-rw-rw----. 1 mysql mysql   96 Nov  4 15:44 /var/lib/mysql/test/ts.par  
\-rw-rw----. 1 mysql mysql  96K Nov  4 15:44 /var/lib/mysql/test/ts\#P\#p0\#SP\#p0sp0.ibd  
\-rw-rw----. 1 mysql mysql  96K Nov  4 15:44 /var/lib/mysql/test/ts\#P\#p0\#SP\#p0sp1.ibd  
\-rw-rw----. 1 mysql mysql  96K Nov  4 15:44 /var/lib/mysql/test/ts\#P\#p1\#SP\#p1sp0.ibd  
\-rw-rw----. 1 mysql mysql  96K Nov  4 15:44 /var/lib/mysql/test/ts\#P\#p1\#SP\#p1sp1.ibd  
\-rw-rw----. 1 mysql mysql  96K Nov  4 15:44 /var/lib/mysql/test/ts\#P\#p2\#SP\#p2sp0.ibd  
\-rw-rw----. 1 mysql mysql  96K Nov  4 15:44 /var/lib/mysql/test/ts\#P\#p2\#SP\#p2sp1.ibd  
  
mysql> create table ts (a int, b date)  
    -> partition by range (year(b))  
    -> subpartition by hash( to\_days(b)) (  
    -> partition p0 values less than (1990) (  
    -> subpartition s0,  
    -> subpartition s1  
    -> ),  
    -> partition p1 values less than (2000) (  
    -> subpartition s2,  
    -> subpartition s3  
    -> ),  
    -> partition p2 values less than MAXVALUE (  
    -> subpartition s4,  
    -> subpartition s5  
    -> )  
    -> );  
Query OK, 0 rows affected (0.15 sec)  
  
mysql> create table ts (a int,b date) engine=innodb  
    -> partition by range(year(b))  
    -> subpartition by hash(to\_days(b)) (  
    -> partition p0 values less than (2000) (  
    -> subpartition s0  
    -> data directory = '/disk0/data'  
    -> index directory ='/disk0/idx',  
    -> subpartition s1  
    -> data directory = '/disk1/data'  
    -> index directory = '/disk1/idx'  
    -> ),  
    -> partition p1 values less than (2010) (  
    -> subpartition s2  
    -> data directory = '/disk2/data'  
    -> index directory = '/disk2/idx',  
    -> subpartition s3  
    -> data directory = '/disk3/data'  
    -> index directory = '/disk3/idx'  
    -> ),  
    -> partition p2 values less than maxvalue (  
    -> subpartition s4  
    -> data directory = '/disk4/data'  
    -> index directory = '/disk4/idx',  
    -> subpartition s5  
    -> data directory = '/disk5/data'  
    -> index directory = '/disk5/idx'  
    -> )  
    -> );  
Query OK, 0 rows affected, 6 warnings (0.32 sec)  
  
mysql> show warnings;  
\+---------+------+----------------------------------+  
| Level   | Code | Message                          |  
\+---------+------+----------------------------------+  
| Warning | 1618 | option ignored |  
| Warning | 1618 | option ignored |  
| Warning | 1618 | option ignored |  
| Warning | 1618 | option ignored |  
| Warning | 1618 | option ignored |  
| Warning | 1618 | option ignored |  
\+---------+------+----------------------------------+  
6 rows in set (0.00 sec)  
  
**--查看分区**  
 MariaDB \[test\]> select \* from INFORMATION\_SCHEMA.PARTITIONS where table\_name = 'employees'\\G  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                 TABLE\_CATALOG: def  
                  TABLE\_SCHEMA: test  
                    TABLE\_NAME: employees  
                PARTITION\_NAME: p0  
             SUBPARTITION\_NAME: NULL  
    PARTITION\_ORDINAL\_POSITION: 1  
 SUBPARTITION\_ORDINAL\_POSITION: NULL  
              PARTITION\_METHOD: RANGE  
           SUBPARTITION\_METHOD: NULL  
          PARTITION\_EXPRESSION: store\_id  
       SUBPARTITION\_EXPRESSION: NULL  
         PARTITION\_DESCRIPTION: 6  
                    TABLE\_ROWS: 0  
                AVG\_ROW\_LENGTH: 0  
                   DATA\_LENGTH: 16384  
               MAX\_DATA\_LENGTH: NULL  
                  INDEX\_LENGTH: 0  
                     DATA\_FREE: 0  
                   CREATE\_TIME: 2016-07-04 00:42:16  
                   UPDATE\_TIME: NULL  
                    CHECK\_TIME: NULL  
                      CHECKSUM: NULL  
             PARTITION\_COMMENT:   
                     NODEGROUP: default  
               TABLESPACE\_NAME: NULL  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 2. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                 TABLE\_CATALOG: def  
                  TABLE\_SCHEMA: test  
                    TABLE\_NAME: employees  
                PARTITION\_NAME: p1  
             SUBPARTITION\_NAME: NULL  
    PARTITION\_ORDINAL\_POSITION: 2  
 SUBPARTITION\_ORDINAL\_POSITION: NULL  
              PARTITION\_METHOD: RANGE  
           SUBPARTITION\_METHOD: NULL  
          PARTITION\_EXPRESSION: store\_id  
       SUBPARTITION\_EXPRESSION: NULL  
         PARTITION\_DESCRIPTION: 11  
                    TABLE\_ROWS: 2  
                AVG\_ROW\_LENGTH: 8192  
                   DATA\_LENGTH: 16384  
               MAX\_DATA\_LENGTH: NULL  
                  INDEX\_LENGTH: 0  
                     DATA\_FREE: 0  
                   CREATE\_TIME: 2016-07-04 00:42:16  
                   UPDATE\_TIME: NULL  
                    CHECK\_TIME: NULL  
                      CHECKSUM: NULL  
             PARTITION\_COMMENT:   
                     NODEGROUP: default  
               TABLESPACE\_NAME: NULL  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 3. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                 TABLE\_CATALOG: def  
                  TABLE\_SCHEMA: test  
                    TABLE\_NAME: employees  
                PARTITION\_NAME: p2  
             SUBPARTITION\_NAME: NULL  
    PARTITION\_ORDINAL\_POSITION: 3  
 SUBPARTITION\_ORDINAL\_POSITION: NULL  
              PARTITION\_METHOD: RANGE  
           SUBPARTITION\_METHOD: NULL  
          PARTITION\_EXPRESSION: store\_id  
       SUBPARTITION\_EXPRESSION: NULL  
         PARTITION\_DESCRIPTION: 16  
                    TABLE\_ROWS: 2  
                AVG\_ROW\_LENGTH: 8192  
                   DATA\_LENGTH: 16384  
               MAX\_DATA\_LENGTH: NULL  
                  INDEX\_LENGTH: 0  
                     DATA\_FREE: 0  
                   CREATE\_TIME: 2016-07-04 00:42:16  
                   UPDATE\_TIME: NULL  
                    CHECK\_TIME: NULL  
                      CHECKSUM: NULL  
             PARTITION\_COMMENT:   
                     NODEGROUP: default  
               TABLESPACE\_NAME: NULL  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 4. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                 TABLE\_CATALOG: def  
                  TABLE\_SCHEMA: test  
                    TABLE\_NAME: employees  
                PARTITION\_NAME: p3  
             SUBPARTITION\_NAME: NULL  
    PARTITION\_ORDINAL\_POSITION: 4  
 SUBPARTITION\_ORDINAL\_POSITION: NULL  
              PARTITION\_METHOD: RANGE  
           SUBPARTITION\_METHOD: NULL  
          PARTITION\_EXPRESSION: store\_id  
       SUBPARTITION\_EXPRESSION: NULL  
         PARTITION\_DESCRIPTION: MAXVALUE  
                    TABLE\_ROWS: 2  
                AVG\_ROW\_LENGTH: 8192  
                   DATA\_LENGTH: 16384  
               MAX\_DATA\_LENGTH: NULL  
                  INDEX\_LENGTH: 0  
                     DATA\_FREE: 0  
                   CREATE\_TIME: 2016-07-04 00:42:16  
                   UPDATE\_TIME: NULL  
                    CHECK\_TIME: NULL  
                      CHECKSUM: NULL  
             PARTITION\_COMMENT:   
                     NODEGROUP: default  
               TABLESPACE\_NAME: NULL  
 4 rows in set (0.00 sec)  
  
**--查看分区表执行计划**  
 MariaDB \[test\]> explain partitions select \* from employees;  
 \+------+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+  
 | id   | select\_type | table     | partitions  | type | possible\_keys | key  | key\_len | ref  | rows | Extra |  
 \+------+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+  
 |    1 | SIMPLE      | employees | p0,p1,p2,p3 | ALL  | NULL          | NULL | NULL    | NULL |    7 |       |  
 \+------+-------------+-----------+-------------+------+---------------+------+---------+------+------+-------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> explain partitions select \* from employees where store\_id < 5;  
 \+------+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+  
 | id   | select\_type | table     | partitions | type | possible\_keys | key  | key\_len | ref  | rows | Extra       |  
 \+------+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+  
 |    1 | SIMPLE      | employees | p0         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |  
 \+------+-------------+-----------+------------+------+---------------+------+---------+------+------+-------------+  
 1 row in set (0.00 sec)  
  
**--增加分区**  
  
 MariaDB \[test\]> alter table employees add partition (partition p3 values less than (20));  
 Query OK, 0 rows affected (0.11 sec)  
 Records: 0  Duplicates: 0  Warnings: 0  
  
 MariaDB \[test\]> alter table employees add partition (partition p5 values less than maxvalue);  
 Query OK, 0 rows affected (0.18 sec)  
 Records: 0  Duplicates: 0  Warnings: 0  
  
**--TRUNCATE指定分区**  
 MariaDB \[test\]> alter table employees truncate partition p0;  
 Query OK, 0 rows affected (0.12 sec)  
  
**--删除指定分区**  
 MariaDB \[test\]> alter table employees drop partition p0;  
 Query OK, 0 rows affected (0.26 sec)  
 Records: 0  Duplicates: 0  Warnings: 0  
  
**--将一个分区拆分成多个分区**  
 MariaDB \[test\]> select partition\_name,partition\_expression,partition\_description,table\_rows from information\_schema.partitions where table\_schema=schema() and table\_name='employees';  
 \+----------------+----------------------+-----------------------+------------+  
 | partition\_name | partition\_expression | partition\_description | table\_rows |  
 \+----------------+----------------------+-----------------------+------------+  
 | p1             | store\_id             | 11                    |          2 |  
 | p2             | store\_id             | 16                    |          2 |  
 | p3             | store\_id             | 20                    |          0 |  
 | p5             | store\_id             | MAXVALUE              |          0 |  
 \+----------------+----------------------+-----------------------+------------+  
 4 rows in set (0.00 sec)  
  
 MariaDB \[test\]> ALTER TABLE employees  
     ->     REORGANIZE PARTITION p1 INTO (  
     ->         PARTITION n0 VALUES LESS THAN (5),  
     ->         PARTITION n1 VALUES LESS THAN (11)  
     -> );  
 Query OK, 2 rows affected (0.49 sec)  
 Records: 2  Duplicates: 0  Warnings: 0  
  
 MariaDB \[test\]> select partition\_name,partition\_expression,partition\_description,table\_rows from information\_schema.partitions where table\_schema=schema() and table\_name='employees';  
 \+----------------+----------------------+-----------------------+------------+  
 | partition\_name | partition\_expression | partition\_description | table\_rows |  
 \+----------------+----------------------+-----------------------+------------+  
 | n0             | store\_id             | 5                     |          0 |  
 | n1             | store\_id             | 11                    |          2 |  
 | p2             | store\_id             | 16                    |          2 |  
 | p3             | store\_id             | 20                    |          0 |  
 | p5             | store\_id             | MAXVALUE              |          0 |  
 \+----------------+----------------------+-----------------------+------------+  
 5 rows in set (0.06 sec)  
  
**--将多个分区合并成一个分区**  
 MariaDB \[test\]> select partition\_name,partition\_expression,partition\_description,table\_rows from information\_schema.partitions where table\_schema=schema() and table\_name='employees';  
 \+----------------+----------------------+-----------------------+------------+  
 | partition\_name | partition\_expression | partition\_description | table\_rows |  
 \+----------------+----------------------+-----------------------+------------+  
 | n0             | store\_id             | 5                     |          0 |  
 | n1             | store\_id             | 11                    |          2 |  
 | p2             | store\_id             | 16                    |          2 |  
 | p3             | store\_id             | 20                    |          0 |  
 | p5             | store\_id             | MAXVALUE              |          0 |  
 \+----------------+----------------------+-----------------------+------------+  
 5 rows in set (0.00 sec)  
  
 MariaDB \[test\]> ALTER TABLE employees  
     -> REORGANIZE PARTITION n0,n1,p2 INTO (  
     -> PARTITION p2 VALUES LESS THAN (16));  
 Query OK, 4 rows affected (0.28 sec)  
 Records: 4  Duplicates: 0  Warnings: 0  
  
 MariaDB \[test\]> select partition\_name,partition\_expression,partition\_description,table\_rows from information\_schema.partitions where table\_schema=schema() and table\_name='employees';  
 \+----------------+----------------------+-----------------------+------------+  
 | partition\_name | partition\_expression | partition\_description | table\_rows |  
 \+----------------+----------------------+-----------------------+------------+  
 | p2             | store\_id             | 16                    |          4 |  
 | p3             | store\_id             | 20                    |          0 |  
 | p5             | store\_id             | MAXVALUE              |          0 |  
 \+----------------+----------------------+-----------------------+------------+  
 3 rows in set (0.03 sec)  
  
**--减少哈希分区的数量**  
 MariaDB \[test\]> create table emp2(id int not null,ename varchar(30),  
     -> hired date not null default '1970-01-01',  
     -> separated date not null default '9999-12-31',  
     -> job varchar(30) not null,  
     -> store\_id int not null)  
     -> partition by hash(store\_id) partitions 4;  
 Query OK, 0 rows affected (0.60 sec)  
  
 MariaDB \[test\]> alter table emp2 coalesce partition 2;  
 Query OK, 0 rows affected (0.33 sec)  
 Records: 0  Duplicates: 0  Warnings: 0  
  
 MariaDB \[test\]> show create table emp2\\G  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
        Table: emp2  
 Create Table: CREATE TABLE \`emp2\` (  
   \`id\` int(11) NOT NULL,  
   \`ename\` varchar(30) DEFAULT NULL,  
   \`hired\` date NOT NULL DEFAULT '1970-01-01',  
   \`separated\` date NOT NULL DEFAULT '9999-12-31',  
   \`job\` varchar(30) NOT NULL,  
   \`store\_id\` int(11) NOT NULL  
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
 /\*!50100 PARTITION BY HASH (store\_id)  
 PARTITIONS 2 \*/  
 1 row in set (0.00 sec)  
  
**增加哈希分区的数量**  
 MariaDB \[test\]> alter table emp2 add partition partitions 5;  
 Query OK, 0 rows affected (0.54 sec)  
 Records: 0  Duplicates: 0  Warnings: 0  
  
 MariaDB \[test\]> show create table emp2\\G  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
        Table: emp2  
 Create Table: CREATE TABLE \`emp2\` (  
   \`id\` int(11) NOT NULL,  
   \`ename\` varchar(30) DEFAULT NULL,  
   \`hired\` date NOT NULL DEFAULT '1970-01-01',  
   \`separated\` date NOT NULL DEFAULT '9999-12-31',  
   \`job\` varchar(30) NOT NULL,  
   \`store\_id\` int(11) NOT NULL  
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
 /\*!50100 PARTITION BY HASH (store\_id)  
 PARTITIONS 7 \*/  
 1 row in set (0.00 sec)  
  
**在表和分区间交换数据**  
mysql> create table e (  
    -> id int not null,  
    -> fname varchar(30),  
    -> lname varchar(30)  
    -> )  
    -> partition by range(id) (  
    -> partition p0 values less than (50),  
    -> partition p1 values less than (100),  
    -> partition p2 values less than (150),  
    -> partition p3 values less than (MAXVALUE)  
    -> );  
Query OK, 0 rows affected (0.32 sec)  
  
mysql> insert into e values (1669,"Jim","Smith"),(337,"Mary","Jones"),(16,"Frank","White"),(2005,"Linda","Black");  
Query OK, 4 rows affected (0.01 sec)  
Records: 4  Duplicates: 0  Warnings: 0  
  
mysql> commit;  
Query OK, 0 rows affected (0.01 sec)  
  
创建交换表   
mysql> create table e2 like e;  
Query OK, 0 rows affected (0.29 sec)  
  
mysql> show create table e2\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       Table: e2  
Create Table: CREATE TABLE \`e2\` (  
  \`id\` int(11) NOT NULL,  
  \`fname\` varchar(30) DEFAULT NULL,  
  \`lname\` varchar(30) DEFAULT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=latin1  
/\*!50100 PARTITION BY RANGE (id)  
(PARTITION p0 VALUES LESS THAN (50) ENGINE = InnoDB,  
 PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,  
 PARTITION p2 VALUES LESS THAN (150) ENGINE = InnoDB,  
 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) \*/  
1 row in set (0.00 sec)  
  
 将分区表改成普通表  
mysql> alter table e2 remove partitioning;  
Query OK, 0 rows affected (0.25 sec)  
Records: 0  Duplicates: 0  Warnings: 0  
  
mysql> show create table e2\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       Table: e2  
Create Table: CREATE TABLE \`e2\` (  
  \`id\` int(11) NOT NULL,  
  \`fname\` varchar(30) DEFAULT NULL,  
  \`lname\` varchar(30) DEFAULT NULL  
) ENGINE=InnoDB DEFAULT CHARSET=latin1  
1 row in set (0.00 sec)  
  
将e表的分区p0中的数据移动到表e2中，p0分区中的数据被移到表e2中  
mysql> alter table e exchange partition p0 with table e2;  
Query OK, 0 rows affected (0.17 sec)  
  
mysql> select partition\_name,table\_rows from information\_schema.partitions where table\_name='e';  
\+----------------+------------+  
| partition\_name | table\_rows |  
\+----------------+------------+  
| p0             |          0 |  
| p1             |          0 |  
| p2             |          0 |  
| p3             |          2 |  
\+----------------+------------+  
4 rows in set (0.00 sec)  
  
mysql> select \* from e2;  
\+----+-------+-------+  
| id | fname | lname |  
\+----+-------+-------+  
| 16 | Frank | White |  
\+----+-------+-------+  
1 row in set (0.00 sec)  
  
**--查询指定分区**  
 MariaDB \[test\]> select \* from employees partition(p1);  
 \+----+-------+--------+------------+------------+----------+----------+  
 | id | fname | lname  | hired      | separated  | job\_code | store\_id |  
 \+----+-------+--------+------------+------------+----------+----------+  
 |  2 | Lily  | Berg   | 1970-01-01 | 9999-12-31 |       20 |        7 |  
 |  3 | Lucy  | Phynix | 1970-01-01 | 9999-12-31 |       20 |       10 |  
 \+----+-------+--------+------------+------------+----------+----------+  
 2 rows in set (0.00 sec)  
  
**--将非分区表转换成分区表**  
 MariaDB \[test\]> CREATE TABLE employees2 (  
     ->          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  
     ->      );  
 Query OK, 0 rows affected (0.08 sec)  
  
 MariaDB \[test\]> insert into employees2(id,fname,lname,job\_code,store\_id) values(1,'John','Terry',10,100);  
 Query OK, 1 row affected (0.00 sec)  
 MariaDB \[test\]> insert into employees2(id,fname,lname,job\_code,store\_id) values(2,'Tom','Carl',10,1);  
 Query OK, 1 row affected (0.03 sec)  
 MariaDB \[test\]> insert into employees2(id,fname,lname,job\_code,store\_id) values(2,'Lily','Berg',20,7);  
 Query OK, 1 row affected (0.04 sec)  
 MariaDB \[test\]> insert into employees2(id,fname,lname,job\_code,store\_id) values(3,'Lucy','Phynix',20,10);  
 Query OK, 1 row affected (0.06 sec)  
 MariaDB \[test\]> insert into employees2(id,fname,lname,job\_code,store\_id) values(4,'Bill','Jones',20,15);  
 Query OK, 1 row affected (0.00 sec)  
 MariaDB \[test\]> insert into employees2(id,fname,lname,job\_code,store\_id) values(5,'Jill','Deco',30,12);  
 Query OK, 1 row affected (0.00 sec)  
 MariaDB \[test\]> insert into employees2(id,fname,lname,job\_code,store\_id) values(6,'Emily','Aaron',30,20);  
 Query OK, 1 row affected (0.02 sec)  
  
 MariaDB \[test\]> select \* from employees2;  
 \+----+-------+--------+------------+------------+----------+----------+  
 | id | fname | lname  | hired      | separated  | job\_code | store\_id |  
 \+----+-------+--------+------------+------------+----------+----------+  
 |  1 | John  | Terry  | 1970-01-01 | 9999-12-31 |       10 |      100 |  
 |  2 | Tom   | Carl   | 1970-01-01 | 9999-12-31 |       10 |        1 |  
 |  2 | Lily  | Berg   | 1970-01-01 | 9999-12-31 |       20 |        7 |  
 |  3 | Lucy  | Phynix | 1970-01-01 | 9999-12-31 |       20 |       10 |  
 |  4 | Bill  | Jones  | 1970-01-01 | 9999-12-31 |       20 |       15 |  
 |  5 | Jill  | Deco   | 1970-01-01 | 9999-12-31 |       30 |       12 |  
 |  6 | Emily | Aaron  | 1970-01-01 | 9999-12-31 |       30 |       20 |  
 \+----+-------+--------+------------+------------+----------+----------+  
 7 rows in set (0.00 sec)  
  
 MariaDB \[test\]> alter table employees2  
     ->      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 MAXVALUE  
     ->      );  
 Query OK, 7 rows affected (0.59 sec)                 
 Records: 7  Duplicates: 0  Warnings: 0  
  
**--测试NULL值在分区中的存储**  
 RANGE分区中，NULL值会被当作最小值来处理；LIST分区中，NULL值必须出现在枚举列表中；HASH/KEY分区中，NULL值会被当作零值来处理  
  
 MariaDB \[test\]> create table tb\_range(id int,name varchar(5))  
     -> partition by range(id)  
     -> (  
     -> partition p0 values less than(-6),  
     -> partition p1 values less than(0),  
     -> partition p2 values less than(1),  
     -> partition p3 values less than maxvalue  
     -> );  
 Query OK, 0 rows affected (0.69 sec)  
  
 MariaDB \[test\]> insert into tb\_range values(null,'null');  
 Query OK, 1 row affected (0.02 sec)  
  
 MariaDB \[test\]> select partition\_name,partition\_expression,partition\_description,table\_rows from information\_schema.partitions where table\_schema=schema() and table\_name='tb\_range';  
 \+----------------+----------------------+-----------------------+------------+  
 | partition\_name | partition\_expression | partition\_description | table\_rows |  
 \+----------------+----------------------+-----------------------+------------+  
 | p0             | id                   | -6                    |          1 |  
 | p1             | id                   | 0                     |          0 |  
 | p2             | id                   | 1                     |          0 |  
 | p3             | id                   | MAXVALUE              |          0 |  
 \+----------------+----------------------+-----------------------+------------+  
 4 rows in set (0.00 sec)  
  
 MariaDB \[test\]> create table tb\_list(id int,name varchar(5))  
     -> partition by list(id)  
     -> (  
     -> partition p1 values in (0),  
     -> partition p2 values in (1)  
     -> );  
 Query OK, 0 rows affected (0.15 sec)  
  
 MariaDB \[test\]> insert into tb\_list values(null,'null');  
 ERROR 1526 (HY000): Table has no partition for value NULL  
  
 MariaDB \[test\]> insert into tb\_list values(null,'null');  
 ERROR 1526 (HY000): Table has no partition for value NULL  
 MariaDB \[test\]> create table tb\_hash(id int,name varchar(5))  
     -> partition by hash(id)  
     -> partitions 2;  
 Query OK, 0 rows affected (0.13 sec)  
  
 MariaDB \[test\]> insert into tb\_hash values(null, 'null');  
 Query OK, 1 row affected (0.01 sec)  
  
 MariaDB \[test\]> select partition\_name,partition\_expression,partition\_description,table\_rows from information\_schema.partitions where table\_schema=schema() and table\_name='tb\_hash';  
 \+----------------+----------------------+-----------------------+------------+  
 | partition\_name | partition\_expression | partition\_description | table\_rows |  
 \+----------------+----------------------+-----------------------+------------+  
 | p0             | id                   | NULL                  |          1 |  
 | p1             | id                   | NULL                  |          0 |  
 \+----------------+----------------------+-----------------------+------------+  
 2 rows in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2121610/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2121610/