Windows版のMySQLで、CSVファイルをインポートしてからSQLファイルとしてエクスポートする手順

目次

この記事を書いた背景

Windows版のMySQLで、大量のテストデータを用意する作業が発生しました。

テストデータを作れるWebサービスは色々とあるものの出力形式がCSVなので、そのファイルをMySQLでインポートする操作と、
複数のPCにインストールされたMySQLに横展開したいので、インポートしたDBをSQLとしてエクスポートする操作があるので、それを記事としてまとめました。

ちょっとずつハマったので・・

手順

作業全体の流れ

操作自体は次の3ステップです。

  1. テストデータを作る
  2. MySQLにテストデータをインポートする
  3. MySQLのデータベースをエクスポートする

注意点などを含めた詳細な手順を次に載せます。

1. テストデータを作る

テストデータは次のWebサービスを利用して作りました。
CSVファイルで出力しています。

SmartData

2. MySQLにテストデータをインポートする

MySQLにはLOAD DATA LOCAL INFILEという外部ファイルからのデータ取り込み機能があります。

しかし、セキュリティの観点からローカルファイルの利用がデフォルトでは無効になっているため、次のどちらかの方法でローカルファイルの利用を許可する必要があります。

mysqlコマンドにオプションを付ける方法

mysqlコマンドの起動オプションに「–local-infile=1」を付ける方法です。

MySQL Command Line Client – Unicodeのショートカットに直接追記するか、コマンド プロンプトで起動する際にオプションで指定します。

ショートカットに追記する際は、オプションを「”–local-infile=1″」のように「”」で書くようにしてください。

my.iniで設定する方法

my.iniで有効にする場合には、次の記述を書き加えてmysqlを再起動してください。

[client]
loose-local-infile=1

インポートの記述

インポートを実行するコマンドは次の構文で記述します。

mysql> LOAD DATA LOCAL INFILE 'C:/path/to/file.csv' INTO TABLE tablename CHARACTER SET cp932 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

注意点

CSVファイルのパスは、「\」ではなく「/」で区切ります。
「tablename」には、インポートしたいテーブル名を指定します。

オプションについて

オプションの意味は次の通りです。

CHARACTER SET cp932
インポートするファイルの文字コードを指定(cp932はWindowsが利用しているShift-JIS)
TERMINATED BY ‘,’
「,」をカラムを区切りに指定(デフォルトは「タブ(\t)」)
OPTIONALLY ENCLOSED BY ‘”‘
「,」を含むデータを囲む文字の指定
LINES TERMINATED BY ‘\r\n’
改行コードにCR/LFを指定(Excelなど多くのWindowsアプリケーションのデフォルト値)
LOAD DATA LNFILEの設定のデフォルトでは改行コードをLFとして扱うので、最終カラムの末尾にCRが付いた状態でインポートされてしまうのを防ぐために指定します。

3. MySQLのデータベースをエクスポートする

作成したデータは、データベースの単位でダンプすることでエクスポートします。

コマンドラインやPowerShellで次のコマンド実行します。

>path\to\mysql\bin\mysqldump -u -root -p databasename > export.sql

export.sqlの出力先によっては(Program Filesの下とか)コマンドプロンプトを管理者権限で立ち上げないと、ファイルの書込権限がなくてエラーします。

新規データベースにインポートする

先の手順で作成したエクスポートファイル(export.sql)を利用する際は、直接SQLを実行する方法と、コマンドプロンプトを利用する方法があります。

直接SQLを実行する方法

直接SQLを実行する場合、MySQL Command Line Client – Unicodeなどを起動して、SQLの中身をコピペします。

SQLが大きい場合、画面がスクロールして時間がかかるかも知れません。

コマンドを利用する方法

コマンド プロンプトを利用する場合、SQLを実行するには事前に受け入れ先のデータベースを用意しておく必要があります。

MySQL Command Line Client – Unicodeなどを起動して、次のコマンドでデータベースを用意します。

mysql> CREATE DATABASE targetdb

続いてコマンド プロンプトを起動して、次のコマンドでエクスポートファイルを読み込ませます。

>path\to\mysql\bin\mysql -u root -p targetdb < export.sql

まとめ

けっして難しい作業ではないのですが、細々とミスりそうなポイントがあるのに注意しないとですね!

10年集客し続けられるサイトを、ワードプレスで自作する9つのポイント プレゼント

【最後まで読んでくれたあなたに】

あなたは、24時間365日、自分の代わりに集客し続けてくれるWebサイトを作りたい!と思ったことはありませんか?

私はこれまで500以上のWebサイトの構築と運営のご相談に乗ってきましたが、Webサイトを作ってもうまく集客できない人には、ある一つの特徴があります。

それは、「先を見越してサイトを構築していないこと」です。
Webサイトで集客するためには、構築ではなく「どう運用するか」が重要です。

しかし、重要なポイントを知らずにサイトを自分で構築したり、業者に頼んで作ってもらってしまうと、あとから全く集客に向いていないサイトになっていたということがよく起こります。

そこで今回、期間限定で
『10年集客し続けられるサイトをワードプレスで自作する9つのポイント』
について、過去に相談に乗ってきた具体的な失敗事例と成功事例を元にしてお伝えします。

・ワードプレスを使いこなせるコツを知りたい!
・自分にピッタリのサーバーを撰びたい!
・無料ブログとの違いを知りたい!
・あとで悔しくならない初期設定をしておきたい!
・プラグイン選びの方法を知っておきたい!
・SEO対策をワードプレスで行うポイントを知りたい!
・自分でデザインできる方法を知りたい!

という方は今すぐ無料でダウンロードしてください。

期間限定で、無料公開しています。

※登録後に表示される利用条件に沿ってご利用ください

コメントを残す