首页 > 数据库 >MySQL尾部空格处理与哪些设置有关?

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

时间：2023-07-20 16:24:52浏览次数：40  
标签：尾部 name MySQL 空格 PAD mysql test sec

在之前的这篇文章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 null, name varchar(16));
Query OK, 0 rows affected (0.01 sec)

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

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

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

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

mysql> 


#length获取字符串字节长度的内置函数，
#char_length获取字符串长度的内置函数
#hex将字符或数字转换为十六进制格式
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 |            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 null, name varchar(16) collate utf8mb4_0900_ai_ci );
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> 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 null, name char(16)  );
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_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 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 |           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

标签：尾部,name,MySQL,空格,PAD,mysql,test,sec
From： https://www.cnblogs.com/kerrycode/p/17568714.html

相关文章

  • linux mysql 安装
    centos安装mysql5.7安装包下载地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads下载安装包：wgethttps://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz解压：tar-zxvfmysql-5.7.36-linux-glibc2.12-x86_64.tar.gz移动重命......
  • MySQL中IF函数使用：字段A的返回值，根据字段B和C的条件来决定
     场景说明：车辆的位置信息是否返回，根据车辆位置的更新时间是否大于车辆的出站时间来决定；如果车辆位置的更新时间大于车辆的出站时间，则为有效的数据，直接返回经纬度数据；否则，视为无效的数据，直接返回NULL。下面是SQL语句的片段：-------------------------------------------------......
  • mysql主从数据库
    今日配置主从数据库，在配置完成时，对主数据库进行创建库操作，从数据库正常同步。但直接对从数据库进行删库操作后。从数据库的sql能力无法正常进行，但主从数据库的IO通信，没有问题。在重新配置主从数据库后。问题解决。 ......
  • oracle trim函数除去空格
    官网语法图TRIM([{{LEADING|TRAILING|BOTH}[trim_character]|trim_character}FROM]trim_source)leading：开头字符trailing：结尾字符both：开头和结尾字符trim_character：去除的字符trim_source：修剪源......
  • flak创建数据库报 NameError: name 'MySQLdb' is not defined
     因为pycharm中无法安装MySQLdb,安装会报错 所以安装pymysql然后替换MySQLdb  进入到mysqldb.py文件中 替换mysqldb方法，导入pymysql取别名为MySQLdb 再次执行便能成功  ......
  • mysql查询数据库重复数据
    查询重复领取的优惠券//查询8888888用户优惠券状态为未使用且数量大于2的用户领取过的优惠券SELECTdiscount_coupon_id,count(discount_coupon_id)ascFROM`faith_user_discount_coupon`whereuser_id='88888888'and`status`='1'GROUPBYdiscount_coupon_idhavingc>......
  • mysql基础~基础知识问答
    一orc系列1 orc切换模式常用的是主动触发高可用切换(例行维护)，被动高可用切换(mysql负载很高，mysql主库down掉) 2orc在以下情况不会发生切换从库发生故障，主从复制异常，从库挂掉，从库出现延时主库hang住，主库出现toomanayconnetions(因为orc建立的长连接，即便连接打满，也不会......
  • Mysql学习笔记（一）
    一、基础概念1.术语数据库(DB)数据库管理系统(DBMS)SQL(StructuredQueryLanguage)2.关系型数据库（二维表）二、SQL1.分类DDL(DataDefinitionLanguage)数据定义语言（操作数据库、表、字段）DML(DataManipulationLanguage)数据操作语言（增删改）DQL(DataQueryLanguage)......
  • java字符串补齐空格
    Java字符串补齐空格在Java编程中，我们经常需要对字符串进行格式化处理。其中一个常见的需求是对字符串进行补齐空格操作，以使字符串在输出时保持对齐。本文将介绍在Java中如何补齐空格，并提供代码示例来说明。1.使用String.format方法补齐空格Java的String类提供了一个format方法，......
  • kubectl apply -f mysql.yaml error: the path "mysql.yaml" does not exist
    问题解决：kubectlapply-fmysql.yamlerror:thepath"mysql.yaml"doesnotexist在使用Kubernetes进行容器编排时，我们经常使用kubectl命令行工具与Kubernetes集群进行交互。其中，kubectlapply命令用于创建或更新Kubernetes资源的配置文件。然而，有时在执行kubectlapply-fmys......

赞助商

阅读排行

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

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

Copyright © 2020-2023 IPS99 版权所有 IPS99