MySQLリリースノートで(略)勉強会8.0.30を開催しました

mysql

MySQL 8.0.30 のリリースノートをみてわいわい言う勉強会を開催しました。

mysql.connpass.com


　ここ数回は18時から開催していたところ、今回19時からの開催にしてみましたが、個人的には半端な時間だなぁという印象でした。皆さんにも尋ねたところ、まさに十人十色のご都合があり、お話を聞かせてもらいたい方もいっぱいいて、みんなが合う時間帯を見つける難しさを感じました。とはいいながらも時間を工面して参加してくださった皆さん、本当にありがとうございました！

　あと、アレです。「車座になってわいわい言うような、顔の見えるイベントをやりたい」という趣旨でカメラオンを（案内ページでは）お願いしていたものですが、オンラインイベントにそれを求めるのは無理なのではないかという思いに、最近傾きつつあります。表情や反応が見えたほうがコミュニケーションとしての情報量は多いのだけど、本質は「技術的な情報交換」なので、むしろカメラオフの気楽さの中で「本来交換すべき情報に集中する」のが、いいのかもな、という気になりつつあります。ということで次回以降、そのへんの方針を変更するかもしれません。


　さて、内容。こんなあたりを中心にお話をしました。



　以下、個人的に気になった（というか、今これを書いている時点で思い出したり思いついたりしている）ものをいくつか。

ミラーへの配布停止の模様

　以前の日記にも書いた話題です。　MySQL 8.0.30 では、これまでファイルが配布されていた世界各地のミラーサーバー（ftpサーバーという名のhttpサーバー）へのファイル群の配布が行われませんでした。
　
sakaik.hateblo.jp

　ミラーサーバーに存在する MySQL 8.0 の最新バージョンは MySQL 8.0.29 ということになるのですが、まさにこれは今回「配布停止」にするほどの問題を抱えているバージョン。　ミラー処理が停止している（というかミラー元となるファイルが更新されていない？）ために、MySQL 8.0.29 が最新である状態のままなのは、あまりよろしくないので、この辺の対応がなにかなされるかもしれませんし、なされるといいなぁ。

なんといっても GIPK

　Generated Invisible Primary Key。8.0.30目玉の新機能ですね。（注：メンテナンスリリースです）
みなさんとお話をしてもなお、いまひとつ使いどころがピンと来ていませんが、「普通に扱っている分には存在が見えないPK」があると便利な世界があるのだろうと（とあるパッケージ製品とか）、想像するところまではできました。

(col1, col2) IN ((v1, v2), (v3,v4)....)

　大昔からある構文だったようです。私が全然勉強不足でした。使わない構文は知る機会にも出会えないので、今回リリースノート内で見て知ることができたのは良かったです。 MySQL 8.0.30 で直ったのは、これが「(Date型, Date以外の型)」の組み合わせになっているときの問題だったようです。よく見つけたな、こんなの。

INSTANT ADD COLUMN 系

　MySQL 8.0.29 での最大の問題だったこれらの機能が、MySQL 8.0.30 でずいぶん修正されました。ただし、まだ修正されきっていない部分があるようで、8.0.31での更なる修正が期待されます。といったところ。

innodb_doublewrite の DETECT_ONLY

　全然分かっていなかったので、丁寧に教えていただきました。ありがとうございます！　簡単にまとめると、今までは doublewrite を「する」「しない」の二択だった。これは、doublewrite すれば打ち所が悪く落ちた場合でもクラッシュリカバリできるし（2カ所に書いているのでそれを比較することで検知可能）、オフにすれば勿論検知すらできずに、データファイルが壊れた状態で稼働を続ける（そして壊れた部分＝地雷＝を踏むと落ちる）、という二択だったということ。　今回の DETECT_ONLY は「検知はできるがクラッシュリカバリは不可能」というものらしく、壊れた状態で稼働を続けるのを防ぐことができる。　doublewrite すれば安全にはなるのだけど、要するに2カ所に書くので結構処理負荷は高い。 というバーターの中で、バランス良く使えるのが DETECT_ONLY だと。
　ひとつ賢くなった！
・・・でも、どうやって検知するのか（doubleではないが何処かに何かを書かなければ検知できないのでは）という話をツッこみそびれてしまった。。不覚。

mysqld の設定や変数など

  • replica-allow-batching が mysqld/variables でデフォルト ON になった。なんだろこれ。
  • federated がデフォルト ON → OFF へ変更。
  • innodb-double-write がデフォルト TRUE → ON へ。これは↑で書いた DETECT_ONLY の追加により、TRUE/FALSE でなく、ON/DETECT_ONLY/OFF へと変更になったため
  • GIPK関係の設定が 追加

