MySQL尾部空格处理与哪些设置有关? 字符集PAD SPACE与NO PAD属性的区别、MySQL字段尾部有空格为什么也能查询出来?

本文主要是介绍MySQL尾部空格处理与哪些设置有关? 字符集PAD SPACE与NO PAD属性的区别、MySQL字段尾部有空格为什么也能查询出来?,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

文章目录

  • 一、问题背景
  • 二、字符集PAD_ATTRIBUTE属性(补齐属性)
    • 2.2、PAD SPACE与NO PAD的具体意义
  • 三、CHAR类型尾部空格的处理
  • 四、其他问题
    • 4.1、在PAD SPACE属性时如何实现精准查询
  • 五、总结

以下内容基于MySQL8.0进行讲解

一、问题背景

一次查询中发现查询出来的内容结尾有空格,跟我预期的结果不一致,由此引发出对本篇的整理。

案例如下:

CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(10)  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;insert into user values
(null,'123456789     '), -- 数字后面有9个空格
(null,'张三   '), -- 名字后面有3个空格 
(null,'李四   '),-- 名字后面有3个空格
(null,'王五')
;

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

以上内容可以看出

  1. 当结尾是由于空格导致的超长插入时不会报错,会自动截取掉多余的空格。
  2. 当查询时尾部有空格时,也能匹配上。

以下我们就研究一下MySQL尾部空格处理与哪些设置有关

二、字符集PAD_ATTRIBUTE属性(补齐属性)

先给出结论: MySQL尾部空格处理与字符集有关,具体的是看字符集的Pad Attributes属性

information_schema> SELECT *  FROM INFORMATION_SCHEMA.COLLATIONS WHERE CHARACTER_SET_NAME = 'utf8mb4' and COLLATION_NAME='utf8mb4_general_ci'; 
+--------------------+--------------------+----+------------+-------------+---------+---------------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
+--------------------+--------------------+----+------------+-------------+---------+---------------+
| utf8mb3_general_ci | utf8mb3            | 33 | Yes        | Yes         | 1       | PAD SPACE     |
+--------------------+--------------------+----+------------+-------------+---------+---------------+
1 row in setinformation_schema> SELECT *  FROM INFORMATION_SCHEMA.COLLATIONS WHERE CHARACTER_SET_NAME = 'utf8mb4' and COLLATION_NAME='utf8mb4_0900_ai_ci'; 
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4            | 255 | Yes        | Yes         | 0       | NO PAD        |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
1 row in set可以看到二者在 PAD_ATTRIBUTE 的上差别。

在这里插入图片描述
MySQL的排序规则有一个属性Pad Attributes属性,这个属性的设置会影响数据库如何处理尾部空格(是否忽略尾部空格),如下官方文档描述

Collation Pad AttributesCollations based on UCA 9.0.0 and higher are faster than collations based on UCA versions prior to 9.0.0. They also have a pad attribute of NO PAD, in contrast to PAD SPACE as used in collations based on UCA versions prior to 9.0.0. For comparison of nonbinary strings, NO PAD collations treat spaces at the end of strings like any other character (see Trailing Space Handling in Comparisons).Comparison of nonbinary string values (CHAR, VARCHAR, and TEXT) that have a NO PAD collation differ from other collations with respect to trailing spaces. For example, 'a' and 'a ' compare as different strings, not the same string. This can be seen using the binary collations for utf8mb4. The pad attribute for utf8mb4_bin is PAD SPACE, whereas for utf8mb4_0900_bin it is NO PAD. Consequently, operations involving utf8mb4_0900_bin do not add trailing spaces, and comparisons involving strings with trailing spaces may differ for the two collations

官方文档,关于比较中尾部空格处理介绍如下:

Trailing Space Handling in Comparisons
MySQL collations have a pad attribute, which has a value of PAD SPACE or NO PAD:
• Most MySQL collations have a pad attribute of PAD SPACE.
• The Unicode collations based on UCA 9.0.0 and higher have a pad attribute of NO PAD; see Section 10.10.1, “Unicode Character Sets”.
For nonbinary strings (CHAR, VARCHAR, and TEXT values), the string collation pad attribute determines treatment in comparisons of trailing spaces at the end of strings:
• For PAD SPACE collations, trailing spaces are insignificant in comparisons; strings are compared without regard to trailing spaces.
• NO PAD collations treat trailing spaces as significant in comparisons, like any other character.
The differing behaviors can be demonstrated using the two utf8mb4 binary collations, one of which is PAD SPACE, the other of which is NO PAD.

2.2、PAD SPACE与NO PAD的具体意义

  • PAD SPACE:在排序和比较运算中,忽略字符串尾部空格。
  • NO PAD:在排序和比较运算中,字符串尾部空格当成普通字符,不能忽略。

