Edited at

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

More than 1 year has passed since last update.

[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)


結果

🍣 < 🍺 < 🍻

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