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

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.

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>

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é)長度的內(nèi)置函數(shù)，
#char_length獲取字符串長度的內(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>

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?|
+------------+

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è)空格

mysql>?select?*?from?test?where?name='';?--?長度為零的空字符串
+----+------+
|?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>

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ì)將字符串尾部的空格去掉，所以你看到長度為零
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>

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>

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>