MySQLのオンライン交流会「生マイ」#3を開催しました

『今夜も生でMySQL(仮題) #03』(通称生マイ)を開催しました。「生マイ」は、Discordを使っておしゃべりしましょうという会です。

mysql.connpass.com

今回は少なめ

 お盆休みの関係だと思いたいのですが、今回はずっと3人でおしゃべりしていました。内容は後述しますが、ひとことで言うと「俺得」な感じで、いろんなことを教えてもらえたり、一緒に試してもらえたりして、個人的には大満足の時間でした。参加くださった皆さんありがとうございました。
 

UnicodeUTF-8UTF-16と。

 とみたさんが最近書かれたブログを見ながら、MySQLへのキャラセットとコレーションの追加についてお話。 UTF-16 を全然分かっていなかったことに気づきました(UTF-8よりも常に長くなるもんだと思っていたら、UTF-8みたいな先頭ビットに役割を持たせていないので、UTF-8で3バイトになるところ、UTF-16では2バイトのままだったりするようです。ただし必ず2バイトからの扱いなので、1バイト文字が多い場合は(頭に 0x00 がつくので)効率悪いと。
 あとは、関連して、MySQLソースコードの strings/ctype-*.cc ファイルを眺めたりしました。日本語の文字セット(とくに ujis系)がとりわけサイズが大きかったのは、割り当てのない領域も愚直に 00 で埋めているかららしいです。ちなみに、ujis/sjis系のコードには、とみたさんのクレジットが記述されていました!

Twitterの「スペース」を試す

 イベントや交流のやりかたのひとつとして、Twitterの「スペース」ってのはどうかね、と話題になりました。私も使ったことがなかったので、早速(人数少なくて身軽だったこともあり)お試し。PCからはできず(聞くだけの人にはなれるみたい)、スマホから。 スマホでの Twitterアプリへのマイク使用許可を出す方法がわからなかったり、ITむつかしい。。
 使用感としては、ClubHouseですね。トーク可能な人と聞くだけの人に分かれていて、聞く人をステージに呼ぶこともできる(トーク権を与える)という感じ。手元のスマホでマイクなどの準備もなかったせいか、音質はあまり良くなく「いつも聞いてるこの人の声」とはずいぶん違ったのは気になりました。外部マイク使うと変わるのかな。
 あとは、テストのつもりで開いてみたら、通りすがりの方が覗いてくれたりしたので、ちょっとそういうのは面白いな、と思いました。今後、突然思いつきでなにか開催するかもしれません。

MySQL ユーザ会の Discord へのお誘い

 この「わいわいおしゃべりする会」を開催するために試験的に開いてみた Discordサーバですが、3回ほど会を開催して、色々使いどころがありそうなので、より広く公開することにしました。「テキストチャンネル」があり、「生マイ」開催日以外でも活用してもらえたらと思いますので、お誘い合わせの上、ぜひ登録してください。(広く広めていただくの歓迎です)

※Discord の日本MySQLユーザ会サーバへの招待リンク:
https://discord.gg/aKUW6d8hhV

※ずっと将来にこの記事をご覧の方へ:もしかしたら上記リンクが失効しているかもしれませんが、そのときにはどこかに最新情報があるかもしれない(あるいはDiscordの運用をやめたのかもしれない)ので、最新情報を検索してみてください。

参加お待ちしています

 「とりあえず3回はやってみる」の宣言どおり3回やってみたところですが、もう少し続けてみたいと思います。理想は「時間と場所は用意したので、あとは幹事抜きでも会話を楽しんでもらえたら・・・」というものですが、なかなか難しいですね。ご興味ある方、ぜひ次回以降のご参加お待ちしています!(Twitterの #mysql_jp ハッシュタグをウォッチしてください)

Laughing dolphins image by DALL-E

歯抜け数字を埋めるSQL

MySQLユーザ会のメーリングリストで、こんな話題がありました。
INSERT SELECT でのテーブル別名の使い方

「歯抜けの数字を、連番になるようにレコードを追加する」という方法について、相関サブクエリを使用して解決したものですが、これ、相関サブクエリを使わずに今ならどう書くかな~と思って試してみました。 MLへの投稿の本題からはずれているので、自分の日記にて。

やりたいこととデータの準備

 PKとして「歯抜け連番」がセットされているテーブルにて、抜けている数字のレコードを追加したい、というのがやりたいことです。

CREATE TABLE tbl2 (id INTEGER PRIMARY KEY,
                   name varchar(10),
                   somedata varchar(10));

INSERT INTO tbl2 VALUES (2, "test2", "some2");
INSERT INTO tbl2 VALUES (3, "test3", "some3");
INSERT INTO tbl2 VALUES (4, "test4", "some4");
INSERT INTO tbl2 VALUES (6, "test6", "some6");
INSERT INTO tbl2 VALUES (10, "test10", "some10");
INSERT INTO tbl2 VALUES (11, "test11", "some11");
INSERT INTO tbl2 VALUES (18, "test18", "some18");
INSERT INTO tbl2 VALUES (21, "test21", "some21");
INSERT INTO tbl2 VALUES (24, "test24", "some22");

内容確認:

mysql> SELECT * FROM tbl2;
+----+-------+----------+
| id | name  | somedata |
+----+-------+----------+
|  2 | test2 | some2    |
|  3 | test2 | some2    |
|  4 | test2 | some2    |
|  6 | test2 | some2    |
| 10 | test2 | some2    |
| 11 | test2 | some2    |
| 18 | test2 | some2    |
| 21 | test2 | some2    |
| 24 | test2 | some2    |
+----+-------+----------+

 このテーブルで、抜けている、1, 5, 7, 8, 9... のレコードを追加したい、というわけです。

方針

 必要となる全部の数字が入ったテーブルを用意し、対象テーブルとのJOINにより不足数字を見つけ出す方法を採ることにしました。
もちろん本当に数字が入ったテーブルを作成するわけではなく、WITH句を使います。
 Window関数を使って何かできないかと考えたのですが、ちょっと良いアイデアが浮かびませんでした。

不足数字をSELECTするSQLの確認

 こんな感じ。

mysql> WITH RECURSIVE num(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<(SELECT MAX(id) FROM tbl2))
    -> SELECT n id, null, null FROM num LEFT OUTER JOIN tbl2 ON (num.n=tbl2.id) WHERE tbl2.id IS NULL;
+------+------+------+
| id   | NULL | NULL |
+------+------+------+
|    1 | NULL | NULL |
|    5 | NULL | NULL |
|    7 | NULL | NULL |
|    8 | NULL | NULL |
|    9 | NULL | NULL |
|   12 | NULL | NULL |
|   13 | NULL | NULL |
|   14 | NULL | NULL |
|   15 | NULL | NULL |
|   16 | NULL | NULL |
|   17 | NULL | NULL |
|   19 | NULL | NULL |
|   20 | NULL | NULL |
|   22 | NULL | NULL |
|   23 | NULL | NULL |
+------+------+------+
15 rows in set (0.00 sec)

INSERT する

 SELECTできることが確認できたので、そのままつっこめばいいだけ。

mysql> INSERT INTO tbl2
    -> WITH RECURSIVE num(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<(SELECT MAX(id) FROM tbl2))
    -> SELECT n id, null, null FROM num LEFT OUTER JOIN tbl2 ON (num.n=tbl2.id) WHERE tbl2.id IS NULL;
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

結果確認:

mysql> SELECT * FROM tbl2 ORDER BY id;
+----+--------+----------+
| id | name   | somedata |
+----+--------+----------+
|  1 | NULL   | NULL     |
|  2 | test2  | some2    |
|  3 | test3  | some3    |
|  4 | test4  | some4    |
|  5 | NULL   | NULL     |
|  6 | test6  | some6    |
|  7 | NULL   | NULL     |
|  8 | NULL   | NULL     |
|  9 | NULL   | NULL     |
| 10 | test10 | some10   |
| 11 | test11 | some11   |
| 12 | NULL   | NULL     |
| 13 | NULL   | NULL     |
| 14 | NULL   | NULL     |
| 15 | NULL   | NULL     |
| 16 | NULL   | NULL     |
| 17 | NULL   | NULL     |
| 18 | test18 | some18   |
| 19 | NULL   | NULL     |
| 20 | NULL   | NULL     |
| 21 | test21 | some21   |
| 22 | NULL   | NULL     |
| 23 | NULL   | NULL     |
| 24 | test24 | some22   |
+----+--------+----------+

気になる点

 この方法で、件数が多くなった場合にどれくらいの速度で動きますかね。
10万件かそこらでは意外とすんなり動く気もしていますが、1000万件となるときついかもという気もします。
誰か試してみませんか。

Dolphin image by Dall-E

LINE Developer Meetup #73 - MySQL の回に参加(視聴)した話

 昨火曜日にオンラインで開催された「LINE Developer Meetup #73 - MySQL」に参加させていただきました。オンライン開催なので「視聴」に近い形態ですが、sli.do や twitter などでわいわいできて、視聴者どうしの会話があったり、登壇者の方も反応してくださったりして、オンラインなのに比較的楽しく「参加」できた気分を味わいました。

line.connpass.com
 
 感想的なものは、聞きながらtwitterにほぼ放出したので、一週間近く経った今でも記憶に残っている範囲で、少し書こうと思います。

全体進行

 登壇者の方を画面に呼び入れたり一旦控え室に戻っていただいたりの操作が鮮やかでしたね。そういや私も長らくオンラインイベントを開催したりしているけど、あまりこういうテクニックは追ってこなかったので、そろそろ身につけたいと思う格好良さでした。やっぱり2台は必要なのかしら。特別なハードウェアは何か使用しているのか。などなど知らなきゃいけないことがいっぱいありそうです。

@tom__bo さんの『バッファープールが大きいMySQL v5.7でDROP TABLEが詰まった原因と対策』

 メインは「DROP DATABASE実行時にエラく時間がかかることに関する調査」ですが、個人的にはその調査をすることになった経緯を前段で丁寧に説明してくれたことに好感を持ちました。なぜそこにハマったのかのコンテキストが共有されると、感情移入しやすくなるので。
 どんな方法で調査をするかというと、、、、ソースを追い始めた(笑)。自分ではこういうソースを追う調査を効率的にできる気はしないのだけど、話を聞くのは好きなのでワクワクしました。セミナー形式なので、どうしても途中でついて行けない部分(一瞬気を抜くと置いて行かれたり)もあり、機会あれば改めてゆっくり伺ってみたい気もしました。

@yyamasaki1 さんの『塩漬けにしているMySQL 8.0.xxをバージョンアップしたくなる、ここ数年でのMySQL 8.0の改善点』

 MySQL 8.0 の最近10回ぶんくらいのマイナーバージョンアップの内容を紹介してくれました。MySQLでは「メンテナンスリリース」として 8.0.XX の XX の部分が上がっていきますが、これがひとつ上がるごとに新機能ができたり動作が変わったりする部分がそこそこあって、その歴史をひとつひとつ説明してくれた、、、、んですが、盛りだくさんすぎて、こちらもちょっと「あれ、この変更ってなんだっけ」とノーミソがそちら方面に行ってしまった隙に説明中のバージョンが2つくらい進んでいたりしたので、また改めてひとつひとつゆっくり聞かせていただく機会が欲しいなーと思いました。

@yoku0825さんの『ぼくらが選んだ次のMySQL 8.0』

 基本的に「なるべく最新版をつかいましょう」と言われることが多いのですが、敢えて最新の2つ前のものを選んだ、というお話(選定した時点では「ひとつ前」だったかも)。 たまに「なんか最新って怖いから少し前のにしよう」と無根拠に言っている人に会いますが、もちろんそれはダメ、論外。基本的なベクトルとしては、古いバージョンに存在するバグやセキュリティ上の問題などが、より新しいバージョンで直される流れなので、基本的には新しいもののほうが安全かつ安定と考えるべきと思います。
 が、yokuさんたちが「敢えて」古いバージョンを選んだ理由が、非常に具体的に「最新版のこの動作が許容できないから採用しない」というもので、さすが細かいところまで見て判断をされているなぁと感じました。

パネルディスカッション

 登壇お三方のトークショー。リリースされたばかりの MySQL 8.0.30 のリリースノートを眺めながらのわいわいトークを聞かせていただきました。この手の「わいわい」は、聞いて楽しめる人と、単なる用意なしの雑談を聞かされている気分になってしまう人とに分かれるので、自分が主催者になった気分でちょっとドキドキしながら聞いていたのですが、みんなの深い知識と、ここぞという処で話題を引き締めてくれる山崎さんの立ち位置のおかげで、いつの間にか普通の聴衆として楽しませていただいていました。

動画と資料へのリンク

 当日の動画、twitterハッシュタグを拾ったtogetter、発表資料などが公開されています。興味持ったらご覧いただけると良いかと思います。

動画:
www.youtube.com


Twitterハッシュタグ拾い(これ以外に、ハッシュタグを付けずにスレッドが続いているものがあるので、気になるツイートがあればその先まで確認してみると良いでしょう):
togetter.com


tom__boさん発表資料:
speakerdeck.com

yyamasaki1さん発表資料:
speakerdeck.com

yokuさん発表資料:
speakerdeck.com

最後に

 Twitter上でのMySQL関連(日本)の情報は、ハッシュタグ #mysql_jp で行っているので、情報収集に活用ください。MySQLの話題をつぶやくときにはこのハッシュタグを付けると、MySQLに興味を持つ人の目に触れやすくなるので、お得・・・になることがあるかもしれません。ぜひぜひご活用を。
 そして今回、濃厚で素敵なイベントを開催してくださり、ありがとうございました!

MySQLでJSON体験(その3:JSONを生成する)

 今回は MySQLを使ってJSON形式のデータを作ることにトライしてみました。正直よくわからんけど、まぁやりたいことは一応実現可能なのかな、といったレベルの理解です。

目標とするデータ形式

 Cocoaのデータっぽい形式を目指すことにします。細かい部分で違ったり端折ったりするので、まったく同じものを目指すわけではありません。

基本的なJSON要素の生成

 MySQLでのJSONデータの生成では、「JSON配列の生成」と「JSONオブジェクトの生成」があります。今回目標とする形式の大枠は、名前と値がセットになった「JSONオブジェクト」なので、JSON_OBJECT()関数を使います。
 引数にはひたすら、名前,値,名前,値,名前,値,....の繰り返しを記述します。

mysql> SELECT JSON_OBJECT('platform', 'iOS', 'model', 'iPhone13,2', 'app_version', '2.0.1') j;
+--------------------------------------------------------------------+
| j                                                                  |
+--------------------------------------------------------------------+
| {"model": "iPhone13,2", "platform": "iOS", "app_version": "2.0.1"} |
+--------------------------------------------------------------------+

platform/model/app_version の3つの要素を持つJSONデータができました。

見やすくするJSON関数

 上で試した出力結果は、JSONデータが1行のデータとして(改行抜きで)表示されています。JSON_PRETTY()関数を使うと、良い感じに改行とインデントを入れて見やすくしてくれるので、今後はこれを使うことにします。併せて、文末を ; ではなく \G で見やすく出力することにします。

mysql> SELECT JSON_PRETTY(JSON_OBJECT('platform', 'iOS', 'model', 'iPhone13,2', 'app_version', '2.0.1')) j\G
*************************** 1. row ***************************
j: {
  "model": "iPhone13,2",
  "platform": "iOS",
  "app_version": "2.0.1"
}

配列要素の追加

 次に daily_summaries のブロックの生成を試みます。 daily_summaries はJSONオブジェクトを複数持つ配列なので、入れ子にする形で JSON_ARRAY()関数を使います。その各要素オブジェクトの中には更にJSONオブジェクトを持つのですが、一段階ずつトライしていきたいので、一旦「ObjTodo」という文字列を入れておくことにします。

mysql> SELECT JSON_PRETTY(
    ->   JSON_OBJECT('platform', 'iOS', 
    ->               'model', 'iPhone13,2',
    ->               'app_version', '2.0.1',
    ->               'daily_summaries', 
    ->                   JSON_ARRAY(
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1650931200000,
    ->                                 'DaySummary','ObjTodo', 
    ->                                 'RecursiveSummary',null),
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1651017600000,'DaySummary','ObjTodo','RecursiveSummary',null),
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1651363200000,'DaySummary','ObjTodo','RecursiveSummary',null)
    ->                   )
    ->               )
    ->               ) j\G
