欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL尾部空格處理方法詳解

 更新時(shí)間：2023年07月21日 08:38:10   作者：瀟湘隱者  
這篇文章主要介紹了MySQL尾部空格處理方法詳解的相關(guān)資料,通過創(chuàng)建合適的索引來實(shí)現(xiàn)區(qū)分有尾部空格和沒有尾部空格的字符串，需要的朋友可以參考下

在之前的這篇文章Oracle/MySQL/PG/SQL Server關(guān)系數(shù)據(jù)庫中NULL與空字符串的區(qū)別中，簡(jiǎn)單對(duì)比、介紹了一下MySQL的NULL/空格/尾部空格處理方式，主要對(duì)比了NULL與長(zhǎng)度為空的字符串，其實(shí)很多地方?jīng)]有分析到位就一筆帶過了。這篇文章重新來細(xì)說一下MySQL的尾部空格處理方式。在MySQL中，有幾個(gè)因素會(huì)影響MySQL如何處理空格。這里簡(jiǎn)單淺析一下.

MySQL的排序規(guī)則有一個(gè)屬性Pad Attributes屬性，這個(gè)屬性的設(shè)置會(huì)影響數(shù)據(jù)庫如何處理尾部空格（是否忽略尾部空格），如下官方文檔描述

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

官方文檔，關(guān)于比較中尾部空格處理介紹如下：

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：在排序和比較運(yùn)算中，忽略字符串尾部空格。

  • NO PAD：在排序和比較運(yùn)算中，字符串尾部空格當(dāng)成普通字符，不能忽略。

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>

下面我們來測(cè)試演練一下，首先新建一個(gè)測(cè)試數(shù)據(jù)庫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,?'?');?--?包含一個(gè)空格
Query?OK,?1?row?affected?(0.00?sec)

mysql>?insert?into?test(id?,?name)?values(4,?'??');--?包含兩個(gè)空格
Query?OK,?1?row?affected?(0.00?sec)

mysql>?


#length獲取字符串字節(jié)長(zhǎng)度的內(nèi)置函數(shù)，
#char_length獲取字符串長(zhǎng)度的內(nèi)置函數(shù)
#hex將字符或數(shù)字轉(zhuǎn)換為十六進(jìn)制格式
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='?';?--?包含一個(gè)空格
+----+------+
|?id?|?name?|
+----+------+
|??2?|??????|
|??3?|??????|
|??4?|??????|
+----+------+
3?rows?in?set?(0.00?sec)

mysql>?select?*?from?test?where?name='??';?--?包含兩個(gè)空格

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

mysql>

官方文檔中也要一個(gè)例子簡(jiǎn)單說明，兩者比較時(shí)，如何處理尾部空格。如下所示，相當(dāng)直觀、明了：

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數(shù)據(jù)庫的排序規(guī)則為utf8mb4_general_ci，它的Pad Attributes屬性為PAD SPACE，在排序和比較運(yùn)算中，數(shù)據(jù)庫將忽略字符串尾部空格，所以上面幾個(gè)SQL的查詢結(jié)果是一樣的。如果你數(shù)據(jù)庫的排序規(guī)則選擇utf8mb4_0900_ai_ci，那么Pad Attributes屬性為NO PAD，此時(shí),在排序和比較運(yùn)算中，字符串尾部空格當(dāng)成普通字符處理，不能忽略.我們知道MySQL的排序規(guī)則分為服務(wù)器級(jí)別，數(shù)據(jù)庫級(jí)別、表級(jí)別、列級(jí)別、SQL語句級(jí)別。優(yōu)先級(jí)順序?yàn)椋篠QL語句級(jí)別>列級(jí)別>表級(jí)別>數(shù)據(jù)庫級(jí)別>服務(wù)器級(jí)別。你可以重新建一個(gè)數(shù)據(jù)庫，設(shè)置數(shù)據(jù)庫排序規(guī)則為utf8mb4_0900_ai_ci，也可以修改其他級(jí)別的排序規(guī)則...。這里我們就直接修改表的列級(jí)別排序規(guī)則。

腳本如下所示：

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,?'?');?--?包含一個(gè)空格
insert?into?test(id?,?name)?values(4,?'??');--?包含兩個(gè)空格

