ローカル環境にあるcsvファイルでMySQLのテーブルにデータをインポートするには、ログイン時にオプションを付ける必要があります。
MySQLにデータベースとテーブルを用意
今回、daiaryデータベースとPostテーブルを用意してみます。まずは、rootユーザーでMySQLにログインし、今現在のユーザーを確認してみます。
mysql -u root -p
MySQLのユーザーは、デフォルトでは、rootとmysql.sysが存在するので、ownerが自分で追加したユーザーになります。データベース名.テーブル名でのアクセスの場合は、最初にUSE文でデータベースを指定しなくても良いようです。
SELECT User, Host FROM mysql.user;
ちなみに、MySQLの場合、
DESC テーブル名
か
SHOW fields FROM テーブル名
でテーブルの構造を表示できます。
⇩ パスワードなどを忘れたときは再設定が必要のようです。
・SQLユーザー要注意!意外と知らないMySQLのrootパスワード設定方法 | Tech2GO
⇩ MySQL 5.7.6でmysql.userテーブルの構造が変わったようです。
・日々の覚書: MySQL 5.7.6でmysql.userテーブルのパスワードのカラム名がなんか変わった
パスワードがハッシュ化されてるってことですかね。
脱線しましたが、データベースを作成します。
CREATE DATABASE diary;
次に、ownerユーザーでdiaryデータベースが使えるように、ownerユーザーに権限を付与します。
GRANT ALL PRIVILEGES ON データベース名.* TO 'ユーザー名'@'ホスト名' IDENTIFIED BY 'ユーザーパスワード' ;
変更を反映させます。
一旦、MySQLをログアウト(切断)し、ownerユーザーでログインし直します。
ログイン。
USE文でデータベースを指定できれば権限の問題は大丈夫と思われます。
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) );
・[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! にアクセスします。
「Table Structure」でテーブルの構成を、「Export Format」で出力ファイルの形式を選択できます。「Generated rows」で何行分のデータを作成するか決められます。「Add Another Column」などでカラムの追加ができます。
なんか、csvが上手くいかないんで、いったんJSON形式で。
「Generate data」をクリックすると、データが生成されるので、コピーし、
JSON to CSV というサイトにアクセスし、上の欄に張り付けると、csvデータとして下の欄に出力されます。
「Download the entire CSV」 でcsvファイルとしてダウンロードできます。
ダウンロードしたcsvファイルを開くと、カラムの順番がテーブル定義と違ってしまっているので、並び替えます。
並び替えます。カラム名などの行は削除しておきます。
連番が0からになってしまっているので、1からスタートするよう修正。
1からになりました。
日付がdatetime型の形式になってないので、編集。
E1のセルを選択した状態で、「="3/1" + RAND() * 91.9999」と入力するとランダムで「yyyy/MM/DD HH:mm:ss」の形式を作ってくれるようです(年がすべて2017になってしまいますが)
日付も編集できたら保存します。
備考として、「書式(O)」>「セル(L)...」から、
「カテゴリー(A)」 の「ユーザー定義」で、「書式コード(F)」のところでフォーマットも追加できるようです。
サクラエディタなどで開いて「ファイル」「名前を付けて保存(A)...」を選択。
「文字コードセット(C):」で「UTF-8」を選択し「保存(S)」。
保存の際、「現在の入力改行コードCRLFに統一しますか?」と聞かれますが、ここは「いいえ(N)」を選択してください。
・WEB製作時に使えそうなダミーデータ生成サービスまとめ10選(テキスト/画像/個人情報) | degitekunote2
テーブルにインポート
ローカルのファイルからデータをインポートするには、MySQLログイン時にオプションをつける必要があります。
mysql --local-infile=1 -u ユーザー名 -p
ログインしたら、csvファイルをインポート。むっちゃWarningsの嵐(涙)。
「SHOW WARNINGS」で確認できるとのこと。
どうやら、「post_title」と「post_description」のカラムのサイズを超えたデータを入れようとしてるために起こる警告らしいです。
テーブルのカラムのデータ型の定義を変更します。
ALTER TABLE テーブル名 MODIFY カラム名 カラム定義
カラムのデータ型が変更できました。
そしたら、いったんテーブルから全データを削除(普通は必ずWHERE句を指定して削除するデータを限定します)、再びデータをcsvファイルからインポート。
今度は警告なくデータがインポートできました。
EclipseのDBViewerで確認してみます。
データが入りました。
ダミーデータをもっと効率的に作成する方法を見つけたいもんですね。
今回はこのへんで。