*************************** 1. row ***************************
j: {
  "model": "iPhone13,2",
  "platform": "iOS",
  "app_version": "2.0.1",
  "daily_summaries": [
    {
      "DaySummary": "ObjTodo",
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1650931200000
    },
    {
      "DaySummary": "ObjTodo",
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651017600000
    },
    {
      "DaySummary": "ObjTodo",
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651363200000
    }
  ]
}

先延ばしにしていたオブジェクト内オブジェクトを記述

 先ほど ObjTodo としていたdaily_summaries内のDaySummaryも記述してみます。記述は面倒くさいですが、素直に入れ子にして書いていくだけですね。

mysql> SELECT JSON_PRETTY(
    ->   JSON_OBJECT('platform', 'iOS', 
    ->               'model', 'iPhone13,2',
    ->               'app_version', '2.0.1',
    ->               'daily_summaries', 
    ->                   JSON_ARRAY(
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1650931200000,
    ->                                 'DaySummary',JSON_OBJECT('MaximumScore', 60.0, 
    ->                                                          'ScoreSum', 60.0,
    ->                                                          'WeightedDurationSum',60.0),
    ->                                 'RecursiveSummary',null),
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1651017600000,
    ->                                 'DaySummary',JSON_OBJECT('MaximumScore', 60.0, 
    ->                                                          'ScoreSum', 60.0,
    ->                                                          'WeightedDurationSum',60.0),
    ->                                 'RecursiveSummary',null),
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1651363200000,
    ->                                 'DaySummary',JSON_OBJECT('MaximumScore', 60.0, 
    ->                                                          'ScoreSum', 60.0,
    ->                                                          'WeightedDurationSum',60.0),
    ->                                 'RecursiveSummary',null)
    ->                   )
    ->               )
    ->               ) j\G
