メインコンテンツまでスキップ

データ一括投入

データベースへの一括データ投入は、サービス開発における重要な要素です。初期データやテストデータを効率的に取り込むことにより、 開発者はリアルなデータ環境でアプリケーションをテストし、データの整合性を確保することができます。この作業は、アプリケーションの品質を高めるために不可欠です。 本記事では、データベースに大量のデータを効率的に投入する方法に焦点を当て、具体的な手順を紹介します。 例として、ひと月の勤務予定表をデータベースに一括投入するケースを取り上げます。

１．表計算ソフト等でクエリ作成を簡略化​

表計算ソフトを使用すれば、同じ値や連続した数値などはドラッグ操作で簡単にコピーすることが可能です。 また、置換機能や各種関数を利用することで、日付などの特定のデータを一括で変更することも容易です。 このように表計算ソフトでは、小規模なデータセットや特定の条件を満たすデータを作成することができます。 また、表計算ソフトはデータの視覚化に優れているため、データの傾向を確認しながらデータを準備することが可能です。 初期データとして「ユーザ1」などの連番のユーザ名を投入する際に、表計算ソフトを利用して、簡単に元データを作成できます。

  1. まずは表計算ソフトのセルに「INSERT INTO テーブル名 (カラム名,...) VALUE (`」と記述します。（WorkBenchであればカラム名はテーブルデータからコンマ区切りで一括コピーできます。）
  1. テーブルに投入したいデータのセルを、例えばmonthカラムの場合=C3のように参照し、次のセルに「 ` ,` 」を入力ます。 これを交互に入力してVALUES句が「 `値 `, `値 `」となるようにします。

  1. クエリの末尾に「`);」を入力してクエリ文を完成させます。

  2. 次に関数の「CONCATENATE」を使って=CONCATENATE(N3,O3,P3, ...)と入力ます。これにより、複数のセルを連結して1つの文字列にします。

  1. 最後に=CONCATENATE(N3,O3,P3, ...)のセルをデータの数だけドラッグでコピーします。これにより、複数のクエリ文を作成します。

この方法を使用すれば、大量のINSERT文を効率的に作成することが可能です。ただし、作成したクエリは必ず確認し、適切な形式であることを確認してからデータベースに投入してください。

この方法の注意点として「null」をバッククォートで囲むと文字列として認識されてしまうので、null を入力する場合は「`null`」を「null」に置換しなければなりません。

一度この表を作成しておけば、次回からは投入したいデータを準備するだけでINSERT文やUPDATE文などのクエリ文を手軽に複数作成することができます。

２．SQLを使用して既存のデータをコピー​

この方法では、データベース内の既存データをSELECTし、それを新しいレコードとしてINSERTします。 既存データと全く同じ値を新しいレコードとして挿入する際に特に有用です。例えば、テストデータの作成などで 既存のレコードをコピーし、年月を変更した新しいレコードを作成する場合などに使用できます。

指定したレコードをコピーする場合はWHERE句を使用してWHERE date_column BETWEEN '2023-04-01' AND '2023-04-30'として指定します。

INSERT INTO テーブル名 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM テーブル名;

例えばひと月分のレコードをコピーして日付のみ変えたい場合は以下のようにします。

INSERT INTO テーブル名 (column1, column2, ..., date_column)
SELECT column1, column2, ..., DATE_FORMAT(date_column, '%Y-07-%d') AS date_column
FROM テーブル名
WHERE date_column BETWEEN '2023-04-01' AND '2023-04-30';

３．csv ファイルを取り込む​

大量のデータを一度にデータベースに追加する場合、CSVファイルからのインポートは非常に効率的です。 例えば、他のシステムからエクスポートしたデータを新しいデータベースに移行する場合や、一括でデータを更新する必要がある場合などに便利です。 また、プログラミングの知識がなくても、CSVファイルを作成し、それをデータベースにインポートすることでデータの登録が可能です。 ただし、CSVファイルのデータは、各フィールドのデータ型が適正で、予め整理されていることが必要です。

ここでは「MySQL Workbench」を使用してusersテーブルにインポートを行ないます。

  1. 下記画像赤枠内「Import records from an external file」をクリックします。
  1. インポートするファイルを選択し「Next」をクリックします。
  1. Use exsting tableにチェックを入れ、インポート先のテーブルを選択します。
  1. 「Next」をクリックするとcsvをインポートするカラムの選択画面に進みます。
  1. 「Next」をクリックして進み、インポートが完了すると「Finish」をクリックすると該当のテーブルにcsvデータが取り込まれています。