MySQL 表分区（三）

•  RANGE表分区：范围表分区，按照一定的范围值来确定每个分区包含的数据，分区函数使用的字段必须只能是整数类型
•  分区的定义范围必须是连续的，且不能有重叠部分，通过使用VALUES LESS THAN来定义分区范围，表分区的范围定义是从小到大定义的

mysql> drop table employees;
Query OK, 0 rows affected (0.07 sec)

mysql> 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) );
Query OK, 0 rows affected (0.17 sec)

mysql> drop table employees;
Query OK, 0 rows affected (0.09 sec)

mysql> 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 (10), PARTITION p3 VALUES LESS THAN (21) );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

mysql> 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 (16), 
    -> PARTITION p2 VALUES LESS THAN (11), PARTITION p3 VALUES LESS THAN (21) );
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

mysql> 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) );
Query OK, 0 rows affected (0.14 sec)

mysql> show create table employees;
+-----------+------------------------------------------------------------------+
| Table     | Create Table                                                     |
+-----------+------------------------------------------------------------------+
| employees | 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!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 (21) ENGINE = InnoDB) */ |
+-----------+------------------------------------------------------------------+
1 row in set (0.01 sec)

  mysql> insert into employees values(1,'a','a',now(),now(),1,21);
  ERROR 1526 (HY000): Table has no partition for value 21

mysql> 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 ) 
    -> PARTITION BY RANGE (name) 
    -> ( PARTITION p0 VALUES LESS THAN ('a'), PARTITION p1 VALUES LESS THAN ('b'), 
    -> PARTITION p2 VALUES LESS THAN ('c'), PARTITION p3 VALUES LESS THAN ('d')); 
ERROR 1697 (HY000): VALUES value for partition 'p0' must have type INT

mysql> drop table employees;
Query OK, 0 rows affected (0.15 sec)

mysql> 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.17 sec)

mysql> show create table employees;
+-----------+--------------------------------------------------------------------------------------+
| Table     | Create Table                                                                         |
+-----------+--------------------------------------------------------------------------------------+
| employees | 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!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> insert into employees values(1,'a','a',now(),now(),1,16);
Query OK, 1 row affected, 2 warnings (0.10 sec)

mysql> insert into employees values(1,'a','a',now(),now(),1,17);
Query OK, 1 row affected, 2 warnings (0.03 sec)

mysql> insert into employees values(1,'a','a',now(),now(),1,100);
Query OK, 1 row affected, 2 warnings (0.09 sec)

mysql> insert into employees values(1,'a','a',now(),now(),1,10000);
Query OK, 1 row affected, 2 warnings (0.04 sec)

mysql> select * from employees;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |       16 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |       17 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |      100 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |    10000 |
+----+-------+-------+------------+------------+----------+----------+
4 rows in set (0.00 sec)

mysql> select * from employees partition(p3);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |       16 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |       17 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |      100 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |    10000 |
+----+-------+-------+------------+------------+----------+----------+
4 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> drop table employees;
Query OK, 0 rows affected (0.12 sec)

mysql> 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, store_id INT ) 
    -> PARTITION BY RANGE ( YEAR(separated) ) 
    -> ( PARTITION p0 VALUES LESS THAN (1991), 
    -> PARTITION p1 VALUES LESS THAN (1996), 
    -> PARTITION p2 VALUES LESS THAN (2001), 
    -> PARTITION p3 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.07 sec)