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

[[MySQL]コマンドだけで、HTML形式のテーブル定義書を作成する](http://qiita.com/mamy1326/items/c0aa9252d61ffed31a6e)
から派生した遊び心で、検証した内容です。

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

# 環境

|環境|ver|
|---|---|
|CentOS|7|
|MySQL|5.7.18|

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

```sql
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/ |
+--------------------------+----------------------------+
```

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

```ini:my.cnf
[mysqld]
character-set-server=utf8mb4

[mysql]
default-character-set=utf8mb4

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

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

```sql
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](https://twitter.com/tmtms) さんの
[MySQLの文字コード事情 2017春版](https://www.slideshare.net/tmtm/mysql-2017-76154739?ref=http://tmtms.hatenablog.com/entry/2017/05/21/myna_nagano)
に超絶詳しいです。ぜひご一読を。

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

```sql
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レコードあるよ！

```sql:テストデータ
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');
```

```sql: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)
```
リビドー丸出しな二人。

```sql: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());
```

```sql: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)
```
名は体を表す的な嗜好。

## 検証
ソートしてみる。

```sql
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)
```

## 結果
🍣 < 🍺 < 🍻

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