首页 > 数据库 >【MySQL】MySQL分区是如何处理NULL的

【MySQL】MySQL分区是如何处理NULL的

时间：2022-10-01 14:00:24浏览次数：99  
标签：mysql 分区 PARTITION VALUES 16384 MySQL TABLE NULL

MySQL的分区实现中，认为NULL比任何non-NULL值都小。和order by类似。

​

RANGE分区是如何处理NULL的？
如果将行插入到按RANGE分区的表中，使得用于确定分区的列值为NULL，则该行将插入到最低分区中。假设数据库abce中有两个分区表：

mysql> CREATE TABLE t1 (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY RANGE(c1) (
    ->     PARTITION p0 VALUES LESS THAN (0),
    ->     PARTITION p1 VALUES LESS THAN (10),
    ->     PARTITION p2 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE t2 (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY RANGE(c1) (
    ->     PARTITION p0 VALUES LESS THAN (-5),
    ->     PARTITION p1 VALUES LESS THAN (0),
    ->     PARTITION p2 VALUES LESS THAN (10),
    ->     PARTITION p3 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.09 sec)

查看一下分区表的信息：

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_SCHEMA = 'abce' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          0 |              0 |       16384 |
| t1         | p1             |          0 |              0 |       16384 |
| t1         | p2             |          0 |              0 |       16384 |
| t2         | p0             |          0 |              0 |       16384 |
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)

mysql>

插入带有NULL的记录：

mysql> INSERT INTO t1 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;
+------+--------+
| id   | name   |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+--------+
| id   | name   |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

查看一下被插入了哪个分区：

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_SCHEMA = 'abce' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p0             |          1 |          16384 |       16384 |
| t1         | p1             |          0 |              0 |       16384 |
| t1         | p2             |          0 |              0 |       16384 |
| t2         | p0             |          1 |          16384 |       16384 |
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)

mysql>

　　

也可以通过删除最小的分区来确认上面插入的两条记录被存储最小的分区了：

mysql> ALTER TABLE t1 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)

mysql> ALTER TABLE t2 DROP PARTITION p0;
Query OK, 0 rows affected (0.16 sec)

mysql> SELECT * FROM t1;
Empty set (0.00 sec)

mysql> SELECT * FROM t2;
Empty set (0.00 sec)

对于使用SQL函数的分区表达式，也会以这种方式处理NULL。假设我们定义一个表，例如：

CREATE TABLE tndate (
    id INT,
    dt DATE
)
PARTITION BY RANGE( YEAR(dt) ) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

YEAR(NULL)返回NULL。如果插入dt为NULL的行，也是被插入到最小分区p0。

 

LIST分区是如何处理NULL的？

当且仅当使用包含NULL的值列表定义其分区之一时，LIST分区的表允许NULL值。与此相反的是，LIST分区的表未在值列表中显式使用NULL会拒绝导致分区表达式为NULL值的行，如下例所示：

