MySQL尾部空格處理與哪些設置有關?

来源:https://www.cnblogs.com/kerrycode/archive/2023/07/20/17568714.html
-Advertisement-
Play Games

在之前的這篇文章Oracle/MySQL/PG/SQL Server關係資料庫中NULL與空字元串的區別[1]中，簡單對比、介紹了一下MySQL的NULL/空格/尾部空格處理方式，主要對比了NULL與長度為空的字元串，其實很多地方沒有分析到位就一筆帶過了。這篇文章重新來細說一下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 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，下麵我們通過實驗對比一下就知道了：
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 在 Visual Studio 中規範化文件編輯
    ## 簡介 ##### IoC Ioc控制反轉，是一種設計模式和原則，旨在解耦組件之間的依賴關係，並將對象的創建和管理委托外部容器。是面向編程中一種重要的概念，用於提高代碼的可維護性. 核心思想：通過將控制權從高層轉移到底層模塊，實現對依賴關係的控制反轉，傳統上，一個對象通常負責自己的依賴項創建和管 ...
  • Learning hard C#學習筆記——讀書筆記 07
    本文介紹了值類型和引用類型在編程中的區別。值類型包括簡單類型、枚舉類型和結構體類型，通常被分配線上程的堆棧上，變數保存的是實例數據本身。引用類型實例則被分配在托管堆上，變數保存的是實例數據的記憶體地址。值類型由操作系統負責管理，而引用類型則由垃圾回收機制（GC）負責管理。本文還通過示例代碼展示了值類型... ...
  • PerfView專題 (第十四篇)： 洞察那些 C# 代碼中的短命線程
    ## 一：背景 ### 1. 講故事 這篇文章源自於分析一些疑難dump的思考而產生的靈感，在dump分析中經常要尋找的一個答案就是如何找到死亡線程的生前都做了一些什麼？參考如下輸出： ``` C# 0:001> !t ThreadCount: 22 UnstartedThread: 0 Backg ...
  • Learning hard C#學習筆記——讀書筆記 06
    本文介紹了 C# 中委托的定義、使用、為什麼引入委托以及委托的本質。同時，還介紹了委托鏈的使用，即將多個委托鏈接在一起，提高程式的可擴展性。 ...
  • 寫驅動實現LED閃爍及基礎概念查看
    > 2023/7/20 初學內核，記錄與分享，感嘆內核學了後真的感覺很多東西都通透了，但是難度太大，只能淺淺初探。 # 前提 內核五大功能 ➢ **進程管理**：進程的創建，銷毀，調度等功能 註：可中斷，不可中斷，就是是否被信號打斷。從運行狀態怎樣改到可中斷等待態，和不可中斷等待態操作系統開始會對每 ...
  • Linux系統安裝CH341驅動
    ARP學習和老化機制在Linux網路通信中起著至關重要的作用。ARP（Address Resolution Protocol）地址解析協議是將IP地址解析為MAC地址的一種機制。本文將詳細介紹Linux中ARP學習和老化的概念、工作原理以及其重要性。 ...
  • 選讀SQL經典實例筆記11_結果集變換
    ![](https://img2023.cnblogs.com/blog/3076680/202307/3076680-20230719150159605-2139117895.png) # 1. 變換結果集成一行 ## 1.1. 結果集 ```sql DEPTNO CNT 10 3 20 5 30 ...
  • 該如何選擇ClickHouse的表引擎
    該如何選擇ClickHouse的表引擎 本文將介紹ClickHouse中一個非常重要的概念—表引擎(table engine)。如果對MySQL熟悉的話，或許你應該聽說過InnoDB和MyISAM存儲引擎。不同的存儲引擎提供不同的存儲機制、索引方式、鎖定水平等功能，也可以稱之為表類型。ClickHo ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類