然后，我們測(cè)試一下，如下所示，此時(shí)由于數(shù)據(jù)庫將字符串尾部空格當(dāng)成普通字符，所以此時(shí)的查詢結(jié)果就不同了。如下所示

mysql>?select?*?from?test?where?name='';?--?長(zhǎng)度為零的空字符串
+----+------+
|?id?|?name?|
+----+------+
|??2?|??????|
+----+------+
1?row?in?set?(0.00?sec)

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

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

mysql>

另外，我們這里測(cè)試的是VARCHAR類型，如果字段類型為CHAR呢？

其實(shí)呢，對(duì)于CHAR類型和VARCHA類型，它們的存儲(chǔ)略有區(qū)別：

CHAR(N)：當(dāng)插入的字符數(shù)小于N，它會(huì)在字符串的右邊補(bǔ)充空格，直到總字符數(shù)達(dá)到N再進(jìn)行存儲(chǔ)；當(dāng)查詢返回?cái)?shù)據(jù)時(shí)默認(rèn)會(huì)將字符串尾部的空格去掉，除非SQL_MODE設(shè)置PAD_CHAR_TO_FULL_LENGTH。

VARCHAR(N)：當(dāng)插入的字符數(shù)小于N，它不會(huì)在字符串的右邊補(bǔ)充空格，insert內(nèi)容原封不動(dòng)的進(jìn)行存儲(chǔ)；如果原本字符串右邊有空格，在存儲(chǔ)和查詢返回時(shí)都會(huì)保留空格

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.

注意事項(xiàng)：

  • PAD_CHAR_TO_FULL_LENGTH只影響CHAR類型，不影響VARCHAR類型。

  • MySQL 8.0.13后，PAD_CHAR_TO_FULL_LENGTH參數(shù)過時(shí)/廢棄了。這個(gè)參數(shù)可能在后續(xù)的MySQL版本中被移除。

當(dāng)前版本（MySQL 8.0.33）中，暫時(shí)還可以在SQL_MODE中設(shè)置這個(gè)參數(shù)，不過默認(rèn)不會(huì)設(shè)置此參數(shù)。那么我們來測(cè)試驗(yàn)證一下：

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,?'?');?--?包含一個(gè)空格
insert?into?test(id?,?name)?values(4,?'??');--?包含兩個(gè)空格

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沒有設(shè)置PAD_CHAR_TO_FULL_LENGTH時(shí)，查詢返回?cái)?shù)據(jù)時(shí)默認(rèn)會(huì)將字符串尾部的空格去掉，所以你看到長(zhǎng)度為零
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>

那么我們手工設(shè)置一下當(dāng)前會(huì)話的SQL_MODE,然后對(duì)比測(cè)試一下：

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>

通過上面的分析講述，我們知道當(dāng)數(shù)據(jù)庫的排序規(guī)則的Pad Attributes屬性為NO PAD時(shí)，此時(shí)SQL_MODE的PAD_CHAR_TO_FULL_LENGTH設(shè)置與否將會(huì)影響查詢結(jié)果。我們新建一個(gè)gsp2數(shù)據(jù)庫，數(shù)據(jù)庫排序規(guī)則為utf8mb4_0900_ai_ci，下面我們通過實(shí)驗(yàn)對(duì)比一下就知道了：

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)

--?對(duì)于CHAR類型，查詢返回?cái)?shù)據(jù)時(shí)默認(rèn)會(huì)將字符串尾部的空格去掉，所以name=''會(huì)得到三條記錄
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設(shè)置PAD_CHAR_TO_FULL_LENGTH。查詢返回?cái)?shù)據(jù)時(shí)，字符串尾部的空格不會(huì)去掉，此時(shí)，name字段時(shí)16個(gè)空格，故而下面查詢條件查不到數(shù)據(jù)。
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>

總結(jié)

關(guān)于MySQL的尾部空格是否忽略，以及對(duì)查詢結(jié)果的影響，既跟數(shù)據(jù)庫的排序規(guī)則有關(guān)（確切來說，是跟數(shù)據(jù)庫排序規(guī)則的Pad Attributes有關(guān)），其實(shí)還跟字符類型和SQL_MODE是否設(shè)置PAD_CHAR_TO_FULL_LENGTH有關(guān)。

