MySQL中字符集與排序規(guī)則的實現(xiàn)
在MySQL中，當使用字符串類型時，有兩方面的知識我們需要特別關(guān)注一下：字符集和排序規(guī)則。如果使用不當，則可能會導致性能問題或在插入數(shù)據(jù)時出現(xiàn)一些異常情況。
字符集定義了對應列允許使用的字符，而排序規(guī)則是用于比較這些字符的基礎(chǔ)規(guī)則。通常，每個類型的字符集都會有多種排序規(guī)則，但一個排序規(guī)則只能屬于一個字符集。
這篇文章，我們就圍繞MySQL中字符集以及排序規(guī)則展開，詳細聊聊相關(guān)的技術(shù)點。
MySQL中的字符集
MySQL支持廣泛的字符集，包括GB2312、GBK、BIG5等本地字符集，以及多種Unicode字符集，如utf8mb3 (MySQL中舊版的utf8)、utf8mb4、ucs2、utf16和utf32。utf8mb4是推薦使用的字符集，因為它能完整支持所有Unicode字符，包括表情符號和不常用的漢字。
可以使用下面的命令在information_schema數(shù)據(jù)庫中查看所有字符集：
mysql> SELECT * FROM information_schema.character_sets ORDER BY character_set_name; +--------------------+----------------------+---------------------------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+---------------------------------+--------+ | armscii8 | armscii8_general_ci | ARMSCII-8 Armenian | 1 | | ascii | ascii_general_ci | US ASCII | 1 | | big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 | | binary | binary | Binary pseudo charset | 1 | …… …… …… | utf16 | utf16_general_ci | UTF-16 Unicode | 4 | | utf16le | utf16le_general_ci | UTF-16LE Unicode | 4 | | utf32 | utf32_general_ci | UTF-32 Unicode | 4 | | utf8 | utf8_general_ci | UTF-8 Unicode | 3 | | utf8mb4 | utf8mb4_0900_ai_ci | UTF-8 Unicode | 4 | +--------------------+----------------------+---------------------------------+--------+
通過上述命令可以列出當前MySQL支持的所有字符集，以及它們的默認排序規(guī)則。每種字符集都有一個默認排序規(guī)則。
在上述命令展示的列表底部，有兩個字符集被描述為UTF-8 Unicode。utf8字符集的MAXLEN為3，而utf8mb4的MAXLEN為4。這里指的是每個字符允許的最大字節(jié)長度。
特別需要留意的是，**根據(jù)UTF-8標準，每個字符允許使用最多4個字節(jié)，這意味著MySQL的utf8字符集實際上并不是真正的UTF-8，因為它只支持每字符最多3個字節(jié)。從MySQL 8開始，utf8mb4成為默認字符集，也是最常使用的字符集。**而utf8被保留用于向下兼容，應該不再使用。
如何定義字符集
定義列的字符集有幾種方式。如果你沒有在表或列級別指定字符集，服務器默認的utf8mb4字符集將被應用（除非你明確聲明了一個不同的服務器或數(shù)據(jù)庫默認設(shè)置）。
我們可以通過創(chuàng)建一個沒有字符集信息的表并讀取其定義來驗證這一點：
CREATE TABLE no_charset ( my_column VARCHAR(255) ); SHOW CREATE TABLE no_charset;
生成的CREATE TABLE語句顯示了默認的字符集和排序規(guī)則已經(jīng)被應用：
CREATE TABLE `no_charset` ( `my_column` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
在表級別定義
你可以顯式地在表級別設(shè)置字符集，通過使用CHARSET=[字符集]語法。例如，這里我們創(chuàng)建一個所有字符列都使用latin1字符集的表：
CREATE TABLE no_charset ( `my_column` VARCHAR(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
在列級別定義
你也可以在列級別設(shè)置字符集。這是最具體的設(shè)置，會覆蓋任何表級別的設(shè)置：
CREATE TABLE `mixed_collations` ( `explicitly_set` VARCHAR(255) CHARACTER SET latin1, `implicitly_set` VARCHAR(255) );
通過運行SHOW CREATE TABLE mixed_collations可以看到表的默認字符集是utf8mb4，但是顯式設(shè)置的列使用了latin1字符集：
CREATE TABLE `mixed_collations` ( `explicitly_set` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `implicitly_set` VARCHAR(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
列級別的聲明將覆蓋表級別的聲明，表級別的聲明將覆蓋數(shù)據(jù)庫默認，數(shù)據(jù)庫默認的字符集覆蓋服務器默認。
MySQL中的排序規(guī)則
字符集定義了可以存儲在列中的合法字符，而排序規(guī)則則是確定如何進行字符串比較的規(guī)則。如果你在排序或比較字符串，MySQL就會使用排序規(guī)則來決定順序以及判斷字符串是否相同。
可以通過查詢information_schema表來顯示所有排序規(guī)則。下面的查詢僅顯示與utf8mb4字符集相關(guān)的排序規(guī)則：
mysql> SELECT
-> *
-> FROM
-> information_schema.collations
-> WHERE
-> character_set_name = 'utf8mb4'
-> ORDER BY
-> collation_name;
+----------------------------+--------------------+-----+------------+-------------+---------+---------------+
| 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_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD |
| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD |
| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |
……
……
……
該查詢將顯示所有排序規(guī)則、相關(guān)字符集名稱、是否為默認值以及其他信息。例如，utf8mb4_0900_ai_ci是utf8mb4字符集的默認排序規(guī)則。
排序規(guī)則的命名規(guī)則
排序規(guī)則的命名通常前綴為字符集名稱，后綴由排序規(guī)則的屬性組合而成。
以下是一些主要后綴及其含義：
| 后綴 | 含義 |
|---|---|
| _ai | 不區(qū)分重音符 |
| _as | 區(qū)分重音符 |
| _ci | 不區(qū)分大小寫 |
| _cs | 區(qū)分大小寫 |
| _ks | 區(qū)分假名 |
| _bin | 二進制比較 |
例如，默認排序規(guī)則utf8mb4_0900_ai_ci可以拆解：
utf8mb4：屬于utf8mb4字符集；0900：使用UCA 9.0.0的權(quán)重鍵；_ai：不區(qū)分重音符；_ci：不區(qū)分大小寫。
那么，字符串比較是否區(qū)分大小寫？答案是：“視具體排序規(guī)則而定！”
驗證排序規(guī)則
我們可以使用COLLATE關(guān)鍵字顯式設(shè)置字符串的排序規(guī)則：
mysql> SELECT "MySQL" COLLATE utf8mb4_0900_ai_ci = "mysql" COLLATE utf8mb4_0900_ai_ci; +-------------------------------------------------------------------------+ | "MySQL" COLLATE utf8mb4_0900_ai_ci = "mysql" COLLATE utf8mb4_0900_ai_ci | +-------------------------------------------------------------------------+ | 1 | +-------------------------------------------------------------------------+
此查詢返回結(jié)果值為1，表示MySQL認為這兩個字符串相等。如果改用區(qū)分大小寫的排序規(guī)則，我們會得到不同的結(jié)果：
mysql> SELECT "MySQL" COLLATE utf8mb4_0900_as_cs = "mysql" COLLATE utf8mb4_0900_as_cs; +-------------------------------------------------------------------------+ | "MySQL" COLLATE utf8mb4_0900_as_cs = "mysql" COLLATE utf8mb4_0900_as_cs | +-------------------------------------------------------------------------+ | 0 | +-------------------------------------------------------------------------+
此查詢返回結(jié)果值為0，表明MySQL認為這兩個字符串不同，因為它們大小寫不同。
類似邏輯也適用于重音符。例如，在使用不區(qū)分重音符的排序規(guī)則時，resume與résumé會被認為是相同的。
如何定義排序規(guī)則
與字符集類似，排序規(guī)則可以在表級別或列級別設(shè)置。如果未顯式定義排序規(guī)則，MySQL會使用字符集的默認排序規(guī)則。
表級別定義
可以在CREATE TABLE語句中使用COLLATE子句定義表的排序規(guī)則。例如，以下創(chuàng)建了一個所有字符列都使用utf8mb4_bin排序規(guī)則的表：
CREATE TABLE table_with_collation ( my_column VARCHAR(255) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
列級別定義
也可以在列定義中設(shè)置排序規(guī)則。例如：
CREATE TABLE table_with_collation ( `explicitly_set` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, `implicitly_set` varchar(255) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
此外，也可以通過ALTER TABLE語句修改現(xiàn)有表列的排序規(guī)則：
ALTER TABLE table_with_collation
CHANGE `explicitly_set` `explicitly_set` varchar(255)
CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
小結(jié)
理解字符集和排序規(guī)則是處理MySQL字符串數(shù)據(jù)的基礎(chǔ)。字符集定義了列中存儲的合法字符，排序規(guī)則決定了字符串比較的方式。
- 字符集可以在列級別、表級別定義，或者繼承自數(shù)據(jù)庫或服務器默認值。最具體的層級（列 > 表 > 數(shù)據(jù)庫 > 服務器）將會被采用。
- 排序規(guī)則可以在列級別、表級別定義，或者繼承自字符集默認。仍然是最具體的層級優(yōu)先。
列的字符集和排序規(guī)則會影響數(shù)據(jù)存儲方式以及數(shù)據(jù)的比較和排序行為。在設(shè)計數(shù)據(jù)庫時需注意這些設(shè)置，以確保行為正確并優(yōu)化性能。
如果不確定應使用哪個字符集或排序規(guī)則，MySQL默認的utf8mb4字符集及其默認排序規(guī)則utf8mb4_0900_ai_ci通常是一個不錯的選擇。它們支持所有Unicode字符并提供大小寫不敏感和重音符不敏感的比較功能。
到此這篇關(guān)于MySQL中字符集與排序規(guī)則的實現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL 字符集與排序規(guī)則內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
本文主要介紹了MySQL中通過SQL語句刪除重復記錄并且只保留一條記錄，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-01-01
防止服務器宕機時MySQL數(shù)據(jù)丟失的幾種方案
這篇文章主要介紹了防止服務器宕機時MySQL數(shù)據(jù)丟失的幾種方案,結(jié)合實踐介紹了Replication和Monitor以及Failover這三個項目的應用,需要的朋友可以參考下2015-06-06
mysql中GROUP_CONCAT函數(shù)使用及遇到問題詳解
這篇文章主要給大家介紹了關(guān)于mysql中GROUP_CONCAT函數(shù)使用及遇到問題的相關(guān)資料,GROUP_CONCAT是用于將分組后的多個行連接為一個字符串的聚合函數(shù),文中通過代碼介紹的非常詳細,需要的朋友可以參考下2023-09-09
詳解MySQL和Redis如何保證數(shù)據(jù)一致性
MySQL與Redis都是常用的數(shù)據(jù)存儲和緩存系統(tǒng),為了提高應用程序的性能和可伸縮性,很多應用程序?qū)ySQL和Redis一起使用,其中MySQL作為主要的持久存儲,而Redis作為主要的緩存,那么本文就給大家介紹一下MySQL和Redis如何保證數(shù)據(jù)一致性,需要的朋友可以參考下2023-08-08
C#如何在海量數(shù)據(jù)下的高效讀取寫入MySQL
這篇文章主要介紹了C#如何在海量數(shù)據(jù)下的高效讀取寫入MySQL的相關(guān)資料,需要的朋友可以參考下2016-12-12
這篇文章主要介紹了Linux系統(tǒng)下Mysql使用簡單教程（一）的相關(guān)資料,需要的朋友可以參考下2016-05-05
這篇文章主要介紹了MySQL btree索引與hash索引區(qū)別，幫助大家更好的理解和學習MySQL索引的相關(guān)知識，感興趣的朋友可以了解下2020-09-09
這篇文章主要為大家詳細介紹了MySQL 5.7安裝配置方法，安裝過程中出現(xiàn)問題的解決方案，感興趣的小伙伴們可以參考一下2016-08-08
這篇文章主要為大家詳細介紹了Windows下MySQL5.7.18安裝教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-07-07
這篇文章主要介紹了MySQL高可用MMM方案安裝部署方法，需要的朋友可以參考下2014-02-02