Character Sets, Collations, Unicode (MySQL 官方文件原子化翻譯)

# 前言

本篇主要為細讀官方文件後, 整理為 Q&A 的原子化翻譯筆記

# Character Sets and Collations in General

MySQL 中, 我們說 character set 是 symbols 以及 encodings 的組合, 那在以下的 example 中, 誰是 symbols, 誰是 encodings?

• Example:

  A = 0, B = 1, a = 2, b = 3

• Answer:
  A, B, a, b 是 symbols, 0, 1, 2, 3 是 encodings

MySQL 中, 若說 character set 是一組 symbols 以及 collations, 那 collations 是什麼？

比較 character set 的規則

在以下的 MySQL character set example 中, 若要比較 symbols 的大小, collation 的規則該是?

• Example:

  A = 0, B = 1, a = 2, b = 3

• Answer:

  b > a > B > A, 透過比較 encoding 的大小

# Character Sets and Collations in MySQL

MySQL 中, 預設的 character set 以及 collation 是？

MySQL 預設使用 character sets utf8mb4 以及 collations utf8mb4_0900_ai_ci

以下的 MySQL example command 的意思是？

• Example:

  SHOW CHARACTER SET LIKE 'utf%';

• Answer:
  列出支援的 character sets, 若不加 LIKE 則列出所有

以下的 MySQL example command 的意思是？

• Example:

  SHOW COLLATION

• Answer:
  列出所有支援的 collations

# Character Set Repertoire

character set 的 repertoire (字元表) 是這個 character set 中 character 的集合
一般來說, string 的 repertoire 可分為兩種:

• ASCII: U+0000 to U+007F
• UNICODE: U+0000 to u+10FFFF, 含所有 character

ASCII 範圍是 UNICODE 範圍的子集, 所以 string 有著 ASCII repertoire 可以安全地被 UNICODE repertoire 涵蓋, 也可安全的轉換成任何 ASCII 的 superset (除了 swe7, 任何 MySQL 的 character set 都是 ASCII 的 superset)

正是因為 repertoire 的使用, string 才得以在多種不同的 character set 之間轉換

repertoire 會隨著 string 的內容變換, 且可能會跟 character set 的 repertoire 有所不同

SET NAMES utf8mb4; SELECT 'abc';
SELECT _utf8mb4'def';

從上面的 example 可以看到, 雖說 character set 設定 utfmb4, 照理來說 repertoire 該是 UNICODE, 但因為 ‘abc’, 以及 ‘def’ 並未超出 ASCII 的範圍, 因此其 repertoire 還是定在 ASCII

再來看看下面的 example, column c1 以及 c2 雖然有不同的 character set, 但因為 value ‘a’, ‘b’ 的 repertoire 都沒有超出 ASCII 的範圍, 所以 CONCAT 不會有錯誤

CREATE TABLE t1 (
  c1 CHAR(1) CHARACTER SET latin1,
  c2 CHAR(1) CHARACTER SET ascii
);
INSERT INTO t1 VALUES ('a','b');
SELECT CONCAT(c1,c2) FROM t1;

而當一個 function 有一個 string argument, 則 repertoire 繼承自該 string
比方說, UPPER(_utf8mb4’abc’) 的 repertoire 為 ASCII, 因為 ‘abc’ 有著 ASCII repertoire (儘管有著 “_utf8mb4”, 但 ‘abc’ 的 character 範圍並沒有超出 ASCII)

那如果是返回 string 結果的 function, 但卻沒有 argument 輸入呢？ 那 function 會使用 character_set_connection 的設定

如果 function 有多個 args, 那會使用多個 args 中範圍最廣的 repertoire, 如下 example:

CONCAT(_ucs2 X'0041', _ucs2 X'0042')
CONCAT(_ucs2 X'0041', _ucs2 X'00C2')

上面的 example 中可以看到, 第一個 CONCAT function 的結果為 ASCII, 因為兩個 args 的 character 都在 ASCII 的範圍內, 而第二個 function 則為 UNICODE, 因為第二個 args 超出了 ASCII 範圍

function 的 repertoire 只會被會對結果造成影養的 args 所影響, 別誤會, 我沒有在唱饒舌歌, 看看下面的 example

