MySQL Charset & Collation 初步學習總結-有解無憂

寫在前面： 本文——mysql字符集(character set)和排序規則(collation)的初步總結，源于學習程序中對select length('漢字');的好奇，由于學習階段及時間問題，部分疑問最終沒有很好的解決.暫時不再探究，總結粗糙，理解不精,主要為個人學習程序記錄，方便后期復習，僅供網友參考，歡迎提出見解，

MySQL中字符集(character set )指的是由一對對symbol和encoding的對應關系組成的集合(粗略理解為編碼方式)，排序規則(collation )主要用于指明字符間的比較方式，( MySQL includes character set support that enables you to store data using a variety of character sets and perform comparisons according to a variety of collations. )詳見8.0手冊第10章總述及10.1節，（本文參考手冊皆指官方mysql 8.0手冊）

MySQL 8.0 默認的 character set(字符集) and collation(排序規則) 是 utf8mb4 和 utf8mb4_0900_ai_ci, 具體來講可以分別指定：服務器(server)，資料庫(database)，表(table)，列(column)以及原義字串(character string literal )的character set 和對應的 collation
- 1.1 查看MySQL支持的[所有的]character set 和collation
  - show character set ; show collation ; 兩者都可添加限定條件陳述句：like或where clause
    #character set 可以簡寫為charset;
    手冊10.3.1 節詳細介紹了character set 和 collation 的命名規則
  - 在MySQL中，全部的charset 與collation的資訊都存放在information_schema庫中，除上述方法外，還可進入information_schema庫中查看CHARACTER_SETS與COLLATIONS表
    use information_schema;
    select * from character_sets,collations [where clause];
- 1.2查看系統當前設定的各種字符集/排序規則
  show [session ]variables like 'char%'/'collation%';
  或 select * from performance_schema.session_variables where variable_name like 'character_set_%';
表1
- 1.4 collation 的命名規則參見手冊10.3.1節，

1.5可解如下困惑：
- 1.4.1 select length('張'); mysql > 2
  在此查詢中，漢字'張' 即原義(或譯為常量?)字串(見手冊10.3.6節/下文2)，因在查詢時沒有特別指定character set 以及collation ，故為默認值，由表1：character_set_connection | gbk可知，編碼方式為GBK，而GBK編碼使用兩個位元組來標識漢字字符碼，所以上述運行結果為 2 ，
  - 1.4.2 use pra ;select legnth(stu_name) from stuinfo where stuid = 1; mysql> 6
    
    字串' 張三 ' 占用6個位元組，故單個漢字(字符)占用3個位元組，可以解釋為：
    - ① 因：創建pra表中的各欄位時，未特意指定編碼型別，故根據手冊10.3.5可知編碼方式應為其所屬的table的編碼型別，使用3.2命令查看，為默認的utf8b4
    - ② 手冊10.9.1節詳細介紹了utf8b4字符集型別，指明了：
      - 在編碼 BMP字符時utf8mb4與utf8/utf8mb3 可以大致等同，每個字符編碼存盤都占用相同的位元組數(英文字符1個位元組，漢字3個位元組)；走出了各種論壇中的不精確表達" utf8mb4存盤漢字占用4個位元組，utf8mb3占用3個位元組 " 的思維定勢，
      - 在編碼SMP字符時，utf8mb4才占用4個位元組(當然，utf8/utf8mb3 不支持存盤SMP字符，這中字符集型別很快會被官方棄用)
      - 兩種型別同時存在時，一般會自動轉化為utf4mb4型別，
      - BMP字符可以粗略理解為常用字符，SMP理解為不常用字符，比如emoj符號，
    - ③ 詳細的各種型別的字符編碼，可參見博客園：字串，那些事

Character string literal 譯為原義字串，指的是在Query clause 中的字串，脫離于表，與表無關，手冊10.3.6節，
- 2.1 形式為 [_charset_name]'string' [COLLATE collation_name]；
- 2.2 解釋：The _charset_name expression is formally called an introducer. It tells the parser, “the string that follows uses character set charset_name.” ，
- 2.3
- 2.4 困惑在系統CMD視窗
  ① select length('你') ; mysql>2
  ②select length(_utf8mb4 '你') ; mysql>2
  運行結果不變，通過命令1.3(表1)可看到character_set_connection = gbk ，①可理解，那么②呢？
  字串'你'之前的 introducer 無效嗎？2.2解釋It tells the parser, “the string that follows 　　uses character set charset_name.到底什么意思，中間還涉及什么程序，
  - 2.4.1 換用了MySQL Client CMD 運行，連接字符集同樣為gbk，運行結果也都為 2 ，換用navicat命令列執行，連接字符集變為utf8mb4 (client ,results字符集也都變為utf8mb4)，兩條select陳述句執行結果都變為 3 (第二條的introducer 修改為 _gbk)，
    結論：introducer 對于字串本身沒有影響,還是受character_set_connection或其他變數影響 (說法不準確)
  - 2.4.2 查閱手冊10.3.8 introducer相關知識：
