PyMySQLのcursorclassについて

PyMySQLの公式ドキュメントのExamplesで使われている、
cursorclass=pymysql.cursors.DictCursor
の話です。


import pymysql.cursors

# Connect to the database
connection = pymysql.connect(
    host='localhost',
    user='user',
    password='passwd',
    database='db',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor # これの話。
)

ドキュメントにも詳しい説明はないし、pep249でも言及されていないようなのでPyMySQLのソースコードも含めて調べてみました。

結論から言えば、実用上は黙って cursorclass=pymysql.cursors.DictCursor を指定しておけばよく、この記事は無駄知識の類のものになります。

まず、cursorclass に指定できる値は pymysql.cursors.DictCursor 以外に何があるのかですが、
これは、こちらのファイルで定義されている4種類のクラスが指定できます。
Github: pymysql/cursors.py

– pymysql.cursors.Cursor
– pymysql.cursors.DictCursor
– pymysql.cursors.SSCursor
– pymysql.cursors.SSDictCursor

https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/connections.py#L179
に、


class Connection:
    # 中略
    def __init__(
        # 中略
        cursorclass=Cursor,

とある通り、デフォルトは、 pymysql.cursors.Cursor です。

この4種類のカーソルの違いですが、
Cursor と SSCursor は結果をタプルで返し、
DictCursor と SSDictCursor は結果を辞書で返してくれます。
結果の形に、CursorとSSCursor、DictCursorとSSDictCursorの間にはそれぞれ違いはありません。

SSとつく二つの方ですが、これらは主にデータが非常に大きいときや、ネットワークが遅いときなどに使います。
SSCursorのコメントがわかりやすいですね。

Unbuffered Cursor, mainly useful for queries that return a lot of data,
or for connections to remote servers over a slow network.
Instead of copying every row of data into a buffer, this will fetch
rows as needed. The upside of this is the client uses much less memory,
and rows are returned much faster when traveling over a slow network
or if the result set is very big.
There are limitations, though. The MySQL protocol doesn’t support
returning the total number of rows, so the only way to tell how many rows
there are is to iterate over every row returned. Also, it currently isn’t
possible to scroll backwards, as only the current row is held in memory.

個人的な感想としては最近の端末には十分なメモリが搭載されていて、数百万行単位のレコードを扱うときも、SS無しの方で十分さばけているので、
とりあえず DictCursor を使って、本当にメモリ不足で困った時だけ SSDictCursor を検討したらいいのかなと思っています。

Cursor(タプル) と DictCursor(辞書)についてはそれぞれ実行して結果を比較しておきましょう。

まず、cursorclassにpymysql.cursors.Cursorを指定した(もしくは何も指定しなかった場合)の結果です。
テーブルは以前の記事で作ったやつをそのまま使います。


with connection.cursor() as cursor:
        sql = "SELECT id, email, password FROM users"
        cursor.execute(sql)
        result = cursor.fetchall()
print(result)
"""
(
    (1, 'webmaster@python.org', 'very-secret'),
    (2, 'sato@python.org', 'very-secret'),
    (3, 'suzuki@python.org', 'very-secret'),
    (4, 'takahashi@python.org', 'very-secret'),
    (5, 'tanaka@python.org', 'very-secret')
)
"""

1レコードごとに結果がタプルで戻っているだけでなく、fetchallすると戻り値はタプルのタプルになっていますね。
戻ってきた結果の各値が、SELECT句のどの列の値なのかが明示されていないので、自分でマッピングする必要があります。
正直これは少し使いにくいです。

続いて、pymysql.cursors.DictCursor を指定した場合の結果です。


with connection.cursor() as cursor:
        sql = "SELECT id, email, password FROM users"
        cursor.execute(sql)
        result = cursor.fetchall()
print(result)
"""
[
    {'id': 1, 'email': 'webmaster@python.org', 'password': 'very-secret'},
    {'id': 2, 'email': 'sato@python.org', 'password': 'very-secret'},
    {'id': 3, 'email': 'suzuki@python.org', 'password': 'very-secret'},
    {'id': 4, 'email': 'takahashi@python.org', 'password': 'very-secret'},
    {'id': 5, 'email': 'tanaka@python.org', 'password': 'very-secret'}
]
"""

ご覧の通り、1レコードごとに「列名:値」の辞書として値が得られ、それらの配列として結果が返されます。
各値がSELECT句のどの列のものなのかはっきりしているのでとても便利です。
また、このままpandasのデータフレームに変換することもできます。
通常はこれを使えば良いでしょう。

PyMySQLでまとめてデータをインサートする(executemanyを使う方法)

※この記事ではDBはAWSのAuroraを想定しています。

Auroraに多くのデータをまとめて登録したいとき、1レコードごとにINSERT文を書いて実行するのは少しイケてない思っていたので、
良い方法を探していたところ、 executemany というメソッドが用意されているのを知りました。
pep249 にも記載があるので、PyMySQL以外のライブラリでも用意されていると思います。

サンプルコード載せるに状況の説明です。
INSERTしたいデータは例えば次のようなものだとします。


print(df)
"""
           date      open      high       low     close
0    2020-07-08  22481.61  22667.95  22438.65  22438.65
1    2020-07-09  22442.30  22679.08  22434.38  22529.29
2    2020-07-10  22534.97  22563.68  22285.07  22290.81
3    2020-07-13  22591.81  22784.74  22561.47  22784.74
4    2020-07-14  22631.87  22677.02  22538.78  22587.01
..          ...       ...       ...       ...       ...
195  2021-04-23  28939.12  29035.34  28770.62  29020.63
196  2021-04-26  29095.49  29241.28  28896.37  29126.23
197  2021-04-27  29174.53  29187.11  28990.19  28991.89
198  2021-04-28  28935.51  29139.70  28875.91  29053.97
199  2021-04-30  28996.66  29046.49  28760.27  28812.63

[200 rows x 5 columns]
"""

そして、INSERT先のテーブルは以下です。
(connection は既にRDSへの接続が完了しているものとします。)


with connection.cursor() as cursor:
    cursor.execute("SHOW CREATE TABLE nikkei")
    result = cursor.fetchone()
    
print(result["Create Table"])
"""
CREATE TABLE `nikkei` (
  `date` date NOT NULL,
  `open` double NOT NULL,
  `high` double NOT NULL,
  `low` double NOT NULL,
  `close` double NOT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
"""

通常であれば、この200あるレコード一つ一つについて、for文で回して、それぞれINSERT文を発行するところなのですが、
cursor.executemany を使うと次のように(表向きは)一発でINSERTできます。
(おそらく内部では1レコードずつINSERT文が発行されているのですが。)


insert_sql = """
    INSERT INTO
        nikkei (
            date,
            open,
            high,
            low,
            close
        )
    VALUES
        (
            %s, %s, %s, %s, %s
        )
"""

with connection.cursor() as cursor:
    cursor.executemany(insert_sql, df.values.tolist())
    connection.commit()

PyMySQLではプレースホルダにformat(%s)とpyformat(%{name}s)を使える

タイトルを逆にいうとPyMySQLのプレースホルダには qmark(?), numeric(:1), named(:name) は実装されてないようです。

※ この記事は、 version 1.0.2 の時点の PyMySQLについて書いています。将来的に他のプレースホルダーも実装されるかもしれません。


!pip freeze | grep PyMySQL
PyMySQL==1.0.2

pep249では、paramstyle として次の5つが定められています。

名前 説明
qmark Question mark style WHERE name=?
numeric Numeric, positional style WHERE name=:1
named Named style WHERE name=:name
format ANSI C printf format codes WHERE name=%s
pyformat Python extended format codes WHERE name=%(name)s

PyMySQLはpep249に従って実装さているので、この5つが全部使えるのかなと思って試したら、そうなってないことに気づきました。
タイトルにも書きました通り、実装されているのは format と pyformat だけです。
とはいえ、実用的なことを考えると、各値に名前をつけないときは、format、名前をつけたいときはpyformatを使えばいいので、この二つで十分だと思います。

実際にやってみます。
テーブルは前の記事で作ったものをそのまま使います。

まずDBに接続します。接続情報は各自の環境の値をご利用ください。


import pymysql.cursors

con_args = {
    "host": "{RDSのエンドポイント/同サーバーのDB場合はlocalhost}",
    "user": "{ユーザー名}",
    "password": "{パスワード}",
    "database": "{DB名}",
    "cursorclass": pymysql.cursors.DictCursor
}

connection = pymysql.connect(**con_args)

まず、 format スタイルから。サンプルコードなどで頻繁に見かけるのはこれです。


with connection.cursor() as cursor:
        sql = "SELECT id, email, password FROM users WHERE email=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)

# {'id': 1, 'email': 'webmaster@python.org', 'password': 'very-secret'}

続いて、 pyformat。 このサンプルコードではありがたみがないですが、多くのプレースホルダーを使うクエリでは可読性向上に期待できます。


with connection.cursor() as cursor:
        sql = "SELECT id, email, password FROM users WHERE email=%(email)s"
        cursor.execute(sql, {"email": "webmaster@python.org"})
        result = cursor.fetchone()
        print(result)

# {'id': 1, 'email': 'webmaster@python.org', 'password': 'very-secret'}

qmark だと cursor.execute でエラーになります。 (エラーになるので例外処理入れてます。)


with connection.cursor() as cursor:
        sql = "SELECT id, email, password FROM users WHERE email=?"
        try:
            cursor.execute(sql, ('webmaster@python.org',))
            result = cursor.fetchone()
            print(result)
        except Exception as e:
            print(e)
            # not all arguments converted during string formatting

numeric も同様。


with connection.cursor() as cursor:
        sql = "SELECT id, email, password FROM users WHERE email=:1"
        try:
            cursor.execute(sql, ('webmaster@python.org',))
            result = cursor.fetchone()
            print(result)
        except Exception as e:
            print(e)
            # not all arguments converted during string formatting

named もエラーになります。これだけエラー文が違います。


with connection.cursor() as cursor:
        try:
            sql = "SELECT id, email, password FROM users WHERE email=:email"
            cursor.execute(sql, {"email": "webmaster@python.org"})
            result = cursor.fetchone()
            print(result)
        except Exception as e:
            print(e)
            # (1064, "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 ':email' at line 1")

PythonでAuroraを操作する(PyMySQLを使う方法)

今回の記事では、PythonのコードでSQLを実行しAuroraを操作する方法を紹介します。
AuroraはMySQL互換のインスタンスを使っているので、PythonのライブラリはMySQL用のものを利用します。
複数種類あるのですが、僕はPyMySQLを利用することが多いです。
(職場で先輩が使ってたというだけの理由で使い続けており、他のライブラリと比較検証したわけじゃないので、
いつかちゃんとメリットデメリット比較したいです。)

PyMySQL のドキュメントはこちら
PyMySQL · PyPI
前回の記事でも書きましたが、サンプルコードが軽く記載してあるだけで、あまり詳しい説明などはありません。
pep249を必要に応じて参照する必要があります。

といっても、使い方は簡単なので、ドキュメントのサンプルコードだけあれば実用上はなんとか使っていけます。
(DBヘの接続、SQLの実行と結果の取り出しができれば十分。)

この記事では、サンプルコードを参考に、最低限の操作をやってみようと思います。

まず、DBにサンプルのTableを作っておきます。


> CREATE TABLE users (
    id int(11) NOT NULL AUTO_INCREMENT,
    email varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
);

Query OK, 0 rows affected (0.05 sec)

まず、DBへの接続です。
DBのエンドポイント/ユーザー名/パスワード/DB名 をそれぞれ設定し、接続をします。
cursorclass=pymysql.cursors.DictCursor を設定するのは、結果をDict形で受け取るためです。
(これは必須ではないのですが、Dictで受け取った方が後々扱いやすいです。)
{}で書いてる部分は各自の環境の値を入れてください。


import pymysql.cursors


con_args = {
    "host": "{RDSのエンドポイント/同サーバーのDB場合はlocalhost}",
    "user": "{ユーザー名}",
    "password": "{パスワード}",
    "database": "{DB名}",
    "cursorclass": pymysql.cursors.DictCursor
}

connection = pymysql.connect(**con_args)

新規レコードを挿入してみます。
SQLインジェクション対策にプレースホルダ(%s)を使います。
自動コミットではないので、 connection.commit() を忘れないようにするのがポイントです。


with connection.cursor() as cursor:
    sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
    cursor.execute(sql, ('webmaster@python.org', 'very-secret'))
    connection.commit()

次はSELECT文です。 1レコード取り出すサンプルコードは次のようになります。


with connection.cursor() as cursor:
    sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
    cursor.execute(sql, ('webmaster@python.org',))
    result = cursor.fetchone()
    print(result)

# 結果
# {'id': 1, 'password': 'very-secret'}

ドキュメントのサンプルコードはこれだけですね。

ほとんど代わり映えしないのですが、結果が複数行になるSELECT文も試しておきましょう。
まずレコード自体が1行しかないとどうしようもないのでデータを増やします。


with connection.cursor() as cursor:
    sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
    cursor.execute(sql, ('sato@python.org', 'very-secret'))
    cursor.execute(sql, ('suzuki@python.org', 'very-secret'))
    cursor.execute(sql, ('takahashi@python.org', 'very-secret'))
    cursor.execute(sql, ('tanaka@python.org', 'very-secret'))
    connection.commit()

結果が複数行になる場合は、fetchone()の代わりに、fetchall()を使います。


with connection.cursor() as cursor:
        sql = "SELECT id, email FROM users"
        cursor.execute(sql)
        result = cursor.fetchall()
        print(result)

# [{'id': 1, 'email': 'webmaster@python.org'}, {'id': 2, 'email': 'sato@python.org'}, 
# {'id': 3, 'email': 'suzuki@python.org'}, {'id': 4, 'email': 'takahashi@python.org'},
# {'id': 5, 'email': 'tanaka@python.org'}]

ちなみにこの結果の型ですが、pandasのDataFrameに簡単に変換できるので便利です。


import pandas as pd


df = pd.DataFrame(result)
print(df)
"""
   id                 email
0   1  webmaster@python.org
1   2       sato@python.org
2   3     suzuki@python.org
3   4  takahashi@python.org
4   5     tanaka@python.org
"""

もちろんfetchoneを繰り返して1行ずつ取り出すこともできるのですが、個人的にはfetchallでさっさと取り出して
データフレームにしてしまった方が扱いやすいと思います。
fetchoneで順番に取り出すとしたら次のようなコードでしょうか。


with connection.cursor() as cursor:
        sql = "SELECT id, email FROM users"
        cursor.execute(sql)

row = cursor.fetchone()
while row is not None:
    print(row)
    row = cursor.fetchone()
"""
{'id': 1, 'email': 'webmaster@python.org'}
{'id': 2, 'email': 'sato@python.org'}
{'id': 3, 'email': 'suzuki@python.org'}
{'id': 4, 'email': 'takahashi@python.org'}
{'id': 5, 'email': 'tanaka@python.org'}
"""

次のような書き方でも動きます。


with connection.cursor() as cursor:
    sql = "SELECT id, email FROM users"
    cursor.execute(sql)

for row in cursor:
    print(row)
    
"""
{'id': 1, 'email': 'webmaster@python.org'}
{'id': 2, 'email': 'sato@python.org'}
{'id': 3, 'email': 'suzuki@python.org'}
{'id': 4, 'email': 'takahashi@python.org'}
{'id': 5, 'email': 'tanaka@python.org'}
"""

Pythonの DB-API 2.0 (PEP 249)について

PEP 249 — Python Database API Specification v2.0と言うものがあることを最近知ったのでその紹介です。

お恥ずかしい話ですが正直に言うと、これまでPython経由でデータベースを操作するコードを書くときは、
誰かが既に書いたコードや本のサンプルなどを参照して、DBヘの接続やSQLの実行部分はほとんどコピーして使っていました。

頻繁に実施する処理ではあるので、このブログにもまとめておこうと思い、
ブログ記事にするからには仕様を正しく理解してから書きたいと考えて調べてたら行きあたったのがこのPEP249です。

これは何かというと、リレーショナルデータベースにアクセスする機能を提供する
Pythonモジュールが似たような作りになるようにAPI仕様を定義したものです。

PEP249のページでも
This API has been defined to encourage similarity between the Python modules that are used to access databases.
と書いてありますね。

なぜこんなものが必要になるかというと、RDBには多くの種類があり、そしてそれぞれのRDBごとにそれを利用するモジュールが作られたとして、
それらが全部バラバラに仕様を決めて実装されていたら、使いにくいし、もし別のDBやモジュールに移行したくなったときに使い勝手が違うと大変だからです。

そこで、PEP249 (DB-API 2.0)として、DB接続や、SQLの実行、結果の取り出しなどのAPIの仕様を定めてくれています。
実際、多くのモジュールがこれに沿って実装されているようです。

PEP249 のページを読んでいくと、DBの接続に使う connect メソッドや、 Connection Object、
SQLを実行するexecuteメソッドや、結果を取り出す、fetchone/ fetchall など見慣れた(コピペで使ってた)メソッド群の説明が書いてあります。

PyMySQL などのドキュメントを見てると、
サンプルコードが少し書いてあるだけで、各メソッドの説明がほとんどなく非常に不親切だなと前々から思っていたのですが、
PEP249に沿って実装されているのであれば話は別です。
これらのモジュールはPEP249を読んで使うものだったのですね。

テーブルに自動採番(オートインクリメント)する列を作成する

今回もDBの話です。DBはMySQLやその互換のAuroraを想定しています。
テーブルに行を追加していくとき、自動的に通し番号でIDをふって欲しいことはよくあります。
MySQLにはそれ専用の、AUTO_INCREMENT属性があります。

参考: 3.6.9 AUTO_INCREMENT の使用

さて、CREATE TABLEするときに列名の横に、 AUTO_INCREMENT をつけるだけで使えるように見えますが、それだとエラーになります。


> CREATE TABLE
    sample_table1 (
        id INTEGER AUTO_INCREMENT,
        name VARCHAR(255)
    );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

it must be defined as a key とある通り、主キーかユニークキーを設定した列でないと、AUTO_INCREMENT属性は設定できません。
ついでに説明しておくと、 there can be only one auto columnとある通り、1テーブルに設定できるのは1列までです。


> CREATE TABLE
    sample_table1 (
        id INTEGER PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(255)
    );
Query OK, 0 rows affected (0.06 sec)

さて、これで連番が振られることを見ておきましょう。


> INSERT INTO 
    sample_table1 (name)
VALUES
   ('suzuki'), ('sato'), ('tanaka');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

> SELECT * FROM sample_table1;
+----+--------+
| id | name   |
+----+--------+
|  1 | suzuki |
|  2 | sato   |
|  3 | tanaka |
+----+--------+
3 rows in set (0.01 sec)

連番が振られていますね。

僕はこれ、列の最大値の次の値が採番されていくと勘違いしていたのですが、実はAUTO_INCREMENTで振られる番号は別のところに保存されています。


> SHOW CREATE TABLE sample_table1\G
*************************** 1. row ***************************
       Table: sample_table1
Create Table: CREATE TABLE `sample_table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

AUTO_INCREMENT=4 と入っているように、テーブルが属性として次の番号を持っています。
そのため、今入っているレコードを消して、新たに行を追加すると、4番から採番されます。


> DELETE FROM sample_table1 WHERE id >= 2;
Query OK, 2 rows affected (0.02 sec)

> INSERT INTO 
    sample_table1 (name)
VALUES
   ('kobayashi'), ('ito'), ('takahashi');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

> SELECT * FROM sample_table1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | suzuki    |
|  4 | kobayashi |
|  5 | ito       |
|  6 | takahashi |
+----+-----------+
4 rows in set (0.01 sec)

ユニーク性を担保する上では便利な仕組みですね。

テーブルの属性としてカウンタを持っているので、 ALTER TABLE 文を使ってリセットしたり、任意の値に設定したりできます。
構文はこうです。


ALTER TABLE [テーブル名] AUTO_INCREMENT = [値];

やってみます。


> ALTER TABLE sample_table1 AUTO_INCREMENT = 100;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

> INSERT INTO 
    sample_table1 (name)
VALUES
   ('watanabe');
Query OK, 1 row affected (0.01 sec)

> SELECT * FROM sample_table1;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | suzuki    |
|   4 | kobayashi |
|   5 | ito       |
|   6 | takahashi |
| 100 | watanabe  |
+-----+-----------+
5 rows in set (0.00 sec)

予想通りに動きました。

MySQLでテーブルに主キー(PRIMARY KEY)制約を設定する

主キー(プライマリーキー)の説明自体はWikipediaを参照。
参考: 主キー – Wikipedia

これをSQLで設定する方法について紹介します。
(なお、DBは MySQL やその互換のAuroraを想定しています。)

新規に作成するテーブルに設定するときは次の構文で設定できます。
参考: 13.1.7 ALTER TABLE 構文


CREATE TABLE
    [テーブル名] (
        [主キーの列名] [データ型] NOT NULL PRIMARY KEY,
        [列名1] [データ型],
        ・・・
    );

NOT NULL は省略可能です。省略しても主キーには自動的にNOT NULL制約が付与されます。

やってみましょう。


>CREATE TABLE
    sample_table1 (
        id INTEGER PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        comment VARCHAR(255)
    );
Query OK, 0 rows affected (0.19 sec)

# 確認
> DESCRIBE sample_table1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(255) | NO   |     | NULL    |       |
| comment | varchar(255) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

NOT NULL は省略しましたが、DESCRIBE の結果でわかる通り、id列にNOT NULL制約が入っていますね。

複数列の組み合わせで主キーを設定する場合は、次のように
PRIMARY KEY ([列名], [列名]) と言うのを書いても設定できます。

構文のイメージはこうです。もちろんですが、複数列の組み合わせでなく1列に対して設定する時もこの構文は使えます。


CREATE TABLE
    [テーブル名] (
        [主キーの列名1] [データ型] NOT NULL,
        [主キーの列名2] [データ型] NOT NULL,
        [列名1] [データ型],
        ・・・,
        PRIMARY KEY ([主キーの列名1], [主キーの列名2])
    );

例えば、 コード と 日付 に対してユニークなレコードを持つテーブルを作る場合は次のようになります。


>CREATE TABLE
    sample_table2 (
        code INTEGER,
        date DATE,
        value INTEGER,
        PRIMARY KEY (code, date)
    );
Query OK, 0 rows affected (0.07 sec)

> DESCRIBE sample_table2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| code  | int(11) | NO   | PRI | NULL    |       |
| date  | date    | NO   | PRI | NULL    |       |
| value | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

主キーを設定した列に対しては自動的にインデックスが作成されます。(インデックスの名前はPRIMARYになります)。
これも一応みておきましょう。


> SHOW INDEX FROM sample_table2\G
*************************** 1. row ***************************
        Table: sample_table2
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: code
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: sample_table2
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 2
  Column_name: date
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

あまり使わないのですが、設定されている主キーを外すときは、 ALTER TABLE 文を使います。

構文は次の通りです。
参考: 13.1.7 ALTER TABLE 構文


ALTER TABLE [テーブル名] DROP PRIMARY KEY;

やってみます。


> ALTER TABLE sample_table2 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 結果確認
> DESCRIBE sample_table2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| code  | int(11) | NO   |     | NULL    |       |
| date  | date    | NO   |     | NULL    |       |
| value | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# インデックスも消える
> SHOW INDEX FROM sample_table2\G
Empty set (0.00 sec)

インデックスは一緒に消えてしまいますが、 NOT NULL制約は残りましたね。

主キーが設定されてないテーブルに後から設定するのも ALTER TABLE 文を使います。


ALTER TABLE [テーブル名] ADD PRIMARY KEY ([列名]);

やってみます。(さっき消したキーを復活させます。)


> ALTER TABLE sample_table2 ADD PRIMARY KEY (code, date);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 結果確認
> DESCRIBE sample_table2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| code  | int(11) | NO   | PRI | NULL    |       |
| date  | date    | NO   | PRI | NULL    |       |
| value | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MySQLのテーブルにレコード作成時刻や更新時刻を自動で記録する列を作る

たまに新しいテーブルを作るたびに調べてるのでメモです。
(タイトルにMySQLと書いてますが、実際はそれと互換のAuroraでやってます。)

DBのテーブルに新しい行を追加したり、更新したりするときにその時刻を記録する列を用意しておくと言うのは結構一般的なことだと思います。
phpMyAdmin からGUIでポチポチ設定してもいいのですが、SQLでもできた方が楽です。

さて、その方法ですが、MySQLのドキュメントにそのまま書いてあります。
11.3.5 TIMESTAMP および DATETIME の自動初期化および更新機能

DEFAULT CURRENT_TIMESTAMP で、行作成時にタイムスタンプが記録され、
ON UPDATE CURRENT_TIMESTAMP で、行更新時にタイムスタンプも更新されます。

列名として、 created_at, updated_at を使う場合は次のようにすれば良いでしょう。
例えば、タイムスタンプの他にidとnameを格納するテーブルを作るとしたら次のようになります。
(ついでに NOT NULL制約も入れました。)


CREATE TABLE
    sample_table (
        id INTEGER,
        name VARCHAR(255),
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );

さて、試してみましょう。まず作成時刻から。


INSERT INTO
    sample_table(id, name)
VALUES
    (1, 'テスト1'),
    (2, 'テスト2'),
    (3, 'テスト3')
;

SELECT * FROM sample_table;
+------+------------+---------------------+---------------------+
| id   | name       | created_at          | updated_at          |
+------+------------+---------------------+---------------------+
|    1 | テスト1    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
|    2 | テスト2    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
|    3 | テスト3    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
+------+------------+---------------------+---------------------+
3 rows in set (0.00 sec)

時刻が勝手に入りましたね。

次に更新です。


UPDATE
    sample_table
SET
    name = 'test 2' 
WHERE
    id = 2
;

SELECT * FROM sample_table;
+------+------------+---------------------+---------------------+
| id   | name       | created_at          | updated_at          |
+------+------------+---------------------+---------------------+
|    1 | テスト1    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
|    2 | test 2     | 2021-04-04 23:36:29 | 2021-04-04 23:38:26 |
|    3 | テスト3    | 2021-04-04 23:36:29 | 2021-04-04 23:36:29 |
+------+------------+---------------------+---------------------+
3 rows in set (0.01 sec)

id = 2 のレコードの updated_at も更新されました。

SQLでデータベースやテーブルの文字コードや照合順序の設定を変える

前回の記事でRDSのクラスタ単位の文字コードや照合順序をutf8mb4に対応させる方法を書きましたが、
後から設定を変えても既存のデータベースやテーブルの設定はそのまま変わりません。

変更する場合はそれぞれ ALTER 文を実行して変更していく必要があります。

まず、テーブルの設定を変更する場合は、次の構文を使います。

ALTER TABLE tbl_name
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
(参考: 13.1.7 ALTER TABLE 構文)


-- 元の設定を確認
MySQL [sample_db]> SHOW CREATE TABLE sample_table\G
*************************** 1. row ***************************
       Table: sample_table
Create Table: CREATE TABLE `sample_table` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `text` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-- 設定を変更
MySQL [sample_db]> ALTER TABLE sample_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 変更結果を確認
MySQL [sample_db]> SHOW CREATE TABLE sample_table\G
*************************** 1. row ***************************
       Table: sample_table
Create Table: CREATE TABLE `sample_table` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `text` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)

ちなみに、 “CONVERT TO” をつけ忘れると、テーブル全体のデフォルト設定だけ変更されて、列の設定が元のままになります。
(列ごとに、個別の文字コード設定を行いたい場合はこちらの方法を使うことになりそうです。)


-- 元の設定を確認
MySQL [sample_db]> SHOW CREATE TABLE sample_table2\G
*************************** 1. row ***************************
       Table: sample_table2
Create Table: CREATE TABLE `sample_table2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `text` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-- 設定を変更 (CONVERT TO 無し)
MySQL [sample_db]> ALTER TABLE sample_table2 CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 変更結果を確認
MySQL [sample_db]> SHOW CREATE TABLE sample_table2\G
*************************** 1. row ***************************
       Table: sample_table2
Create Table: CREATE TABLE `sample_table2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `text` varchar(255) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

各列のCHARACTERが latin1 のままですね。

続いて、データベースのデフォルトの文字コードと照合順序の設定の変更方法です。
新規作成したテーブルはデータベースのデフォルト設定を引き継ぐので、多くのテーブルを作る場合は、最初にデータベース単位で設定しておくべきでしょう。

DATABSE単位の設定は文字コード周りくらいしかないので、 ALTER DATABASE のドキュメントもシンプルです。

参考: 13.1.1 ALTER DATABASE 構文

ALTER DATABASE sample_db CHARACTER SET utf8mb4 COLLATE utf8mb4_bin


-- 元の設定を確認
MySQL [sample_db]> SHOW CREATE DATABASE sample_db;
+-----------+----------------------------------------------------------------------+
| Database  | Create Database                                                      |
+-----------+----------------------------------------------------------------------+
| sample_db | CREATE DATABASE `sample_db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+-----------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 設定を変更 
MySQL [sample_db]> ALTER DATABASE sample_db CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 1 row affected (0.03 sec)

-- 変更結果を確認
MySQL [sample_db]> SHOW CREATE DATABASE sample_db;
+-----------+-------------------------------------------------------------------------------------------+
| Database  | Create Database                                                                           |
+-----------+-------------------------------------------------------------------------------------------+
| sample_db | CREATE DATABASE `sample_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |
+-----------+-------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Amazon RDS(Aurora)で日本語文字列や絵文字を使えるようにする

RDSのデフォルトの設定のクラスタで、データベースとテーブルを特に文字コードを設定せずに作成した場合、
VARCHAR(文字列)型の列を作ってもそこに日本語などのマルチバイト文字を格納することはできません。

カラムか、テーブルか、データベース単位で設定してもいいのですが、パラメーターグループを使って設定する方法を紹介します。
Aurora(正確にはそれが互換性を持っているMySQL)では、文字コード(Character Set)と照合順序(Collation)をそれぞれ独立した設定として持っています。
照合順序というのはソート順やアルファベットの大文字小文字を同一視するかどうかといったルールのセットです。

文字コードは日本語を使いたいのであればUTF8を使えるように設定する必要がありますが、
歴史的な経緯により、MySQLのUTF8には、utf8とutf8mb4の2種類があります。
utf8には3バイトまでの文字しか含まれておらず、絵文字等の4バイト文字も使えるようにするためにはutf8mb4を設定する必要があります。

参考: 10.1.10.7 utf8mb4 文字セット (4 バイトの UTF-8 Unicode エンコーディング)

さて、とりあえず何も設定しなかった場合の設定を見ておきましょう。


MySQL [(none)]> SHOW VARIABLES LIKE 'char%';
+--------------------------+-------------------------------------------------------------------------+
| Variable_name            | Value                                                                   |
+--------------------------+-------------------------------------------------------------------------+
| character_set_client     | utf8                                                                    |
| character_set_connection | utf8                                                                    |
| character_set_database   | latin1                                                                  |
| character_set_filesystem | binary                                                                  |
| character_set_results    | utf8                                                                    |
| character_set_server     | latin1                                                                  |
| character_set_system     | utf8                                                                    |
| character_sets_dir       | /rdsdbbin/oscar-5.7.serverless_mysql_aurora.2.08.3.38.0/share/charsets/ |
+--------------------------+-------------------------------------------------------------------------+
8 rows in set (0.01 sec)

MySQL [(none)]> SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.01 sec)

character_set_client, character_set_connection, character_set_results
はデフォルトでは utf8 になっていますが、 character_set_database と、 character_set_server が、latin1 になっています。
絵文字含む日本語文字を使うためにはこれら5つをutf8mb4に設定する必要があります。

ここからがややこしいことなのですが、RDSのパラメーターグループではこの5つをそれぞれ設定できるようになっているのに、
character_set_client, character_set_connection, character_set_results にはそれが反映されません。
そして、 character_set_database には character_set_server に設定した値が入ります。
反映されない3つは、クライアント側で設定する必要があるようです。

また、collation の方も、 collation_connection には設定した値が反映されず、
collation_server に設定した値が、collation_server と collation_database に反映されます。

これを踏まえて設定していきます。まず、RDSのパラメーターグループには次のふたつを設定します。

character_set_server: utf8mb4
collation_database: utf8mb4_bin

この段階で、設定は次のようになります。


MySQL [(none)]> SHOW VARIABLES LIKE 'char%';
+--------------------------+-------------------------------------------------------------------------+
| Variable_name            | Value                                                                   |
+--------------------------+-------------------------------------------------------------------------+
| character_set_client     | utf8                                                                    |
| character_set_connection | utf8                                                                    |
| character_set_database   | utf8mb4                                                                 |
| character_set_filesystem | binary                                                                  |
| character_set_results    | utf8                                                                    |
| character_set_server     | utf8mb4                                                                 |
| character_set_system     | utf8                                                                    |
| character_sets_dir       | /rdsdbbin/oscar-5.7.serverless_mysql_aurora.2.08.3.38.0/share/charsets/ |
+--------------------------+-------------------------------------------------------------------------+
8 rows in set (0.01 sec)

MySQL [(none)]> SHOW VARIABLES LIKE 'coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8mb4_bin     |
| collation_server     | utf8mb4_bin     |
+----------------------+-----------------+
3 rows in set (0.00 sec)

次にクライアント側の設定です。
僕は、Amazon Linux 2 で標準のRDSになった、MariaDBを利用しています。
設定ファイルは、 /etc/my.cnf なのですが、
その中を見ると、 !includedir /etc/my.cnf.d となっていて、他のファイルを読み込んでいます。

/etc/my.cnf.d/client.cnf と言うファイルの中に、 [client]と言うセクションがあるのでそこに設定します。


$ sudo vim /etc/my.cnf.d/client.cnf

[client]
# 以下の1行を追加
default-character-set = utf8mb4

MySQL [(none)]> SHOW VARIABLES LIKE 'char%';
+--------------------------+-------------------------------------------------------------------------+
| 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       | /rdsdbbin/oscar-5.7.serverless_mysql_aurora.2.08.3.38.0/share/charsets/ |
+--------------------------+-------------------------------------------------------------------------+
8 rows in set (0.00 sec)

MySQL [(none)]> SHOW VARIABLES LIKE 'coll%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_bin        |
| collation_server     | utf8mb4_bin        |
+----------------------+--------------------+
3 rows in set (0.01 sec)

これで、ほぼ設定できました。
実用上これで困ることなく使うことができると思います。

後1点, collation_connection の設定が utf8mb4_bin にならずに、 utf8mb4_general_ci になってしまうのですが、
どなたか collation_connection の設定を utf8mb4_bin にする方法をご存知の方がいらしたら教えていただけないでしょうか。

そのセッションに限った設定であれば、
SET collation_connection = utf8mb4_bin;
で設定できるのですが、接続を切ると元に戻ってしまいます。

また、
SET GLOBAL collation_connection = utf8mb4_bin;
は RDS では実行できないようです。

先述の通り、パラメーターグループで設定しても反映されません。
あまり困ることもないのですが、ここだけ設定がズレていて気持ち悪いので可能であればutf8mb4_binに揃えたいです。