更新資料庫的字元集和定序

本頁說明如何更新 Cloud SQL 資料庫的字元集和排序規則。

總覽

在 MySQL 適用的 Cloud SQL 中建立資料庫時，您可以指定資料庫的字元集和對照順序設定。如果您未指定這些設定的自訂值，資料庫就會使用預設值。在 MySQL 中，您可以在不同物件層級 (包括資料庫、資料表和資料欄) 指定字元集和定序的自訂值。如要進一步瞭解如何在 MySQL 中使用字元集和定序設定，請參閱「字元集、定序、Unicode」。

如果您已建立資料庫和資料庫中的某些物件，但想變更資料庫的字元集或對照順序，可以按照本文的程序進行。

這項程序包含下列步驟：

  1. 檢查目前的字元集和定序值。
  2. 決定要執行的更新層級。
  3. 備份執行個體。
  4. 為儲存的資料庫物件產生重建指令。
  5. 更新資料庫，並視需要更新資料表和資料欄。
  6. 驗證更新並找出錯誤。
  7. 重新建立儲存的資料庫物件。
  8. 再次備份執行個體。

檢查目前的字元集和定序值

檢查資料庫和資料庫物件的字元集和定序設定現有值。檢查資料庫時，您也需要檢查資料表、資料欄和儲存物件的設定。您可以使用下列陳述式檢查資料庫及其資料庫物件。

檢查執行個體設定

如要在執行個體 (伺服器) 層級檢查預設全域值，請啟動 MySQL 殼層並輸入下列指令：

mysql> SELECT @@character_set_server,@@collation_server;

輸出內容範例：

+------------------------+--------------------+
| @@character_set_server | @@collation_server |
+------------------------+--------------------+
| utf8mb4                | utf8mb4_0900_ai_ci |
+------------------------+--------------------+
1 row in set (0.00 sec)

您可以確認預設全域值與稍後為資料庫提供的自訂值不同。

檢查資料庫設定

如要在資料庫層級檢查現有的字元集和對照順序值，請使用下列陳述式。執行每項陳述式後，請列出所有需要使用新字元集或對照順序值更新的資料庫和資料庫物件。清單內容取決於您要變更的資料庫，以及需要更新設定的資料表、資料欄和資料庫物件數量。

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE schema_name
NOT IN ('performance_schema', 'mysql', 'sys', 'information_schema');

舉例來說，您會收到下列輸出內容：

+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| def          | test1       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | test2       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | test3       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | test4       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+

不過，您想將資料庫變更為下列自訂字元集值：

+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| def          | test1       | ascii                      | ascii_general_ci       |     NULL | NO                 |
| def          | test2       | latin1                     | latin1_swedish_ci      |     NULL | NO                 |
| def          | test3       | utf16                      | utf16_general_ci       |     NULL | NO                 |
| def          | test4       | ucs2                       | ucs2_general_ci        |     NULL | NO                 |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+

因此，如果想變更資料庫中的值，您必須更新資料庫中的每個物件。

檢查表格設定

如要檢查表格的現有值，請使用下列陳述式：

SELECT T.table_name, T.table_schema, CCSA.character_set_name, CCSA.collation_name
FROM information_schema.`tables` T,
  information_schema.`collation_character_set_applicability` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema
NOT IN ('performance_schema', 'mysql', 'sys','information_schema');

輸出內容範例：

+------------+--------------+--------------------+-------------------+
| TABLE_NAME | TABLE_SCHEMA | CHARACTER_SET_NAME | COLLATION_NAME    |
+------------+--------------+--------------------+-------------------+
| t1         | test1        | ascii              | ascii_general_ci  |
| t2         | test2        | latin1             | latin1_swedish_ci |
| t3         | test3        | utf16              | utf16_general_ci  |
| t4         | test4        | ucs2               | ucs2_general_ci   |
+------------+--------------+--------------------+-------------------+

檢查表格欄的設定

如要檢查資料欄的值，請使用下列陳述式：

SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys', 'mysql');

輸出內容範例：

+--------------+------------+-------------+--------------------+------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME   |
+--------------+------------+-------------+--------------------+------------------+
| test1        | t1         | c           | ascii              | ascii_general_ci |
| test1        | t1         | i           | NULL               | NULL             |
| test2        | t2         | j           | NULL               | NULL             |
| test2        | t2         | v           | ascii              | ascii_general_ci |
| test3        | t3         | k           | NULL               | NULL             |
| test3        | t3         | summary     | utf16              | utf16_general_ci |
| test4        | t4         | col         | ucs2               | ucs2_general_ci  |
| test4        | t4         | p           | NULL               | NULL             |
+--------------+------------+-------------+--------------------+------------------+

檢查有效結構定義或資料庫

如要查看有效結構定義或資料庫的詳細資料，請使用下列陳述式。

SELECT TABLE_SCHEMA, TABLE_NAME, CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
       COLUMN_NAME, COLUMN_TYPE, C.CHARACTER_SET_NAME
FROM information_schema.TABLES AS T
JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
WHERE TABLE_SCHEMA=SCHEMA()
AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext', 'set' )
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

輸出內容範例：

+------------+
| DATABASE() |
+------------+
| test3      |
+------------+
+--------------+------------+------------------+-------------+-------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | DEFAULT_CHAR_SET | COLUMN_NAME | COLUMN_TYPE | CHARACTER_SET_NAME |
+--------------+------------+------------------+-------------+-------------+--------------------+
| test3        | t3         | utf16            | summary     | text        | utf16              |
+--------------+------------+------------------+-------------+-------------+--------------------+

檢查預存程序的設定

如要檢查預存程序的值，請使用下列陳述式：

SHOW PROCEDURE STATUS WHERE db = 'DB_NAME';

將 DB_NAME 換成資料庫名稱。

輸出內容範例：

+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db    | Name | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| test2 | p1   | PROCEDURE | root@%  | 2024-08-09 11:47:05 | 2024-08-09 11:47:05 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | latin1_swedish_ci  |
| test2 | p2   | PROCEDURE | root@%  | 2024-08-09 11:48:36 | 2024-08-09 11:48:36 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | latin1_swedish_ci  |
+-------+------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

如要取得預存程序的定義，請使用下列陳述式：

SHOW CREATE PROCEDURE DB_NAME.SP_NAME;

更改下列內容：

  • DB_NAME：MySQL 資料庫的名稱
  • SP_NAME：預存程序的名稱

如果預存程序未採用您預期的字元集或對照順序值，您可能需要捨棄並重新建立該程序，並使用新的字元集或對照順序。

檢查資料庫對照順序對應

在先前的範例輸出中，預存程序是以 latin1_swedish_ci 做為預設定序建立，對應的字元集為 latin1。也就是說，所有處理作業都會使用 latin1 字元集 (除非程序的特定參數標記了自訂字元集)。如果只變更資料庫層級的字元集和排序規則，資料庫層級的更新不會自動更新預存程序。程序會繼續使用 latin1 字元集和相應的排序規則運作。捨棄並重新建立預存程序，可確保程序開始使用您剛更新的資料庫新字元集和新對照順序。

您可以查詢 information_schema.COLLATION_CHARACTER_SET_APPLICABILITY 資料表，瞭解資料庫排序規則和字元集之間的對應關係。

以以下陳述為例：

SELECT CHARACTER_SET_NAME
FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
WHERE COLLATION_NAME = (SELECT database_collation FROM information_schema.ROUTINES
                        WHERE ROUTINE_SCHEMA = "test2" and ROUTINE_NAME = "p1");

輸出內容範例：

+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| latin1             |
+--------------------+

檢查觸發條件的設定

如要檢查觸發條件的值，請使用下列陳述式做為範例。

SHOW TRIGGERS FROM DB_NAME;

將 DB_NAME 替換為 MySQL 資料庫名稱。

如要取得觸發條件的定義，請使用下列範例陳述式。

