2018.09.13
以前調べた記事を参照。
よって、4バイトで表現される文字を、エンコーディングがutf8に設定されたデータベース(もしくはテーブル、もしくはカラム)に入れるとおかしなことになる。
MySQLサーバーにmysql
コマンドで接続する状況でテストする。
/etc/my.cnf
を編集して、MySQLサーバーとクライアントのエンコーディングをutf8
とutf8mb4
でそれぞれ以下の操作をする。
MySQLの設定を確認する。
[mysqld]
にはcharacter-set-server=utf8
が、
[client]
にはdefault-character-set=utf8
が書かれている。
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
character-set-server=utf8
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
default-character-set=utf8
MySQLサーバーに接続してみてステータスを確認すると、各characterset
がutf8
になっていることがわかる。
# MySQLのバージョンを確認
$ mysql -uroot
mysql> status
--------------
mysql Ver 14.14 Distrib 5.6.41, for Linux (x86_64) using EditLine wrapper
Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.41 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 33 min 3 sec
Threads: 1 Questions: 64 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.032
--------------
データベースとテーブルを作成して、レコードを追加してみる。
-- 注:「--」はコメントを表す
-- データベースを作成
mysql> CREATE DATABASE utf8_database;
Query OK, 1 row affected (0.00 sec)
-- 作成したデータベースを確認
-- 「DEFAULT CHARACTER SET utf8」という記述が見える。
mysql> SHOW CREATE DATABASE utf8_database;
+---------------+------------------------------------------------------------------------+
| Database | Create Database |
+---------------+------------------------------------------------------------------------+
| utf8_database | CREATE DATABASE `utf8_database` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+---------------+------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- データベースにテーブルを作成
mysql> CREATE TABLE `utf8_table` (id int NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.02 sec)
-- 作成したテーブルを確認
-- 「DEFAULT CHARSET=utf8」という記述が見える。
mysql> SHOW CREATE TABLE utf8_table;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| utf8_table | CREATE TABLE `utf8_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- テーブルにレコードを挿入
mysql> INSERT INTO utf8_table (name) values ('name01');
Query OK, 1 row affected (0.00 sec)
-- 挿入したレコードを確認
mysql> SELECT * FROM utf8_table;
+----+--------+
| id | name |
+----+--------+
| 1 | name01 |
+----+--------+
1 row in set (0.00 sec)
-- 16進数表現で確認
mysql> SELECT id, HEX(name) FROM utf8_table;
+----+--------------+
| id | HEX(name) |
+----+--------------+
| 1 | 6E616D653031 |
+----+--------------+
1 row in set (0.01 sec)
-- 4バイト文字を入れてみる
-- ターミナル上では、「?」は「\U+1F363」と表示された。
-- レコードの挿入はできず、エラーが発生した。
mysql> INSERT INTO utf8_table (name) values ('?');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> INSERT INTO utf8_table (name) values ('????')' at line 1
/etc/my.cnf
を編集してMySQLの設定を変更する。
MySQLサーバーの設定を変更したあとはMySQLサーバーの再起動が必要になる。
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#character-set-server=utf8 # ← この行をコメントアウトした!
character-set-server=utf8mb4 # ← この行を追加した!
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
#default-character-set=utf8 # ← この行をコメントアウトした!
default-character-set=utf8mb4 # ← この行を追加した!
MySQLサーバーを再起動する。
$ sudo systemctl restart mysqld
MySQLの設定を確認する。
--------------
mysql Ver 14.14 Distrib 5.6.41, for Linux (x86_64) using EditLine wrapper
Connection id: 2
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.41 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 3 min 11 sec
Threads: 1 Questions: 7 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.036
--------------
データベースとテーブルを作成して、レコードを追加してみる。
-- データベースを作成
mysql> CREATE DATABASE utf8mb4_database;
Query OK, 1 row affected (0.00 sec)
-- データベースを作成
mysql> CREATE DATABASE utf8mb4_database;
Query OK, 1 row affected (0.00 sec)
-- 作成したデータベースを確認
-- 「DEFAULT CHARACTER SET utf8mb4」という記述が見える。
mysql> SHOW CREATE DATABASE utf8mb4_database;
+------------------+------------------------------------------------------------------------------+
| Database | Create Database |
+------------------+------------------------------------------------------------------------------+
| utf8mb4_database | CREATE DATABASE `utf8mb4_database` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+------------------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- データベースにテーブルを作成
mysql> USE utf8mb4_table;
mysql> CREATE TABLE `utf8mb4_table` (id int NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.02 sec)
-- 作成したテーブルを確認
-- 「DEFAULT CHARSET=utf8mb4」という記述が見える。
mysql> SHOW CREATE TABLE utf8mb4_table;
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| utf8mb4_table | CREATE TABLE `utf8mb4_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
-- テーブルにレコードを挿入
mysql> INSERT INTO utf8mb4_table (name) values ('name01');
Query OK, 1 row affected (0.00 sec)
-- 挿入したレコードを確認
mysql> SELECT * FROM utf8mb4_table;
+----+--------+
| id | name |
+----+--------+
| 1 | name01 |
+----+--------+
1 row in set (0.00 sec)
-- 16進数表現で確認
mysql> SELECT id, HEX(name) FROM utf8mb4_table;
+----+--------------+
| id | HEX(name) |
+----+--------------+
| 1 | 6E616D653031 |
+----+--------------+
1 row in set (0.00 sec)
-- 4バイト文字を入れてみる
-- ターミナル上では、「?」は「\U+1F363」と表示された。
mysql> INSERT INTO utf8mb4_table (name) values ('?');
Query OK, 1 row affected (0.01 sec)
-- 挿入したレコードを確認
-- ちゃんとスシが入っている。
mysql> SELECT * FROM utf8mb4_table;
+----+--------+
| id | name |
+----+--------+
| 1 | name01 |
| 2 | ? |
+----+--------+
2 rows in set (0.00 sec)
-- 16進数表現で確認
mysql> SELECT id, HEX(name) FROM utf8mb4_table;
+----+--------------+
| id | HEX(name) |
+----+--------------+
| 1 | 6E616D653031 |
| 2 | F09F8DA3 |
+----+--------------+
2 rows in set (0.00 sec)
エンコーディングをutf8mb4
に設定して作成したデータベースのテーブルには、4バイトの文字をINSERTすることができた。
MySQLのエンコーディングの設定方法と実際の動作を確認することができた。
Ruby on Railsで何も考えずエンコーディングをutf8
にしてしまい、「絵文字を保存しようとするとエラーが出る!」と言われたときに表示されたエラーを再現することができた。
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> INSERT INTO utf8_table (name) values ('????')' at line 1
次は、
を調べていきたい。