27. 使用MySQL之全球化和本地化

hisun9發表於2024-11-15
MySql

1. 字符集和校對順序

資料庫表被用來儲存和檢索資料。不同的語言和字符集需要以不同的方式儲存和檢索。因此，MySQL需要適應不同的字符集（不同的字母和字元），適應不同的排序和檢索資料的方法。

在討論多種語言和字符集時，將會遇到以下重要術語：

  • 字符集為字母和符號的集合；

  • 編碼為某個字符集成員的內部表示；

  • 校對為規定字元如何比較的指令。

補充：

  • 校對為什麼重要：

    排序英文正文很容易，對嗎？或許不。考慮詞APE、apex和Apple。它們處於正確的排序順序嗎？這有賴於你是否想區分大小寫。使用區分大小寫的校對順序，這些詞有一種排序方式，使用不區分大小寫的校對順序有另外一種排序方式。這不僅影響排序（如用ORDER BY排序資料），還影響搜尋（例如，尋找apple的WHERE子句是否能找到APPLE）。在使用諸如法文à或德文ö這樣的字元時，情況更復雜，在使用不基於拉丁文的字符集（日文、希伯來文、俄文等）時，情況更為複雜。

在MySQL的正常資料庫活動（SELECT、INSERT等）中，不需要操心太多的東西。使用何種字符集和校對的決定在伺服器、資料庫和表級進行。

2. 使用字符集和校對順序

MySQL支援眾多的字符集。

  • 為檢視所支援的字符集完整列表，使用以下語句：

    show character set;

    輸出如下（未截全）：

    img

    這條語句顯示所有可用的字符集以及每個字符集的描述和預設校對。

  • 為了檢視所支援校對的完整列表，使用以下語句：

    show collation;

    輸出如下（未截全）：

    img

    此語句顯示所有可用的校對，以及它們適用的字符集。可以看到有的字符集具有不止一種校對。例如，latin1對不同的歐洲語言有幾種校對，而且許多校對出現兩次，一次區分大小寫（由_cs表示），一次不區分大小寫（由_ci表示）。

  • 通常系統管理在安裝時定義一個預設的字符集和校對。此外，也可以在建立資料庫時，指定預設的字符集和校對。為了確定所用的字符集和校對，可以使用以下語句：

    show variables like 'character%';
show variables like 'collation%';

    輸出如下：

    img

    img

  • 實際上，字符集很少是伺服器範圍（甚至資料庫範圍）的設定。不同的表，甚至不同的列都可能需要不同的字符集，而且兩者都可以在建立表時指定。

    為了給表指定字符集和校對，可使用帶子句的CREATE TABLE：

    create table mytable
(
    column1 int,
    column2 varchar(10)
)default character set hebrew
collate hebrew_general_ci;

    此語句建立一個包含兩列的表，並且指定一個字符集和一個校對順序。

    這個例子中指定了CHARACTER SET和COLLATE兩者。一般，MySQL按如下確定使用什麼樣的字符集和校對。

    • 如果指定CHARACTER SET和COLLATE兩者，則使用這些值。

    • 如果只指定CHARACTER SET，則使用此字符集及其預設的校對（如SHOW CHARACTER SET的結果中所示）。

    • 如果既不指定CHARACTER SET，也不指定COLLATE，則使用資料庫預設。

  • 除了能指定字符集和校對的表範圍外，MySQL還允許對每個列設定它們，如下所示：

    create table mytable1
(
    column1 int,
    colume2 varchar(10),
    column3 varchar(10) character set latin1 collate latin1_general_ci
)default character set hebrew
collate hebrew_general_ci;

    這裡對整個表以及一個特定的列指定了CHARACTER SET和COLLATE。

  • 如前所述，校對在對用ORDER BY子句檢索出來的資料排序時起重要的作用。如果你需要用與建立表時不同的校對順序排序特定的SELECT語句，可以在SELECT語句自身中進行：

    select * from customers
order by cust_name collate latin1_general_cs;

    此SELECT使用COLLATE指定一個備用的校對順序（在這個例子中，為區分大小寫的校對）。這顯然將會影響到結果排序的次序。

    插一句題外話

    試了一下上面的select語句，結果報錯

    img

    迴歸正題

    補充：

    • 臨時區分大小寫：

      上面的SELECT語句演示了在通常不區分大小寫的表上進行區分大小寫搜尋的一種技術。當然，反過來也是可以的

    • SELECT的其他COLLATE子句：

      除了這裡看到的在ORDER BY子句中使用以外，COLLATE還可以用於GROUP BY、HAVING、聚集函式、別名等。

  • 最後，值得注意的是，如果絕對需要，串可以在字符集之間進行轉換。為此，使用Cast()或Convert()函式。