SHOW CREATE trigger DB_NAME.TRIGGER_NAME;

更改下列內容：

  • DB_NAME：MySQL 資料庫的名稱
  • TRIGGER_NAME：觸發條件的名稱

如果觸發程序未採用您預期的字元集或定序值，您可能需要捨棄並重新建立觸發程序，並使用新的字元集或定序。確認觸發程序使用的是正確的資料庫對照順序對應。 如要進一步瞭解如何檢查資料庫對照，請參閱「檢查資料庫對照對應」。

檢查活動設定

如要檢查事件的值，請使用下列陳述式做為範例。

SHOW EVENTS FROM DB_NAME;

將 DB_NAME 換成資料庫名稱。

如要取得事件的定義，請使用下列範例陳述式。

SHOW CREATE EVENT DB_NAME.EVENT_NAME;

更改下列內容：

  • DB_NAME：資料庫名稱
  • EVENT_NAME：活動名稱

如果事件未採用您預期的字元集或定序值，您可能需要捨棄並重新建立事件，並使用新的字元集或定序。確認事件是否使用正確的資料庫對照順序對應。 如要進一步瞭解如何檢查資料庫對照，請參閱「檢查資料庫對照對應」。

檢查函式的設定

如要檢查函式的值，請使用下列陳述式做為範例。

SHOW FUNCTION STATUS WHERE db = 'DB_NAME';

將 DB_NAME 換成資料庫名稱。

如要取得函式的定義，請使用下列範例陳述式。

SHOW CREATE FUNCTION DB_NAME.FUNCTION_NAME;

更改下列內容：

  • DB_NAME：MySQL 資料庫的名稱
  • FUNCTION_NAME：函式名稱

如果函式未採用您預期的字元集或定序值，您可能需要捨棄並重新建立函式，並使用新的字元集或定序。確認函式使用的資料庫對照順序對應正確無誤。 如要進一步瞭解如何檢查資料庫對照，請參閱「檢查資料庫對照對應」。

字元集或定序。確認函式使用的資料庫對照順序對應正確無誤。 如要進一步瞭解如何檢查資料庫對照，請參閱「檢查資料庫對照對應」。

決定要執行的更新層級

檢查資料庫的字元集和定序設定後，您會看到需要更新的資料庫清單，以及可能需要更新的資料庫物件清單。

您需要執行的工作數量，取決於需要變更設定的資料庫物件類型。

備份執行個體

執行任何更新前，請先備份執行個體。詳情請參閱建立按照需求執行的備份。 建議您在執行備份作業時，不要在執行個體上執行任何 DDL 作業。

為資料庫中儲存的資料庫物件產生重建指令

如果需要捨棄並重新建立資料庫中任何儲存的資料庫物件，請使用新的字元集和排序規則，然後立即使用 mysqldump 工具產生指令。更新資料庫和資料表欄後，您可以使用產生的 mysqldump 檔案，在資料庫中重建儲存的資料庫物件。這個指令會為所有預存程序、函式、觸發條件和事件產生陳述式。

執行下列指令，即可使用輸出內容稍後重新建立特定資料庫的所有預存程序、函式和事件 (重新建立預存資料庫物件時)。

$ mysqldump -uDBAuser -p -h IP_ADDRESS \
   -P 3306 DB_NAME --no-data --no-create-db \
   --no-create-info --routines --triggers --events \
   --set-gtid-purged=OFF --events > dump_objects.sql

更改下列內容：

  • IP_ADDRESS：MySQL 適用的 Cloud SQL 執行個體 IP 位址
  • DB_NAME：MySQL 資料庫的名稱

更新資料庫，並視需要更新資料表和資料欄。

在這個步驟中，您會更新資料庫的字元集和定序值。此外，您可能需要重建資料表和欄。

執行資料庫層級的更新

如果您只執行資料庫層級的更新，請執行下列陳述式來更新資料庫：

ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