status/performance_schema系など

  • Innodb_redo_* 系の値やp_sテーブルが追加。SHOW INNODB STATUSで見ていたものが、statusやp_s で取れるようになった
  • collation関係。utf8がmb3を指しているのをmb4へと切り替えていく過程として、Collation 名に単に「utf8」とされていたものを utf8mb3 および utf8mb4 へと明示する変更

参考URL


　

MySQL: ゼロはFALSE、イチはTRUE

mysql

MySQLにおける、TRUEとFALSEとゼロとイチ。


同僚が WHERE column1 = 1 OR 2 が全ての行を返すと一瞬で看破していてすごい


後者で悩まされたの思い出したなぁ

select * from sample where id = 1 or 2;
+----+-----+
| id | col |
+----+-----+
|  1 | a   |
|  2 | b   |
|  3 | c   |
+----+-----+

select * from sample where id = (1 or 2);
+----+-----+
| id | col |
+----+-----+
|  1 | a   |
+----+-----+


で。
うっかり私は「ゼロがFALSE、それ以外はTRUE」と勘違いをして、こんなツイートをしてしまいました。

本のタイトルになりそうですね。
「ゼロかそれ以外か - MySQLという生き方」

間違いでした！

　MySQLにおいて、「ゼロはFALSE、イチはTRUE」それ以外はTRUEでもFALSEでもありません。



データ登録：

mysql> CREATE TABLE t24 (num float);
mysql> insert into t24  VALUES (-2), (-1), (0), (1), (2), (3), (-0.3), (0.4),(1.2);


確認：

mysql> SELECT num, (num=FALSE), (NUM=TRUE) FROM t24;
+------+-------------+------------+
| num  | (num=FALSE) | (NUM=TRUE) |
+------+-------------+------------+
|   -2 |           0 |          0 |
|   -1 |           0 |          0 |
|    0 |           1 |          0 |
|    1 |           0 |          1 |
|    2 |           0 |          0 |
|    3 |           0 |          0 |
| -0.3 |           0 |          0 |
|  0.4 |           0 |          0 |
|  1.2 |           0 |          0 |
+------+-------------+------------+
9 rows in set (0.00 sec)

わお。

追記[2022/08/15]

　タイトルが正確ではないという指摘をいただきました。本来の意味としては、まったくご指摘のとおりです。

どちらかというと「FALSEはゼロ、TRUEはイチ」の方が正しいかもしれません！

TRUE + TRUEは2ですが、2 IS TRUEは1

　本記事は、数字に焦点を当てたもので「ゼロ、イチはわかった。ではそれ以外は？」という話の展開であるため、このようなタイトル付けをしていますが、考え方としては指摘のとおりなので、お読みのみなさまにおかれましてはそのように理解していただければと思います。

同追記（おまけ）

mysql> SELECT TRUE+TRUE, TRUE/2, TRUE+TRUE=TRUE, TRUE-TRUE=FALSE;
+-----------+--------+----------------+-----------------+
| TRUE+TRUE | TRUE/2 | TRUE+TRUE=TRUE | TRUE-TRUE=FALSE |
+-----------+--------+----------------+-----------------+
|         2 | 0.5000 |              0 |               1 |
+-----------+--------+----------------+-----------------+

再追記

　TRUEかFALSEかの判定って、イコールではなく IS を使うべきだったのか？ だとすると当初の私の「勘違い」だと思っていた「ゼロ以外はTRUE」は正しかったことになる。
　現実的には、今回の元ネタのように誤って書いてしまった挙動の理解のために役にたつ知識であって、わざわざ数値演算をした結果を TRUE/FALSE判定することはないと思うので、知らなくてもかまわないのですが、この「IS」の挙動、知らなかったなぁ・・・。

mysql> SELECT num, num IS FALSE, num IS TRUE FROM t24;
+------+--------------+-------------+
| num  | num IS FALSE | num IS TRUE |
+------+--------------+-------------+
|   -2 |            0 |           1 |
|   -1 |            0 |           1 |
|    0 |            1 |           0 |
|    1 |            0 |           1 |
|    2 |            0 |           1 |
|    3 |            0 |           1 |
| -0.3 |            0 |           1 |
|  0.4 |            0 |           1 |
|  1.2 |            0 |           1 |
+------+--------------+-------------+
9 rows in set (0.01 sec)

つい追記[2022/08/16]

　なんだかもにょもにょするのですが、たぶん「真か偽か」という状態の話と「TRUEかFALSEか」という値の話は別々にすべきことなのかもしれない、との考えに至りました。




Zero dolphin image by Dall-E

続続・歯抜けを埋めるSQL（WHERE句のRAND()関数に気をつけろ！編）

mysql

ひとつ前の日記で、こんなことを書きました。

