MySQL 表分区（五）

• 哈希表分区，按照一个自定义的函数返回值来确定每个分区包含的数据，这个自定义函数也可以仅仅是一个字段名字
• 通过PARTITION BY HASH (expr)子句来表达哈希表分区，其中的expr表达式必须返回一个整数，基于分区个数的取模（%）运算。根据余数插入到指定的分区
• 对哈希表分区来说只需要定义分区的个数，其他的事情由内部完成

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 HASH(store_id)
    -> PARTITIONS 4; 
Query OK, 0 rows affected (0.20 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) DEFAULT NULL,
  `store_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`store_id`)
PARTITIONS 4 */ |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

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

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

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

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

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

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

mysql> select * from employees;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        8 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        1 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        5 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        2 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        3 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        7 |
+----+-------+-------+------------+------------+----------+----------+
6 rows in set (0.00 sec)

mysql> select partition_name from information_schema.partitions where table_name='employees';
+----------------+
| PARTITION_NAME |
+----------------+
| p0             |
| p1             |
| p2             |
| p3             |
+----------------+
4 rows in set (0.00 sec)

mysql> select * from employees partition(p0);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        8 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.00 sec)

mysql> select * from employees partition(p1);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        1 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        5 |
+----+-------+-------+------------+------------+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from employees partition(p2);
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        2 |
+----+-------+-------+------------+------------+----------+----------+
1 row in set (0.01 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 |        3 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        7 |
+----+-------+-------+------------+------------+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from employees;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        8 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        1 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        5 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        2 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        3 |
|  1 | a     | a     | 2020-05-07 | 2020-05-07 |        1 |        7 |
+----+-------+-------+------------+------------+----------+----------+
6 rows in set (0.00 sec)