更改下列內容：

  • DB_NAME：要更新的資料庫名稱
  • NEW_CHARSET：要套用至資料庫的新字元集
  • NEW_COLLATION：要套用至資料庫的新對照順序

執行資料庫和資料表層級的更新

如果您要更新資料庫和資料表層級，請執行下列操作：

  1. 執行下列陳述式來更新資料庫：

    ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

    更改下列內容：

    • DB_NAME：要更新的資料庫名稱
    • NEW_CHARSET：要套用至資料庫的新字元集
    • NEW_COLLATION：要套用至資料庫的新對照順序

  2. 針對要更新的每個資料表，執行下列陳述式：

    ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

    更改下列內容：

    • TABLE_NAME：要更新的資料表名稱
    • NEW_CHARSET：要套用至資料表的新字元集
    • NEW_COLLATION：要套用至資料表的新對照順序

執行資料庫、資料表和資料欄層級的更新

在這個層級，您需要重新編碼現有資料，才能符合新的字元集和排序規則設定。如果您要執行資料庫、資料表和資料欄層級的更新，請按照下列步驟操作：

  1. 更新表格欄位之前，請先詳閱下列注意事項：

    • ALTER TABLE 字元集和定序轉換會導致資料表重建，並鎖定資料表。因此，任何嘗試存取資料表的有效查詢都會遭到封鎖。
    • ALTER TABLE 字元集和定序轉換需要時間，因為系統會擷取記憶體中的完整資料表。這項作業可能會增加任何平行執行的工作負載延遲時間，因為緩衝區集區頁面會用於更新後的資料表。
    • 如果查詢會存取多個資料表，且部分資料表正在更新，其他資料表尚未更新，則查詢在這段交易期間可能會產生不一致的結果。
    • 執行 ALTER TABLE 字元集和對照順序轉換程序時，會建立影子資料表，因此請據此規劃磁碟用量。
    • ALTER TABLE 字元集和定序轉換也會導致資料表上的任何索引重建。
    • 您不需要分別更新副本執行個體。系統會自動複製 ALTER、DROP 和 CREATE 指令。不過，執行 DDL 指令可能會導致作業期間的副本延遲時間增加。
    • 如果還原的資料庫備份檔是在套用這些更新之前建立，則必須重新套用更新。

  2. 檢查表格中是否有任何與新字元集不相容的資料元組。如有任何不相容問題，請先修正，再更新字元集。否則，轉換過程中會發生錯誤。您可以使用下列 SELECT CONVERT 陳述式，以新的字元集驗證資料。

    SELECT COLUMN_NAME,CONVERT(COLUMN_NAME USING NEW_CHARSET)
FROM TABLE_NAME
WHERE COLUMN_NAME != CONVERT(COLUMN_NAME USING NEW_CHARSET);
     
    SELECT c,CONVERT(c USING 'ascii')
FROM t WHERE c != CONVERT(c USING ascii);

    輸出內容範例：

    +------+--------------------------+
| c    | convert(c using 'ascii') |
+------+--------------------------+
| é    | ?                        |
| é    | ?                        |
| £    | ?                        |
+------+--------------------------+

    在本例中，由於輸出內容會傳回這三個元組，因此有三個不一致的值需要修正。請先檢查查詢的輸出內容，修正資料，直到查詢為所有需要轉換的資料欄傳回零元組，再繼續下一個步驟。您也可以在同一個 SELECT 查詢中合併多個資料欄。

  3. 選擇要更新資料表字元集和定序的選項 (以資料欄為單位)。

  4. 如要更新特定資料表欄的字元集，請按照「資料欄層級」分頁中的步驟操作，您只需要重新編碼所選資料欄。否則，請按照「資料表層級」分頁中的步驟，重新編碼整個資料表。

    資料表層級

    1. 執行下列陳述式來更新資料庫：

      ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      更改下列內容：

      • DB_NAME：要更新的資料庫名稱
      • NEW_CHARSET：要套用至資料庫的新字元集
      • NEW_COLLATION：要套用至資料庫的新對照順序

    2. 針對每個受影響的資料表，執行下列陳述式來更新資料表：

      ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      更改下列內容：

      • TABLE_NAME：要更新的資料表名稱
      • NEW_CHARSET：要套用至資料表的新字元集
      • NEW_COLLATION：要套用至資料表的新對照順序

    3. 在資料表層級更新，讓資料表中的所有資料欄都更新為新的字元集和排序規則。這個選項會完全重建表格。如要使用這個選項，請執行下列陳述式：

      ALTER TABLE DB_NAME.TABLE_NAME