IF(column1 < column2, 'smaller', 'greater')

上面的 example 中可以看到, 雖然有三個 args, 但實際上會對結果造成影響的是 2 跟 3, arg1 雖然也是 string 表達, 但因為對結果輸出的 repertoire 沒有影響, 因為不會被列入 repertoire 的決定因素內

UTF8 For Metadata

MySQL 中, 什麼是 metadata?

任何形容資料庫的都稱為 metadata, 像是, column name, database name, user names, version names, SHOW 的輸出, 還有 INFORMATION_SCHEMA table 的內容

MySQL 中, metadata 以哪種 character set 儲存?

UTF-8

以下的 MySQL example command 的意思是？

• Example:

  SHOW VARIABLES LIKE 'character_set_system';

• Answer:
  顯示 character_system_set system variable, 為 metadata 的 character set

以下的 MySQL example 中, column1 的 column name 的 character set 取決於？

• Example:

  SELECT column1 FROM t

• Answer:
  character_set_results, 預設為 utf8mb4

MySQL 中, 預設回傳的 column name character set 為 utf8mb4, 如果我想變更, 可以使用哪一個 command?

SET NAMES

MySQL 中, 預設回傳的 column name character set 為 utf8mb4, 如果想要變更, 最佳的解決方案是在 server 端轉換還是在 client 端轉換？

client

MySQL 中, 如果 character_set_results 設為 NULL, 那 return metadata 的 character set 會參照？

character_set_system

以下的 MySQL example 中, character set 是如何轉換的？

• Example:

  SELECT * FROM t1 WHERE USER() = latin1_column;

• Answer:
  USER() return 的值為 metadata, 預設為 utf8mb4, 所以 MySQL 會自動將 latin1_column 轉成 utf8mb4

以下的 MySQL example 中, character set 是如何轉換的？

• Example:

  INSERT INTO t1 (latin1_column) SELECT USER();

• Answer:
  USER() return 的值為 metadata, 預設為 utf8mb4, 但因為要存入的 column ‘latin1_column’ 的 character set 為 latin, 所以 USER() 會自動被轉換為 ‘latin1’

# Specifying Character Sets and Collations

MySQL 中, character sets 以及 collations 有哪四個等級

server, database, table, column

# Collation Naming Conventions

從以下的 example 中的 collations 命名方式, 可知道其分別與什麼 character set 相關？

• Example:
  latin1_swedish_ci
  utf8mb4_general_ci
• Answer:
  latin1
  utf8mb4

MySQL character set 中的 binary, 共對應幾種 collation?

1 種, 就是 binary collation, 且無後綴

從以下的 example 中的 collations 命名, 可知道其差異是？

• Example:
  utf8mb4_hungarian_ci
  utf8mb4_turkish_ci
• Answer:
  使用 hugarian 規則來排序 utf8mb4 character set
  使用 turkish 規則來排序 utf8mb4 character set

以下的 collation 後面的 _ci 代表的意思是？

• Example:
  utf8mb4_turkish_ci
• Answer:
  代表 sort 規則是 case insensitive

以下的 collation 後面的 _ai 代表的意思是？

• Example:
  utf8mb4_turkish_ai
• Answer:
  代表 sort 規則是 accent insensitive

以下的 collation 後面的 _as 代表的意思是？

• Example:
  utf8mb4_turkish_as
• Answer:
  代表 sort 規則是 accent sensitive

以下的 collation 後面的 _cs 代表的意思是？

• Example:
  utf8mb4_turkish_cs
• Answer:
  代表 sort 規則是 case sensitive

以下的 collation 後面的 _ks 代表的意思是？

• Example:
  utf8mb4_japanese_ks
• Answer:
  代表 sort 規則是 kana sensitive (只用在日語), 代表會將 katakana 跟 hiragana 做區隔, 若無 ks 則會一視同仁

以下的 collation 後面的 _bin 代表的意思是？

• Example:
  utf8mb4_japanese_bin
• Answer:
  代表 sort 規則是 binary, 依 binary code 來排列

collation 以 _ci 結尾的, 是 accent sensitive 還是 insensitive?

accent insensitive

collation 以 _cs 結尾的, 是 accent sensitive 還是 insensitive?

accent sensitive

