MySQLの文字コードの確認と変更 ― character_set変数とDB・表・列の見方、CONVERT TOで直す

※この記事にはアフィリエイト広告（プロモーション）が含まれます。

MySQL の文字コードは、サーバー・データベース・表・列・接続のそれぞれに設定があります。データが入る列の設定と、送る側の接続の設定がずれると文字化けやエラーになります。この記事の出力は MySQL 8.4.11（Ubuntu 26.04 の標準パッケージ）で採りました。

まず見る（結論）

SHOW VARIABLES LIKE 'character_set%';           -- サーバーと今の接続
SHOW CREATE TABLE 表名\G                         -- 表と列
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4;   -- 表の列をまとめて変える

• 実際にデータが入るのは列の文字コード。データベースや表の設定は、新しく作る表・列の既定値にすぎない
• ALTER DATABASE も ALTER TABLE ... CHARACTER SET も、既存の列を変えない。列まで変えるのは CONVERT TO
• MySQL 8.4 の既定は utf8mb4 / utf8mb4_0900_ai_ci。この照合順序では「は」「ば」「ハ」が同じ値として比べられる

サーバーと接続の設定を見る

SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

client / connection / results の3つは接続ごとの値です。mysql --default-character-set=latin1 で接続すると、この3つが latin1 になりました。mysql コマンドの STATUS（\s）でも Server characterset から Conn. characterset までの4行で見られます。

Ubuntu の標準パッケージでは /etc/mysql/ 配下の設定ファイルに character の行は1つもなく、上の値は MySQL 8.4 の既定値です。

データベース・表・列の設定を見る

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
  FROM information_schema.SCHEMATA;

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
  FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = 'fsv3' AND CHARACTER_SET_NAME IS NOT NULL;
+------------+-------------+--------------------+--------------------+
| TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME     |
+------------+-------------+--------------------+--------------------+
| t          | name        | utf8mb3            | utf8mb3_general_ci |
| t2         | name        | utf8mb4            | utf8mb4_0900_ai_ci |
+------------+-------------+--------------------+--------------------+

1つの表なら SHOW CREATE TABLE か SHOW FULL COLUMNS FROM 表名 でも分かります。SHOW CREATE TABLE では、列の文字コードが表の既定と違うときだけ列の行に CHARACTER SET が出ます。

このデータベース fsv3 は CREATE DATABASE fsv3 CHARACTER SET utf8 で作りました。MySQL 8.4 の utf8 は utf8mb3 の別名で、SHOW CREATE DATABASE には utf8mb3 と出ます。utf8mb3 の列に絵文字を入れると次のエラーになります。

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\xA3' for column 'name' at row 1

文字コードを変える

utf8mb3 の表 t を3つの方法で変えて、列が変わったかを見ました。

2つ目を実行した後の SHOW CREATE TABLE です。

CREATE TABLE `t` (
  `id` int NOT NULL,
  `name` varchar(10) CHARACTER SET utf8mb3 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

エラーや警告は出ないので、表の行だけを見ると列も変わったと思い込みやすくなります。列を1つだけ変えるなら ALTER TABLE t MODIFY name varchar(10) CHARACTER SET utf8mb4; です（MODIFY では、書かなかった NOT NULL などが消えるので、定義を全部書きます）。

照合順序で「は」と「ば」が同じになる

既定の照合順序 utf8mb4_0900_ai_ci は、アクセント（濁点）と大文字小文字を区別しません。日本語ではかなの種類も区別しませんでした。

SELECT 'A' = 'a', 'は' = 'ば', 'は' = 'ハ', 'ｱ' = 'ア', 'ｶﾞ' = 'ガ';
→ 1, 1, 1, 1, 1

主キーの列に「はし」を入れた後は、「ばし」も「ハシ」も ERROR 1062（Duplicate entry 'ばし' for key 'u.PRIMARY' など）になり、入りませんでした。WHERE name = 'ぱし' で「はし」の行が返ります。

ALTER TABLE u MODIFY name varchar(10) COLLATE utf8mb4_ja_0900_as_cs_ks;

日本語用の utf8mb4_ja_0900_as_cs_ks にすると、「はし」「ばし」「ハシ」の3行とも入りました。utf8mb4_ja_0900_as_cs（末尾の _ks なし）では、濁点は区別しますが、「は」と「ハ」は同じ扱いでした。

クライアントの設定がずれたときの文字化け

utf8mb4 の端末から --default-character-set=latin1 で接続し、「山田」を入れました。エラーは出ません。

$ mysql --default-character-set=latin1 -e "INSERT INTO fsv.t VALUES (9, '山田');"
$ mysql --default-character-set=latin1 -e "SELECT id, name, HEX(name), CHAR_LENGTH(name) FROM fsv.t;"
id	name	HEX(name)	CHAR_LENGTH(name)
1	??	E4BD90E897A4	2
9	山田	C3A5C2B1C2B1C3A7E2809DC2B0	6

$ mysql -e "SELECT id, name, HEX(name), CHAR_LENGTH(name) FROM fsv.t;"
id	name	HEX(name)	CHAR_LENGTH(name)
1	佐藤	E4BD90E897A4	2
9	山田	C3A5C2B1C2B1C3A7E2809DC2B0	6

UTF-8 のバイト列を latin1 の文字として受け取り、それをもう一度 utf8mb4 に変換して保存しています。そのため CHAR_LENGTH が 2 ではなく 6 になっています。同じ接続設定のアプリから見る限り正しく表示されるので、気づくのが遅れます。WHERE name = '山田' では1件も返りませんでした。

入ってしまったデータは、変換を逆にたどると戻せました。

SELECT id, CONVERT(CAST(CONVERT(name USING latin1) AS BINARY) USING utf8mb4) AS fixed FROM fsv.t WHERE id = 9;
→ 9  山田

確かめてから UPDATE で書き戻します。アプリの接続設定（JDBC の characterEncoding、PHP の charset など）を utf8mb4 に直すのが先です。

関連: MySQLのERROR 1366「Incorrect string value」 ／ MySQLのTEXT型の最大文字数 ／ MySQLでCSVを取り込む（LOAD DATA）