DBを使わず、sqlで複数行の結果を得たい

Question

やりたい事

mysqlで、DBを使わず、sqlで複数行の結果を得たいです。
目的は3000行程度のサンプルをsqlのみで作る事です。
(なお業務等ではなく、"このような事は出来るのか？"という勉強用です。なのでDBを使う代替案はご遠慮下さい)

環境

mysql 8.0.17

例

例えば、これで1行の結果が得れます。

mysql> select "A" as c1;
+----+
| c1 |
+----+
| A  |
+----+

3行程度ならこれでいけます。

mysql> (select "A" as c1,"B" as c2) union (select "C", "D") union (select "D","E");
+----+----+
| c1 | c2 |
+----+----+
| A  | B  |
| C  | D  |
| D  | E  |
+----+----+

流石にこれ以上はやってられないので、できれば自動生成したいです。

条件

下記は不可の条件です。下記は避けて実施したいです。

• 既に存在するテーブルをselectして、そこから表示を生成する
• ストアドプロシージャを利用する
• テンポラリテーブルを利用する
• シェル等で長文のsqlを自動生成する

妙な条件があるのは、力技っぽくて後学の為にならなそう、と判断したからです。
また今後仕事に使う事があるのなら、システムに変更が必要だったりするものも除きたいです。

一応joinで倍々に増やした後、limitで減らす、というのも考えたのですが、
もっとスマートな方法があるのでは、と期待しています。

よろしくおねがいします。

追記

なんか相当ズレた質問だったようで、すみません。

自分がやりたいのは、

mysql> select MD5(RAND());
+----------------------------------+
| MD5(RAND())                      |
+----------------------------------+
| 6fc47216844543da2c587b4465781e29 |
+----------------------------------+

これを色々SQLを弄ると

+----------------------------------+
| MD5(RAND())                      |
+----------------------------------+
| 6fc47216844543da2c587b4465781e29 |
| 14d745e539e89ba748d3c63293a5e686 |
| 8b0c4ee93625d523850e79be293c796e |
| a5c1f346e7c20e35fb10a6d54789fba4 |
+----------------------------------+

こんな感じの出力が得れるのでは、と思ってた次第です。

Comments

[退会済みユーザー]

「"このような事は出来るのか？"という勉強用」というのは
SQLの勉強を、DB環境を構築せずに行いたいということでしょうか？
それともSQLの勉強の1つとして、テーブルを介さずにデータを生成したいということでしょうか？

[katosei]

SQLの勉強の1つとして、です。

DBを作成出来る環境はあるのですが、そうそう弄れない環境も多いので
出来る選択肢を増やしておきたい意図です。

具体的に”これ”というものはないですが、この質問を思い立った理由が
「10行ぐらいのデータ適当に欲しいけど、テーブル作るの面倒臭い」だったので
ワンライナーのように即興で出来る何かがあれば、学んでおきたいと思った次第です。

Answer 1

「sql チューリング完全」でググるとSQLはチューリング完全のようです。
with recursiveがポイントとなるようで、どうやらMySQLでも使えるようです。
なのでたぶんできます。

Comments

[katosei]

レスポンスが遅くなり申し訳有りません。
おかげさまで、無事できました。

```
mysql> WITH RECURSIVE r(i, md5) AS ( SELECT 1,md5(rand()) UNION ALL SELECT i+1,md5(md5) FROM r WHERE i < 10 ) SELECT * FROM r;
+------+----------------------------------+
| i | md5 |
+------+----------------------------------+
| 1 | 7acf9df571e086269e8d3264eb62e117 |
| 2 | b6622c29760160d1e1a542cfbea6f3e0 |
| 3 | d0772606e37d1e87b391b464dfbd1838 |
| 4 | ff5b0ade766ac498ce45f33e4046fdfa |
| 5 | a26ce08a67240648f8e079553a96cd71 |
| 6 | 493be49766df70ac777d4eae05f639ff |
| 7 | 4db386c106da819b2d9094281a41a19b |
| 8 | 1a494b7a24e058285f4fec386d490927 |
| 9 | 8332ad2e256416cf851d61c6e7fdf069 |
| 10 | a7d9c10d6052881424eb69b3ece64b42 |
+------+----------------------------------+
10 rows in set (0.00 sec)
```

これぐらいなら即興で書けそうです。ありがとうございます。

Answer 2

以下のSQLはSQLiteですがWITH句とDUAL表、CROSS JOINが使える
SQLなら似たようなものが作成できると思います。

WITH T1 AS (
  SELECT 0 UNION ALL SELECT 0
), T2 AS (
  SELECT * FROM T1 S1 CROSS JOIN T1 S2
), R AS (
  SELECT * FROM T2 S1 CROSS JOIN T2 S2 
)

SELECT ABS(RANDOM()) % 10
  FROM R AS R1
    CROSS JOIN R AS R2
    CROSS JOIN R AS R3
  LIMIT 3000;

Comments

[katosei]

レスポンス遅くなりました。

元々はunionを指定個数並べるしか無いと思ってたんですが、目からウロコでした。
ありがとうございました。

Answer 3

エディタでコピペした方が早そうだけど。
SQLを覚えたくないとか、使いたくないなら、データベースとは関係ない方面の技術を身につければ良いだけでは？

Comments

[katosei]

いや、むしろSQLの使い方を覚えたいのです。

shellだったらcatやechoで適当な文字列を出した後に、
パイプでつないでawkやsedの練習が出来ると思います。
それをsqlでやりたいだけです。
シェル芸とかワンライナーみたいなノリだと思って下さい。