collation 中, UCA 的全寫是？

Unicode Collation Algorithm

collation utf8mb4_0900_ai_ci 中的 0900 的意思是？

UCA 版本號

collation utf8mb4_unicode_520_ci 中的 520 的意思是？

UCA 版本號

collation utf8mb4_unicode_ci 中, 並無標示版本號, 所以會使用哪一個版本？

UCA 4.0.0

# Server Character Set and Collation

以下的 MySQL example code 的意思是?

• Example:

  mysqld --character-set-server=utf8mb4 \
    --collation-server=utf8mb4_0900_ai_ci

• Answer:
  啟動 mysql daemon, 並指定 server character set 以及 collation

以下的 cmake example code 的意思是?

• Example:

  cmake . -DDEFAULT_CHARSET=latin1 \
    -DDEFAULT_COLLATION=latin1_german1_ci

• Answer:
  使用 cmake 來指定 MySQL server 的 character set 以及 collation

MySQL 中, 當我建立資料庫但並未指定 character set 或 collation 時, 預設會使用？

會使用 character_set_server 以及 collation_server system variable

# Database Character Set and Collation

以下的 MySQL example code 的意思是?

• Example:

  CREATE DATABASE db_name
      [[DEFAULT] CHARACTER SET charset_name]
      [[DEFAULT] COLLATE collation_name]
  
  ALTER DATABASE db_name
      [[DEFAULT] CHARACTER SET charset_name]
      [[DEFAULT] COLLATE collation_name]

• Answer:
  建立資料庫時指定 character set 以及 collation
  更改資料庫的 character set 以及 collation
  default 值為 character_set_server 以及 collation_server

MySQL 中, 如果 character_set_database 有特別指定, 但 collation 沒指定, 那該 database 會使用哪個 collation

與該 character set 相關的預設 collation, 可使用 SHOW COLLATION 取得詳細資料

MySQL 中, 如果 collation_database 有特別指定, 但 character_set_database 沒指定, 那該 database 會使用哪個 character set

會使用與該 collation 相關的 character set

以下的 MySQL example code 的意思是?

• Example:

  USE db_name;
  SELECT @@character_set_database, @@collation_database;

• Answer:
  取得 database db_name 的 character_set 以及 collation

以下的 MySQL example code 的意思是?

• Example:

  SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
  FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

• Answer:
  取得 database db_name 的 character_set 以及 collation

MySQL 中, 如果 table character_set 或 collation 未指定, 其預設值為？

character_set_database, collation_database

MySQL 中, 當使用 LOAD DATA Statement, 若無特別指定, 預設使用哪個 character set?

character_set_database

# Table Character Set and Collation

以下的 MySQL example code 的意思是?

• Example:

  CREATE TABLE tbl_name (column_list)
      [[DEFAULT] CHARACTER SET charset_name]
      [COLLATE collation_name]]
  
  ALTER TABLE tbl_name
      [[DEFAULT] CHARACTER SET charset_name]
      [COLLATE collation_name]

• Answer:
  設定或修改 table 的 character set 及 collation

MySQL 中, 如果 character_set_table 有特別指定, 但 collation 沒指定, 那該 table 會使用哪個 collation

與該 character set 相關的預設 collation
若要取得預設 collation 的詳細資料, 可使用 SHOW COLLATION

MySQL 中, 如果 collation_table 有特別指定, 但 character_set_table 沒指定, 那該 database 會使用哪個 character set

會使用與該 collation 相關的 character set

MySQL 中, 如果 character_set_table 與 collation_table 都沒有特別指定, 那預設會使用？

character_set_database 與 collation_database

# Column Character Set and Collation

以下的 MySQL example code 的意思是?

• Example:

  CREATE TABLE t1
  (
      col1 VARCHAR(5)
        CHARACTER SET latin1
        COLLATE latin1_german1_ci
  );
  
  ALTER TABLE t1 MODIFY
      col1 VARCHAR(5)
        CHARACTER SET latin1
        COLLATE latin1_swedish_ci;

• Answer:

  CREATE TABLE t1
  (
      # 定義 col1 的 character set 以及 collation
      col1 VARCHAR(5)
        CHARACTER SET latin1
        COLLATE latin1_german1_ci
  );
  
  ALTER TABLE t1 MODIFY
      # 修改 col1 的 character set 以及 collation
      col1 VARCHAR(5)
        CHARACTER SET latin1
        COLLATE latin1_swedish_ci;

