Kenny

2023.10.27

データベースの文字コード、何にしていますか?

みなさん、はじめまして。Kennyです。

弊社のプロダクト「Letro」の開発チームのプロジェクトマネージャーを担当しています。

私は子供の頃から「コード」（code）オタクです。郵便番号や電話番号、商品のバーコードの下に表示されているJANコード、などなど、数字やアルファベットの羅列を見ているだけで何かゾクゾクしてしまいます（笑）。

その中でも特に大好きなのが「文字コード」です。

文字コードとは

文字コードとは、文字や記号を通信やコンピューターで取り扱う為に、各文字や記号に対して通し番号を付与したものです。

たとえば「A」というアルファベット文字をASCII（アスキー: American Standard Code for Information Interchange）という文字コードで表すと以下のようになります。

文字	ASCIIコード(16進数)
A	0x41

ASCIIはアメリカで制定された文字コードなので、漢字やカナ、ハングル、アラビア文字などのアルファベット以外の文字に対するコードは用意されてません。

そこで、ASCIIを拡張した形で世界中で様々な文字コードが誕生しました。

日本でも以下のような文字コードが開発されました。

文字コード名	説明
JISコード（ISO-2022-JP）	日本の学術組織を結んだ研究用のコンピュータネットワークで使用されて来たJUNETコードをJIS標準化したもの
EUC-JP	米AT&T社がUNIXの日本語化の際に制定した文字コード
Shift_JIS	全角文字の部分をJISコードからズラし（シフト : Shift）て作成した文字コード
Microsoftコードページ932（CP932）	米マイクロソフト社及びMS-DOSのOEMベンダーがShift_JISを独自に拡張した文字コード
MacJapanese	米アップル社がShift_JISを独自に拡張した文字コード

しかし、文字コード同士の互換性に問題があり、文字コードの自動判定がうまくいかずに、いわゆる「文字化け」という現象も日常的に発生していました。

大手のWebサイトのHTMLヘッダーを見ると、「龜」（Shift_JIS : 0xF3FD）や「龠」（Shift_JIS : 0xF3FE）や「美乳」（Shift_JIS : 0xC8FE, 0xC6FD）などの一見、無意味な漢字がコメントとして挿入されていました。これらの漢字は、Shift_JIS以外の文字コードでは登場しないコードパターンである為、文字化けを発生させにくい文字として広く知られていました。「雀の往来」なんていう洒落たフレーズを文字化け防止に採用していた例もありました。

日本国内だけでもこんな状況であったので、インターネットが普及し始めて世界中のコンピューター同士がデータやメッセージのやり取りを行うのに当たり、非常に大きな障壁となっていました。

ユニコードプロジェクト

1984年、ISO（International Organization for Standardization : 国際標準化機構）の文字コード規格委員会で世界中の文字を一つの文字コードで扱う新しい文字コード（ISO 10646）を制定する作業が始まりました。
しかし、この取り組みは「漢字のコード統一化」を目指していた中国の反対もあって、残念ながら頓挫してしまいます。

さて、ISOでの動きとは別に、米ゼロックス社のJoe BeckerとLee Collinsは、1987年頃から世界中の文字を一つの文字コードで扱う新しい文字コードの開発をしていました。これが「ユニコード」（Unicode）です。

このプロジェクトには、ゼロックス以外にも多くの企業が賛同し、1990年に「ユニコードコンソーシアム」が誕生しました。ユニコードコンソーシアムには、日本からもジャストシステムが参加し、ユニコードに日本の膨大な文字を組み込む途方もない作業が始まりました。

この作業には、様々な紆余曲折があったのですが、残念ながらここでは割愛させていただきます。

もし興味のある方は、以下の書籍を是非、お読みになってください。

『ユニコード戦記文字符号の国際標準化バトル』（著者：小林龍生、出版社：東京電機大学出版局）

UTF-8

ユニコードには、以下の3つの文字コードが存在します。

UTF-8
UTF-16
UTF-32

UTF-の後に続く数字は、コード単位のビット数です。1つの文字を表現するのに、複数のコードを組み合わせる場合もあります。

この中で、現在、最も多く利用されているのは「UTF-8」です。

UTF-8の概要は以下の通りとなります。

1から4バイトの可変長のコードで管理する。
ASCIIコードと同じ文字（1バイト）は共通の文字コードで表現可能。
漢字やカナなどを表現しようとすると3バイトが必要となる。ユニコード以前の文字コードでは2バイトで表現できた。

