MySQLコネクターでMySQL8.0をのデータベースのテーブルに対してSELECTすると、絞込条件で濁点や半濁点を指定していないにもかかわらず、濁点や半濁点を含む検索結果が返されない（濁点や半濁点が区別されない）場合があります。例えば、「ローコート」という条件で絞り込みを行おうとすると、「ローコート」と「ローコード」の両方が結果としてヒットしてしまいます。 原因 MySQL 8.0のデフォルトの照合順序(Collation)は utf8mb4_0900_ai_ci デフォルトであるため。この照合順序は、濁点と半濁点を区別しないため、濁点・半濁点を含まない検索条件を指定しても、濁点・半濁点を含む結果が返される場合があります。 対応方法 MySQLの Select rowsアクションを利用する際、Where conditionにおいて、= や likeの後に binaryを追加し、その後に検索したい値を指定します。 Workatoに限らず発生する可能性のある問題ですが、WorkatoからMySQLデータベースを利用される際は参考としていただければ幸いです。 参考

前提条件 MySqlのインストール MySql WorkBenchのインストール DapeprをNuGetからインストール DapperExtensionをNuGetからインストール MySQL WorkBenchでDBを作成する RiderにMySqlに接続するツールがあり、スキーマ、テーブル、カラムが作成できます。 DBの作成の仕方がわからなかったため、MySql WorkBenchからDbだけを作成します WorkBenchでDbだけ作成します。 DBへ接続 DBを作成したらRiderから接続します。 DataBaseにスキーマ名を記入します。 Rider上のテーブルの作成 DBち(スキーマ)を作成するとテーブルを作成できるようになります。 Rider上でのDBの操作 カラムを追加したり、外部キーを追加したりできます。 NuGetからMySql.Dataをダウンロード NugetからMySqlクライアントMySql.Dataをインストールします スキーマからモデルを生成する ここからソースをダウンロードし、Riderのフォルダの中に格納しすぐモデルファイルを作成できるようにします。 MySql用にカスタマイズしていきます。 右クリックから実行しモデルファイルを生成します。 テストソース using System; using System.Linq; using MySql.Data.MySqlClient; using Dapper; using DapperExtensions; namespace mySql { class Program { public class test { public Int64 id { get; set; } = 0; public string code { get; set; } = null; public string name { get; set; } = null; } static void Main(string[] args) { MySqlConnectionStringBuilder b = new MySqlConnectionStringBuilder(); b.Server = "localhost"; b.Port = 3306; b.Database = ""; b.UserID = ""; b.Password = ""; MySqlConnection con = new MySqlConnection(b.ConnectionString); //MySQL形式でSQLを書き出す DapperExtensions.DapperExtensions.SqlDialect = new DapperExtensions.Sql.MySqlDialect(); //Dapperの_の入ったカラム名を有効にする。 Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true; con.Open(); string sql = "select * from test;"; var result = con.Query<test>(sql); foreach(var p in result) { Console.WriteLine("ID:" + p.id + " 名:" + p.name); } test test1 = new test(); test1.name = "test2"; test1.code = "java"; con.Insert<test>(test1); string sql3 = "select * from test Where code = 'java';"; var test_result2 = con.QueryAsync<test>(sql3); Console.WriteLine(test_result2.Result); foreach (test test_t in test_result2.Result) { con.Delete<test>(test_t); } var result5= con.QueryAsync<test>(sql); Console.WriteLine(" count " + result5.Result.Count()); con.Close(); } } } 課題 DapperExtensionのInsertAsyncなどAsync系が実行できなかった。 続く

select * from syndicate_status where video_id=80379 and social_media='raw_youtube' update syndicate_status set status=9 where video_id=80379 and social_media='raw_youtube'

（PostgreSQL Advent Calendar 2021 2日目） SQLの集計結果において日付の抜けがあるとき、カレンダーテーブル（日付表）を使うと簡単に埋められます。この記事では、カレンダーテーブルの使い方と作り方を説明します。主にPostgreSQLを対象としてますが、記事の最後のほうでMySQLでの作り方を説明します。 日付の抜けがある集計結果 日付をキーにして集計をするとき、日付が抜けてしまうことがあります。 たとえば次のSQLでは、注文テーブルをもとにして1週間分の売上金額を日付ごとに集計しています。 select 注文.日付, sum(注文.金額) as 売上 from 注文 where 注文.日付 between '2021-12-01' and '2021-12-07' group by 注文.日付 order by 注文.日付 ここで、実行結果が次のようだとしましょう。 日付 | 売上 ------------+------- 2021-12-01 | 8000 2021-12-03 | 10800 2021-12-05 | 20100 2021-12-06 | 22000 2021-12-07 | 25000 (5 rows) この結果を見ると、1週間分の集計なのに 2021-12-02 と 2021-12-04 の日付が抜けていますね。これは、この日の注文がなかったせいです。 注文がなかった日の売上を0円として集計するにはどうしたらいいでしょうか。つまり次のような集計結果を得るにはどうしたらいいでしょうか。 日付 | 売上 ------------+------- 2021-12-01 | 8000 2021-12-02 | 0 2021-12-03 | 10800 2021-12-04 | 0 2021-12-05 | 20100 2021-12-06 | 22000 2021-12-07 | 25000 (7 rows) 日付の抜けをカレンダーテーブルで埋める ここで、カレンダーテーブルについて説明します。 「カレンダーテーブル」とは、日付の一覧が格納されたテーブルです。 select cal.* from calendar as cal -- ← これがカレンダーテーブル where cal.date between '2021-12-01' and '2021-12-07'; 実行結果： date ------------ 2021-12-01 2021-12-02 2021-12-03 2021-12-04 2021-12-05 2021-12-06 2021-12-07 (7 rows) もしこのようなカレンダーテーブルがあれば、先ほどの集計結果と外部結合することにより、抜けた日付を埋めることができます。 select cal.date as 日付, coalesce(t.売上, 0) as 売上 from ( /* 日付の抜けがある集計結果に、*/ select 注文.日付, sum(注文.金額) as 売上 from 注文 where 注文.日付 between '2021-12-01' and '2021-12-07' group by 注文.日付 -- order by 注文.日付 ) as t /* カレンダーテーブルを外部結合する */ right outer join calendar as cal on t.日付 = cal.date where cal.date between '2021-12-01' and '2021-12-07' order by cal.date 実行結果： 日付 | 売上 ------------+------- 2021-12-01 | 8000 2021-12-02 | 0 2021-12-03 | 10800 2021-12-04 | 0 2021-12-05 | 20100 2021-12-06 | 22000 2021-12-07 | 25000 (7 rows) このように、「日付の抜けがある集計結果」を「日付の抜けがないカレンダーテーブル」と外部結合することで、集計結果における日付の抜けを埋められます。 それでは、このようなカレンダーテーブルを作るにはどうしたらいいでしょうか。そのためには、PostgreSQLのgenerate_series()関数を使います。 PostgreSQLのgenerate_series()関数 PostgreSQLには、generate_series() という関数があります。これは連続した整数を生成するための関数であり、Pythonのrange()関数、Rubyの1..n、UNIXのseqコマンドと同様のものです。 select g.i from generate_series(1, 7) as g(i); /* ここで「as g(i)」というエイリアスを使うことで、 テーブル名として g、カラム名として i という別名がつく */ 実行結果： i --- 1 2 3 4 5 6 7 (7 rows) 整数は順番に生成されるので、SQLに order by 句をつける必要はありません。また第3引数にstepを指定でき、たとえば -1 を指定すると逆順を生成できます。 select g.i from generate_series(7, 1, -1) as g(i); 実行結果： i --- 7 6 5 4 3 2 1 (7 rows) なお generate_series()関数は整数だけでなく日時(timestamp型)も生成できますが、今回は扱いません。またgenerate_series()関数についての詳細は、PostgreSQLのマニュアルを参照してください。 カレンダーテーブルを生成する PostgreSQLでは、日付を表すdate型に次のような性質があります。 date型に整数を足した結果は、date型 date型からdate型を引いた結果は、integer型 /* date型に整数を足した結果は、date型 */ postgresql=> select '2021-12-31'::date + 7; ?column? ------------ 2022-01-07 -- ← date型 (1 row) /* date型からdate型を引いた結果は、integer型 */ postgresql=> select '2021-12-31'::date - '2020-12-01'::date; ?column? ---------- 30 -- ← integer型 (1 row) この性質と、先ほど説明したgenerate_series()関数を使えば、カレンダーテーブルが生成できます。たとえば 2021-12-01 から1週間の日付を生成するには、次のようにします。「日付」＋「0〜6の整数」で1週間分の日付を生成していることに注目してください。 select '2021-12-01'::date + g.i as date from generate_series(0, 6) as g(i); 実行結果： date ------------ 2021-12-01 2021-12-02 2021-12-03 2021-12-04 2021-12-05 2021-12-06 2021-12-07 (7 rows) これでカレンダーテーブルが簡単に生成できるようになりました。もし動作の仕組みが分かりにくいと感じたら、次のSQLを見れば分かるようになるでしょう。 select '2021-12-01'::date as date -- date型 , g.i -- integer型 , '2021-12-01'::date + g.i as date -- date型+integer型 from generate_series(0, 6) as g(i); 実行結果： date | i | date ------------+---+------------ 2021-12-01 | 0 | 2021-12-01 2021-12-01 | 1 | 2021-12-02 2021-12-01 | 2 | 2021-12-03 2021-12-01 | 3 | 2021-12-04 2021-12-01 | 4 | 2021-12-05 2021-12-01 | 5 | 2021-12-06 2021-12-01 | 6 | 2021-12-07 (7 rows) このカレンダーテーブルを使って、先ほどの集計結果の日付を埋めてみましょう。 select cal.date as 日付, coalesce(t.売上, 0) as 売上 from ( /* 日付の抜けがある集計結果に、*/ select 注文.日付, sum(注文.金額) as 売上 from 注文 where 注文.日付 between '2021-12-01' and '2021-12-07' group by 注文.日付 -- order by 注文.日付 ) as t /* カレンダーテーブルを外部結合する */ right outer join ( select '2021-12-01'::date + g.i as date from generate_series(0, 6) as g(i) ) as cal on t.日付 = cal.date order by cal.date 実行結果： 日付 | 売上 ------------+------ 2021-12-01 | 2000 2021-12-02 | 0 2021-12-03 | 2000 2021-12-04 | 0 2021-12-05 | 2000 2021-12-06 | 2000 2021-12-07 | 2000 (7 rows) ストアド関数を定義する カレンダーテーブルを generate_series() で毎回作るのは、少し面倒です。そこで、これをストアド関数にしてしまいましょう。このとき、引数には3つのアプローチがあります。 (A) 開始日と日数（integer型）を指定する (B) 開始日と終了日（date型）を指定する (C) 開始日と期間（interval型）を指定する (C)のinterval型というのは、PostgreSQLにおいて期間を表すデータ型です。たとえば '1 months'::interval で「1ヶ月」という期間を表し、'1 months - 1 days'::interval で「1ヶ月から1日を引いた期間」を表します1。またdate型とinterval型とを足すことができ、たとえば '2021-12-01'::date に '1 months'::interval を足すと '2022-01-01 00:00:00'2（つまり1ヶ月後）になるし、 '1 months - 1 days'::interval を足すと '2021-12-31 00:00:00'（つまりその月の最終日）になります。 /* 1ヶ月後の日付を求める */ psql=> select '2021-12-01'::date + '1 months'::interval; ?column? --------------------- 2022-01-01 00:00:00 (1 row) /* その月の最終日を求める */ psql=> select '2021-12-01'::date + '1 months - 1 days'::interval; ?column? --------------------- 2021-12-31 00:00:00 (1 row) interval型についての詳細はPostgreSQLのマニュアルを参照してください。 これを踏まえたうえで、(A)と(B)と(C)それぞれの方法でストアド関数を定義してみます3。PostgreSQLのストアド関数は引数によるオーバーロードができるので、3つとも同じ関数名にしても問題なく共存できます。PostgreSQLにおけるストアド関数についての詳細はPostgreSQLのマニュアルを参照してください。 /* (A) 開始日と日数を指定する */ create or replace function calendar(start date, ndays integer) returns table(date date) as $$ begin return query select start + g.i from generate_series(0, ndays - 1) as g(i); end $$ language plpgsql; /* (B) 開始日と終了日を指定する */ create or replace function calendar(start date, last date) returns table(date date) as $$ begin return query select start + g.i from generate_series(0, last - start) as g(i); end /* 「last - start」が「日数 - 1」を表す */ $$ language plpgsql; /* (C) 開始日と期間を指定する */ create or replace function calendar(start date, period interval) returns table(date date) as $$ begin return query select start + g.i from generate_series(0, (start + period)::date - 1 - start) as g(i); end /* 「(start + period)::date - 1」が終了日を表す */ $$ language plpgsql; それぞれの使い方は次のようになります。 /* (A) 開始日と日数を指定する */ select * from calendar('2021-12-01', 7); /* (B) 開始日と終了日を指定する */ select * from calendar('2021-12-01', '2021-12-07'::date); /* (C) 開始日と期間を指定する */ select * from calendar('2021-12-01', '1 weeks'::interval); -- または select * from calendar('2021-12-01', '7 days'::interval); 実行結果はどれも同じです。 date ------------ 2021-12-01 2021-12-02 2021-12-03 2021-12-04 2021-12-05 2021-12-06 2021-12-07 (7 rows) なお「(C) 開始日と期間を指定する」の方法は、「1ヶ月間」のように日数では指定しにくい期間を指定するときに便利です（「1ヶ月間」の日数は月によって異なるため）。 /* 1ヶ月分のカレンダー */ psql=> select * from calendar('2021-12-01'::date, '1 months'::interval); date ------------ 2021-12-01 2021-12-02 2021-12-03 ....省略.... 2021-12-30 2021-12-31 (31 rows) またストアド関数にしない場合と比べて、ストアド関数にしたほうがほんのわずか（0.1ミリ秒程度）遅くなります。つまりストアド関数のほうが動作コストがかかります。ただしその動作コストは、集計作業にかかる動作コストに比べれば無視できる程度のはずなので、気にする必要はありません（気になるなら自分自身で計測してみてください）。 カレンダーテーブルを再帰SQLで作る generate_series()関数はPostgreSQL用なので、他のRDBMSでは使えません。そのような場合でも、再帰SQLを使えばカレンダーテーブルを定義できます。たとえばMySQLでは8.0から再帰SQLが使えるようになったので、MySQL 8.0で試してみましょう。 /* MySQL 8.0: 再帰SQLを使って、1週間分のカレンダーを生成する */ with recursive cal as ( select cast('2021-12-01' as date) as date union all select date_add(cal.date, interval 1 day) from cal where cal.date < '2021-12-07' ) select * from cal; 実行結果： +------------+ | date | +------------+ | 2021-12-01 | | 2021-12-02 | | 2021-12-03 | | 2021-12-04 | | 2021-12-05 | | 2021-12-06 | | 2021-12-07 | +------------+ 7 rows in set (0.01 sec) このように、再帰SQLを使ってもカレンダーテーブルを生成できます。ただし、PostgreSQLでは generate_series() 関数を使ったほうが柔軟な指定ができ、パフォーマンスもいいでしょう。 なお再帰SQLについては、MySQLのマニュアルやPostgreSQLのマニュアルを参照してください。 カレンダーテーブルを連番テーブルで作る MySQL 5.xのように再帰SQLが使えない場合は、連番が格納されたテーブルを使えばカレンダーテーブルを生成できます。 たとえば、0〜999までの連番が格納されたテーブル「seq999」を用意します4。 create table seq999 ( i integer primary key ); insert into seq999(i) values (0) , (1) , (2) , (3) /* ....省略.... */ , (999); insert文はプログラムで生成するといいでしょう。UNIXなら次のようにします。 $ echo "insert into seq999(i)" > insert.sql $ echo "values (0)" >> insert.sql $ seq 1 999 | sed 's/\(.*\)/ , (\1)/' >> insert.sql このような連番テーブルがあれば、カレンダーテーブルは次のようにして生成できます。再帰SQLよりも簡単なので、MySQL 8.0であってもこちらのほうがいいかもしれません。 select date_add('2021-12-01', interval seq.i day) as date from seq999 as seq where seq.i < 7; 実行結果 on MySQL： +------------+ | date | +------------+ | 2021-12-01 | | 2021-12-02 | | 2021-12-03 | | 2021-12-04 | | 2021-12-05 | | 2021-12-06 | | 2021-12-07 | +------------+ 7 rows in set (0.01 sec) なお連番テーブルの作り方については、書籍『達人に学ぶSQL徹底指南書 第2版』の「第10章 SQLで数列を扱う」にも説明があります。持っている人は読んでみてください。 まとめ この記事ではカレンダーテーブル（日付表）について説明しました。カレンダーテーブルを使うと抜けた日付を埋めることができるので、日付をキーにした集計結果で使うといいでしょう。 またカレンダーテーブルを作る方法と、それをストアド関数にする方法を紹介しました。カレンダーテーブルの作成は、PostgreSQLではgenerate_series()関数を使い、他のRDBMSでは再帰SQLまたは連番テーブルを使いましょう。 集計作業において、カレンダーテーブルは重宝します。もし日付をキーにした集計用SQLにおいてカレンダーテーブルを使っていないなら、潜在的なバグだといえます。しかし集計のたびにカレンダーテーブルをいちいち作るのは面倒です。カレンダーテーブルを生成するストアド関数が標準で使える世界線が来てほしいものです。 Postgresqlのinterval型では、三単現の 's' をつけてもいいし、つけなくてもいいです。つまり '1 month' と '1 months' のどちらでも構いません。また '1 mon' や '1 mons' のような短い書き方もできます。 ↩ date型にinterval型を足した結果は、date型ではなくtimestamp型になります。そのため、実行結果は 2021-01-01 や 2020-12-31 ではなく 2021-01-01 00:00:00 や 2020-12-31 00:00:00 になります。 ↩ なおこれらのストアド関数では、逆順での日付の生成をサポートしていません。必要ならカスタマイズするか、SQLに order by date desc をつけてください。 ↩ 連番の個数は適宜変更してください。また連番は、0始まりと1始まりの両方が考えられます。その場合のテーブル名は、格納する連番が0〜999なら（つまり0始まりなら）「seq999」、1〜1000なら（つまり1始まりなら）「seq1000」という名前にするといいでしょう。 ↩