MySQL 表分区（七）

• 通过 alter table 命令可以执行增加，删除，重新定义，合并或者拆分表分区的管理动作

mysql> use course;
Database changed
mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE) 
    -> PARTITION BY RANGE( YEAR(purchased) ) 
    -> ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (1995), 
    -> PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (2005), 
    -> PARTITION p4 VALUES LESS THAN (2010), PARTITION p5 VALUES LESS THAN (2015) ); 
Query OK, 0 rows affected (0.48 sec)

mysql> CREATE TABLE tr2 (id INT, name VARCHAR(50), purchased DATE);
Query OK, 0 rows affected (0.16 sec)

mysql> show create table tr2;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tr2   | CREATE TABLE `tr2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table tr;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tr    | CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`purchased`))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */ |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from tr2;
Empty set (0.00 sec)

mysql> select * from tr2 where purchased<'1990-01-01';
Empty set (0.00 sec)

mysql> delete from tr2 where purchased<'1990-01-01';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tr values(2,'abc','1999-12-21');
Query OK, 1 row affected (0.06 sec)

mysql> insert into tr values(1,'abc','1999-12-21');
Query OK, 1 row affected (0.08 sec)

mysql> insert into tr values(2,'abc','1989-12-21');
Query OK, 1 row affected (0.02 sec)

mysql> select * from tr;
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    2 | abc  | 1989-12-21 |
|    2 | abc  | 1999-12-21 |
|    1 | abc  | 1999-12-21 |
+------+------+------------+
3 rows in set (0.00 sec)

mysql> alter table tr drop partition p0;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from tr;
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    2 | abc  | 1999-12-21 |
|    1 | abc  | 1999-12-21 |
+------+------+------------+
2 rows in set (0.00 sec)

mysql> show create table tr;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tr    | CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`purchased`))
(PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */ |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from tr;
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    2 | abc  | 1999-12-21 |
|    1 | abc  | 1999-12-21 |
+------+------+------------+
2 rows in set (0.01 sec)

mysql> select * from tr partition(p2);
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    2 | abc  | 1999-12-21 |
|    1 | abc  | 1999-12-21 |
+------+------+------------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE `tr_history` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `name` varchar(50) DEFAULT NULL,
    ->   `purchased` date DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.23 sec)

mysql> select * from tr_history;
Empty set (0.00 sec)

mysql> alter table tr exchange partition p2 with table tr_history;
Query OK, 0 rows affected (0.15 sec)

mysql> select * from tr;
Empty set (0.00 sec)

mysql> select * from tr partition(p2);
Empty set (0.00 sec)

mysql> select * from tr_history;
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    2 | abc  | 1999-12-21 |
|    1 | abc  | 1999-12-21 |
+------+------+------------+
2 rows in set (0.01 sec)

mysql> alter table tr drop partition p2;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tr;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tr    | CREATE TABLE `tr` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`purchased`))
(PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB) */ |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into tr values(1,'a','2000-01-01');
Query OK, 1 row affected (0.09 sec)

mysql> select * from tr;
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    1 | a    | 2000-01-01 |
+------+------+------------+
1 row in set (0.01 sec)

mysql> select * from tr partition(p3);
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    1 | a    | 2000-01-01 |
+------+------+------------+
1 row in set (0.00 sec)

mysql> select * from tr_history;
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    2 | abc  | 1999-12-21 |
|    1 | abc  | 1999-12-21 |
+------+------+------------+
2 rows in set (0.00 sec)

mysql> select * from tr partition(p3);
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    1 | a    | 2000-01-01 |
+------+------+------------+
1 row in set (0.01 sec)

mysql> alter table tr exchange partition p3 with table tr_history;
Query OK, 0 rows affected (0.21 sec)

mysql> select * from  tr_history;
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    1 | a    | 2000-01-01 |
+------+------+------------+
1 row in set (0.01 sec)

mysql> select * from tr partition(p3);
+------+------+------------+
| id   | name | purchased  |
+------+------+------------+
|    2 | abc  | 1999-12-21 |
|    1 | abc  | 1999-12-21 |
+------+------+------------+
2 rows in set (0.00 sec)

• 在原分区上增加一个表分区可以通过 alter table … add partition 语句来完成

mysql> CREATE TABLE members ( id INT, fname VARCHAR(25), lname VARCHAR(25), dob DATE ) 
    -> PARTITION BY RANGE( YEAR(dob) ) 
    -> ( PARTITION p0 VALUES LESS THAN (1980),
    -> PARTITION p1 VALUES LESS THAN (1990),
    -> PARTITION p2 VALUES LESS THAN (2000) ); 
Query OK, 0 rows affected (0.23 sec)

mysql> ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table members;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| members | CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`dob`))
(PARTITION p0 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> ALTER TABLE members ADD PARTITION (PARTITION n VALUES LESS THAN (1970));
ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
 
mysql> select * from members;
Empty set (0.01 sec)

mysql> insert into members values(1,'a','a','1967-01-01');
Query OK, 1 row affected (0.05 sec)

mysql> insert into members values(1,'a','a','1977-01-01');
Query OK, 1 row affected (0.04 sec)

mysql> select * from members partition(p0);
+------+-------+-------+------------+
| id   | fname | lname | dob        |
+------+-------+-------+------------+
|    1 | a     | a     | 1967-01-01 |
|    1 | a     | a     | 1977-01-01 |
+------+-------+-------+------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE members REORGANIZE PARTITION p0 INTO ( PARTITION n0 VALUES LESS THAN (1970), PARTITION n1 VALUES LESS THAN (1985) );
ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
mysql> ALTER TABLE members REORGANIZE PARTITION p0 INTO ( PARTITION n0 VALUES LESS THAN (1970), PARTITION n1 VALUES LESS THAN (1980) );
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from members;
+------+-------+-------+------------+
| id   | fname | lname | dob        |
+------+-------+-------+------------+
|    1 | a     | a     | 1967-01-01 |
|    1 | a     | a     | 1977-01-01 |
+------+-------+-------+------------+
2 rows in set (0.01 sec)

mysql> show create table members;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| members | CREATE TABLE `members` (
  `id` int(11) DEFAULT NULL,
  `fname` varchar(25) DEFAULT NULL,
  `lname` varchar(25) DEFAULT NULL,
  `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (year(`dob`))
(PARTITION n0 VALUES LESS THAN (1970) ENGINE = InnoDB,
 PARTITION n1 VALUES LESS THAN (1980) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2010) ENGINE = InnoDB) */ |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from members partition(n0);
+------+-------+-------+------------+
| id   | fname | lname | dob        |
+------+-------+-------+------------+
|    1 | a     | a     | 1967-01-01 |
+------+-------+-------+------------+
1 row in set (0.00 sec)

mysql> select * from members partition(n1);
+------+-------+-------+------------+
| id   | fname | lname | dob        |
+------+-------+-------+------------+
|    1 | a     | a     | 1977-01-01 |
+------+-------+-------+------------+
1 row in set (0.00 sec)

posted @ 2020-05-07 21:42 丁海龙阅读(179) 评论(0) 收藏举报

刷新页面返回顶部