[MySQL] 文字コード問題 -みんな大好き寿司ビール🍣🍺-

[MySQL]コマンドだけで、HTML形式のテーブル定義書を作成する
から派生した遊び心で、検証した内容です。

遊び心

単純に定義書を出してもいいのですが、ちょっと遊び心で、🍣🍺問題と、foreign keyの理解も含めてサンプルデータで確認してみました。

環境

環境	ver
CentOS	7
MySQL	5.7.18

文字コード設定

MySQLインストール後、ちゃんと設定しないと、文字コードは以下になっていたります。(一例)

mysql> show variables like "chara%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

それを以下のように設定します。

my.cnf

[mysqld]
character-set-server=utf8mb4

[mysql]
default-character-set=utf8mb4

[client]
loose-default-character-set=utf8mb4

mysql再起動で、以下になります。
ここを揃えておくのは非常に重要です。
サーバー、クライアント、データベース、テーブル、カラム、など、文字コードは粒度が低くなっても個別に設定ができちゃいます。
闇しかありませんので、最上位で統一しておきましょう。

mysql> show variables like "chara%";
+--------------------------+----------------------------+
| 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     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

参考情報

文字コード事情は、@tmtms さんの
MySQLの文字コード事情 2017春版
に超絶詳しいです。ぜひご一読を。

テーブル

ユーザーとその嗜好を保持するテーブルを作りました。

mysql> CREATE DATABASE mamy1326;
Query OK, 1 row affected (0.00 sec)

mysql> USE mamy1326;
Database changed

mysql> CREATE TABLE m_user (
 user_id bigint unsigned NOT NULL AUTO_INCREMENT,
 family_name varbinary(100) NOT NULL COMMENT '姓',
 first_name varbinary(100) NOT NULL COMMENT '名',
 birth_day date NOT NULL COMMENT '生年月日',
 PRIMARY KEY (user_id)
) COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE wishlist (
 wishlist_id bigint unsigned NOT NULL AUTO_INCREMENT,
 user_id bigint unsigned NOT NULL COMMENT 'ユーザーID',
 item_name varbinary(100) NOT NULL COMMENT '欲しい物の名前',
 created_at datetime NOT NULL COMMENT '作成日',
 FOREIGN KEY(user_id) REFERENCES m_user(user_id),
 PRIMARY KEY (wishlist_id)
) COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.02 sec)

データ

みんな大好き🍣🍺
🍻もあるから2レコードあるよ！

テストデータ

insert into m_user (user_id, family_name, first_name, birth_day) value (1,'食べたい','寿司','1973-11-15');
insert into m_user (user_id, family_name, first_name, birth_day) value (2,'飲みたい','ビール','1973-11-15');

m_user

mysql> select * from m_user;
+---------+--------------+------------+------------+
| user_id | family_name  | first_name | birth_day  |
+---------+--------------+------------+------------+
|       1 | 食べたい     | 寿司       | 1979-01-01 |
|       2 | 飲みたい     | ビール     | 1979-01-01 |
+---------+--------------+------------+------------+
2 rows in set (0.00 sec)

リビドー丸出しな二人。

wishlistテストデータ

insert into wishlist (wishlist_id, user_id, item_name, created_at) value (1, 1, '🍣', sysdate());
insert into wishlist (wishlist_id, user_id, item_name, created_at) value (2, 2, '🍺', sysdate());
insert into wishlist (wishlist_id, user_id, item_name, created_at) value (3, 2, '🍻', sysdate());

wishlist

mysql> select *,HEX(WEIGHT_STRING(item_name)) from wishlist;
+-------------+---------+-----------+---------------------+-------------------------------+
| wishlist_id | user_id | item_name | created_at          | HEX(WEIGHT_STRING(item_name)) |
+-------------+---------+-----------+---------------------+-------------------------------+
|           1 |       1 | 🍣          | 2017-04-26 15:37:57 | F09F8DA3                      |
|           2 |       2 | 🍺          | 2017-04-26 15:38:09 | F09F8DBA                      |
|           3 |       2 | 🍻          | 2017-04-26 15:40:24 | F09F8DBB                      |
+-------------+---------+-----------+---------------------+-------------------------------+
3 rows in set (0.00 sec)

名は体を表す的な嗜好。

検証

ソートしてみる。

mysql> select *,HEX(WEIGHT_STRING(item_name)) from wishlist order by item_name;
+-------------+---------+-----------+---------------------+-------------------------------+
| wishlist_id | user_id | item_name | created_at          | HEX(WEIGHT_STRING(item_name)) |
+-------------+---------+-----------+---------------------+-------------------------------+
|           1 |       1 | 🍣          | 2017-04-26 15:37:57 | F09F8DA3                      |
|           2 |       2 | 🍺          | 2017-04-26 15:38:09 | F09F8DBA                      |
|           3 |       2 | 🍻          | 2017-04-26 15:40:24 | F09F8DBB                      |
+-------------+---------+-----------+---------------------+-------------------------------+
3 rows in set (0.00 sec)

mysql> select *,HEX(WEIGHT_STRING(item_name)) from wishlist order by item_name desc;
+-------------+---------+-----------+---------------------+-------------------------------+
| wishlist_id | user_id | item_name | created_at          | HEX(WEIGHT_STRING(item_name)) |
+-------------+---------+-----------+---------------------+-------------------------------+
|           3 |       2 | 🍻          | 2017-04-26 15:40:24 | F09F8DBB                      |
|           2 |       2 | 🍺          | 2017-04-26 15:38:09 | F09F8DBA                      |
|           1 |       1 | 🍣          | 2017-04-26 15:37:57 | F09F8DA3                      |
+-------------+---------+-----------+---------------------+-------------------------------+
3 rows in set (0.00 sec)

結果

🍣 < 🍺 < 🍻

間違いなどありましたらご指摘くださいませ！！！🍣🍺🍻