mysql> CREATE TABLE ts1 (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY LIST(c1) (
    ->     PARTITION p0 VALUES IN (0, 3, 6),
    ->     PARTITION p1 VALUES IN (1, 4, 7),
    ->     PARTITION p2 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO ts1 VALUES (9, 'mothra');
ERROR 1504 (HY000): Table has no partition for value 9

mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1504 (HY000): Table has no partition for value NULL

​只有c1的值在0和8之间，才可以将null值插入表ts1。

 

mysql> CREATE TABLE ts2 (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY LIST(c1) (
    ->     PARTITION p0 VALUES IN (0, 3, 6),
    ->     PARTITION p1 VALUES IN (1, 4, 7),
    ->     PARTITION p2 VALUES IN (2, 5, 8),
    ->     PARTITION p3 VALUES IN (NULL)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE ts3 (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY LIST(c1) (
    ->     PARTITION p0 VALUES IN (0, 3, 6),
    ->     PARTITION p1 VALUES IN (1, 4, 7, NULL),
    ->     PARTITION p2 VALUES IN (2, 5, 8)
    -> );
Query OK, 0 rows affected (0.01 sec)

在为分区定义值列表时，可以(并且应该)像对待任何其他值一样对待NULL。 例如，VALUES IN(NULL)和VALUES IN(1, 4, 7, NULL)都是有效的，VALUES IN(1, NULL, 4, 7), VALUES IN(NULL, 1, 4, 7), 等等。可以在每个表ts2和ts3中插入一行c1为NULL的行：

mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO ts3 VALUES (NULL, 'mothra');
Query OK, 1 row affected (0.00 sec)

　　

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_SCHEMA = 'abce' AND TABLE_NAME LIKE 't_';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1         | p1             |          0 |              0 |       16384 |
| t1         | p2             |          0 |              0 |       16384 |
| t2         | p1             |          0 |              0 |       16384 |
| t2         | p2             |          0 |              0 |       16384 |
| t2         | p3             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
5 rows in set (0.00 sec)

mysql>

　　

HASH和KEY分区是如何处理NULL的？

对于按HASH或KEY分区的表，NULL的处理方式略有不同。在这些情况下，任何产生NULL值的分区表达式都被视为其返回值为零。我们可以通过检查创建按HASH分区的表并使用包含适当值的记录填充它对文件系统的影响来验证此行为。假设有一个使用以下语句创建的表th：

mysql> CREATE TABLE th (
    ->     c1 INT,
    ->     c2 VARCHAR(20)
    -> )
    -> PARTITION BY HASH(c1)
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
    -> FROM INFORMATION_SCHEMA.PARTITIONS
    -> WHERE TABLE_SCHEMA = 'abce' AND TABLE_NAME LIKE 'th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th         | p0             |          0 |              0 |       16384 |
| th         | p1             |          0 |              0 |       16384 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

mysql>

 

插入测试记录：

mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM th;
+------+---------+
| c1   | c2      |
+------+---------+
| NULL | mothra  |
+------+---------+
|    0 | gigan   |
+------+---------+
2 rows in set (0.01 sec)

　　

NULL MOD N的值总是NULL。在hash和key分区中，是将其放在分区0中。

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
-> FROM INFORMATION_SCHEMA.PARTITIONS
-> WHERE TABLE_SCHEMA = 'abce' AND TABLE_NAME LIKE 'th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th | p0 | 2 | 8192 | 16384 |
| th | p1 | 0 | 0 | 16384 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

mysql>

 

 

本文地址：【MySQL】MySQL分区是如何处理NULL的 - abce - 博客园 (cnblogs.com)

标签：mysql,分区,PARTITION,VALUES,16384,MySQL,TABLE,NULL
From： https://www.cnblogs.com/abclife/p/16725613.html

相关文章

  • mysql中的事务隔离级别序列化如何实现
    ......
  • mysql基础（十六）：定义条件、定义处理程序
    简介定义条件是事先定义程序执行过程中可能遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增......
  • mysql基础（十一）：数据类型
    起步数据类型数据类型的属性整数代码案例#创建数据表，指定字段类型CREATETABLEtest_int1(f1TINYINT,f2SMALLINT,f3MEDIUMINT,f4INTEGER,f5BIGINT);#插入没有超出范......
  • mysql基础（六）：单行函数
    起步简介操作数据对象接受参数返回一个结果只对一行进行变换每行返回一个结果可以嵌套参数可以是一列或一个值数值基本函数代码案例#绝对值SELECTABS(-123),ABS(32),SIG......
  • mysql基础（三）：运算符
    前言算术运算符算术运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行加（+）、减（-）、乘（*）、除（/）和取模（%）运算加减运算符一个整数类型的值对整数进......
  • mysql基础（五）：多表查询
    表结构如下代码案例#两个表的连接查询SELECTemployee_id,department_nameFROMemployees,departmentsWHEREemployees.`department_id`=departments.department_id;#......
  • mysql基础（二）：select语句
    SQL分类SQL语言在功能上主要分为如下3大类：DDL（DataDefinitionLanguages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除......
  • Mysql-基础篇
    通用语法及分类DDL:数据定义语言，用来定义数据库对象（数据库、表、字段）DML:数据操作语言，用来对数据库表中的数据进行增删改DQL:数据查询语言，用来查询数据库中表的记录......
  • mysql的安装
    1.1下载地址https://downloads.mysql.com/archives/community/  1.2解压到安装路径mysql-5.7.31-winx64.zip 是免安装的版本。解压zip文件将解压后的文件夹放......
  • MYSQL学习笔记之基本操作
    基本操作(1)登录MYSLQ步骤如下：①(win+R)--->cmd-->命令窗口--->输入"mysql-uroot-P端口号-p"②登录成功效果WelcometotheMySQLmonitor.Commandsendwith;or......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99