到此這篇關(guān)于MySQL尾部空格處理方法詳解的文章就介紹到這了,更多相關(guān)MySQL尾部空格處理內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

  • mysql用戶創(chuàng)建與授權(quán)的簡(jiǎn)單實(shí)例

    mysql用戶創(chuàng)建與授權(quán)的簡(jiǎn)單實(shí)例

    MySQL等主流數(shù)據(jù)庫的最高權(quán)限一般是root用戶,有時(shí)我們需要提供數(shù)據(jù)庫的賬號(hào)和密碼以使用某些服務(wù),下面這篇文章主要給大家介紹了關(guān)于mysql用戶創(chuàng)建與授權(quán)的相關(guān)資料,需要的朋友可以參考下
    2022-03-03
  • linux mysql5.6版本的安裝配置過程

    linux mysql5.6版本的安裝配置過程

    mysql官網(wǎng)開始發(fā)布相關(guān)的5.6系列的各個(gè)版本，對(duì)于mysql5.6系列的版本對(duì)一起的版本進(jìn)行了全局性的細(xì)節(jié)性加強(qiáng)
    2013-06-06
  • Mysql查詢時(shí)如何使用for update行鎖還是表鎖

    Mysql查詢時(shí)如何使用for update行鎖還是表鎖

    這篇文章主要介紹了Mysql查詢時(shí)如何使用for update行鎖還是表鎖問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2023-08-08
  • mysql odbc字符集設(shè)置(中文顯示亂碼)

    mysql odbc字符集設(shè)置(中文顯示亂碼)

    mysql odbc字符集設(shè)置(中文顯示亂碼),碰到這個(gè)問題的朋友可以參考下。
    2011-08-08
  • mysql怎么關(guān)閉sql_mode=ONLY_FULL_GROUP_BY模式

    mysql怎么關(guān)閉sql_mode=ONLY_FULL_GROUP_BY模式

    前段時(shí)間在項(xiàng)目開發(fā)過程中發(fā)現(xiàn)了系統(tǒng)異常,打開日志查看發(fā)現(xiàn)了如下的這個(gè)報(bào)錯(cuò),查找相關(guān)資料終于解決了,這篇文章主要給大家介紹了關(guān)于mysql怎么關(guān)閉sql_mode=ONLY_FULL_GROUP_BY模式的相關(guān)資料,需要的朋友可以參考下
    2024-01-01
  • MySQL恢復(fù)誤刪數(shù)據(jù)圖文教程

    MySQL恢復(fù)誤刪數(shù)據(jù)圖文教程

    MySQL誤刪數(shù)據(jù)庫造成了數(shù)據(jù)的丟失,這是非常尷尬的,下面這篇文章主要給大家介紹了關(guān)于MySQL恢復(fù)誤刪數(shù)據(jù)的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2023-06-06
  • MLSQL Stack如何讓流調(diào)試更加簡(jiǎn)單詳解

    MLSQL Stack如何讓流調(diào)試更加簡(jiǎn)單詳解

    這篇文章主要給大家介紹了關(guān)于MLSQL Stack如何讓流調(diào)試更加簡(jiǎn)單的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用MLSQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧
    2019-06-06
  • MySQL 游標(biāo)的定義與使用方式

    MySQL 游標(biāo)的定義與使用方式

    這篇文章主要介紹了MySQL 游標(biāo)的定義與使用方式，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下
    2021-01-01
  • Mysql中的select ...for update

    Mysql中的select ...for update

    這篇文章主要介紹了Mysql中的select ...for update用法,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2023-08-08
  • mysql游標(biāo)的原理與用法實(shí)例分析

    mysql游標(biāo)的原理與用法實(shí)例分析

    這篇文章主要介紹了mysql游標(biāo)的原理與用法,結(jié)合實(shí)例形式分析了mysql游標(biāo)的概念、原理、用法及操作注意事項(xiàng),需要的朋友可以參考下
    2020-04-04

最新評(píng)論