1000回ループを回して1件ずつDELETEしたのに、1006件が消えている謎は、気持ち悪いので少し考えてみたいと思います。ナゾが解けた方はコメント欄やTwitterやご自身のブログなどで教えてください。
　実はこの動作、2回実施しているのですが、1回目は 99029件になりました。こちらは既に削除済みのIDを再度削除に行くなどが発生したのだと理解していたのですが、見直してみるとこれも、ずいぶんと重複しすぎている感もありますね。（10万の中から数字をひとつ決めるのに、こんなに被らないだろうと）


具体的に発生している事象の詳細は、ひとつ前の日記をご覧ください。

さっそくおしえていただきました！

@tmtmsさんの以下のコメントから始まるスレッドを追ってもらえれば、全部書いてありますが、この日記でも少し整理しておきたいと思います。



こたえはコレ

あ、RANDってクエリの先頭で固定化されるわけじゃなく、1行につき1回ずつRANDを計算するのでそれ有り得ますね…

SQLむずい…
https://dev.mysql.com/doc/refman/8.0/ja/mathematical-functions.html#function_rand
> WHERE 句内の RAND() は、行ごと (テーブルから選択する場合) または行の組合せごと (複数テーブル結合から選択する場合) に評価されます。 したがって、オプティマイザのために、RAND() は定数値ではなく、インデックスの最適化に使用できません。

整理＆検証

　要するに今回 ひとつのSQL文を実行したわけですが、WHERE句に書かれたRAND()関数は、評価のたびに実施されます。　今回はWHERE句に他の絞り込み条件がないので、10万回のRAND()関数が内部では走ったということです。　
　（そんなの、SQL文を書いている時に想像もできないよね・・・）


　試しに WHERE句ではなく SELECT句にRAND()関数を書いたときは以下のようになります。この例では 14-14の行だけが「マッチ」したことになります。場合によってはひとつもマッチしないこともあるし、３つも４つもマッチすることもある、という事象も納得できますね。

mysql> SELECT id, FLOOR(RAND()*15) FROM t22 WHERE id<=15;
+----+------------------+
| id | FLOOR(RAND()*15) |
+----+------------------+
|  1 |                2 |
|  2 |                0 |
|  3 |               10 |
|  4 |                3 |
|  5 |                1 |
|  6 |               11 |
|  7 |                8 |
|  8 |                9 |
|  9 |                6 |
| 10 |                5 |
| 11 |                6 |
| 12 |                0 |
| 13 |               11 |
| 14 |               14 |
| 15 |                6 |
+----+------------------+

例のSQL文の改善案

　プロシジャ内に記述されたループの中での実行なので、
DELETEを行うSQL文内でRAND()関数を使用するのではなく、その前の行あたりで、

SET @rid=FLOOR(RAND()*100000);

して、WHERE句には、この @rid を使用するようにすれば良さそうです。

mysql> delimiter //
mysql> CREATE PROCEDURE del1000ken()
    -> BEGIN
    ->   declare  i int default 0;
    ->   while i<1000 do
    ->     SET  @rid=FLOOR(RAND()*100000);
    ->     DELETE FROM t22 WHERE id = @rid;
    ->     SET i=i+1;
    ->   end while;
    -> END;
    -> //
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;


mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL del1000ken();
Query OK, 1 row affected (0.08 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM t22;
+----------+
| COUNT(*) |
+----------+
|    99007 |
+----------+
1 row in set (0.00 sec)

　1分半かかっていたDELETEのプロシジャが、一瞬で終わるようになりました。遅いのは削除処理ではなく、ループ内で毎回10万回のRAND()を評価している部分だったんですね。

お礼

　このRAND()関数のように、クエリ内で固定されない関数には要注意ですね。@tmtms さん、@yoku0825 さん、ありがとうございました！！！スッキリしました！　



おまけ

　ひとつのSQL文実行の中で固定化されない例としては、SYSDATE()があります。

+---------------------+---------------------+----------+---------------------+---------------------+
| SYSDATE()           | NOW()               | SLEEP(3) | SYSDATE()           | NOW()               |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2022-08-13 00:15:08 | 2022-08-13 00:15:08 |        0 | 2022-08-13 00:15:11 | 2022-08-13 00:15:08 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (3.00 sec)

Dolphins image by DALL-E

続・歯抜けを埋めるSQL（10万件編）

mysql

先日、こんな日記を書きました。
sakaik.hateblo.jp



この中で、

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

と書いたのですが、10万件程度ならすぐに試せそうなので、やってみました。

条件と手順

  • 1から10万までの primary key auto_increment な値を持つ列がある
  • そのうち 1000件ほどを歯抜けにしてみる
  • 前回作った「歯抜けを埋めるSQL」を試してみる

テーブルとデータの準備

テーブル作成

mysql> CREATE TABLE t22 (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30), somedata VARCHAR(10));
Query OK, 0 rows affected (0.03 sec)

