株式会社クイックのWebサービス開発blog

HAPPYなサービスプランナー・エンジニア・デザイナーのブログです。

MySQLでテストデータ作成に便利な表現4パターン!

直近適当なテストデータを作成する機会があった、みっきーです。
今までMySQLで大量のテストデータを作ったことがなく、新しい発見がありました。
忘れっぽいので、早速クエリたちを忘れないために書き記そうと思います。

パターン1 別のテーブルを元にテストデータを作りたい

大量のデータが詰まっている複数テーブルから、一部のデータを抜き出して新テーブルを作りたい時に使用しました。 全部のデータが不要な場合は、必要なカラムだけを必要な条件に絞って移すことができます。

クエリの中で、テストデータを入れたいテーブルは「新テーブル」 元になるテーブルは「元テーブル」と記載します。

INSERT INTO 新テーブル名 (
  id,
  name
)
SELECT 
  id,
  name
FROM 元テーブル名
WHERE 条件;

INSERTでSELECTできるなんて知らなかった!あぁ〜。便利。

パターン2 主キーが一致するデータをアップデートしたい

おっちょこちょいな私は、よくミスをします。
パターン1でデータを作った後に、追加漏れをしているカラムを見つけたり...
そもそも指定したカラムが間違っていたり...
ちょっとした絶望を感じたそんな時は、アップデートで解決できちゃいました!

UPDATE
  元テーブル, 新テーブル
SET 
  元テーブル.tsuika_shitai_yo = 新テーブル.tsuika_shitai_yo,
  元テーブル.tsuika_shitai_na = 新テーブル.tsuika_shitai_na
WHERE 元テーブル.id = 新テーブル.id;

ん〜。便利! これでミスっても大丈夫。

パターン3 主キーが外部参照されているテーブルに元テーブルと一致するデータを追加したい

オートインクリメントのようなことがしたいけど、主キーが複数のテーブルから参照されていてにっちもさっちもいかない状態になってしまいました。
1つずつ外部参照を外すのも、更新してから元に戻すのも非常にめんどくさいです。

そんなときSQLは変数が使えることを思い出しました。 変数が使えるならカウントぐらいできるのではと思い立ち、早速ためしてみました。

SELECT
  @rowCount := @rowCount + 1
FROM 
  元テーブル,
  (SELECT @rowCount := 0) AS row;

結果

@rowCount
1
2
3
...

できた! ということはそのままインサートもできるのでは!?

INSERT INTO 新テーブル ( 
  tsuika_shitai_id,
  元テーブル_id
) 
SELECT
  @rowCount := @rowCount + 1,
元テーブル_id
FROM 
  元テーブル,
  (select @rowCount := 0) AS rowCount;

できた!!!これは!便利!!!!!

外部キーチェックを外してオートインクリメント設定する方法もありました。

SET FOREIGN_KEY_CHECKS=0

解決方法って探せばいくらでも出てきますね。謎解きみたいで楽しいです。

パターン4 とにかく大量のデータをバラツキのある状態で登録したい

パターン3から派生して、数値系のカラムを大量生産しつつ、ばらつかせた数値を入れることにもチャレンジしてみました。

時給のテストデータっぽくする

SELECT
  @rowCount := @rowCount + 1,
  @rowCount * CEIL(RAND() * 1000)
FROM
  元テーブル,
  (SELECT @rowCount := 0) AS rowCount;

結果

id 時給
1 496
2 1242
3 1851

登録日のテストデータっぽくする

SELECT
  @rowCount := @rowCount + 1,
  (SELECT DATE_ADD('2020-11-01', INTERVAL 30 * RAND() DAY))
FROM
  元テーブル,
  (SELECT @rowCount := 0) AS val;

結果

id 登録日
1 2020-11-09
2 2020-11-21
3 2020-11-16

もう。便利すぎー!

まとめ

今回はこのパターンである程度のテストデータが作成できました。 公式マニュアルには初めて見る関数がたくさんあり、複雑な動的データ作成ができそうです。

機会があれば、さらに関数の動作みて便利なクエリを作っていきたいと思います!


\\『真のユーザーファーストでマーケットを創造する』仲間を募集中です!! //

919.jp