插句題外話

  1. CAST() 函式

    CAST() 是 MySQL 中用於顯式轉換資料型別的函式。它可以將一個表示式（比如一個列值或常量）從一種資料型別轉換為另一種資料型別。

    語法：

    CAST(expression AS target_data_type)

    • expression：表示需要轉換的資料或表示式，通常是列名、常量值等。

    • target_data_type：目標資料型別，是要將 expression 轉換成的型別。常見的目標資料型別包括 SIGNED（帶符號整數）、UNSIGNED（無符號整數）、CHAR、DATE、DATETIME、DECIMAL 等。

    常見的目標資料型別：

    • SIGNED：轉換為有符號整數型別。

    • UNSIGNED：轉換為無符號整數型別。

    • CHAR、VARCHAR：轉換為字元型別（可以指定長度）。

    • BINARY、VARBINARY：轉換為二進位制資料型別。

    • DATE、DATETIME：轉換為日期或日期時間型別。

    • DECIMAL、FLOAT、DOUBLE：轉換為數字型別。

    舉幾個例子：

    1. 將字串轉換為整數：

      SELECT CAST('123' AS UNSIGNED);

      這個查詢會將字串 '123' 轉換為無符號整數 123。

    2. 將浮點數轉換為整數：

      SELECT CAST(123.45 AS SIGNED);

      這個查詢會將浮點數 123.45 轉換為帶符號整數 123。

    3. 將數字轉換為字串：

      SELECT CAST(123 AS CHAR);

      這個查詢會將整數 123 轉換為字串 '123'。

    4. 將日期字串轉換為 DATE 型別：

      SELECT CAST('2024-12-01' AS DATE);

      這個查詢會將字串 '2024-12-01' 轉換為 DATE 型別。

    特點：

    • CAST() 主要用於資料型別之間的轉換，不支援字符集轉換。

    • 語法簡潔，常用於將一個資料型別轉換為另一個型別，尤其是在進行數值計算或資料格式調整時。

    • 對於日期、時間和字元資料型別的轉換，CAST() 會自動進行格式化處理。

  2. CONVERT() 函式

    CONVERT() 是一個功能更強大的函式，不僅支援資料型別轉換，還支援字符集轉換。它可以用於將一個值從一種資料型別轉換為另一種資料型別，或在字符集之間進行轉換。

    語法：

    CONVERT(expression, target_data_type)

    或者

    CONVERT(expression USING target_charset)

    • expression：表示需要轉換的資料或表示式，通常是列名、常量值等。

    • target_data_type：目標資料型別，和 CAST() 類似，可以是 SIGNED、CHAR、DATE 等。

    • target_charset：目標字符集，只在字符集轉換時使用，例如 utf8、latin1 等。

    舉幾個例子：

    1. 將字串轉換為整數：

      SELECT CONVERT('123' SIGNED);

      這個查詢會將字串 '123' 轉換為帶符號整數 123。

    2. 將浮點數轉換為字串：

      SELECT CONVERT(123.45, CHAR);

      這個查詢會將浮點數 123.45 轉換為字串 '123.45'。

    3. 將字串從一個字符集轉換為另一個字符集：

      SELECT CONVERT('你好' USING latin1);

      這個查詢會將字串 '你好' 從 UTF-8 編碼轉換為 latin1 編碼，可能會導致字元丟失或亂碼，因為 latin1 不能表示所有的 Unicode 字元。

    4. 將欄位值轉換為不同的字符集：

      SELECT CONVERT(name USING utf8) FROM users;

      這個查詢將 users 表中的 name 欄位值從當前字符集轉換為 utf8 字符集。

    特點：

    • CONVERT() 函式不僅支援資料型別轉換，還可以進行字符集轉換，尤其適用於需要轉換字串編碼時。

    • 在處理字符集轉換時，CONVERT() 會嘗試將字串中的字元從一個字符集對映到另一個字符集。如果目標字符集無法表示某些字元，可能會丟失或出現亂碼。

    • CONVERT() 在字符集轉換時，可以在 USING 子句中指定目標字符集，支援的字符集包括 utf8、latin1、gbk 等。

    對比：

    • CAST() 只支援資料型別轉換，而不支援字符集轉換。

    • CONVERT() 不僅可以進行資料型別轉換，還可以在字符集之間轉換。

相關文章