增加mysql分区的指令_三十八、MySQL基础系列笔记之MySQL分区管理

MySQL提供了添加、删除、重庆义、合并、拆分、交换分区的命令用于管理分区的操作，这些命令都使用alter table实现。

RANGE与LIST分区管理

对于添加、删除、重定义，RANGE与LIST是相似的。

删除分区

命令：alter table 表名 drop partition

创建分区表并写入数据# 创建分区表

create table demo10(

id int not null,

name varchar(20) not null

)partition by range (id) (

partition p0 values less than (10),

partition p1 values less than (30),

partition p2 values less than maxvalue

);

# 写入数据

insert into demo10 values (1,'lisi'),(2,'wangwu'),

(10,'zhoaliu'),(13,'sunwang'),(31,'meili');

id为1，2的数据写入到了p0分区，10，13的数据写入到了p1分区；31的数据写入到p2分区。

删除p2分区mysql> alter table demo10 drop partition p2;

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

注意：

1)删除分区后，并没有显示实际从表中删除了多少数据，并非没有数据被删除，而只要在p2分区中的数据全部被删除了。

2)不同的是，删除分区后，LIST分区中不同包含已经被删除了的分区值列表，因此无法写入含有已经删除了的分区的值列表数据。

新增分区

命令：alter table 表名 add partitionalter table demo10 add partition (partition p4 values less than maxvalue);

mysql> insert into demo10 values (33,'zi');

Query OK, 1 row affected (0.00 sec)

LIST分区create table demo11(

id int

)partition by list(id) (

partition p0 values in (1,2),

partition p1 values in (3,4)

);

# 新增list分区

mysql> alter table demo11 add partition(partition p2 values in(4,5));

ERROR 1495 (HY000): Multiple definition of same constant in list partitioning

# 成功

mysql> alter table demo11 add partition(partition p2 values in(5,6));

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

注意：LIST分区，第一次新增(4,5)时失败，因为4已经存在了。因此可以看出，新增LIST分区时，必须指定且只能指定一个唯一的分区。

重定义分区

命令：alter table 表名 reorganize partition into

以之前创建的emp表为列：Create Table: CREATE TABLE `emp` (

`id` int NOT NULL,

`name` varchar(30) DEFAULT NULL,

`store_id` int NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/*!50100 PARTITION BY RANGE (`store_id`)

(PARTITION p0 VALUES LESS THAN (15) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THAN (35) ENGINE = InnoDB,

PARTITION p2 VALUES LESS THAN (55) ENGINE = InnoDB,

PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

1 row in set (0.00 sec)

下面案例中对p3分区进行了重新定义，将其拆分成了2个分区。alter table emp reorganize partition p3 into (

partition p3 values less than(65),

partition p4 values less than maxvalue

);

既然可以拆分那么也可以合并，如p0为15,p1为35，那么合并之后是35，而非50alter table emp reorganize partition p0,p1 into (

partition p1 values less than(35)

);

HASH与KEY分区管理

HASH分区与KEY分区类型，管理操作也是类似。RANGE与LIST可以删除分区，不同的是HASH或KEY分区不能从表中删除分区，而是合并分区。

命令：alter table 表名 coalesce partition

案例：创建一张表hash分区数量为4的表，然后将分区修改为2个

先来创建一个HASH分区create table demo12 (

id int not null,

name varchar(20) not null

)partition by hash(id) partitions 4;

修改分区为2个alter table demo12 coalesce partition 2;

# 增加分区会报错

mysql> alter table demo12 coalesce partition 9;

ERROR 1508 (HY000): Cannot remove all partitions, use DROP TABLE insteadcoalesce不能用来增加分区，否则会报错。

交换分区

命令：ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt

含义：将分区表pt中的一个分区或者子分区p中的数据和普通表nt中的数据进行交换。

交换分区必须满足如下条件：表nt不能是分区表。由于交换分区不能通过分区对分区的方式进行，若需求如此，可用一个普通表作为中间表，通过交换两次分区来实现；

表nt不能是临时表；

表pt和nt的结构完全一致(包括索引名称、索引列)，表分区除外；

表nt上不能有外键，也不能有其他表的外键依赖nt；

nt表的所有数据，应在分区p定义的范围内。若数据都在限定范围内可通过WITHOUT VALIDTION来跳过逐行验证。

交换分区案例

1)创建分区表并插入数据CREATE TABLE demo13(

id INT NOT NULL,

name VARCHAR(20)

)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

);

INSERT INTO demo13 VALUES(160,'lisi'),(180,'wangwu'),(15,'meili'),(2021,'ziru');

2)创建普通表CREATE TABLE demo14 LIKE demo13;

3)将demo14分区表修改为非分区表

命令：ALTER TABLE 表名 REMOVE PARTITIONINGALTER TABLE demo14 REMOVE PARTITIONING;

# 查看结果,已经不是分区表

mysql> show create table demo14;

+--------+-----------------------------------------------------------------------------------------------------------------+

| Table  | Create Table                                                                                                    |

+--------+-----------------------------------------------------------------------------------------------------------------+

| demo14 | CREATE TABLE `demo14` (

`id` int NOT NULL,

`name` varchar(20) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

4)查看demo13分区表中的数据分布mysql> SELECT partition_name,table_rows FROM information_schema.partitions WHERE table_name='demo13';

+----------------+------------+

| PARTITION_NAME | TABLE_ROWS |

+----------------+------------+

| p0             |          1 |

| p1             |          0 |

| p2             |          0 |

| p3             |          3 |

+----------------+------------+

4 rows in set (0.00 sec)

5)执行交换分区并查看交换之后的数据分布# 开始交换

ALTER TABLE demo13 EXCHANGE PARTITION p0 WITH TABLE demo14;

# 查看分区表数据分布

mysql> SELECT partition_name,table_rows FROM information_schema.partitions WHERE table_name='demo13';

+----------------+------------+

| PARTITION_NAME | TABLE_ROWS |

+----------------+------------+

| p0             |          0 |

| p1             |          0 |

| p2             |          0 |

| p3             |          3 |

+----------------+------------+

4 rows in set (0.00 sec)

# 查看非分区表

mysql> select * from demo14;

+----+-------+

| id | name  |

+----+-------+

| 15 | meili |

+----+-------+

1 row in set (0.00 sec)

交换分区的使用，可以方便地完成对包含大量数据的分区、子分区的备份，迁移等工作。

使用交换分区注意点：交换分区不会触发任何被交换的表或分区上的触发器；

表中自增列的值会被重置；

交换分区的命令中，IGNORE关键字不会产生影响。

期待和优秀的你一起同行!

2021年01月16日

转载：感谢您对自如初博客网站的认可，所有文章均属原创文章，技术类文章转载请注明出处，“自如初博客”；文学类文章请带文本链接地址，否则视为侵权！

很赞哦！(1)