CONVERT TO CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      更改下列內容：

      • TABLE_NAME：要更新的資料表名稱
      • NEW_CHARSET：要套用至資料表的新字元集
      • NEW_COLLATION：要套用至資料表的新對照順序

    資料欄層級

    在資料欄層級更新。這個選項也會完全重建表格。如果更新多個資料欄，這個選項可能會導致多次重建。

    如要使用這個選項，請執行下列陳述式：

    1. 執行下列陳述式來更新資料庫：
      2. ALTER DATABASE DB_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      更改下列內容：

      • DB_NAME：要更新的資料庫名稱
      • NEW_CHARSET：要套用至資料庫的新字元集
      • NEW_COLLATION：要套用至資料庫的新對照順序
    2. 針對每個受影響的資料表，執行下列陳述式來更新資料表：
      3. ALTER TABLE TABLE_NAME CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      更改下列內容：

      • TABLE_NAME：要更新的資料表名稱
      • NEW_CHARSET：要套用至資料表的新字元集
      • NEW_COLLATION：要套用至資料表的新對照順序
    3. 執行下列陳述式，在資料欄層級更新：
      4. ALTER TABLE DB_NAME.TABLE_NAME
MODIFY COLUMN_DEFINITION CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;

      更改下列內容：

      • DB_NAME：要更新的資料庫名稱
      • TABLE_NAME：要更新的資料表名稱
      • COLUMN_DEFINITION：指定確切的資料欄定義，包括限制。資料欄定義可能帶有舊的字元集和定序值。您必須使用新值更新資料欄定義。
      • NEW_CHARSET：要套用至資料表資料欄的新字元集
      • NEW_COLLATION：要套用至資料表資料欄的新對照順序

      針對資料表中的所有字串資料欄重複執行這項陳述式， 這些資料欄需要更新字元集或排序規則。

驗證更新並找出錯誤

在 ALTER TABLE 作業期間，請找出任何錯誤，指出資料表中的現有資料與新字元集不相容。例如：

ERROR 1366 (HY000): Incorrect string value: '\xC3\xA9' for column 'c' at row 1

如果遇到任何資料相容性錯誤，建議您再次檢查表格是否有任何資料相容性錯誤，並在重新執行 ALTER TABLE 陳述式以重新編碼資料前修正錯誤。

此外，在 ALTER TABLE 作業期間，資料表 (外鍵資料表和參照的資料表) 可能會在外鍵檢查時失敗，而資料欄會重新編碼。如果發生這種情況，您可以停用外部鍵檢查 (SET FOREIGN_KEY_CHECKS=0;)，然後再次執行更新。

重新建立儲存的資料庫物件

成功更新資料庫後，您可以使用產生的 mysqldump 檔案，以字元集和排序規則值重建儲存的資料庫物件。

如要重新建立儲存的資料庫物件，請執行下列操作：

  1. 確認資料庫中是否有儲存的資料庫物件。
  2. 請確認沒有平行載入作業，會在捨棄和重建期間預期出現儲存的資料庫物件。

  3. 您使用 mysqldump 工具產生的 mysqldump 檔案不含捨棄觸發程序的陳述式。如要捨棄觸發程序，請開啟 MySQL 終端機，產生觸發程序的 DROP 陳述式。

    mysql> SELECT CONCAT('DROP TRIGGER ', TRIGGER_NAME, ';') AS
