投稿

投稿日:

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]
# 以下の１行を追加
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に揃えたいです。

投稿日:

RDSのタイムゾーンを日本時間(Asia/Tokyo)にする

EC2の時刻設定の話を書いたついでに、RDSの時刻設定を変える方法も紹介しておきます。
(ちなみに僕が使ってるRDSは Amazon Aurora Serverless です。)

結論から言うと、RDSにはパラメーターグループと呼ばれる設定値のコンテナのようなものがあり、
それを使ってシステム環境変数を設定します。

ドキュメントはここかな。(これを読むより実際に動かした方がわかりやすい)
参考: DB パラメータグループを使用する

まず、デフォルトの設定を確認しておきましょう。
何も設定せずに、RDSのクラスタを立ち上げ、現在時刻を表示してみます。

これ実行したのは 2021-03-26 23:48:09 です。


MySQL [(none)]> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2021-03-26 14:48:09 |
+---------------------+
1 row in set (0.01 sec)

MySQL [(none)]> SHOW VARIABLES LIKE 'time_%';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| time_format   | %H:%i:%s          |
| time_zone     | SYSTEM            |
| timestamp     | 1616770134.005551 |
+---------------+-------------------+
3 rows in set (0.01 sec)

time_zone は SYSTEM となっていますが9時間ずれていてUTCだとわかりますね。
これを修正していきます。

– RDSの管理画面の左ペインから、パラメーターグループを選択する
– パラメータグループの作成 を押下
– 以下のように項目選択
パラメータグループファミリー aurora-mysql5.7
タイプ DB Cluster Parameter Group
グループ名 任意
説明 任意
– 作成 を押下
– 作成したパラメーターグループを選択し、パラメーターグループアクションから編集を選択
– time_zone を探して、 Asia/Tokyo を選択
– 変更を保存
– データベースの一覧に戻り、設定を変更したいデータベースを選択
– 変更を押下
– DB クラスターのパラメータグループ に先ほど作ったパラメーターグループを選択し、続行を押下
– 変更を適用するタイミング は すぐに適用 を選択
– クラスターの変更 を押下

この後、セッションを切ってしばらく待った後繋ぎ直すと反映されています。


MySQL [(none)]> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2021-03-26 23:59:23 |
+---------------------+
1 row in set (0.01 sec)

MySQL [(none)]> SHOW VARIABLES LIKE 'time_%';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| time_format   | %H:%i:%s          |
| time_zone     | Asia/Tokyo        |
| timestamp     | 1616770768.009869 |
+---------------+-------------------+
3 rows in set (0.00 sec)

Aurora Serverless と言うより RDS では、他のステム変数も同じようにパラメーターグループを使って編集できます。