MySQL 中, 如果 character_set_table 有特別指定, 但 collation 沒指定, 如下 example, 那該 database 會使用哪個 collation?

• Example:

  CREATE TABLE t1
  (
      col1 CHAR(10) CHARACTER SET utf8
  ) CHARACTER SET latin1 COLLATE latin1_bin;

• Answer:
  與該 character set 相關的預設 collation
  若要取得預設 collation 的詳細資料, 可使用 SHOW COLLATION

MySQL 中, 如果 collation_table 有特別指定, 但 character_set_table 沒指定, 如下 example, 那該 column 會使用哪個 character set?

• Example:

  CREATE TABLE t1
  (
      col1 CHAR(10) COLLATE utf8_polish_ci
  ) CHARACTER SET latin1 COLLATE latin1_bin;

• Answer:
  會使用與該 collation 相關的 character set

MySQL 中, 如果 character_set_column 與 collation_column 都沒有特別指定, 如下 example, 那預設會使用？

• Example:

  CREATE TABLE t1
  (
      col1 CHAR(10)
  ) CHARACTER SET latin1 COLLATE latin1_bin;

• Answer:
  character_set_table 與 collation_table

MySQL 中, 如果修改一個 column 的 character set, 但現存內容與指定的 character set 並不相容, 那可能會造成什麼後果？

資料丟失

# Character String Literal Character Set and Collation

以下的 MySQL example code 分別使用哪個 character set 及 collation?

• Example:

  SELECT 'abc';
  SELECT _latin1'abc';
  SELECT _binary'abc';
  SELECT _utf8mb4'abc' COLLATE utf8mb4_danish_ci;

• Answer:

  # 使用 character_set_connection system variable
  SELECT 'abc';
  # 使用 latin1 character set, 以及其 default 的 collation
  SELECT _latin1'abc';
  # 使用 binary character set, 以及其 default 的 collation
  SELECT _binary'abc';
  # 使用 utf8mb4 character set, 以及 utf8mb4_danish_ci collation
  SELECT _utf8mb4'abc' COLLATE utf8mb4_danish_ci;
  # 使用 character_set_connection system variable, 如果與指定的 collation 不相容, 則報錯
  SELECT 'Müller' COLLATE utf8mb4_general_ci;

以下的 MySQL example code 中, escaping 都會由哪個 character set 解析？

• Example:

  SELECT HEX('à\n'), HEX(_sjis'à\n');

• Answer:
  會由 character_set_connection 解析

# The National Character Set

以下的 MySQL example description 的意思相同嗎?

• Example:

  VARCHAR(10) CHARACTER SET utf8
  NATIONAL VARCHAR(10)
  NVARCHAR(10)
  NCHAR VARCHAR(10)
  NATIONAL CHARACTER VARYING(10)
  NATIONAL CHAR VARYING(10)

• Answer:
  相同, MySQL 的預設 national character set 為 utf8

以下的 MySQL example query 所使用的 character set 相同嗎?

• Example:

  SELECT N'some text';
  SELECT n'some text';
  SELECT _utf8'some text';

• Answer:
  相同, MySQL 的預設 national character set 為 utf8

以下的 MySQL example query 所使用的 character set 相同嗎?

• Example:

  CREATE TABLE t1 (f1 CHAR(N) UNICODE);
  CREATE TABLE t1 (f1 CHAR(N) CHARACTER SET ucs2);

• Answer:
  相同

# Connection Character Sets and Collations

# Connection Character Set and Collation System Variables

MySQL statement 的 character set 是?

character_set_client

在 MySQL server 收到來自 client 的 statement 後, 會將 character_set_client 轉換成哪一種 variable?

MySQL server 會將 statement 從 character_set_client 轉換成 character_set_connection

在 MySQL server 預設會將 client 的 statement 轉換成 character_set_connection, 那如果是 literal string with introducer, 會以哪個為準

會以 introducer 為先

MySQL 中, collation_connection 會被使用來比較 literal string 嗎？

會的