官方文档中也要一个例子简单说明,两者比较时,如何处理尾部空格。如下所示,相当直观、明了:

mysql> SELECT 'a ' = 'a'  COLLATE utf8mb4_general_ci;
+------------+
| 'a ' = 'a' |
+------------+
|          1 |
+------------+mysql> SELECT 'a ' = 'a'  COLLATE utf8mb4_0900_ai_ci;
+------------+
| 'a ' = 'a' |
+------------+
|          0 |
+------------+

以上示例可以看出不同的字符集对于尾部空格的处理方式也不同。最上面的示例中name字段我们用的是 utf8mb4_general_ci字符集,即Pad Attributes属性为PAD SPACE,所以忽略尾部空格,不管是字段存储中带空格,还是查询语句where条件后面带空格都能匹配上。


接下来我们用utf8mb4_0900_ai_ci字符集,即Pad Attributes属性为NO PAD测试一下:
CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(10)  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '姓名',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;insert into user values
(null,'123456789     '), -- 数字后面有9个空格
(null,'张三   '), -- 名字后面有3个空格 
(null,'李四   '),-- 名字后面有3个空格
(null,'王五')
;

在这里插入图片描述
我们发现当属性为NO PAD时是不会忽略尾部的空格的,所以where后面不带空格时查询不出来内容。

另外,我们这里测试的是VARCHAR类型,如果字段类型为CHAR呢?

三、CHAR类型尾部空格的处理

其实呢,对于CHAR类型和VARCHA类型,它们的存储略有区别:

CHAR(N):当插入的字符数小于N,它会在字符串的右边补充空格,直到总字符数达到N再进行存储;当查询返回数据时默认会将字符串尾部的空格去掉,除非SQL_MODE设置PAD_CHAR_TO_FULL_LENGTH。

VARCHAR(N):当插入的字符数小于N,它不会在字符串的右边补充空格,insert内容原封不动的进行存储;如果原本字符串右边有空格,在存储和查询返回时都会保留空格

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

PAD_CHAR_TO_FULL_LENGTH

By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR columns, for which trailing spaces are retained on retrieval.

Note: As of MySQL 8.0.13, PAD_CHAR_TO_FULL_LENGTH is deprecated. Expect it to be removed in a future version of MySQL.

注意事项:

  • PAD_CHAR_TO_FULL_LENGTH只影响CHAR类型,不影响VARCHAR类型。
  • MySQL 8.0.13后,PAD_CHAR_TO_FULL_LENGTH参数过时/废弃了。这个参数可能在后续的MySQL版本中被移除。

当前版本(MySQL 8.0.33)中,暂时还可以在SQL_MODE中设置这个参数,不过默认不会设置此参数。那么我们来测试验证一下:

drop table test;
create table test(id int not null, name char(10)  );
insert into test(id , name) values(1, null);
insert into test(id , name) values(2, '');
insert into test(id , name) values(3, ' '); -- 包含一个空格
insert into test(id , name) values(4, '  ');-- 包含两个空格mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_modeValue: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)mysql> --如下所示,SQL_MODE没有设置PAD_CHAR_TO_FULL_LENGTH时,查询返回数据时默认会将字符串尾部的空格去掉,所以你看到长度为零
mysql> select id, length(name), char_length(name),hex(name) from test;
+----+--------------+-------------------+-----------+
| id | length(name) | char_length(name) | hex(name) |
+----+--------------+-------------------+-----------+
|  1 |         NULL |              NULL | NULL      |
|  2 |            0 |                 0 |           |
|  3 |            0 |                 0 |           |
|  4 |            0 |                 0 |           |
+----+--------------+-------------------+-----------+
4 rows in set (0.00 sec)mysql>

那么我们手工设置一下当前会话的SQL_MODE,然后对比测试一下:

mysql> set sql_mode='PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> show variables like 'sql_mode';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| sql_mode      | PAD_CHAR_TO_FULL_LENGTH |
+---------------+-------------------------+
1 row in set (0.01 sec)mysql> select id, length(name), char_length(name),hex(name) from test;
+----+--------------+-------------------+----------------------------------+
| id | length(name) | char_length(name) | hex(name)                        |
+----+--------------+-------------------+----------------------------------+
|  1 |         NULL |              NULL | NULL                             |
|  2 |           10 |                10 | 20202020202020202020 |
|  3 |           10 |                10 | 20202020202020202020 |
|  4 |           10 |                10 | 20202020202020202020 |
+----+--------------+-------------------+----------------------------------+
4 rows in set (0.00 sec)mysql>

