slack とかを使っていると、新たに部屋に参加した人は挨拶するじゃないですか。「こんにちは!」って。そしたら、既に部屋にいる人は「こんにちはこんにちは!」って返しますよね。その後に入ってきた人は、これを見て「こんにちはこんにちはこんにちは!」って挨拶したり、次の人は「こんにちはこんにちはこんにちはこんにちは!」って書き込んだり、しますよね。
これを SQL でさくっと書けないか、と、とある チャンネルに @yoku0825 さんが書いた疑似SQLがこれ。
WITH RECURSIVE t AS (SELECT 1 AS c, 'こんにちは' AS str UNION ALL SELECT c + 1, CONCAT(str, str) FROM t WHERE c < ?) SELECT CONCAT(str, '!') FROM t;
実はこれ、実行すると、
Data too long (ERROR 1406 (22001))
というエラーになります。
yokuさんによると、「『シードのSELECTの時に型が決まっちゃうから…とか』だっけ?」ということで、キャストするといいんじゃないか、というのは @tmtms さんのアイデア。
WITH RECURSIVE t AS (SELECT 1 AS c, CAST('こんにちは' AS CHAR(1000)) AS str UNION ALL SELECT c + 1, CONCAT(str, str) FROM t WHERE c < 10) SELECT CONCAT(str, '!') FROM t;
この挙動についてはマニュアルにも説明があります(@tmtmsさん情報):
MySQL :: MySQL 8.0 Reference Manual :: 13.2.15 WITH (Common Table Expressions)
これで、エラーなく実行されるのだけど、実は初期アイデアにちょっとした問題があって、 CONCAT(str, str) としていることで、文字列がひとつずつ長くなるのではなく倍々になっていっていまうのですね。そんなわけで、
WITH RECURSIVE t AS (SELECT 1 AS c, CAST('こんにちは' AS CHAR(1000)) AS str UNION ALL SELECT c+1, CONCAT(str, 'こんにちは') FROM t WHERE c < 10) SELECT CONCAT(str, '!') greeting FROM t;
実行結果は、こんな感じ。
mysql> WITH RECURSIVE t AS -> (SELECT 1 AS c, CAST('こんにちは' AS CHAR(1000)) AS str -> UNION ALL -> SELECT c+1, CONCAT(str, 'こんにちは') FROM t WHERE c < 10) -> SELECT CONCAT(str, '!') greeting FROM t; +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | greeting | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | こんにちは! | | こんにちはこんにちは! | | こんにちはこんにちはこんにちは! | | こんにちはこんにちはこんにちはこんにちは! | | こんにちはこんにちはこんにちはこんにちはこんにちは! | | こんにちはこんにちはこんにちはこんにちはこんにちはこんにちは! | | こんにちはこんにちはこんにちはこんにちはこんにちはこんにちはこんにちは! | | こんにちはこんにちはこんにちはこんにちはこんにちはこんにちはこんにちはこんにちは! | | こんにちはこんにちはこんにちはこんにちはこんにちはこんにちはこんにちはこんにちはこんにちは! | | こんにちはこんにちはこんにちはこんにちはこんにちはこんにちはこんにちはこんにちはこんにちはこんにちは! | +-----------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec)
ちなみに、キャストしている char(1000) の 1000 には特に細かい意味はなくて、512までは VARCHAR、513から16383までは text、16384以上になると longtext になります。
mysql> drop table t2; create table t2 as SELECT CAST("ABCD" as CHAR(512)) c1;desc t2; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | c1 | varchar(512) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ mysql> drop table t2; create table t2 as SELECT CAST("ABCD" as CHAR(513)) c1;desc t2; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | c1 | text | YES | | NULL | | +-------+------+------+-----+---------+-------+
mysql> drop table t2; create table t2 as SELECT CAST("ABCD" as CHAR(16383)) c1;desc t2; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | c1 | text | YES | | NULL | | +-------+------+------+-----+---------+-------+ mysql> drop table t2; create table t2 as SELECT CAST("ABCD" as CHAR(16384)) c1;desc t2; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | c1 | longtext | YES | | NULL | | +-------+----------+------+-----+---------+-------+
今回の実行結果を新規テーブルに入れると、 text ではなく longtext になる理由は、不明。
mysql> CREATE TABLE t5 AS -> WITH RECURSIVE t AS (SELECT 1 AS c, CAST('こんにちは' AS CHAR(1000)) AS str UNION ALL SELECT c+1, CONCAT(str, 'こんにちは') FROM t WHERE c < 10) -> SELECT CONCAT(str, '!') FROM t; Query OK, 10 rows affected (0.12 sec) Records: 10 Duplicates: 0 Warnings: 0mysql> desc t5; +--------------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+----------+------+-----+---------+-------+ | CONCAT(str, '!') | longtext | YES | | NULL | | +--------------------+----------+------+-----+---------+-------+
あぁ面白かった。ところでこれ、1個からn個までの こんにちは をリストにして出しているけど、そもそもリストじゃなくて、n回目の挨拶文だけ得られれば十分だったんじゃないの?
mysql> SELECT CONCAT(REPEAT('こんにちは',5),'!') greeting; +--------------------------------------------------------------------------------+ | greeting | +--------------------------------------------------------------------------------+ | こんにちはこんにちはこんにちはこんにちはこんにちは! | +--------------------------------------------------------------------------------+
こんなことをわいわいやっている、MySQLコミュニティ界隈、MySQLユーザ会、MySQLについて興味を持ったら、2020年5月25日(月) 19時から開催される 「MySQLユーザ会20周年&MySQL25周年記念イベント@Zoom」にもぜひご参加ください。ウェビナー形式です。
mysql.connpass.com