*************************** 1. row ***************************
j: {
  "model": "iPhone13,2",
  "platform": "iOS",
  "app_version": "2.0.1",
  "daily_summaries": [
    {
      "DaySummary": {
        "ScoreSum": 60.0,
        "MaximumScore": 60.0,
        "WeightedDurationSum": 60.0
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1650931200000
    },
    {
      "DaySummary": {
        "ScoreSum": 60.0,
        "MaximumScore": 60.0,
        "WeightedDurationSum": 60.0
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651017600000
    },
    {
      "DaySummary": {
        "ScoreSum": 60.0,
        "MaximumScore": 60.0,
        "WeightedDurationSum": 60.0
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651363200000
    }
  ]
}

数値の桁についてちょっと寄り道

 先ほど生成したJSONデータ。 小数点以下ゼロの値を与えたときに、そのまま生成されていました。内部で数値として解釈されて小数点以下のゼロは省略して出力されることを予想していたので、やや意外でした。
 ちょっとした追試を。

mysql> SELECT JSON_PRETTY(
    ->   JSON_OBJECT('platform', 'iOS', 
    ->               'daily_summaries', 
    ->                   JSON_ARRAY(
    ->                     JSON_OBJECT('DateMillisSinceEpoch',1650931200000,
    ->                                 'DaySummary',JSON_OBJECT('MaximumScore', 60.0, 
    ->                                                          'ScoreSum', 60.00,
    ->                                                          'WeightedDurationSum',60),
    ->                                 'RecursiveSummary',null)
    ->                   )
    ->               )
    ->               ) j\G
*************************** 1. row ***************************
j: {
  "platform": "iOS",
  "daily_summaries": [
    {
      "DaySummary": {
        "ScoreSum": 60.00,
        "MaximumScore": 60.0,
        "WeightedDurationSum": 60
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1650931200000
    }
  ]
}

 DaySummaryの中身の数字が、与えたとおりの桁数で生成されましたね。内部でも一貫して文字列として扱われているのかしらん。。(結論なし)

テーブルデータからの生成トライ(テーブルとデータの準備)

 ここまで試してきたJSON生成は、関数の引数に、名前,値,名前,値・・の順に与え続ける必要があるものでした。テーブルからデータを取得して生成したいときはどうしたらいいのか、試みました。
 まずデータを用意します。実は、DaySummary 要素は3つの要素を単にグルーピングするだけのもので、実は daily_summaries 要素と 1対1 なので、テーブルを分ける必要がまったくなかったことに後で気づきましたが、今回はまぁこのままで。

CREATE TABLE cocoa_main (
    id INTEGER PRIMARY KEY, 
    platform VARCHAR(10),
    model VARCHAR(30),
    app_version VARCHAR(10)
);
INSERT INTO cocoa_main VALUES (1, 'iOS', 'iPhone13,2', '2.0.1');
CREATE TABLE cocoa_daily_sum (
    id INTEGER PRIMARY KEY,
    main_id INTEGER,
    DateMillisSinceEpoch BIGINT,
    RecursiveSummary VARCHAR(10)
);
INSERT INTO cocoa_daily_sum VALUES 
  (11, 1, 1650931200000, null),
  (12, 1, 1651017600000, null),
  (13, 1, 1651363200000, null);
CREATE TABLE cocoa_daySummary (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  daily_sum_id INTEGER,
  MaximumScore FLOAT,
  ScoreSum  FLOAT,
  WeightedDurationSum  FLOAT
);
INSERT INTO cocoa_daySummary VALUES 
  (null, 11, 60.0, 60.0, 60.0),
  (null, 12, 120.0, 60.0, 180.0),
  (null, 13, 4620.0, 18840.0, 18840.0);
mysql> SELECT * FROM cocoa_main;
+----+----------+------------+-------------+
| id | platform | model      | app_version |
+----+----------+------------+-------------+
|  1 | iOS      | iPhone13,2 | 2.0.1       |
+----+----------+------------+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM cocoa_daily_sum;
+----+---------+----------------------+------------------+
| id | main_id | DateMillisSinceEpoch | RecursiveSummary |
+----+---------+----------------------+------------------+
| 11 |       1 |        1650931200000 | NULL             |
| 12 |       1 |        1651017600000 | NULL             |
| 13 |       1 |        1651363200000 | NULL             |
+----+---------+----------------------+------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM cocoa_daySummary;
+----+--------------+--------------+----------+---------------------+
| id | daily_sum_id | MaximumScore | ScoreSum | WeightedDurationSum |
+----+--------------+--------------+----------+---------------------+
|  1 |           11 |           60 |       60 |                  60 |
|  2 |           12 |          120 |       60 |                 180 |
|  3 |           13 |         4620 |    18840 |               18840 |
+----+--------------+--------------+----------+---------------------+
3 rows in set (0.00 sec)

テーブルからシンプル部分のJSON生成

 冒頭の即値を与えたときと同様に、シンプルな部分だけまず生成してみます。JSON_OBJECT()関数の引数として、名前とカラムを交互に羅列していきます。

mysql> SELECT JSON_PRETTY(
    ->   JSON_OBJECT(
    ->     'platform', platform,
    ->     'model', model,
    ->     'app_version', app_version
    ->   ) 
    -> ) j FROM cocoa_main\G
*************************** 1. row ***************************
j: {
  "model": "iPhone13,2",
  "platform": "iOS",
  "app_version": "2.0.1"
}

入れ子となった daily_summariesを生成

 次に、cocoa_daily_sum テーブルを結合して、結果をJSON配列として含ませるトライです。
結合しっぱなしではレコードが3件に分かれてしまうので、cocoa_main の id列でグルーピングすることと、入れ子部分の配列には JSON_ARRAYAGG() という集約関数を使っているところがポイントです。
 ARRAYAGGで生成する配列の中身には、JSON_OBJECTが入るので、JSON_OBJECT()関数で各要素を作ることになります。(答えにたどり着いてしまえば「そのとおり」な書き方ですが、ここにたどり着くまでに結構な時間をつぶしてしまいました・・・・。JSON_OBJECTAGG()や JSON_ARRAYAGG()にはウィンドウ関数を使うこともできるため、そちらの迷路に迷い込んでしまったりなどなど。 将棋の先生方が「指されてみればなるほどという手です」とよく言いますが、それに近い心境かもしれません)

mysql> SELECT JSON_PRETTY(
    ->   JSON_OBJECT(
    ->     'platform', platform,
    ->     'model', model,
    ->     'app_version', app_version,
    ->     'daily_summaries', JSON_ARRAYAGG( JSON_OBJECT('DateMillisSinceEpoch', ds.DateMillisSinceEpoch, 'RecursiveSummary', ds.RecursiveSummary))
    ->   ) 
    -> ) j FROM cocoa_main m
    -> LEFT OUTER JOIN cocoa_daily_sum ds ON (m.id=ds.main_id)
    -> GROUP BY m.id\G
*************************** 1. row ***************************
j: {
  "model": "iPhone13,2",
  "platform": "iOS",
  "app_version": "2.0.1",
  "daily_summaries": [
    {
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651363200000
    },
    {
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651017600000
    },
    {
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1650931200000
    }
  ]
}

テーブルからの生成、最後の仕上げ

 配列の各要素となるJSONオブジェクトの中には、更に入れ子があります。内側にある DaySummaryオブジェクトも出力に追加して、一連のトライを終了としましょう。
 結局やっていることは、JSON_OBJECT中に DaySummaryという要素名を持つJSONオブジェクトをもういっこ追加しているというだけです。本節冒頭で述べたようにこのオブジェクトは実は単に見かけ上のグルーピングをしているだけであり、実態は 1対1なので、AGG系関数を使用することなく記述できています。

mysql>  SELECT JSON_PRETTY(
    ->    JSON_OBJECT(
    ->      'platform', platform,
    ->      'model', model,
    ->      'app_version', app_version,
    ->      'daily_summaries', JSON_ARRAYAGG( 
    ->                             JSON_OBJECT('DateMillisSinceEpoch', ds.DateMillisSinceEpoch, 
    ->                                         'RecursiveSummary', ds.RecursiveSummary,
    ->                                         'DaySummary', JSON_OBJECT('MaximumScore',MaximumScore, 
    ->                                                                   'ScoreSum', ScoreSum, 
    ->                                                                   'WeightedDurationSum', WeightedDurationSum) )
    ->                                       )
    ->    ) 
    ->  ) j FROM cocoa_main m
    ->  LEFT OUTER JOIN cocoa_daily_sum ds ON (m.id=ds.main_id)
    ->  LEFT OUTER JOIN cocoa_daySummary das ON (ds.id=das.daily_sum_id)
    ->  GROUP BY m.id\G
*************************** 1. row ***************************
j: {
  "model": "iPhone13,2",
  "platform": "iOS",
  "app_version": "2.0.1",
  "daily_summaries": [
    {
      "DaySummary": {
        "ScoreSum": 18840.0,
        "MaximumScore": 4620.0,
        "WeightedDurationSum": 18840.0
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651363200000
    },
    {
      "DaySummary": {
        "ScoreSum": 60.0,
        "MaximumScore": 120.0,
        "WeightedDurationSum": 180.0
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1651017600000
    },
    {
      "DaySummary": {
        "ScoreSum": 60.0,
        "MaximumScore": 60.0,
        "WeightedDurationSum": 60.0
      },
      "RecursiveSummary": null,
      "DateMillisSinceEpoch": 1650931200000
    }
  ]
}

感想

 JSON生成の入り口から始まり、テーブル上のデータを元にしてJSON形式にまとめて結果を返す、というトライをしてみましたが、実際のプロダクトで「MySQLからJSON形式でデータを取得したい」ということになってこのSQLを書いた場合、正直言ってこのSQLをメンテナンスできる自信はありません。今回は「ようやくとりあえず動くものにはなった」という段階だったので、理解が進むともう少し洗練された書き方ができるようになるのかもしれませんが・・・・。
 あとは、入れ子になっているように見えて、実は単なるグループにしてみたいだけという例もあり(姓と名という要素があるときに、それらをまとめて「氏名」というJSONオブジェクトにするみたな感じ)、入れ子の深さとDB的なデータの持ち方に、直接の関係はないのだなという印象を受けました。このあたりは、様々なデータを触っているうちに、肌感覚のようなもので見えてくることがあるかもしれません。

 こういうJSON生成は、DBMSでやるよりも、ORマッパなどで各テーブルからの結果をオブジェクトに入れてそちらで生成させるほうが、得意かもしれませんね。 一応今回は、MySQLで生成する場合というトライでした。実運用でこういった事をやられている方がいましたら、ぜひブログとかで使用例などを聞かせてください。

Dolphins image by DALL-E

MySQL の Generated Invisible PK(GIPK)を試す

7月にリリースされた MySQL 8.0.30 では「Generated Invisible Primary Key(GIPK)」という機能が追加されました。各テーブルにPKがあることを必須とし、ない場合はテーブル作成時に自動的に不可視列としてAUTO_INCREMENTなPK列を追加してくれる、という機能です。

Invisible Column の仕組み自体は MySQL 8.0.23 (2021年1月)で追加されたものです。当時の日記を読み返すと、使い道がよくわからないと言いながらも、8.0.30で追加された機能と似たような使い方を想像はしていたようです。悪くない。

sakaik.hateblo.jp

sakaik.hateblo.jp


 ということで、どんなクセを持ったものなのか触ってみたいと思います。

きっかけは tmtms さん

 自動でカラムを追加してくれるということは、そのカラムと同じ名前のものを明示していた場合、どうなるんだろうという、tmtmsさんのツイートをみて、試してみたいと思ったのがきっかけでした。



モードの確認と設定

 GIPKの機能によりPKを自動生成させるには「GIPKモード」にしておく必要があります。@@sql_generate_invisible_primary_key 変数にて設定します。デフォルトはオフ(0)です。

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    0 |
+--------------------------------------+

オンにするには、1 または on または true にします。

mysql> SET  @@sql_generate_invisible_primary_key=on;
Query OK, 0 rows affected (0.00 sec)

確認するとオン(1)になっていることがわかります。

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    1 |
+--------------------------------------+


 なお、今回はテスト用に一時的に自分のセッションだけ GIPKモードにしたものなので、再接続など別セッションとなった場合はまたオフになることにご注意ください。
 global と session の各設定状況を見てもその通りになっていますね。

mysql> show global variables like '%invisible%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | OFF   |
+------------------------------------+-------+
mysql> show session variables like '%invisible%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | ON    |
+------------------------------------+-------+

 変数名をSELECTしたときには 0/1 で表示されるのに、variables を見た場合は ON/OFF になっているのが、ちょっと面白いですね。

GIPKモードがオンとオフの動作の違い

 一旦、GIPKモードをオフにして、PKなしのテーブル(id, name の2つの列を持つ)t1 を作成して、作成されたテーブル定義を確認してみます。

mysql> SET  @@sql_generate_invisible_primary_key=off;

mysql> CREATE TABLE t1 (id INTEGER, name VARCHAR(10));
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp932

 当然、CREATE TABLE 文で指定した2つの列を持つテーブルが作成されました。

 次に、GIPKモードをオンにして、同様の列を持つテーブル t2 を作成してみます。

mysql> SET  @@sql_generate_invisible_primary_key=on;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 (id INTEGER, name VARCHAR(10));
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932

 CREATE TABLE 文で指定していない my_row_id という列が追加されました。 8.0.23以降の(8.0.30ではありません)場合に "INVISIBLE"が有効となるように記述されていますね。

データを入れてみる

 ユーザが作成を指示したテーブルは、id, name の2つの列を持つテーブルですから、勝手に付けたカラムが通常の操作を阻害するものとなってはいけません。簡単なINSERT文で試してみます。

mysql> INSERT INTO t2 VALUES (3, "test");
Query OK, 1 row affected (0.01 sec) 

mysql> select * FROM t2;
+------+------+
| id   | name |
+------+------+
|    3 | test |
+------+------+

 よかった。 INSERT 文の VALUES の前に列を明示していませんが、(3カラムではなく)2カラムのINSERT文が無事実行されました。

 列を明示してSELECTすると、my_row_id にも値が入っていることが確認できます。

mysql> select my_row_id, id, name from t2;
+-----------+------+------+
| my_row_id | id   | name |
+-----------+------+------+
|         1 |    3 | test |
+-----------+------+------+

同名の列(my_row_id)がすでにある場合は?

 冒頭の tmtms さんのツイートにある疑問を、ここで試してみましょう。
PKではない my_row_id 列を持つ CREATE TABLE 文を実行します。

mysql> CREATE TABLE t3 (my_row_id INTEGER, name VARCHAR(10));
ERROR 4108 (HY000): Failed to generate invisible primary key. Column 'my_row_id' already exists.

 エラーになりました。 
ん?でもちょっと待ってください。 Column 'my_row_id' already exists ですって? 単純に同じ名前のカラムを2つ以上指定して CREATE TABLE を実行した場合は、別のエラー(Duplicate column name)となるので、それとは別のロジックによりエラーが出されていることになります。このへん、昨日 LINE Developers Meetup のMySQLの回でお話聞かせてもらった tom__boさんならソースコード追い始めるのでしょうが、私はここで断念。 機会あったらこれを題材にして追い方講座など、お話聞いてみたいものです。

mysql> CREATE TABLE t3 (id INTEGER, id INTEGER, name VARCHAR(10));
ERROR 1060 (42S21): Duplicate column name 'id'

GIPKは消せるの?

 単純には消せません。GIPKモードというのは「テーブルにPKを必須とする」モードなので、PKがなくなることが許されないのです。・・・と分かったような書きっぷりをしてしまいましたが、エラーメッセージの「今のバージョンではまだ」という記述がちょっと気になりますね。将来はDROPできるようになる(=PK必須モードというわけではない)のかもしれません。
>>This version of MySQL doesn't yet support 'existing primary key drop without adding a new primary key.

mysql> ALTER TABLE t3 DROP COLUMN my_row_id;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'existing primary key drop without adding a new primary key. In @@sql_generate_invisible_primary_key=ON mode table should have a primary key. Please add a new primary key to be able to drop existing primary key.'


 なお、エラーメッセージで指示されたとおり、GIPK列をDROPすると同時に新たなPK列を追加すれば、エラーなく実行できます。

mysql> ALTER TABLE t3 DROP COLUMN my_row_id, ADD COLUMN (id INTEGER PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `name` varchar(10) DEFAULT NULL,
  `id` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=cp932

GIPK の機能を使わないで明示的に指定することももちろんできる

 GIPKは「CREATE TABLE に PK の指定がない場合に、自動で隠しPK列を追加しておく」という機能なので、PKを明示すれば発動しませんし、また、そのPK列を明示的にINVISIBLEにする(変な日本語)ことも可能となります。

mysql> CREATE TABLE t4 (id INTEGER AUTO_INCREMENT PRIMARY KEY INVISIBLE, name VARCHAR(10));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t4 VALUES ("test");
Query OK, 1 row affected (0.01 sec)

テーブルにひとつはVISIBLEな列は必要

 8.0.30 の Generated Invisible な話に限らず、8.0.23からの話ではありますが、Invisible列だけのテーブルを作ったり、そういうテーブルになるようなテーブル定義操作はできません。

mysql> ALTER TABLE t4 DROP COLUMN name;
ERROR 4028 (HY000): A table must have at least one visible column.

おわりに

 ということで、Generate Invisible Primary Key ならびに、そもそもの Invisible カラムについて、遊んで見ました。検証中に少し気になることを検索していたら、既に Updraft さんが 8月1日に似たようなことを試されていました。さすがです。 遅れを取った。。。

updraft.hatenadiary.com


追記(2022/08/09)

以下の記事(MySQL道普請便 第177回『MySQL 8.0.30の新機能:不可視プライマリキーについて』)も参照ください。
gihyo.jp


Dolphins image by DALL-E

MySQL 8.0.30/MySQL5.7.39リリース。ミラーへの配布は終了?

MySQL 8.0.30 と MySQL 5.7.39 がリリースされました。

.

.


MySQL 8.0.30 に関するツイートをまとめました(togetter)
togetter.com


MySQL リリースノートでわいわい言う勉強会8.0.30』 は、8月中下旬くらいにオンラインで開催予定です。

ミラーサーバへの配布は行われず

 これまでMySQLは、世界各国のミラーサーバへのファイルの配布を行っていましたが、今回の MySQL 8.0.30 では、この配布が行われなかったようです(同時リリースの他のバージョンやツール類も同様)。
 国内では、IIJ のミラーサーバーが 2020年3月の MySQL 8.0.20 を最後にミラーを行わなくなり、そして今回、2022年7月のリリースでは、JAISTや その他世界各国でミラーしていたサーバで(私が確認している数個のサイト程度ですが)すべてミラーが行われていませんでした。
 このことを「またひとつ、MySQLからオープンさが失われた」と見る向きもあるでしょうが(ひっそりと html形式のマニュアル配布が停止されたこともありましたし)、個人的には、ミラーftpがいよいよその役目を終えたのだとも理解しています。

 もともとは、ソフトウェアの新バージョンが公開されたときに、配布元のサーバにアクセスが集中してパンクすることを避けるために互助的な仕組みを作ったのが、ftpミラーサーバだったと理解しています。今では、堅牢なファイル配布の仕組みを自前で用意することはさほど難しくなく、MySQLチームとしてはもうftpミラーを使用する理由はない(むしろ今までは、配布が行き渡るのを待ってからリリースしていたように見えており、ミラーを使うほうが待ち時間が増えていた)ので、今回の中止(のように見える動き)もさもありなんと。時代の流れですねぇ。。。

 余談ですが、昔はMySQLをダウンロードするときに「No thank you , just start a download」とか出たあとで、ミラーサーバを自分で選んでいましたよね。それがなくなったのって、いつだったんでしょうね。そういえばなくなっていたな~と今ごろ気づきました。覚えている方、どこかでお話を聞かせてください(書いてくださいw)。

 まぁいずれにせよ、ひとつの時代が静かに終わっていくのを目の当たりにして、少しだけしょんぼりした気分になりました。

MySQLでJSON体験(その2:JSON_TABLEを使う)

MySQLJSONを扱う入門として 前回の日記 では比較的シンプルな関数群を試してみました。今回は、JSON 内の配列部分をテーブルにして返す JSON_TABLE 関数を試してみます。

JSON_TABLE() の基本的な使い方。

 JSON_TABLE()は、JSON内の配列部分を引数に与えて、その配列内の要素をテーブル形式にして返してくれる関数です*1。「1個のJSONデータ」から複数の行が生成されます。
 以下は、JSON_TABLE()関数の第一引数に生のJSON配列をそのまま与えてみた例です。

mysql> SELECT * FROM JSON_TABLE('[
    '>     {
    '>       "DateMillisSinceEpoch": 1650931200000,
    '>       "ConfirmedClinicalDiagnosisSummary": "sample01",
    '>       "RecursiveSummary": null,
    '>       "SelfReportedSummary": null
    '>     },
    '>     {
    '>       "DateMillisSinceEpoch": 1651017600000,
    '>       "ConfirmedClinicalDiagnosisSummary": null,
    '>       "RecursiveSummary": null,
    '>       "SelfReportedSummary": null
    '>     },
    '>     {
    '>       "DateMillisSinceEpoch": 1651363200000,
    '>       "ConfirmedClinicalDiagnosisSummary": "sample03",
    '>       "RecursiveSummary": null,
    '>       "SelfReportedSummary": null
    '>     }
    '> ]', '$[*]' COLUMNS (
    ->      daily_sum BIGINT PATH '$.DateMillisSinceEpoch',
    ->      confd_diag_sum VARCHAR(20) PATH '$.ConfirmedClinicalDiagnosisSummary'
    ->    )
    ->   ) t;
+---------------+----------------+
| daily_sum     | confd_diag_sum |
+---------------+----------------+
| 1650931200000 | sample01       |
| 1651017600000 | NULL           |
| 1651363200000 | sample03       |
+---------------+----------------+

 JSON_TABLE()は、このように、第1引数にJSON配列を、第2引数にはどのJSONパスの内容を何という名前のカラムとして生成するのかを定義する情報を与える使い方ができます。

即値を与えるのじゃ使いにくい

 関数の引数にJSONの値をそのまま記述するのは使いにくいので、一歩進めて変数を使うことにした方法です。JSON_TABLE()関数を使用する部分は、少しスッキリしました。

mysql> SET @myjson='[
    '>     {
    '>       "DateMillisSinceEpoch": 1650931200000,
    '>       "ConfirmedClinicalDiagnosisSummary": "sample01",
    '>       "RecursiveSummary": null,
    '>       "SelfReportedSummary": null
    '>     },
    '>     {
    '>       "DateMillisSinceEpoch": 1651017600000,
    '>       "ConfirmedClinicalDiagnosisSummary": null,
    '>       "RecursiveSummary": null,
    '>       "SelfReportedSummary": null
    '>     },
    '>     {
    '>       "DateMillisSinceEpoch": 1651363200000,
    '>       "ConfirmedClinicalDiagnosisSummary": "sample03",
    '>       "RecursiveSummary": null,
    '>       "SelfReportedSummary": null
    '>     }
    '> ]';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM JSON_TABLE(@myjson, '$[*]' COLUMNS (
    ->      daily_sum BIGINT PATH '$.DateMillisSinceEpoch',
    ->      confd_diag_sum VARCHAR(20) PATH '$.ConfirmedClinicalDiagnosisSummary'
    ->    )
    ->   ) t;
+---------------+----------------+
| daily_sum     | confd_diag_sum |
+---------------+----------------+
| 1650931200000 | sample01       |
| 1651017600000 | NULL           |
| 1651363200000 | sample03       |
+---------------+----------------+
3 rows in set (0.00 sec)

 

テーブル上のJSONデータを使用する(トライ1:失敗)

 前回の日記で作成した j1 テーブルのデータから配列部分を取ってきて、第1引数に与えることを試みます。配列部分は

j->"$.daily_summaries"

で示されるパスにあるので、これを使ってみたのが以下の例。

mysql> SELECT * FROM JSON_TABLE((SELECT j->"$.daily_summaries" FROM j1),
    ->   '$[*]' COLUMNS (
    ->      daily_sum BIGINT PATH '$.DateMillisSinceEpoch'
    ->    )
    ->   ) t;
ERROR 1210 (HY000): Incorrect arguments to JSON_TABLE

 あらら。エラーです。少しずつ記述法を変えるなどして試したのですが、どうやら第1引数にはサブクエリは(まだ?)採れないようです。

テーブル上のJSONデータを使用する(トライ2:一応成功、ただし格好悪い)

 そこで、先ほど試した「一旦変数に入れる」ワザを使います。

mysql> SET @myj:= (SELECT j->"$.daily_summaries"  FROM j1);
  '$[*]' COLUMNS (
     daily_sum BIGINT PATH '$.DateMillisSinceEpoch'
   )
  ) t;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM JSON_TABLE(@myj,
    ->   '$[*]' COLUMNS (
    ->      daily_sum BIGINT PATH '$.DateMillisSinceEpoch'
    ->    )
    ->   ) t;
+---------------+
| daily_sum     |
+---------------+
| 1650931200000 |
| 1651017600000 |
| 1651363200000 |
| 1651449600000 |
(略)
| 1657756800000 |
| 1657843200000 |
| 1658102400000 |
+---------------+
32 rows in set (0.00 sec)

 一応成功しました。

テーブル上のJSONデータを使用する(トライ3:一応成功、ただし納得してない)

 先ほどの例では、やりたいことは実現できましたが、一旦変数に入れるあたりがRDBMSっぽくなくて格好悪いです。べつにカッコつける必要もないのだけど。

 ネットを見ていると、JSONデータを格納しているテーブルそのものと、JSON_TABLE()によって作られたテーブルとをJOINすれば良いらいしいということでやってみたら、それっぽい結果に。

mysql> SELECT j1b.* 
    ->   FROM j1 
    ->    JOIN JSON_TABLE(j1.j->"$.daily_summaries",
    ->     '$[*]' COLUMNS (
    ->      daily_sum BIGINT PATH '$.DateMillisSinceEpoch',
    ->      score_sum INTEGER PATH '$.ConfirmedTestSummary.ScoreSum'
    ->    )
    ->   ) j1b;
+---------------+-----------+
| daily_sum     | score_sum |
+---------------+-----------+
| 1650931200000 |        60 |
| 1651017600000 |        60 |
| 1651363200000 |        60 |
| 1657670400000 |       600 |
(略)
| 1658102400000 |        60 |
+---------------+-----------+
32 rows in set (0.00 sec)

 ただし、これ、結合条件を書いていないところが気持ち悪くて、たぶん今回レコードが1件しかないおかげでまともに動作しているように見えるだけなのかも、、、という気もしています。必要なときにもう少しツッコんで調べることにします。

最終目的地へ

 もともとは、「COCOAアプリが吐き出したJSONデータを、MySQLを使って見てみたい」ということで、今回の「お勉強」が始まったのでした。ということで、最終目的地のひとつに到着します。先ほど得たデータは、日ごとの陽性者との接触時間を表したデータでした。daily_sum として取得した列は、unix timestamp のミリ秒なので、演算を加えて日付にしてみました。ということで、GO!

mysql> SELECT DATE(FROM_UNIXTIME(j1b.daily_sum/1000)) dt, j1b.score_sum 
    ->   FROM j1 
    ->    JOIN JSON_TABLE(j1.j->"$.daily_summaries",
    ->     '$[*]' COLUMNS (
    ->      daily_sum BIGINT PATH '$.DateMillisSinceEpoch',
    ->      score_sum INTEGER PATH '$.ConfirmedTestSummary.ScoreSum'
    ->    )
    ->   ) j1b
    -> ;
+------------+-----------+
| dt         | score_sum |
+------------+-----------+
| 2022-04-26 |        60 |
| 2022-04-27 |        60 |
| 2022-05-01 |        60 |
| 2022-05-02 |       840 |
| 2022-05-05 |        60 |
| 2022-05-06 |        60 |
| 2022-05-08 |        60 |
| 2022-05-10 |        60 |
| 2022-05-25 |        60 |
| 2022-06-05 |        60 |
| 2022-06-07 |       300 |
| 2022-06-08 |       240 |
| 2022-06-09 |        60 |
| 2022-06-12 |        60 |
| 2022-06-13 |        60 |
| 2022-06-14 |        60 |
| 2022-06-16 |        60 |
| 2022-06-20 |        60 |
| 2022-06-24 |        60 |
| 2022-06-30 |        60 |
| 2022-07-01 |        60 |
| 2022-07-03 |        60 |
| 2022-07-04 |        60 |
| 2022-07-06 |        60 |
| 2022-07-07 |        60 |
| 2022-07-08 |        60 |
| 2022-07-11 |     18840 |
| 2022-07-12 |        60 |
| 2022-07-13 |       600 |
| 2022-07-14 |        60 |
| 2022-07-15 |        60 |
| 2022-07-18 |        60 |
+------------+-----------+
32 rows in set (0.00 sec)

 7/11 に、大変たくさんの時間、陽性の方と過ごしたようです。 18840 という数字については、もう少し COCOAファイルの定義書を読む必要がありますが、スマホの画面に表示された値を見比べると、この値を 24 で割ると、接触していた分数(の合計)になるようです。
(ちなみに、幸いにもこの接触による私への感染は、なかったようです(あれから2週間以上経過))

おわりに

 こんな感じで、JSON_TABLE()を使うと、ひとつのJSONデータから、テーブルの形式に変換して値を抽出することができるようになることがわかりました。 今回は1件のデータだけを持つテーブルを元にして試したので、今後、複数レコードあるケースなど、もうすこし試してみるべきかもしれません。(個人的にはここで、取得に関しては一旦おしまいにします)

 せっかく触り始めたので、生成系の関数も試してみたくなってきました。そのうち試してみたいと思います。

*1:実際はもうちょっといろいろなものを与えられます。詳しくはマニュアル参照