不可忽視的MySQL字符集
MySQL的字符集從latin1經(jīng)過utf8 到utf8mb4 ，算是經(jīng)歷曲折的路線。特別是從使用一個字符集變更另一個字符集時，實踐當中都非常無奈,不是沒辦法，而是麻煩。到了MySQL8.0多出了一個字符集utf8mb4_0900_*的字符集，有必要了解一下。
1. 字符集基礎
先了解下MySQL字符集都有哪些地方使用，進入數(shù)據(jù)庫VARIABLES參數(shù)一目了然：
- mysql> SHOW VARIABLES WHERE variable_name LIKE '%character%' OR variable_name LIKE '%collation%' ;
- +-------------------------------+--------------------------------------+
- | Variable_name | Value |
- +-------------------------------+--------------------------------------+
- | character_set_client | utf8mb4 |
- | character_set_connection | utf8mb4 |
- | character_set_database | utf8mb4 |
- | character_set_filesystem | binary |
- | character_set_results | utf8mb4 |
- | character_set_server | utf8mb4 |
- | character_set_system | utf8 |
- | character_sets_dir | /opt/idc/mysql8.0.23/share/charsets/ |
- | collation_connection | utf8mb4_bin |
- | collation_database | utf8mb4_bin |
- | collation_server | utf8mb4_bin |
- | default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
- +-------------------------------+--------------------------------------+
從上面client，connection，database，results層層環(huán)節(jié)扣著，任何一個環(huán)節(jié)的字符集不兼容都會出現(xiàn)亂碼問題。
MySQL8.0 utf8mb4 Unicode字符集一個新的通用排序歸類，被命名為utf8mb4_0900_ai_ci。MySQL 8.0默認的是utf8mb4_0900_ai_ci，屬于 utf8mb4_unicode_ci中的一種。
這些字段每個意義，代表著什么含義：
- uft8mb4 表示用 UTF-8 編碼方案，每個字符最多占4個字節(jié)。
- utf8mb3也是Unicode字符集的UTF-8編碼，每個字符使用一到三個字節(jié)。(utf8: utf8mb3的別名)
- 0900 就是Unicode 校對算法版本。(Unicode歸類算法是用于比較符合Unicode標準要求的兩個Unicode字符串的方法)。
- ai指的是口音不敏感。也就是說，排序時e，è，é，ê和ë之間沒有區(qū)別，不區(qū)分重音。
- ci表示不區(qū)分大小寫。排序時p和P之間沒有區(qū)別。
再了解一下一些場景下 utf8 和 utf8mb4 的問題點：
utf8編碼最多支持3字節(jié)的數(shù)據(jù)，而emoji表情符， 偏生字是4個字節(jié)的utf8無法存儲的，致辭延伸出utf8mb4字符集解決這個問題。
日常常用的字符集：
- utf8mb4_bin：將字符串每個字符用二進制數(shù)據(jù)編譯存儲，區(qū)分大小寫，而且可以存二進制的內
- utf8mb4_general_ci：ci即case insensitive，不區(qū)分大小寫。沒有實現(xiàn)Unicode排序規(guī)則，在遇到某些特殊語言或字符集，排序結果可能不一致。但在絕大多數(shù)情況下，這些特殊字符的順序并不需要那么精確。
utf8mb4_unicode_ci：是基于標準的Unicode來排序和比較，能夠在各種語言之間精確排序，Unicode排序規(guī)則為了能夠處理特殊字符的情況，實現(xiàn)了略微復雜的排序算法。
2. 字符集配置
mysql字符集如何設置，更改操作：
1)my.cnf配置文件信息，建議初始化時就設置好。
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
character-set-client-handshake = FALSE #此處是忽略客戶端的字符集，使用服務器的設置
2)init_connect=‘SET NAMES utf8mb4’ #服務器為每個連接的客戶端執(zhí)行的字符串,對于一些超級管理源就不生效的
3)字符集變更，包含庫，表，column的變更。都可以完全的擁有自己的字符集。
- ##更改DATABASE
- ALTER DATABASE `db1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
- ##更改TABLE
- ALTER TABLE `t1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
- ##更改column字段
- ALTER TABLE `t1` modify `name` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '昵稱';
4)日常字符集檢查工作：
- SELECT b.SCHEMA_NAME, b.DEFAULT_CHARACTER_SET_NAME, b.DEFAULT_COLLATION_NAME ,a.TABLE_NAME,
- a.TABLE_COLLATION
- FROM information_schema.SCHEMATA b left join information_schema.TABLES a
- on b.SCHEMA_NAME =a. TABLE_SCHEMA
- WHERE b.SCHEMA_NAME not in ('information_schema' ,'mysql','performance_schema', 'sys')
- ORDER BY TABLE_SCHEMA,TABLE_NAME ;
通過多方便設置，更改操作，檢查。字符集不再是隱藏問題。
3. 字符集對于數(shù)據(jù)庫的影響
字符集對整個數(shù)據(jù)庫影響面還是比較可觀的。庫更改 對于原先存在的表字段 都不影響 依次類推。所以數(shù)據(jù)庫>表>字段 都可以單獨設置字符集。
常見問題1：有索引 沒有走 因為進行了 字符集隱式轉換
常見問題2：在尾隨空格方面不同
字符串值(CHAR、VARCHAR和TEXT)的比較與其他排序規(guī)則在尾隨空格方面不同。For example, ‘a’ and 'a ’ 作為不同的字符串比較，而不是相同的字符串。
對于字符集排序來說，字符串末尾的空格也有對應的處理。
- mysql> SELECT COLLATION_NAME, PAD_ATTRIBUTE FROM INFORMATION_SCHEMA.COLLATIONS
- WHERE CHARACTER_SET_NAME = 'utf8mb4';
- +----------------------------+---------------+
- | COLLATION_NAME | PAD_ATTRIBUTE |
- +----------------------------+---------------+
- | utf8mb4_general_ci | PAD SPACE |
- | utf8mb4_bin | PAD SPACE |
- | utf8mb4_unicode_ci | PAD SPACE |
- | utf8mb4_icelandic_ci | PAD SPACE |
- | utf8mb4_latvian_ci | PAD SPACE |
- | .........
- | utf8mb4_0900_ai_ci | NO PAD |
- | utf8mb4_de_pb_0900_ai_ci | NO PAD |
- | utf8mb4_0900_bin | NO PAD |
- +----------------------------+---------------+
注意：在選擇使用utf8mb4_0900 字符集之后空格 就需要處理。
常見問題3：對于數(shù)據(jù)的大小寫敏感
除了lower_case_table_names之外，怎樣有效使用大小寫字符集設置，采用ut8mb4_bin字符集 既可，查詢和數(shù)據(jù)插入解決。
常見問題4：表情符，偏生字
常見錯誤代碼:1366 Incorrect string value: ‘\xF0\x9F\x99\x82’ for column ‘name’ at row 2
指定Utf8mb4字符集，再配合character-set-client-handshake屬性
備注：對于jdbc來說沒有utf8mb4這樣的字符集說法。
4. 總結
從初期安裝開始，就需要配置好字符集，8.0版本的utf8mb4_0900目前為止也沒有類似的bug之類的，但還需要謹慎使用。選擇utf8mb4_unicode_ci即可。
一件件小事，積累起來就是變成大事。
