Windows 10 HomeにてMySQLのテーブルにcsvファイルでデータをインポート

ローカル環境にあるcsvファイルでMySQLのテーブルにデータをインポートするには、ログイン時にオプションを付ける必要があります。

MySQLにデータベースとテーブルを用意

今回、daiaryデータベースとPostテーブルを用意してみます。まずは、rootユーザーでMySQLにログインし、今現在のユーザーを確認してみます。

mysql -u root -p

f:id:ts0818:20170825071659j:plain

MySQLのユーザーは、デフォルトでは、rootとmysql.sysが存在するので、ownerが自分で追加したユーザーになります。データベース名.テーブル名でのアクセスの場合は、最初にUSE文でデータベースを指定しなくても良いようです。

SELECT User, Host FROM mysql.user;

f:id:ts0818:20170825071703j:plain

ちなみに、MySQLの場合、 

DESC テーブル名

SHOW fields FROM テーブル名

でテーブルの構造を表示できます。

f:id:ts0818:20170825072546j:plain

⇩  パスワードなどを忘れたときは再設定が必要のようです。 

MySQL ユーザの操作(作成、パスワード変更、削除)

MySQLユーザーのパスワードを忘れてしまったとき

SQLユーザー要注意!意外と知らないMySQLのrootパスワード設定方法 | Tech2GO

 

⇩  MySQL 5.7.6でmysql.userテーブルの構造が変わったようです。

日々の覚書: MySQL 5.7.6でmysql.userテーブルのパスワードのカラム名がなんか変わった

パスワードがハッシュ化されてるってことですかね。

 

脱線しましたが、データベースを作成します。

CREATE DATABASE diary;

f:id:ts0818:20170825075519j:plain

 次に、ownerユーザーでdiaryデータベースが使えるように、ownerユーザーに権限を付与します。

GRANT ALL PRIVILEGES ON データベース名.* TO 'ユーザー名'@'ホスト名' IDENTIFIED BY 'ユーザーパスワード' ;

f:id:ts0818:20170825081123j:plain

変更を反映させます。

f:id:ts0818:20170825081704j:plain

一旦、MySQLをログアウト(切断)し、ownerユーザーでログインし直します。

f:id:ts0818:20170825081934j:plain

ログイン。

f:id:ts0818:20170825081930j:plain

USE文でデータベースを指定できれば権限の問題は大丈夫と思われます。

f:id:ts0818:20170825082147j:plain

postテーブルを作成します。

CREATE TABLE post(
  post_id int NOT NULL AUTO_INCREMENT,
  post_title VARCHAR(64),
  post_description VARCHAR(255),
  post_content TEXT,
  post_date datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(post_id)
); 

f:id:ts0818:20170825090629j:plain

 

[MySQL]データベースの作成、ユーザの追加と権限の設定 | 本日も乙

ダミーデータを用意

作成したテーブルにcsvファイルでデータをインポートするために、ダミーデータのcsvファイルを用意したいと思います。

Excelが使える場合であれば、「ExcelDBTool」、「ExcelDevTool」などのExcelアドオン(拡張機能)が便利のようです。

自分は、Microsoft office系が入ってないので利用できずです。(libre office系では利用できなさそうでしたし)

 

ExcelからDBのレコードを参照・更新したい - Infrastructure Engineer`s Notes

【レビュー】「Excel」のかゆいところに手が届く使い勝手向上アドイン「ExcelDevTool」 - 窓の杜

⇩  ExcelDBToolは無償で個人利用するには条件があります

ExcelDBTool - エクセルでデータ作成、DB接続、データ取得更新

 

あんまり多いデータは作れないらしいですが、Database test data generator - Fill your database with random test data! が便利らしいです。(ただし、データが英語んなっちゃいますね)

ポチポチやるだけで数万オーダーのテストデータを生成する - Qiita

早速、Database test data generator - Fill your database with random test data! にアクセスします。

f:id:ts0818:20170825091127j:plain

「Table Structure」でテーブルの構成を、「Export Format」で出力ファイルの形式を選択できます。「Generated rows」で何行分のデータを作成するか決められます。「Add Another Column」などでカラムの追加ができます。

なんか、csvが上手くいかないんで、いったんJSON形式で。

f:id:ts0818:20170825094441j:plain

「Generate data」をクリックすると、データが生成されるので、コピーし、

f:id:ts0818:20170825094438j:plain

JSON to CSV というサイトにアクセスし、上の欄に張り付けると、csvデータとして下の欄に出力されます。

f:id:ts0818:20170825101841j:plain

「Download the entire CSV」 でcsvファイルとしてダウンロードできます。

ダウンロードしたcsvファイルを開くと、カラムの順番がテーブル定義と違ってしまっているので、並び替えます。

f:id:ts0818:20170825103411j:plain

並び替えます。カラム名などの行は削除しておきます。

f:id:ts0818:20170825103408j:plain

 連番が0からになってしまっているので、1からスタートするよう修正。

f:id:ts0818:20170825111140j:plain

1からになりました。

f:id:ts0818:20170825111137j:plain

日付がdatetime型の形式になってないので、編集。

f:id:ts0818:20170825114908j:plain

E1のセルを選択した状態で、「="3/1" + RAND() * 91.9999」と入力するとランダムで「yyyy/MM/DD HH:mm:ss」の形式を作ってくれるようです(年がすべて2017になってしまいますが)

f:id:ts0818:20170825114857j:plain

日付も編集できたら保存します。

備考として、「書式(O)」>「セル(L)...」から、

f:id:ts0818:20170825114903j:plain

「カテゴリー(A)」 の「ユーザー定義」で、「書式コード(F)」のところでフォーマットも追加できるようです。

f:id:ts0818:20170825114852j:plain

 

サクラエディタなどで開いて「ファイル」「名前を付けて保存(A)...」を選択。

f:id:ts0818:20170825104159j:plain

文字コードセット(C):」で「UTF-8」を選択し「保存(S)」。

f:id:ts0818:20170825104156j:plain

保存の際、「現在の入力改行コードCRLFに統一しますか?」と聞かれますが、ここは「いいえ(N)」を選択してください。

f:id:ts0818:20170825110045j:plain

 

WEB製作時に使えそうなダミーデータ生成サービスまとめ10選(テキスト/画像/個人情報) | degitekunote2

 

テーブルにインポート

ローカルのファイルからデータをインポートするには、MySQLログイン時にオプションをつける必要があります。 

mysql --local-infile=1 -u ユーザー名 -p

f:id:ts0818:20170825095301j:plain

ログインしたら、csvファイルをインポート。むっちゃWarningsの嵐(涙)。

f:id:ts0818:20170825120234j:plain

「SHOW WARNINGS」で確認できるとのこと。

f:id:ts0818:20170825120229j:plain

どうやら、「post_title」と「post_description」のカラムのサイズを超えたデータを入れようとしてるために起こる警告らしいです。

テーブルのカラムのデータ型の定義を変更します。

ALTER TABLE テーブル名 MODIFY カラム名 カラム定義

f:id:ts0818:20170825121057j:plain

カラムのデータ型が変更できました。

f:id:ts0818:20170825123207j:plain

 

そしたら、いったんテーブルから全データを削除(普通は必ずWHERE句を指定して削除するデータを限定します)、再びデータをcsvファイルからインポート。

f:id:ts0818:20170825121054j:plain

 今度は警告なくデータがインポートできました。

EclipseのDBViewerで確認してみます。

f:id:ts0818:20170825121811j:plain

データが入りました。

f:id:ts0818:20170825121805j:plain

ダミーデータをもっと効率的に作成する方法を見つけたいもんですね。