```
An introducer does not change the string to the introducer character set like CONVERT() would do. It does not change the string value, although padding may occur. The introducer is just a signal. (不太理解)
```
  ? ① 查閱12.11節 Cast Functions and operators 的 convert(expr using transcoding_name)函式：converts data between different character sets. ，貌似是真正的轉換，
  
  ? ② 這不同于introducer中的表述：It does not change the string value, although padding may occur. The introducer is just a signal(它到底是干嘛的)
  
  ? ③ 運行 select length(convert('你' using utf8mb4))； mysql> 3 ,而此時character_set_connection 仍然為gbk
  那么結合①，到底introducer 到底發揮什么樣的作用，character_set_connection 發揮什么樣的作用， ?
  - 2.4.3 查看手冊10.4節 Connection character set and collation ,該部分涉及到了客戶端與服務器的互動程序中的編碼轉換程序，
    - 1. 客戶端與服務器的互動大致涉及三個變數：character_set_client , character_set_conneciton , character_set_results.
    - 1. 整體程序可粗略解釋如下，更詳細可參考七把刀簡書博文，
      ① 服務器從客戶端接收以character_set_client 編碼的陳述句(statements)；
      ② 服務器將接收到的statements 從character_set_client 轉譯(translate/convert)為character_set _connection.
      此處提到：For string literals that have an introducer such as _utf8mb4 or _latin2, the introducer determines the character set. (怎么determine呢，上面沒感覺determine呀)
      
      ? 又提及：collation_connection 對于literal strings 的比較是重要的,對于表列中的字串的比較無關緊要，
      ③ server 將執行結果以character_set_results 的編碼形式傳回client
      
      ④：在七把刀簡書博文中的介紹部分不能理解：指定introducer 后的解釋，
    - 1. 無法類比當前所糾結的查詢的實際程序，直接的一個函式到達服務器后是如何執行的，程序中的編碼是如何轉換的，考慮查看原始碼？
  - 2.4.4 總：與當下學習任務關聯度不大，在該問題上耗時過長，不再花費時間糾結，粗略結論：①introducer 在整個程序中沒有起到多大作用；
    ② convert函式可以實實在在的看到效果；
    ③單獨或一同修改(client,connection,results)并結合三者(無introducer ，有introducer, convert轉換)試驗后，效果迷離，心累，不再探究；
    ④問題關鍵還是沒有理解introducer, 各字符集，以及客戶端與服務器互動時的編碼轉換程序，日后涉及，在經驗積累的基礎上再行探究，
分類
- 3.1 欄位級別
  - 查看某一 table 所有欄位的詳細資訊(含排序規則collation一列(根據10.3.1中的collation命名規則易知對應的character set))
    show full columns from table_name;
  - 查看當前選中的資料庫中所有表的資訊(含table_name , Engine, version , create_time , update_time, collation 等)
    show table status [ from databse_name / where name like '%name%'];
  - 修改欄位的charset 和 collation
    alter table table_name modify filed_name field_type field_charset_name;
- 3.2 表級別
  - 查看建表陳述句(最新的，含修改過的 )( 其中包含當前表設定的默認的character set ，collation資訊)
    show create table table_name;
  - 修改表的charset和collation
    alter table table_name charset charset_name;
- 3.3 資料庫級別
  - 查看當前資料庫默認的字符集，以及排序規則
    - show variables where variable_name = 'character_set_database'
    - use database_name;
      select @@character_set_database,@@collation_database;
    - select default_character_set_name,default_collation_name
      from information_shema.schemata
      where shcema_name = 'db_name';
      (可能這種方法涉及當前用戶的權限問題，未查證)
  - 查看建資料庫陳述句，從而了解當前資料庫默認的字符集，以及排序規則，
    show create database database_name
  - 修改資料庫的character set 和collation
    alter database database_name charset utf8mb4
- 3.4 服務器級別
  - 查看服務器字符集配置
    show variables where variable_name = 'character_set_server ;'
    也可以簡單使用 show variables like 'char%';
  - 配置 server 的默認charset 和collation (詳細參見10.3.2節)
    - 永久性配置：修改my.ini檔案中的mysqld --character-set-server=utf8mb4 ,重啟MySQL服務，(my.ini檔案一般位于C盤 Program Files或者Programdata Files檔案夾下的mysql目錄下)
    - 暫時配置：命令列輸入：set character_set_server= utf8mb4;
    - 手冊中還介紹了cmke 命令，
- 3.5 查看**connection , client , results ** 字符集，排序規則
  例如： select @@character_set_connection,@@collation_connetcion;
  或者： show variables like 'char%' ;
4.其他
- 函式 length()，char_length()，character_length() 區別參見手冊12 章 Functions and operators ，
- 手冊13.7節Database Adminstration Statement 的13.7.6.3 介紹了Set Names Statements.
  - set names('charset_name' [collate 'collation_name'] | default);
  - 該陳述句將三個session 系統變數 character_set_client , character_set_connection , character_set_results 同時設定為了指定的字符集 charset_name ，collate 陳述句可選，執行后可使用1.3命令查看效果，(該設定僅當次會話中有效)
  - 可以使用default值恢復默認映射，默認值取決于服務器配置
    The default mapping can be restored by using a value of DEFAULT. The default depends on the server configuration
- 細節及注意點，查看手冊，博客set name statements總結詳細，可參考，

總結粗糙，理解不精,日后更新完善，歡迎提出見解，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/251620.html

標籤：MySQL

上一篇：回顧MySQL基礎

下一篇：SQL陳述句中 ` 的作用