MySQLのテーブル作成後に、文字コードをutf8mb4に変更する

2020年11月19日

MySQL

MySQL のテーブルの文字コード latin1 と utf8mb4 が混在しているシステムを utf8mb4 に統一する機会がありました。調査した内容や実行したコマンドを残しておきます。MySQL のバージョンは 5.7 です。

目次

  1. やったこと
  2. データベースの文字コードと照合順序を確認する
  3. テーブルの文字コードと照合順序を確認する
  4. 利用できる照合順序を確認する
  5. カラムの文字コードと照合順序を確認する
  6. テーブル内のすべての文字カラムを変換する
  7. データベースの文字コードを変更する
  8. テーブルの文字コードを変更する
  9. カラムの文字コードを変更する

やったこと

  1. データベース、全テーブルの文字コードを調べる
  2. 文字コードが utf8mb4 ではないテーブルを見つけたら、そのテーブルのカラムの文字コードを調べる
  3. カラムとテーブルの文字コードを utf8mb4 に変換する
  4. データベースの文字コードを変更する

順番に見ていきましょう。

データベースの文字コードと照合順序を確認する

データベースの文字コードは INFORMATION_SCHEMA データベースの SCHEMATA テーブルから確認することができます。

SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA
    WHERE SCHEMA_NAME = 'db_name';
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| db_name     | utf8mb4                    | utf8mb4_general_ci     |
+-------------+----------------------------+------------------------+

詳細は The INFORMATION_SCHEMA SCHEMATA Table | MySQL 5.7 Reference Manual を参照。

テーブルの文字コードと照合順序を確認する

テーブルの文字コードと照合順序は INFORMATION_SCHEMA データベースの TABLES テーブルから確認することができます。

SELECT TABLE_NAME,TABLE_COLLATION
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA='db_name';
+------------+--------------------+
| TABLE_NAME | TABLE_COLLATION    |
+------------+--------------------+
| tbl_name1  | latin1_swedish_ci  |
| tbl_name2  | utf8mb4_general_ci |
+------------+--------------------+

詳細は The INFORMATION_SCHEMA TABLES Table | MySQL 5.7 Reference Manual を参照。

利用できる照合順序を確認する

文字コードと利用できる照合順序の組み合わせは INFORMATION_SCHEMA データベースの COLLATION_CHARACTER_SET_APPLICABILITY テーブルから確認ができます。利用できる照合順序が MySQL のバージョンによって違うので、照合順序を変更する前にどの照合順序が使えるかを調べると良いです。

SELECT COLLATION_NAME,CHARACTER_SET_NAME
    FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
    WHERE COLLATION_NAME='utf8mb4_general_ci';
+--------------------+--------------------+
| COLLATION_NAME     | CHARACTER_SET_NAME |
+--------------------+--------------------+
| utf8mb4_general_ci | utf8mb4            |
+--------------------+--------------------+

詳細は The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table | MySQL 5.7 Reference Manual を参照。

カラムの文字コードと照合順序を確認する

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  [AND table_schema = 'db_name']
  [AND column_name LIKE 'wild']
+--------------+--------------------+-------------------+---------------+
| COLUMN_NAME  | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE   |
+--------------+--------------------+-------------------+---------------+
| id           | latin1             | latin1_swedish_ci | varchar(255)  |
| tbl_name2_id | NULL               | NULL              | int(11)       |
| column_name1 | latin1             | latin1_swedish_ci | varchar(255)  |
| column_name2 | latin1             | latin1_swedish_ci | varchar(255)  |
+--------------+--------------------+-------------------+---------------+

詳細は The INFORMATION_SCHEMA COLUMNS Table | MySQL 5.7 Reference Manual を参照。

テーブル内のすべての文字カラムを変換する

ここまでで、現在のデータベース、テーブル、カラムの文字コードが分かります。ここからは、文字コードを変更する方法を紹介します。

以下のコマンドで、テーブルのデフォルトの文字コードと、すべてのカラムの文字コードを変換できます。テーブルの文字コードとカラムの文字コードを揃えたい場合はこのコマンドが楽です。

ALTER TABLE tbl_name
    CONVERT TO CHARACTER SET charset_name 
    [COLLATE collation_name]

例

ALTER TABLE tbl_name
    CONVERT TO CHARACTER SET utf8mb4 
    COLLATE utf8mb4_general_ci

参考

データベースの文字コードを変更する

データベースの文字コードと照合順序を変更したい場合は以下の SQL コマンドで。

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

例

ALTER DATABASE db_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

詳細は Database Character Set and Collation | MySQL 5.7 Reference Manual を参照。

テーブルの文字コードを変更する

テーブルの文字コードと照合順序を変更したい場合は以下 SQL コマンドで。このコマンドでは、カラムの文字コードは変わりません。

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

例

ALTER TABLE tbl_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci

詳細は Table Character Set and Collation | MySQL 5.7 Reference Manual を参照。

カラムの文字コードを変更する

文字カラムの文字コードと照合順序を変更するには、以下のコマンドを使って 1 カラムずつ変更します。まとめて変えたい場合は少し前に紹介した ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name ステートメントを使います。

ALTER TABLE t1 MODIFY
    col_name {CHAR | VARCHAR | TEXT} (col_length)
        CHARACTER SET charset_name
        COLLATE collation_name;

例

ALTER TABLE t1 MODIFY
    col_name VARCHAR(50)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_general_ci;

詳細は ALTER TABLE Statement | MySQL 5.7 Reference Manual を参照。

-技術ブログ
-