実際に使う際には、その結果をサブクエリとして、それを弄る感じで遊びたいと思ってます。
DB作るなら、数百万行ぐらいのランダムデータ作るぐらいは出来ている状態です。

[Orlofsky]

ナチュラルジョインしてRAND関数を使えば簡単にできることでしょう。
質問にSQLを追加してどうやったらもっと希望するデータが作れるか、改善方法を求めては？
「SQL入門」でググるなり、書籍を買ってきて勉強した方が早そうですけど。

Answer 4

ここを読んでみてください。

B.5.2.10 パケットが大きすぎます

パケットは、MySQL サーバーに送信される単一の SQL ステートメント、クライアントに送信される単一の行、またはマスターレプリケーションサーバーからスレーブに送信されるバイナリログイベントです。

Comments

[katosei]

読みましたが、すみません、意図が読み取れませんでした。

[Zuishin]

SQL の長さの制限がどこを見ればわかるか書いています。この制限内しかできません。

SQL 生成の自動化自体は適当なスクリプト言語でできるでしょう？ 問題になるのはここくらいだと思いますが。

それかやり方自体を大幅に変えて SQL でループを使うかですね。
https://dev.mysql.com/doc/refman/5.6/ja/loop.html

ストアドプロシージャが一つ必要になりますが。

[gentaro]

ストアド使いたくないらしいからループもなしって話になると思うので、もうその時点で私は回答を諦めました。（縛る意味がわかんないので）

[katosei]

ありがとうございます。
大量にSQLを生成して投げる方法は、限度があるみたいですね。

ストアドプロシージャを使うやり方は認識しています。
これはシステム側に保存されてしまうと思うので、仕事では使いづらそう、という認識です。

変な条件ばかりですみません。

[Zuishin]

よく読んだら

> シェル等で長文のsqlを自動生成する

もだめなんですね。
どうやって自動生成するつもりなんだろう。

[katosei]

そもそもSQLに詳しくないので、なんか色々やれば出来るもんだと思ってました。
(縛ったというか、もっとスマートな方法があると思いこんでました。)

機能上、難しいという事がわかればそれで十分成果なので、
別のやり方を探したいと思います。

[Zuishin]

仕事でどう使うかさっぱりです。保持されないデータなんか、遊びにしか使えないんじゃないでしょうか。

Answer 5

行を作るのが面倒という事で良いでしょうか？
データ分析で必要な連続した値を生成する

MySQLではどうしても元になるデータが必要になってしまいますので、SQLだけでなら諦めて下さい。

Comments

[katosei]

レスポンスが遅くなりました。

どうにかベストアンサーに記載した内容で目的の出力を得る事が出来ました。
ありがとうございました。

[sazi]

ver 8でしたか。
勉強目的で8が使えるというのは貴重ですね。

[katosei]

逆に言うと8未満のバージョンでは難しいみたいですね。
(そこらへんから、わかってなかったです)
皆さんのレスポンスから、ひしひしと感じました。

with句が8から使えるなったってあんまり意識してませんでしたが、
いざ使ってみると便利なもんですね！
継続して色々触ってみようかと思います。

[sazi]

postgresなら現状有償の制限なくチューリング完全です。
便利な機能が多いので、小規模なら一択でも問題ないかと思われるのでお勧めです。

[katosei]

あれ、ちなみに自分が使っているのはcomunity serverの方なので、無償版です。

[sazi]

商用だと有償部分が発生するので、利用者が多いのはmariadbの方じゃないかと思います。
その場合はチューリング完全ではないので、使いたくても使えないという懸念はありますね。

Answer 6

シェル等で長文のsqlを自動生成する

これがアリならエディタ使ってコピペで増やして可変にしたいところは何らかの置換ツールを使うのが一番楽だと思いますが。つか何の勉強にもなってない気がするけど、どういう意図なんだコレ…。

---

追記
誤読してた。
やりたくない条件って意味か。（ややこしい…）

正直意図がわかんないし、DBを使いこなそうとしてるようにも見えないんで、回答放棄します。無視してください。

Comments

[katosei]

そのままsqlで複数行の結果を出してみたいだけです。
実際に使うなら、サブクエリでその結果を弄る感じになるかと思います。(任意のテストデータを即興で作りたい)

シェル芸とかワンライナーみたいなものが、sqlでも出来るのかしりたいのです。

[gentaro]

> シェル芸とかワンライナーみたいなものが、sqlでも出来るのかしりたいのです。
まぁそういう意図であれば「問題・課題が含まれていない質問」に該当すると判断されると思いますが。
https://teratail.com/help/avoid-asking

[katosei]

「SQLを使ってこのような事は技術的に可能か？」は課題ではないのでしょうか？

と思ってはいるのですが、ここで議論しても何も産まないですね。

荒れたりとか、言い訳してズルズル引き伸ばすのも本位ではないので
この件についてはそろそろ幕引きにさせて下さい。
自分の質問がちゃんと固まっていなかったのが一番の原因なので。

[gentaro]

リンク先を読めば書いてある話ですが

> teratailは困っている人の質問を解決するサービスです。そのため、漠然とした興味から票を募るような質問や、意見の主張をすることを目的とした投稿は推奨していません。
> 何か困っている理由があり、最適解を見つける為の知見を得たい場合は、その理由や何が知りたいのかを明確に記述してください。

これを読んだ上で、この質問の「困っている」ことと「解決するべき課題」は何で、「漠然とした興味ではない」とする材料は何だろう？と考えると、私には見つけられなかったという事です。（変な縛りをしているところを含めて）

それを実現するべき具体的な理由があれば別に良いんですけど。