10万件のデータを作る。最初は100万でやろうとしていたのにゼロ書き間違えて10万になってしまったので、プロシジャ名と一致していないのはご愛敬。

mysql> delimiter //
mysql> CREATE PROCEDURE make100man()
    -> BEGIN
    ->   declare  i int default 0;
    ->   while i<100000 do
    ->     INSERT INTO t22 VALUES (null, "sampledata", "oh!!");
    ->     SET i=i+1;
    ->   end while;
    -> END;
    -> //
ERROR 1304 (42000): PROCEDURE make100man already exists
mysql> delimiter ;

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL make100man();
Query OK, 1 row affected (5.19 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.11 sec)

5秒ほどで10万件のデータが作成されました。

ここからランダムにだいたい1000件くらいのデータを削除します。

mysql> delimiter //
mysql> CREATE PROCEDURE del1000ken()
    -> BEGIN
    ->   declare  i int default 0;
    ->   while i<1000 do
    ->     DELETE FROM t22 WHERE id = FLOOR(RAND()*100000);
    ->     SET i=i+1;
    ->   end while;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL del1000ken();
Query OK, 1 row affected (1 min 34.72 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

削除は、やはり時間がかかりますね。1分半ほどかかりました。

一応件数確認。

mysql> SELECT COUNT(*) FROM t22;
+----------+
| COUNT(*) |
+----------+
|    98994 |
+----------+

　あれ。。。ランダムなので重複あった場合、1000件未満の削除しかなされないことは予想していたのですが、消えすぎてる・・・？　1000回しかループ回してないのに1000以上消えてる。。なんかおかしいですが、今回の作業に影響はないので、先に進みます。（いやほんと気持ち悪い。何がおきてるんだ・・）

例のSQLを試す

　ということで、先日の日記の結論となったSQLを流してみます。今回はINSERTはせずに、見つけ出すところまで（SELECTだけ）を実施することにします。

mysql> WITH RECURSIVE num(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<(SELECT MAX(id) FROM t22))
    -> SELECT n id, null, null FROM num LEFT OUTER JOIN t22 ON (num.n=t22.id) WHERE t22.id IS NULL;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

　あらら。エラーになってしまいました。 CTE内でrecursive階層に制限があるのでした。一時的にこの値を上限開放して続行することにします。

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

mysql> WITH RECURSIVE num(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<(SELECT MAX(id) FROM t22))
    -> SELECT n id, null, null FROM num LEFT OUTER JOIN t22 ON (num.n=t22.id) WHERE t22.id IS NULL;
+-------+------+------+
| id    | NULL | NULL |
+-------+------+------+
|    75 | NULL | NULL |
|    85 | NULL | NULL |
|   168 | NULL | NULL |
|   291 | NULL | NULL |
:
| 99023 | NULL | NULL |
| 99044 | NULL | NULL |
| 99075 | NULL | NULL |
| 99110 | NULL | NULL |
| 99153 | NULL | NULL |
| 99522 | NULL | NULL |
| 99840 | NULL | NULL |
| 99858 | NULL | NULL |
| 99896 | NULL | NULL |
+-------+------+------+
1006 rows in set (0.19 sec)

　0.2秒程度で返ってきました。件数もちょうど歯抜け部分の件数と一致しています。

なお、このMySQLは、MySQL 8.0.30、メモリ系のパラメタはほぼデフォルトのままで動作させているものです。10万件の中の1000件を埋める程度なら、予想通り楽勝ですね。


未練

1000回ループを回して1件ずつDELETEしたのに、1006件が消えている謎は、気持ち悪いので少し考えてみたいと思います。ナゾが解けた方はコメント欄やTwitterやご自身のブログなどで教えてください。
　実はこの動作、2回実施しているのですが、1回目は 99029件になりました。こちらは既に削除済みのIDを再度削除に行くなどが発生したのだと理解していたのですが、見直してみるとこれも、ずいぶんと重複しすぎている感もありますね。（10万の中から数字をひとつ決めるのに、こんなに被らないだろうと）

追記[2022/08/13]

　「未練」となっていた不思議な動作について、判明しました。不思議でもなんでもありませんでした。動作原理をしっかり知っていれば納得の挙動です。次の日記を参照ください。


.

Dolphins image by DALL-E

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

mysql event mysql

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

mysql.connpass.com

今回は少なめ

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

UnicodeとUTF-8とUTF-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

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 の回に参加(視聴)した話

mysql event

　昨火曜日にオンラインで開催された「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に興味を持つ人の目に触れやすくなるので、お得・・・になることがあるかもしれません。ぜひぜひご活用を。
　そして今回、濃厚で素敵なイベントを開催してくださり、ありがとうございました！