MySQL + Python で utf8mb4 対応テーブルの挙動を確認する

## 概要
- MySQL に文字セット utf8mb4 に設定したテーブルを作成して、Python 3 からデータ操作(参照・追加・更新・削除)を試す
- 環境: GMOデジロックのレンタルサーバー「コアサーバー」 + MySQL 5.7 + Python 3.6 + mysqlclient (MySQLdb)

## MySQL のバージョンを確認

```
$ mysqld --version
mysqld  Ver 5.7.27 for Linux on x86_64 (MySQL Community Server (GPL))
```

## MySQL の文字セットと照合順序を確認

起動している MySQL デーモンの文字セットと照合順序の設定を MySQL monitor にて確認。

```
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
```

参考:

- [MySQL :: MySQL 5\.6 リファレンスマニュアル :: 5\.1\.4 サーバーシステム変数](https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html)
- [MySQL :: MySQL 5\.6 リファレンスマニュアル :: 10\.1\.4 接続文字セットおよび照合順序](https://dev.mysql.com/doc/refman/5.6/ja/charset-connection.html)
- [MySQL :: MySQL 5\.6 リファレンスマニュアル :: 10\.1\.14\.1 Unicode 文字セット](https://dev.mysql.com/doc/refman/5.6/ja/charset-unicode-sets.html)

## データベースを作成

MySQL monitor にてデータベースを作成。

```
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test_db            |
+--------------------+
2 rows in set (0.01 sec)

mysql> use test_db;
Database changed
```

参考:

- [MySQL :: MySQL 5\.6 リファレンスマニュアル :: 13\.1\.10 CREATE DATABASE 構文](https://dev.mysql.com/doc/refman/5.6/ja/create-database.html)

## テーブルを作成

テーブル作成用の SQL を書いてファイルに保存。

```create-table.sql 
CREATE TABLE IF NOT EXISTS test_table (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(256) NOT NULL,
  PRIMARY KEY (id)
)
ENGINE=InnoDB
CHARACTER SET utf8mb4
COLLATE utf8mb4_bin;
```

mysql コマンドでテーブルを作成。

```
$ mysql -h localhost -u alice -p test_db < create-table.sql
```

MySQL monitor にてテーブルを確認。

```
mysql> show tables from test_db;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_table        |
+-------------------+
1 row in set (0.00 sec)

mysql> show create table test_db.test_table;
+------------+-----------------------------------------------
| Table      | Create Table                                  
+------------+-----------------------------------------------
| test_table | CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+------------+-----------------------------------------------
1 row in set (0.00 sec)
```


参考:

- [MySQL :: MySQL 5\.6 リファレンスマニュアル :: 13\.1\.17 CREATE TABLE 構文](https://dev.mysql.com/doc/refman/5.6/ja/create-table.html)
- [MySQL :: MySQL 5\.6 リファレンスマニュアル :: 3\.5 バッチモードでの MySQL の使用](https://dev.mysql.com/doc/refman/5.6/ja/batch-mode.html)

## Python のバージョンを確認

```
$ python3 --version
Python 3.6.8
```

## Python のパッケージ mysqlclient をインストール

mysqlclient は Python 2 で使えるパッケージ MySQL-python (MySQLdb1) から派生して Python 3 に対応したパッケージ。

今回は mysqlclient のバージョン 1.3.4 を使う。

```
$ python3 -m pip install mysqlclient==1.3.4
```

今回の動作確認環境であるGMOデジロックのレンタルサーバー「コアサーバー」では該当バージョンがすでにインストール済みだった。

```
$ python3 -m pip list | grep mysqlclient
mysqlclient            1.3.4  
```

参考:

- [Python モジュールのインストール — Python 3\.6\.10rc1 ドキュメント](https://docs.python.org/ja/3.6/installing/)
- [GitHub \- PyMySQL/mysqlclient\-python: MySQL database connector for Python \(with Python 3 support\)](https://github.com/PyMySQL/mysqlclient-python)
- [GitHub \- farcepest/MySQLdb1: MySQL database connector for Python \(legacy version\)](https://github.com/farcepest/MySQLdb1)

## Python + mysqlclient でデータ操作

データを追加・取得・更新・削除する Python のソースコードを用意。

```crud.py
# mysqlclient を使う
import MySQLdb

# connect db
connection = MySQLdb.connect(host="localhost",
                             user="alice",
                             passwd="your-password",
                             db="test_db",
                             charset="utf8mb4")
cursor = connection.cursor(MySQLdb.cursors.DictCursor)

# insert
insert_sql = "INSERT INTO test_table (name) VALUES (%s)"
cursor.execute(insert_sql, ("寿司ビール🍣🍺𩸽🐟🐠🐡文字化けしないで",))
print(f"insert count={cursor.rowcount}")
connection.commit()

# select
select_sql = "SELECT id, name FROM test_table"
cursor.execute(select_sql)
print(f"select count={cursor.rowcount}")
for row in cursor:
  print(f"{row['id']}: {row['name']}")
  target_id = row["id"]

# update
update_sql = "UPDATE test_table set name=%s WHERE id=%s"
cursor.execute(update_sql, ("Alice", target_id))
print(f"update count={cursor.rowcount}")
connection.commit()

# delete
delete_sql = "DELETE FROM test_table WHERE id=%s"
cursor.execute(delete_sql, (target_id,))
print(f"delete count={cursor.rowcount}")
connection.commit()

connection.close()
```

実行結果。
寿司ビール絵文字などが文字化けせずに追加・取得・表示できている。

```
$ python3 crud.py 
insert count=1
select count=1
1: 寿司ビール🍣🍺𩸽🐟🐠🐡文字化けしないで
update count=1
delete count=1
```

参考:

- [Welcome to MySQLdb’s documentation\! — MySQLdb 1\.2\.4b4 documentation](https://mysqlclient.readthedocs.io/)
- [【Python3】MySQL 操作をひと通りマスター！導入方法とCRUDサンプルコード集 \| ITエンジニアラボ](https://it-engineer-lab.com/archives/1181)
- [Python3でMySQLを使う – 基本操作からエラー処理までサンプルコード付 \| Crane & to\.](https://www.craneto.co.jp/archives/1219/)

## テーブルとデータベースを削除

MySQL monitor から使い終わったテーブルとデータベースを削除。

```
mysql> drop table test_db.test_table;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database test_db;
Query OK, 0 rows affected (0.00 sec)
```