通过上面的分析讲述,我们知道当数据库的排序规则的Pad Attributes属性为NO PAD时,此时SQL_MODE的PAD_CHAR_TO_FULL_LENGTH设置与否将会影响查询结果。我们新建一个test2数据库,数据库排序规则为utf8mb4_0900_ai_ci,下面我们通过实验对比一下就知道了:

mysql> use test2;
Database changed
mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_modeValue: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)-- 对于CHAR类型,查询返回数据时默认会将字符串尾部的空格去掉,所以name=''会得到三条记录
mysql> select * from test where name='';
+----+------+
| id | name |
+----+------+
|  2 |      |
|  3 |      |
|  4 |      |
+----+------+
3 rows in set (0.01 sec)mysql> select * from test where name=' ';
Empty set (0.00 sec)mysql> select * from test where name='  ';
Empty set (0.00 sec)mysql> set sql_mode='PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_modeValue: PAD_CHAR_TO_FULL_LENGTH
1 row in set (0.01 sec)
-- SQL_MODE设置PAD_CHAR_TO_FULL_LENGTH。查询返回数据时,字符串尾部的空格不会去掉,此时,name字段时10个空格,故而下面查询条件查不到数据。
mysql>  select * from test where name='';
Empty set (0.01 sec)mysql> select * from test where name=' ';
Empty set (0.00 sec)mysql> select * from test where name='  ';
Empty set (0.00 sec)mysql>

四、其他问题

4.1、在PAD SPACE属性时如何实现精准查询

上述案例中我们知道PAD_ATTRIBUTE属性为PAD SPACE时,在排序和比较运算中,忽略字符串尾部空格。此时where后面用等值匹配会匹配到结尾有空格的数据,那么如何实现精准查询呢?

drop table user;
CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(10)  CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT '姓名',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;insert into user values
(null,'张三'), -- 名字后无空格
(null,'张三 '),-- 名字后1个空格
(null,'张三  ')-- 名字后2个空格
;

在这里插入图片描述

方法一:like

select * from user where `name` like ?

在这里插入图片描述

方法二:BINARY

select * from user where `name` = BINARY ?

BINARY 不是函数,是类型转换运算符,它用来强制它后面的字符串为一个二进制字符串,可以理解成精确匹配

在这里插入图片描述

五、总结

关于MySQL的尾部空格是否忽略,以及对查询结果的影响,既跟数据库的排序规则有关(确切来说,是跟数据库排序规则的Pad Attributes有关),其实还跟字符类型和SQL_MODE是否设置PAD_CHAR_TO_FULL_LENGTH有关。

  1. MySQL的CHAR、VARCHAR、TEXT等字符串字段在等值比较(“=”)时,基于PAD SPACE校对规则,会忽略掉尾部的空格;
  2. 如果想要精确查询就不能用等值查询(“=”),而应改用LIKE或BINARY;








参考资料:

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html

https://dev.mysql.com/doc/refman/8.0/en/charset-binary-collations.html

这篇关于MySQL尾部空格处理与哪些设置有关? 字符集PAD SPACE与NO PAD属性的区别、MySQL字段尾部有空格为什么也能查询出来?的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/927736

相关文章

Python使用getopt处理命令行参数示例解析(最佳实践)

《Python使用getopt处理命令行参数示例解析(最佳实践)》getopt模块是Python标准库中一个简单但强大的命令行参数处理工具,它特别适合那些需要快速实现基本命令行参数解析的场景,或者需要... 目录为什么需要处理命令行参数?getopt模块基础实际应用示例与其他参数处理方式的比较常见问http

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例(伪代码)在数据库系统中,r

Java Response返回值的最佳处理方案

《JavaResponse返回值的最佳处理方案》在开发Web应用程序时,我们经常需要通过HTTP请求从服务器获取响应数据,这些数据可以是JSON、XML、甚至是文件,本篇文章将详细解析Java中处理... 目录摘要概述核心问题:关键技术点:源码解析示例 1:使用HttpURLConnection获取Resp

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Java中Switch Case多个条件处理方法举例

《Java中SwitchCase多个条件处理方法举例》Java中switch语句用于根据变量值执行不同代码块,适用于多个条件的处理,:本文主要介绍Java中SwitchCase多个条件处理的相... 目录前言基本语法处理多个条件示例1:合并相同代码的多个case示例2:通过字符串合并多个case进阶用法使用

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

Java实现优雅日期处理的方案详解

《Java实现优雅日期处理的方案详解》在我们的日常工作中,需要经常处理各种格式,各种类似的的日期或者时间,下面我们就来看看如何使用java处理这样的日期问题吧,感兴趣的小伙伴可以跟随小编一起学习一下... 目录前言一、日期的坑1.1 日期格式化陷阱1.2 时区转换二、优雅方案的进阶之路2.1 线程安全重构2

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分