皆さんが普段目にされているWebページやメールなどのメッセージは、ほぼ全てがUTF-8で記述されています。もちろん、皆さんが今、ご覧になっているこのページもUTF-8です。

データベースの文字コード

最近のシステム（特にインターネットと繋がっているもの）であれば、データベースの文字コードはUTF-8で設定されていると思います。

しかし、UTF-8には実はいくつかの種類があり、その違いを分からずに設定してしまうと不具合の原因になる場合があります。

ここからは、MySQLを例に説明していきます。

MySQLで設定が可能なUTF-8コードにもいくつか種類がありますが、日本語を保存する際に使われることが多いのは次の2つです。

utf8
utf8mb4

標準的と思われる「utf8」を設定したくなるところですが、ちょっと待ってください!!　「utf8」は、実は「utf8mb3」のエイリアス（別名）になっていて、1～3バイトで定義された文字しか扱うことができません。

「utf8mb4」であれば、4バイトで定義された文字も扱うことができます。

では、4バイトが必要な文字って何でしょうか?　主に次の2つになります。

一部の字体の異なる漢字（異体字）　例）「𠀋」 – 「丈」の異体字
絵文字

これらの文字を「utf8」で設定されたデータベースに保存するとどうなるでしょうか?　早速試してみましょう。

データベースの文字コードは、CREATE DATABASEを行う際に「CHARACTER SET」で指定します。

mysql＞ CREATE DATABASE hoge CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql＞ USE hoge;
Database changed
mysql＞ CREATE TABLE `memos` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT, `memo` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql＞ INSERT INTO `memos` (`memo`) VALUES ('お友達のお誕生日が近いので🎂の予約をしないと');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8E\x82\xE3\x81...' for column 'memo' at row 1

mysql＞ CREATE DATABASE hoge CHARACTER SET utf8;

Query OK, 1 row affected (0.00 sec)

mysql＞ USE hoge;

Database changed

mysql＞ CREATE TABLE `memos` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT, `memo` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

mysql＞ INSERT INTO `memos` (`memo`) VALUES ('お友達のお誕生日が近いので🎂の予約をしないと');

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8E\x82\xE3\x81...' for column 'memo' at row 1

エラーが出てしまって、絵文字を含むデータを保存できませんでした。大切なお友達のケーキの予約をうっかり忘れてしまいそうです。

では、今度は文字コードを「utf8mb4」に変えて再チャレンジしてみましょう!!

mysql＞ CREATE DATABASE new_hoge CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql＞ USE new_hoge;
Database changed

mysql＞ CREATE TABLE `new_memos` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT, `memo` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql＞ INSERT INTO `new_memos` (`memo`) VALUES ('お友達のお誕生日が近いので🎂の予約をしないと');
Query OK, 1 row affected (0.01 sec)

mysql＞ SELECT * FROM `new_memos`;
+----+---------------------------------------------------------+
| id | memo |
+----+---------------------------------------------------------+
| 1 | お友達のお誕生日が近いので🎂の予約をしないと |
+----+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql＞ CREATE DATABASE new_hoge CHARACTER SET utf8mb4;

Query OK, 1 row affected (0.00 sec)

mysql＞ USE new_hoge;

Database changed

mysql＞ CREATE TABLE `new_memos` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT, `memo` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

mysql＞ INSERT INTO `new_memos` (`memo`) VALUES ('お友達のお誕生日が近いので🎂の予約をしないと');

Query OK, 1 row affected (0.01 sec)

mysql＞ SELECT * FROM `new_memos`;

+----+---------------------------------------------------------+

| id | memo |

+----+---------------------------------------------------------+

| 1 | お友達のお誕生日が近いので🎂の予約をしないと |

+----+---------------------------------------------------------+

1 row in set (0.00 sec)

今度はちゃんと保存されていますね。ケーキの予約を忘れずに済みそうです（笑）。

絵文字や難しい漢字などをデータベースに保存する際には、データベースの文字コードを「utf8mb4」に設定することを忘れないようにしましょう。

照合順序の話

もう一つ、データベースでの文字コードに関する話として「照合順序」（コレート : COLLATE）というものがあります。

照合順序とは、文字の大小関係を比較する際のルールとなるもので、データベースに関する以下のような操作の場面で参照されます。

