PlanetScale - SELECT INSERT UPDATE を実行する

PlanetScale (MySQL 互換) の基本的な DML (select, insert, update) を C# の環境で実行してみた。

接続文字列のパラメーターは、NuGet から「DotNetEnv」を使って隠しています。

DotNetEnv.Env.Load(".env");

var server = DotNetEnv.Env.GetString("SERVER");
var user = DotNetEnv.Env.GetString("USER");
var password = DotNetEnv.Env.GetString("PASSWORD");
var database = DotNetEnv.Env.GetString("DATABASE");
var port = DotNetEnv.Env.GetString("PORT");
var ssl = DotNetEnv.Env.GetString("SSLMODE");

var connectionString = $"server={server};user={user};database={database};port={port};password={password};SslMode={ssl}";

SELECT

SELECT 文を実行する例はこんな感じ。

private void Select(string connectionString)
{
    var data = new List<(int, string, string)>();

    using (var connection = new MySqlConnection(connectionString))
    {
        connection.Open();

        var command = new MySqlCommand("SELECT * FROM users;", connection);
        var result = command.ExecuteReader();

        while (result.Read())
        {
            int id = result.GetInt32("id");
            string email = result.GetString("email");
            string name1 = result.GetString("first_name");
            string name2 = result.GetString("last_name");

            data.Add((id, email, $"{name1} {name2}"));
        }
    }

    foreach (var d in data)
    {
        Console.WriteLine($"id={d.Item1}, email={d.Item2}, name={d.Item3}");
    }
}

特にこれといって特徴のないコードになります。

INSERT

INSERT 文を実行する例はこんな感じ。

private void Insert(string connectionString)
{
    using (var connection = new MySqlConnection(connectionString))
    {
        connection.Open();

        var sql1 = "INSERT INTO users (email, first_name, last_name) VALUES ('aa', 'bb', 'cc');";
        var sql2 = "SELECT LAST_INSERT_ID() FROM users;";

        var command = new MySqlCommand(sql1 + sql2, connection);

        // 挿入したカラムの ID を取得
        long id1 = (long)command.ExecuteScalar();
        long id2 = command.LastInsertedId;

        Console.WriteLine($"inserted id={id1}={id2}");
    }
}

挿入したテーブルに（auto increment をする）id がある場合、行に割り当てられた id を返却するコードにしておくこと。

例だと Insert 文の後すぐに、LAST_INSERT_ID() FROM *** を呼び出して、id を入手しています。Transaction を使ったコードがわかりやすい連携になります。

おおよそのデータ操作は、ひとつのテーブルにデータを挿入するだけではないです。いくつかのテーブルにパラメーターを保存するため、このような操作をするとよいです。

Entity Framework のような DbSet では、LastInsertedId を取得する際にこの種のコードは表現しなくても自動的に id が取得できます。

UPDATE

UPDATE 文を実行する例はこんな感じ。

private void Update(string connectionString)
{
    using (var connection = new MySqlConnection(connectionString))
    {
        connection.Open();

        var command = new MySqlCommand("UPDATE users SET email = 'rance@rudras.wld', first_name = 'あてな', last_name = '２号' WHERE id = 1;", connection);

        command.ExecuteNonQuery();
    }
}

特にこれといって特徴のないコードになります。

Transaction & Insert

Transaction を使ったときのサンプル。

private void InsertUsingTransaction(string connectionString, bool raiseException)
{
    using (var connection = new MySqlConnection(connectionString))
    {
        connection.Open();

        using (var transaction = connection.BeginTransaction())
        {
            try
            {
                var sql1 = "INSERT INTO users (email, first_name, last_name) VALUES ('test', 'using', 'transaction');";
                var sql2 = "SELECT LAST_INSERT_ID() FROM users;";

                var command1 = new MySqlCommand(sql1 + sql2, connection);

                // 挿入したカラムの ID を取得
                long id1 = (long)command1.ExecuteScalar();
                long id2 = command1.LastInsertedId;

                var sql3 = $"INSERT INTO sample (data, data2) VALUES ('{id1}', 'related value');";
                var sql4 = "SELECT LAST_INSERT_ID() FROM users;";

                var command2 = new MySqlCommand(sql3 + sql4, connection);

                // 挿入したカラムの ID を取得
                long id3 = (long)command2.ExecuteScalar();
                long id4 = command2.LastInsertedId;

                // 例外を発生させるサンプル
                if (raiseException)
                {
                    throw new Exception();
                }

                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
            }
        }
    }
}

おまけ１　日付を取得する

下記のコードは、時間が日本だとズレて取得してしまうようです。（日本時間ではなく UTC+0000）

private void Now(string connectionString)
{
    using (var connection = new MySqlConnection(connectionString))
    {
        connection.Open();

        var command = new MySqlCommand("SELECT NOW();", connection);
        var result = (DateTime)command.ExecuteScalar();

        Console.WriteLine(result);
    }
}

どうしてそうなるのか。PlanetScale のコンソールに問い合わせてみた結果がこちら：

JST や asis/tokyo を設定していないようです。かといって、/etc/my.cnf のようなものを直接編集できるわけでもないので、どうしようもなさそうです。

• incorrect timezone conversion

仕方ないので対応の一例がこちら：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created_datetime` datetime DEFAULT ((now() + interval 9 hour)),
  `updated_datetime` datetime DEFAULT ((now() + interval 9 hour)),
  PRIMARY KEY (`id`)
)

now() で取得した時刻のズレに対して、９時間を足して日本時間の UTC+0900 にします。ほかにも、データの記録は UTC+0000 にしておいて、取得のタイミングで +0900 を加えて日本時間にする、といった方法などがあると思います。（アプリケーションの規模や利用範囲で検討する）

おまけ２

CREATE TABLE sample
...
CONSTRAINT `fk_1` FOREIGN KEY (`no`) REFERENCES `main` (`no`) ON DELETE CASCADE

のような外部キーを利用することができないみたいです。

• why FOREIGN KEYs are not supported

これは結構面倒で、テーブルの整合性には注意がより必要になるはずです。

サンプル

GitHub にサンプルを公開しています。

次回で EF (entity framework) を利用した DML をメモします。

参考

基礎からのMySQL 第3版

Learning MySQL: Get a Handle on Your Data

実践ハイパフォーマンスMySQL 第3版