drop_trigger_statements FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DB_NAME;

  4. 複製輸出內容。

  5. 在 MySQL 殼層或指令碼中執行陳述式，刪除資料庫的觸發程序。

  6. 放置觸發程序後，請套用您使用 mysqldump 工具產生的傾印檔案。 如要套用傾印檔案並重新建立物件，請輸入下列內容：

    $ mysql -uDBAuser -p -h IP_ADDRESS \
  -P 3306 DB_NAME < dump_objects.sql

    更改下列內容：

    • IP_ADDRESS：MySQL 適用的 Cloud SQL 執行個體 IP 位址
    • DB_NAME：資料庫名稱

再次備份執行個體

更新完成後，請再次備份執行個體。詳情請參閱建立按照需求執行的備份。

指令碼範例

如要更新資料庫、資料表和資料欄，本節提供範例指令碼，協助您重新編碼資料。

更新多個資料表的指令碼

下列指令碼會為指定資料庫中的所有資料表產生陳述式。您可以使用這項陳述式重新編碼現有資料，以符合新的字元集和對照設定。

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;  ",
"ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION; ")
AS alter_sql FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_TYPE='BASE TABLE';

更改下列內容：

  • DB_NAME：要更新的資料庫名稱
  • NEW_CHARSET：要套用至資料表的新字元集
  • NEW_COLLATION：要套用至資料表的新對照順序

輸出內容範例：

+--------------------------------------------------------------------------------------------+
|alter_sql
+--------------------------------------------------------------------------------------------+
| ALTER TABLE test1.t1 CHARACTER SET <new-charset> COLLATE <new-collation>;
  ALTER TABLE test1.t1 CONVERT TO CHARACTER SET <new-charset> COLLATE <new-collation>;       |
+--------------------------------------------------------------------------------------------+

更新資料表中多個資料欄的指令碼

下列指令碼會為指定資料表中的所有資料欄產生陳述式。您可以使用這項陳述式重新編碼現有資料，以符合新的字元集和對照設定。

  1. 為資料庫中的所有資料表生成 ALTER TABLE 陳述式

    SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION;  ")
AS alter_table_statements
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_TYPE='BASE TABLE';

    更改下列內容：

    • DB_NAME：要更新的資料庫名稱
    • NEW_CHARSET：要套用至資料表的新字元集
    • NEW_COLLATION：要套用至資料表的新對照順序

    輸出內容範例：

    +------------------------------------------------------------------------------+
| alter_table_statements                                                       |
+------------------------------------------------------------------------------+
| ALTER TABLE test1.t1 CHARACTER SET <new-charset> COLLATE <new-collation>;    |
+------------------------------------------------------------------------------+

  2. 為每個資料表生成 ALTER TABLE MODIFY 指令。

    SELECT CONCAT( "ALTER TABLE ", table_name, " MODIFY ", column_name, 'COLUMN_DEFINITION' , " CHARACTER SET NEW_CHARSET COLLATE NEW_COLLATION ")
AS alter_table_column_statements FROM information_schema.columns
WHERE table_schema = 'DB_NAME' AND table_name = 'TABLE_NAME'
AND data_type IN ('char', 'varchar', 'tinytext', 'text', 'mediumtext', 'longtext','set','enum');

    更改下列內容：

    • DB_NAME：要更新的資料庫名稱
    • TABLE_NAME：要更新的資料表名稱
    • COLUMN_DEFINITION：在取得每項查詢的結果後，請替換每個資料欄的資料欄定義 (各資料欄的定義可能不同)
    • NEW_CHARSET：要套用至資料表資料欄的新字元集
    • NEW_COLLATION：要套用至資料表資料欄的新對照順序

    輸出內容範例：

    +-------------------------------------------------------------------------------------------------------------------------------------+
|alter_table_column_statements                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------+
| ALTER TABLE t1 MODIFY c <column-definition-replaced-after-obtaining-result-set> CHARACTER SET <new-charset> COLLATE <new-collation>  |
+-------------------------------------------------------------------------------------------------------------------------------------+

後續步驟