インデックス作成
WHERE句での検索
ORDER BY句でのソート
GROUP BY句でのグルーピング

でも「文字の大小関係の比較」と言われても、ちょっとピンと来ないですよね?

具体的には、以下のようなルールとなります。

「大文字」と「小文字」を区別するか否か?
- 例）半角の「A」と「a」を区別するか?
「濁音・半濁音の有無」を区別するか否か?
- 例）「ハ」と「バ」と「パ」を区別するか?
「ひらがな」と「カタカナ」を区別するか否か?
- 例）「は」と「ハ」を区別するか?
「半角文字」と「全角文字」を区別するか否か?
- 例）半角の「A」と全角の「Ａ」を区別するか?

文字コードが「utf8mb4」の場合に、MySQLでよく利用される照合順序の一覧は以下の通りです。

照合順序名	「大文字」と「小文字」の区別	「濁音・半濁音の有無」の区別	「ひらがな」と「カタカナ」の区別	「半角文字」と「全角文字」の区別
utf8mb4_bin	〇	〇	〇	〇
utf8mb4_general_ci	×	〇	〇	〇
utf8mb4_unicode_ci	×	×	×	×

では、実際にそれぞれの照合順序をテストしてみましょう!!

先程のテストで作成した文字コードを「utf8mb4」に設定したデータベースを使います。MySQLでは、WHERE句の中にCOLLATE句を書いて照合順序を指定できます。

まず、テスト用のテーブルとデータを登録します。

mysql＞ USE new_hoge; 
Database changed 

mysql＞ CREATE TABLE `family_jobs` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `job` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; 
Query OK, 0 rows affected (0.01 sec) 

mysql＞ INSERT INTO `family_jobs` (`name`,`job`) VALUES ('パパ','APIエンジニア'); 
Query OK, 0 rows affected (0.01 sec) 

mysql＞ SELECT * FROM `family_jobs`; 
+----+--------+--------------------+
| id | name | job |
+----+--------+--------------------+
| 1 | パパ | APIエンジニア |
+----+--------+--------------------+
1 row in set (0.00 sec)

mysql＞ USE new_hoge;

Database changed

mysql＞ CREATE TABLE `family_jobs` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `job` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;

Query OK, 0 rows affected (0.01 sec)

mysql＞ INSERT INTO `family_jobs` (`name`,`job`) VALUES ('パパ','APIエンジニア');

Query OK, 0 rows affected (0.01 sec)

mysql＞ SELECT * FROM `family_jobs`;

+----+--------+--------------------+

| id | name | job |

+----+--------+--------------------+

| 1 | パパ | APIエンジニア |

+----+--------+--------------------+

1 row in set (0.00 sec)

では、「大文字」と「小文字」の区別が行われるかどうかを確認していきましょう。

mysql＞ SELECT * FROM `family_jobs` WHERE `job` LIKE '%api%' COLLATE utf8mb4_bin; 
Empty set (0.00 sec) 

mysql＞ SELECT * FROM `family_jobs` WHERE `job` LIKE '%api%' COLLATE utf8mb4_general_ci; 
+----+--------+--------------------+ 
| id | name | job | 
+----+--------+--------------------+ 
| 1 | パパ | APIエンジニア | 
+----+--------+--------------------+ 
1 row in set (0.00 sec) 

mysql＞ SELECT * FROM `family_jobs` WHERE `job` LIKE '%api%' COLLATE utf8mb4_unicode_ci; 
+----+--------+--------------------+ 
| id | name | job | 
+----+--------+--------------------+ 
| 1 | パパ | APIエンジニア | 
+----+--------+--------------------+ 
1 row in set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `job` LIKE '%api%' COLLATE utf8mb4_bin;

Empty set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `job` LIKE '%api%' COLLATE utf8mb4_general_ci;

+----+--------+--------------------+

| id | name | job |

+----+--------+--------------------+

| 1 | パパ | APIエンジニア |

+----+--------+--------------------+

1 row in set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `job` LIKE '%api%' COLLATE utf8mb4_unicode_ci;

+----+--------+--------------------+

| id | name | job |

+----+--------+--------------------+

| 1 | パパ | APIエンジニア |

+----+--------+--------------------+

1 row in set (0.00 sec)

先程の表の通りとなっていますね。

今度は「濁音・半濁音の有無」の区別です。

mysql＞ SELECT * FROM `family_jobs` WHERE `name` = 'ハハ' COLLATE utf8mb4_bin; 
Empty set (0.00 sec) 

