MySQL尾部空格处理与哪些设置有关?

这篇具有很好参考价值的文章主要介绍了MySQL尾部空格处理与哪些设置有关?。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

在之前的这篇文章Oracle/MySQL/PG/SQL Server关系数据库中NULL与空字符串的区别[1]中,简单对比、介绍了一下MySQL的NULL/空格/尾部空格处理方式,主要对比了NULL与长度为空的字符串,其实很多地方没有分析到位就一笔带过了。这篇文章重新来细说一下MySQL的尾部空格处理方式。在MySQL中,有几个因素会影响MySQL如何处理空格。这里简单浅析一下.

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

Collation Pad Attributes

Collations 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

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

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.

我们先来看看PAD SPACE与NO PAD的具体意义:

  • PAD SPACE:在排序和比较运算中,忽略字符串尾部空格。
  • NO PAD:在排序和比较运算中,字符串尾部空格当成普通字符,不能忽略。
SELECT * 
FROM INFORMATION_SCHEMA.COLLATIONS
       WHERE CHARACTER_SET_NAME = 'utf8mb4';

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT * 
    -> FROM INFORMATION_SCHEMA.COLLATIONS
    ->        WHERE CHARACTER_SET_NAME = 'utf8mb4'
    ->   AND COLLATION_NAME IN('utf8mb4_general_ci','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        |
| utf8mb4_general_ci | utf8mb4            |  45 |            | Yes         |       1 | PAD SPACE     |
+--------------------+--------------------+-----+------------+-------------+---------+---------------+
2 rows in set (0.00 sec)

mysql>

下面我们来测试演练一下,首先新建一个测试数据库gsp

CREATE DATABASE IF NOT EXISTS gsp   
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;

mysql> create table test(id int not nullname varchar(16));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(id , namevalues(1null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(id , namevalues(2'');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test(id , namevalues(3' '); -- 包含一个空格
Query OK, 1 row affected (0.00 sec)

mysql> insert into test(id , namevalues(4'  ');-- 包含两个空格
Query OK, 1 row affected (0.00 sec)

mysql> 


#length获取字符串字节长度的内置函数,
#char_length获取字符串长度的内置函数
#hex将字符或数字转换为十六进制格式
mysql> select idlength(name), char_length(name), hex(namefrom test;
+----+--------------+-------------------+-----------+
| id | length(name) | char_length(name) | hex(name) |
+----+--------------+-------------------+-----------+
|  1 |         NULL |              NULL | NULL      |
|  2 |            0 |                 0 |           |
|  3 |            1 |                 1 | 20        |
|  4 |            2 |                 2 | 2020      |
+----+--------------+-------------------+-----------+
4 rows in set (0.00 sec)

mysql> select * from test where name='';
+----+------+
| id | name |
+----+------+
|  2 |      |
|  3 |      |
|  4 |      |
+----+------+
3 rows in set (0.00 sec)

mysql>

mysql> select * from test where name=' '-- 包含一个空格
+----+------+
| id | name |
+----+------+
|  2 |      |
|  3 |      |
|  4 |      |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from test where name='  '-- 包含两个空格

+----+------+
| id | name |
+----+------+
|  2 |      |
|  3 |      |
|  4 |      |
+----+------+
3 rows in set (0.00 sec)

mysql>

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

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          1 |
+------------+
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          0 |
+------------+

我们上面的例子中,gsp数据库的排序规则为utf8mb4_general_ci,它的Pad Attributes属性为PAD SPACE,在排序和比较运算中,数据库将忽略字符串尾部空格,所以上面几个SQL的查询结果是一样的。如果你数据库的排序规则选择utf8mb4_0900_ai_ci,那么Pad Attributes属性为NO PAD,此时,在排序和比较运算中,字符串尾部空格当成普通字符处理,不能忽略.我们知道MySQL的排序规则分为服务器级别,数据库级别、表级别、列级别、SQL语句级别。优先级顺序为:SQL语句级别>列级别>表级别>数据库级别>服务器级别。你可以重新建一个数据库,设置数据库排序规则为utf8mb4_0900_ai_ci,也可以修改其他级别的排序规则...。这里我们就直接修改表的列级别排序规则。脚本如下所示:

drop table test;
create table test(id int not nullname varchar(16collate utf8mb4_0900_ai_ci );
insert into test(id , namevalues(1null);
insert into test(id , namevalues(2'');
insert into test(id , namevalues(3' '); -- 包含一个空格
insert into test(id , namevalues(4'  ');-- 包含两个空格

然后,我们测试一下,如下所示,此时由于数据库将字符串尾部空格当成普通字符,所以此时的查询结果就不同了。如下所示

mysql> select * from test where name=''-- 长度为零的空字符串
+----+------+
| id | name |
+----+------+
|  2 |      |
+----+------+
1 row in set (0.00 sec)

mysql> select * from test where name=' '-- 含一个空格
+----+------+
| id | name |
+----+------+
|  3 |      |
+----+------+
1 row in set (0.00 sec)

mysql> select * from test where name='  ';-- 包含两个空格
+----+------+
| id | name |
+----+------+
|  4 |      |
+----+------+
1 row in set (0.00 sec)

mysql>

另外,我们这里测试的是VARCHAR类型,如果字段类型为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 nullname char(16)  );
insert into test(id , namevalues(1null);
insert into test(id , namevalues(2'');
insert into test(id , namevalues(3' '); -- 包含一个空格
insert into test(id , namevalues(4'  ');-- 包含两个空格

mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: 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 idlength(name), char_length(name),hex(namefrom 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 idlength(name), char_length(name),hex(namefrom test;
+----+--------------+-------------------+----------------------------------+
| id | length(name) | char_length(name) | hex(name)                        |
+----+--------------+-------------------+----------------------------------+
|  1 |         NULL |              NULL | NULL                             |
|  2 |           16 |                16 | 20202020202020202020202020202020 |
|  3 |           16 |                16 | 20202020202020202020202020202020 |
|  4 |           16 |                16 | 20202020202020202020202020202020 |
+----+--------------+-------------------+----------------------------------+
4 rows in set (0.00 sec)

mysql>

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

mysql> use gsp2;
Database changed
mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: 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_mode
        Value: PAD_CHAR_TO_FULL_LENGTH
1 row in set (0.01 sec)
-- SQL_MODE设置PAD_CHAR_TO_FULL_LENGTH。查询返回数据时,字符串尾部的空格不会去掉,此时,name字段时16个空格,故而下面查询条件查不到数据。
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> 

总结

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

参考资料

[1]

1: https://mp.weixin.qq.com/s?__biz=Mzg2OTAwMTE3NQ==&mid=2247488201&idx=1&sn=ff31b7d0f824992c77ff432a9013a42f&chksm=cea2e071f9d5696777621e1122dcd619d4b734f2d28ca8ce25c96b316cc6bc95a1849c04367d#rd

[2]

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

[3]

3: https://dev.mysql.com/doc/refman/8.0/en/charset-binary-collations.html文章来源地址https://www.toymoban.com/news/detail-586972.html

到了这里,关于MySQL尾部空格处理与哪些设置有关?的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 高层管理者应向 CISO 提出哪些有关公司安全的问题

    不可接受的信息安全事件:Positive Technologies 案例研究 在首席信息安全官和高层管理代表的共同努力下,Positive Technologies 公司形成并建立了自己的防御战略。随着业务的发展,公司成功地从大量抽象的网络风险转变为三个关键的不可接受的信息安全事件: 1.         在

    2024年02月04日
    浏览(51)
  • 【CentOS】有关时间的设置

    CentOS 7 语法信息 查看时间 设置时间 不带日期时不会修改日期 设置日期 不带时间时会设置时间为00:00:00 交互式设置时区 特别注意红色部分 以上修改并不会立即生效,需按照红色要求将TZ设置到环境变量中并重新登录 语法 显示当前及所有时区 修改时区 语法 读取硬件时钟 使

    2024年02月06日
    浏览(31)
  • 利用 设置空格

    想要实现上面效果,一开始直接el-col :span=\\\"8\\\" {{ item.name }}nbsp;nbsp;/el-col 或者el-col :span=\\\"8\\\" {{ item.name + \\\'  \\\' }}/el-col或者el-col :span=\\\"8\\\" {{ item.name + \\\'nbsp;nbsp;\\\' }}/el-col 都无效,后使用下面方法实现想要的空格效果

    2024年02月22日
    浏览(32)
  • 设置vscode显示tab和空格

    要在 Visual Studio Code 中显示空格和制表符,您需要更新设置。以下是操作步骤: 打开 Visual Studio Code。 点击左侧边栏的齿轮图标,然后选择“设置”(Settings)。这将打开设置选项卡。 在设置搜索框中,输入 \\\"renderWhitespace\\\"。 在 \\\"Editor: Render Whitespace\\\" 设置下,点击下拉菜单并选择

    2024年02月14日
    浏览(34)
  • 如何在上架App之前设置证书并上传应用

    在上架App之前想要进行真机测试的同学,请查看《iOS- 最全的真机测试教程》,里面包含如何让多台电脑同时上架App和真机调试。 P12文件的使用详解 注意: 同样可以在Build Setting 的sign中设置证书,但是有点麻烦,建议就在General中设置证书,比较方便,还可以查看错误的地方

    2024年02月07日
    浏览(52)
  • Hive字符串函数-空格处理

    平常我们在数据开发的过程中,字符串函数里面包含空格会导致数据的一致性被破坏,造成我们的开发脚本出错,所以我们在数据预处理的时候,有两种情况需要考虑,一种是字符串里面空格的内容是否需要清洗,另一种字符串长度缺少是否需要用空格填充。 语法:trim(str

    2023年04月08日
    浏览(40)
  • 有关使用HttpServletRequest的Cookie的设置和获取

    介绍了如何在HttpServletRequest中对Cookie的进行设置和获取。 在服务器端的HttpServletRequest中对Cookie的进行设置后,客户端在接下来的请求中会携带此设置好的Cookie,所以可以在服务器端接收请求时提取这个Cookie的值。 服务器端设置Cookie值 : 服务器端提取Cookie值 : Stackoverflow:

    2024年02月09日
    浏览(50)
  • chatgpt赋能python:Python中如何设置空格

    作为一门流行的编程语言,Python被广泛地应用于各种领域。在编写Python代码时,空格的使用非常重要。适当的空格设置可以让代码易读易懂,同时也有助于提高代码的可维护性和可重用性。 在Python中,空格是用于分隔单词、数字和符号的一种字符。空格可以用于分隔语句、函

    2024年02月08日
    浏览(44)
  • Eclipse 设置 tab width 为 4 个空格 (spaces)

    Window - Preferences - General - Editors - Text Editors,选中右侧的 Insert space for tabs. Apply and Close. Window - Preference - C++ - Code Style - Formatter,点击右侧的 Editor,选择左侧 Tab policy 的值为 Spaces only,应用即可。 若出现应用 Apply 按钮为灰色的情况,需要回到上一步。点击 New… 按钮,根据当前

    2024年02月20日
    浏览(43)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包