Zenn
Open6

MySQLの文字コードとか

MySQL
Jerome_1010Jerome_1010

文字コード 初期設定

mysql公式のmysql8のdocker imageを起動するとこんな感じの設定になっている

mysql> show variables like '%char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | latin1                         |
| character_set_connection | latin1                         |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | latin1                         |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+

mysql> SHOW VARIABLES LIKE 'collation\_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | latin1_swedish_ci  |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+

つらつらメモしていく

Jerome_1010Jerome_1010

このページが全体像を俯瞰して理解するのに役立つ https://dev.mysql.com/doc/refman/8.0/ja/charset-connection.html

デフォルト設定ではcharacter_set_client, character_set_connection , character_set_results が全てlatin1なので、クライアント - サーバー - DB間は全てlatin1でやりとりされているっぽい。
character_set_databaseがutf8mb4なのでDBに値を出し入れする際にlatin1とutf8mb4で変換がかかっていそう

Jerome_1010Jerome_1010

my.confにdefault-character-setとcharacter_set_connectionを設定すると

[mysql]
default-character-set=utf8mb4

[mysqld]
character_set_connection=utf8mb4

無事utf8mb4に揃う

+--------------------------+--------------------------------+
| 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-8.0/charsets/ |
+--------------------------+--------------------------------+

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+

collation_connectionをutf8mb4_0900_ai_ciにするにはcharacter_set_connectionを指定する必要があったので注意
参考: https://dev.mysql.com/doc/refman/8.0/ja/charset-connection.html

Jerome_1010Jerome_1010

文字コードsuffixの意味

https://dev.mysql.com/doc/refman/8.0/en/charset-collation-names.html

suffix meaning 要するに つまり
_ai Accent-insensitive アクセントを区別しない ぱ = は
_as Accent-sensitive アクセントを区別する ぱ ≠ は
_ci Case-insensitive 文字の大小を区別しない A = a
_cs Case-sensitive 文字の大小を区別しない A ≠ a
_ks Kana-sensitive かな・カナを区別する あ ≠ ア
_bin Binary バイナリで文字を比較する 基本全ての文字を区別する*(多分)

*PAD, NOPADの違いもあるので注意

Jerome_1010Jerome_1010

どのcollationを選ぶか

単純に考えるとより誤解のない厳密な動作をしそうな_bin系が良いのではと思ったがcollationの目的を考えるとそうでもなさそう。

クエリ

例えばウムラウト(éやüなど)文化圏のユーザー名が非ウムラウト文化圏のとあるサービスに登録されているケースを考えてみる。
そして、(これまた非ウムラウト文化圏)のサービスの運営者が、ユーザー名でクエリしたいとする。

運営者はウムラウトを意識しないだろうが、この時クエリ結果にウムラウトを区別した結果を返すべきなのか、そうでないべきなのかおそらく決めが必要な部分だと思う。ユースケースによるはずだ。

個人的にはcafeとcaféは区別してほしくないユーザーが多いかなと思うし、名前だと区別したいかなと思う。
改めてどのカラムにどんな文字列が入ってくるか、どう使いたいかなどユースケースと相談の部分だと感じた。

例え日本語ユーザー・運営者だけでもケアが必要な部分かなと思う。👇は極端な例。
https://ja.wikipedia.org/wiki/シークヮーサー#表記

ソート

https://dev.mysql.com/blog-archive/mysql-8-0-1-japanese-collation-for-utf8mb4/ にある通り漢字を適切に並べたい場合はja向けのcollationを使った方が良さそう