mysql＞ SELECT * FROM `family_jobs` WHERE `name` = 'ハハ' COLLATE utf8mb4_general_ci; 
Empty set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `name` = 'ハハ' COLLATE utf8mb4_unicode_ci;
+----+--------+--------------------+
| id | name | job |
+----+--------+--------------------+
| 1 | パパ | APIエンジニア |
+----+--------+--------------------+
1 row in set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `name` = 'ハハ' COLLATE utf8mb4_bin;

Empty set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `name` = 'ハハ' COLLATE utf8mb4_general_ci;

Empty set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `name` = 'ハハ' COLLATE utf8mb4_unicode_ci;

+----+--------+--------------------+

| id | name | job |

+----+--------+--------------------+

| 1 | パパ | APIエンジニア |

+----+--------+--------------------+

1 row in set (0.00 sec)

「ハハ」と「パパ」とが同じ結果になるなんて・・な気もしますが、ルール通りの検索結果にはなっていますね。

次は「ひらがな」と「カタカナ」の区別です。

mysql＞ SELECT * FROM `family_jobs` WHERE `name` = 'ぱぱ' COLLATE utf8mb4_bin; 
Empty set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `name` = 'ぱぱ' COLLATE utf8mb4_general_ci; 
Empty set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `name` = 'ぱぱ' COLLATE utf8mb4_unicode_ci;
+----+--------+--------------------+
| id | name | job |
+----+--------+--------------------+
| 1 | パパ | APIエンジニア |
+----+--------+--------------------+
1 row in set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `name` = 'ぱぱ' COLLATE utf8mb4_bin;

Empty set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `name` = 'ぱぱ' COLLATE utf8mb4_general_ci;

Empty set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `name` = 'ぱぱ' COLLATE utf8mb4_unicode_ci;

+----+--------+--------------------+

| id | name | job |

+----+--------+--------------------+

| 1 | パパ | APIエンジニア |

+----+--------+--------------------+

1 row in set (0.00 sec)

こちらも良さそうです。名前の振り仮名が「ひらがな」と「カタカナ」とで混在しちゃったデータの検索とかに使えそうです。

最後は「半角文字」と「全角文字」の区別です。

mysql＞ SELECT * FROM `family_jobs` WHERE `job` LIKE '%ＡＰＩ%' COLLATE utf8mb4_bin;
Empty set (0.00 sec) 

mysql＞ SELECT * FROM `family_jobs` WHERE `job` LIKE '%ＡＰＩ%' COLLATE utf8mb4_general_ci; 
Empty set (0.00 sec) 

mysql＞ SELECT * FROM `family_jobs` WHERE `job` LIKE '%ＡＰＩ%' COLLATE utf8mb4_unicode_ci;
+----+--------+--------------------+
| id | name | job |
+----+--------+--------------------+
| 1 | パパ | APIエンジニア |
+----+--------+--------------------+
1 row in set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `job` LIKE '%ＡＰＩ%' COLLATE utf8mb4_bin;

Empty set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `job` LIKE '%ＡＰＩ%' COLLATE utf8mb4_general_ci;

Empty set (0.00 sec)

mysql＞ SELECT * FROM `family_jobs` WHERE `job` LIKE '%ＡＰＩ%' COLLATE utf8mb4_unicode_ci;

+----+--------+--------------------+

| id | name | job |

+----+--------+--------------------+

| 1 | パパ | APIエンジニア |

+----+--------+--------------------+

1 row in set (0.00 sec)

こちらも期待通りの検索結果となりました。

照合順序の指定により、検索結果が変わることをご確認いただけたと思います。

皆さんも、データの中身や期待される検索結果の要件に合わせて、適切な照合順序を指定するようにしてみてください。

まとめ

システム開発において、文字コードを意識することは非常に重要だと思います。

今回、書き切れなかった話もたくさんありますので、また次の機会にご紹介させていただければと思います。

Kenny

ITエンジニアになって四半世紀。メインフレームの時代からインターネット、そしてAIの時代へと、コンピューターの世界の荒波を何とか乗り越えてきました。

RELATION ENTRY

Kennyの記事一覧へ

▶︎

データベースの文字コード、何にしていますか?

文字コードとは

ユニコードプロジェクト

UTF-8

データベースの文字コード

照合順序の話

まとめ

RELATION ENTRY

HOT ENTRY

RECENT