MySQL 中, collation_connection 會被使用來比較 column value string 嗎？

不會, 會使用 collation_column

MySQL 中, MySQL server 會將 query 的結果轉換成哪種 character set 並回傳給 client?

character_set_result

以下的 MySQL example code 的意思是?

• Example:

  SET character_set_results = NULL;
  SET character_set_results = binary;

• Answer:
  MySQL Server 預設會將 query result 轉換成 character_set_result, 若不希望這個轉換行為, 可將 character_set_result 設為 null

以下的 MySQL example code 的意思是?

• Example:

  SELECT * FROM performance_schema.session_variables
  WHERE VARIABLE_NAME IN (
  'character_set_client', 'character_set_connection',
  'character_set_results', 'collation_connection'
  ) ORDER BY VARIABLE_NAME;

• Answer:
  取得跟 Server 與 Client 之間 session 有關的 character set 以及 collation system variable

以下的 MySQL example code 的意思是?

• Example:

  SHOW SESSION VARIABLES LIKE 'character\_set\_%';
  SHOW SESSION VARIABLES LIKE 'collation\_%';

• Answer:
  列出所有與 session 有關的 variable

# Impermissible Client Character Sets

MySQL 中, 下面幾種 character set 可以被設為 character_set_client 嗎？

• Example:

  ucs2
  utf16
  utf16le
  utf32

• Answer:
  不行

MySQL 中, 當我嘗試使用以下 statement 來設定 ucs2, utf16, utf16le, utf32 為 character set 都會 error, 原因是？

• Example:

  mysql --default-character-set=character_name
  SET NAMES 'charset_name'
  SET CHARACTER SET 'charset_name'

• Answer:
  因為這些 statement 或 flag 都會嘗試將上述的 character_set 設定給 character_set_client

# Client Program Connection Character Set Configuration

MySQL 中, 預設 server 是依據什麼資料來設定 character_set_client, character_set_results, 以及 character_set_connection

使用者的 default collation

MySQL 中, 如果 server 不支援使用者要求的 character_set 或 collation, server 會使用哪一種 character set?

server character set and collation

MySQL 中, compiled-in 的 default character set 是哪一種？

utf8mb4

MySQL 中, 如果不考慮其他資訊來源, MySQL client 會使用哪一種 character set?

compiled-in character set, 即 utf8mb4

MySQL 中, 如果有開啟 auto-detect character set, client 會使用哪裡的資訊來設定 default character set?

OS locale

MySQL 中, 如果有開啟 auto-detect character set, client 會使用哪裡的資訊來設定 default character set?

OS locale

MySQL 中, 如果 OS character set 跟 MySQL character set 的沒有完全一樣, 那會使用哪一種 OS character set?

會 map 到最相近的 MySQL character set

MySQL 中, 如果在 OS character set 自動 Map 到最相近的 MySQL character set 之後, MySQL Client 並不支援該 character set, 那會使用哪一種 character set?

compiled-in character set

MySQL 中, 有哪兩種方法可以定義 default character set?

• 在 my.cnf 檔案設定
• 每次連線時, 都使用 SET_NAMES ‘charset_name’

# SQL Statements for Connection Character Set Configuration

以下的 MySQL example code 又等同哪一句 SQL Statement?

• Example:

  SET character_set_client = charset_name;
  SET character_set_results = charset_name;
  SET character_set_connection = charset_name;

• Answer:

  SET NAMES 'charset_name'

以下的 MySQL example code 的意思是?

• Example:

  SET NAMES 'charset_name' COLLATE 'collation_name'

• Answer:
  MySQL 預設會依照 character_set_connection 去設定 default 的 collation_connection, 但如果要特別指定的話, 如上 example

以下的 MySQL example code 又等同哪一句 SQL Statement?

• Example:

  SET character_set_client = charset_name;
  SET character_set_results = charset_name;
  SET collation_connection = @@collation_database;

• Answer:

  SET CHARACTER SET 'charset_name'

以下的 MySQL my.cnf example code 的意思是?

• Example:

  [mysql]
  default-character-set=koi8r

• Answer:
  指定每次連線都去修改三個與 connection 相關的 system variable, 同 SET NAMES ‘charset_name’

# Connection Character Set Error Handling

# Connect-Time Error Handling