List分区 mysql list分区查询如何用

转载

mob64ca1407216b 2023-10-08 10:00:56

文章标签 List分区 mysql 表分区 MySQL 数据保存 文章分类 MySQL 数据库

这编博客我们将谈论一下LIST分区

##LIST分区（列表分区）

MySQL中的列表分区在许多方面与范围分区类似，与范围分区一样，LIST分区必须显式地定义每个分区。两种分区类型之间的主要区别在于，LIST分区中每个分区的定义和选择是 <font style='color:coral'>基于某列的值从属于一个值列表集中的一个值,</font> 而不是像RANGE分区（范围分区）那种是基于一组连续值范围中的一个列值。LIST分区通过使用 “PARTITION BY LIST(expr)”来实现。其中expr（表达式）是一个列值或者是一个基于列值的表达式返回一个整数值。然后通过“ VALUES IN (value_list)” 的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

> 请注意
>
在MySQL 8.0中，当按列表进行分区时，有时可能只能匹配整数列表(可能是NULL, 后面会介绍“MySQL分区如何处理NULL”)。

但是，在使用列表列分区时，可能会在值列表中使用其他列类型，这将在本节后面进行描述。

***与使用范围分区的情况不同，列表分区不需要以任何特定顺序声明。***

例如：

CREATE TABLE t_list_employee (
            id INT NOT NULL,  -- 员工编号
            ename 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  -- 所在音像店编号
        )ENGINE=INNODB ;

假定有20个音像店，分布在4个有经销权的地区，如下表所示：

| 地区 | 音像店编号 |
| :-: | :-: |
| 北区 |  3, 5, 6, 9, 17|
| 东区 | 1, 2, 10, 11, 19, 20|
| 西区 | 4, 12, 13, 14, 18|
| 中心区 | 7, 8, 15, 16|

要按照属于同一个地区的音像店的数据保存在同一个分区中的方式来分割表，可以使用下面的“CREATE TABLE”语句

CREATE TABLE t_list_employee (
                id INT NOT NULL,  -- 员工编号
                ename 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  -- 所在音像店编号
            )ENGINE=INNODB
               PARTITION BY LIST(store_id) (
            	 PARTITION p_North   VALUES IN (3,5,6,9,17),
            	 PARTITION p_East    VALUES IN (1,2,10,11,19,20),
            	 PARTITION p_West    VALUES IN (4,12,13,14,18),
            	 PARTITION p_Central VALUES IN (7,8,15,16)
    	      );

这使得在表中添加或删除指定区域相关的员工记录变得很容易。

例如，

假设西部地区的所有商店都卖给了另一家公司。在MySQL 8.0中，可以使用语句：
ALTER TABLE t_list_employee TRUNCATE PARTITION p_East 删除在西部区域存储的员工相关的所有行。这比执行DELETE语句：DELETE FROM t_list_employee WHERE store_id IN (4,12,13,14,18)要有效得多。（可以使用 ALTER TABLE t_list_employee DROP PARTITION p_West 来删除这些行，但是也会从表的定义中删除分区p_West; 然后你还需要使用一个ALTER TABLE…添加PARTITION语句以恢复表的原始分区模式。）

> 注意：
>
> 列表分区与范围分区的情况不同，列表分区不能像范围分区使用 MAXVALUE 这样的“包罗万象”; 列表分区表达式的所有期望值都应该包含在PARTITION by 子句中的值。当 INSERT语句中包含一个不匹配的分区列值，会出现错误

如下例所示:

List分区 mysql list分区查询如何用_表分区