エクセルを使ってMySQLデータを作成で作業効率アップ【超おすすめ】

MySQLのテストデータ作成で困ったことはないでしょうか？

数十件程度のテストデータであれば、手作業でコツコツと作成することができます。

数千・数万件のテストデータを作成するのは非常に時間もかかって大変です。

こちらでは、エクセルの機能を使って短時間でテストデータを作成する方法についてご紹介します。

MySQL基本コマンド【おさらい】

MySQL基本コマンドの使い方は、最低限マスターしておきましょう。

こちらでは、MySQL基本コマンドを簡単にご紹介します。

◇MySQL基本コマンド

1. CREATE DATABASE
2. CREATE TABLE
3. INSERT
4. UPDATE
5. DELETE
6. DROP
7. TRUNCATE

CREATE DATABASEの書き方【MySQLおさらい】

新規でデータベースを作成する場合は、CREATE DATABASE文を書きます。

[例]データベースdb_test作成

CREATE DATABASE db_test;

CREATE DATABASE db_test CHARACTER SET utf8;

CREATE DATABASE db_test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLEの書き方【MySQLおさらい】

新規でテーブルを作成する場合は、「CREATE TABLE」で書きます。

[例]テーブルname_tbl作成

CREATE TABLE `name_tbl` (
  `id` int(8) NOT NULL,
  `name` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
  `kana` varchar(30) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERTの書き方【MySQLおさらい】

[例]INSERTの書き方

INSERTは、データを登録するときに使用します。

INSERT INTO `name_tbl` (`id`, `name`, `kana`) VALUES
(1,'name1','kana1'),
(2,'name2','kana2'),
(3,'name3','kana3'),
(4,'name4','kana4'),
(5,'name5','kana5');

UPDATEの書き方【MySQLおさらい】

[例]UPDATEの書き方

UPDATEは、データを更新するときに使用します。

update name_tbl set name='update_name1',kana='update_kana1' where id=1;
update name_tbl set name='update_name2',kana='update_kana2' where id=2;
update name_tbl set name='update_name3',kana='update_kana3' where id=3;
update name_tbl set name='update_name4',kana='update_kana4' where id=4;
update name_tbl set name='update_name5',kana='update_kana5' where id=5;

DELETEの書き方【MySQLおさらい】

[例]DELETEの書き方

WHEREに条件を指定したidレコードのみ削除します。

削除する場合、論理削除と物理削除があるので仕様に合わせてテーブルを作成します。

DELETE FROM name_tbl WHERE id = 1;

DELETEは、全てのレコードを削除することも可能です。

DELETE FROM name_tbl; 

DROPの書き方【MySQLおさらい】

[例]DROPの書き方

DROPは、テーブルを削除するときに使用します。

DROP TABLE name_tbl;

TRUNCATEの書き方【MySQLおさらい】

[例]TRUNCATEの書き方

TRUNCATEは、テーブルのデータを空にするときに使用します。

TRUNCATE name_tbl;

エクセルの機能を使ってMySQLデータを作成する方法

エクセルでMySQLデータを作成したことありますでしょうか？

以外と知られていませんが、MySQLデータのテストデータを作成するときにエクセルを活用すると便利です。

サイト運用で、たくさんのデータ登録や更新作業が発生するときに利用することもあるので覚えておいて損はないです。

エクセルの機能を活用してMySQLの構文を書くだけなので、慣れれば非常に簡単です。

phpmyadminを利用してテーブルを作成

データベース作成後に、テーブルを作成します。

データベースやテーブルの作成は、ローカル環境にxamppなどをインストールしてphpmyadminで作成すると短時間で作成できます。

データベースとテーブルの作成時間は10分程度です。

--
-- テーブルの構造 `pref_tbl`
--

CREATE TABLE `pref_tbl` (
  `id` int(4) NOT NULL,
  `code` char(6) DEFAULT NULL COMMENT '団体コード',
  `pref_code` tinyint(2) DEFAULT NULL COMMENT '都道府県コード',
  `city_name` char(10) DEFAULT NULL COMMENT '市区町村',
  `city_kana` char(10) DEFAULT NULL COMMENT '市区町村カナ'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='都道府県テーブル';

--
-- テーブルのインデックス `pref_tbl`
--
ALTER TABLE `pref_tbl`
  ADD PRIMARY KEY (`id`),
  ADD KEY `code` (`code`);


--
-- テーブルのAUTO_INCREMENT `pref_tbl`
--
ALTER TABLE `pref_tbl`
  MODIFY `id` int(4) NOT NULL AUTO_INCREMENT;
COMMIT;

都道府県データをエクセルで作成

エクセルでデータを作成するためには、都道府県データが必要になります。

今回登録する都道府県データは、総務省が一般公開している全国地方公共団体コードを使用します。

都道府県データは、総務省のホームページから無料でダウンロードすることができます。

INSERT INTO `pref_tbl` (`code`, `pref_code`, `city_name`, `city_kana`) VALUES
('010006',1,'北海道',''),
('011002',1,'札幌市','ｻｯﾎﾟﾛｼ'),
・
・
・
('472140',47,'宮古島市','ﾐﾔｺｼﾞﾏｼ'),
('472158',47,'南城市','ﾅﾝｼﾞｮｳｼ');

エクセルには、文字列を結合する「＆」や「カンマ」、「カッコ」などの簡単な記号を使ってINSERT文を書きます。

最後の「カンマ」を「セミコロン」に変更すると、データベースに登録する都道府県データの完成です。

INSERT INTO `pref_tbl` (`code`, `pref_code`, `city_name`, `city_kana`) VALUES
="('"&A2&"',"&B2&",'"&C2&"','"&E2&"'),"
・
・
・
="('"&A863&"',"&"'"&B863&"',"&"'"&C863&"','"&E863&"'),"

都道府県データをUPDATE

エクセルでINSERT文が作成できたら次は、UPDATE文をエクセルで作成します。

都道府県データをINSERTする際にidを自動で作成しているので、idを条件に都道府県データの更新を行います。

update pref_tbl set city_kana = 'サッポロシ' where id =2;
update pref_tbl set city_kana = 'ハコダテシ' where id =3;
update pref_tbl set city_kana = 'オタルシ' where id =4;
update pref_tbl set city_kana = 'アサヒカワシ' where id =5;
・
・
・
update pref_tbl set city_kana = 'ナンジョウシ' where id =862;

INSERT文と同様に文字列を結合する「＆」や「カンマ」、「カッコ」などの記号を使ってUPDATE文を書きます。

まとめ

総務省が無償で提供している全国地方公共団体コードのデータを使ってINSERT文、UPDATE文の作成方法についてご紹介しました。

エクセルでMySQLの構文を作る際は、特に難しい関数を使う必要がありません。

エクセルの基礎的な知識とMySQLの基礎的な知識があれば、都道府県データの作成・更新を行うことができます。

慣れてしまえば、30分もあれば登録から更新まで作業を終わらせることができます。