概要
参照関係を持つテーブルにCSVデータをインポートすることを考える。
- 親テーブルの主キーにAUTO_INCREMENTが設定されていて、子テーブルがそのキーを参照するリレーションを対象とする
- データの追加ではなく、空のテーブルに新たにデータを登録するケースを対象とする
テーブル構成
以下のようなテーブル構造とする。
- branchesテーブルは支社に関する情報を記録する
- AUTO_INCREMENTの支社ID(プライマリーキー)
- 支社名
- warehousesテーブルは支社に属する倉庫に関する情報を記録する
- AUTO_INCREMENTの倉庫ID(プライマリーキー)
- 倉庫が属する支社を参照する支社ID(外部キー)
- 倉庫名
- customersテーブルは倉庫から出荷される先の顧客に関する情報を記録する
- AUTO_INCREMENTの顧客ID(プライマリーキー)
- 顧客を担当する倉庫のID(外部キー)
- 顧客名
ExcelでCSVファイルの作成
元データの入力
以下の3つのテーブルデータを別のシートに入力する。
＜branchesテーブル＞
| id | name |
| 1 | 関東支社 |
| 2 | 関西支社 |
＜warehousesテーブル＞
| id | branch_id | name |
| 1 | 1 | 千葉倉庫 |
| 2 | 1 | 八王子倉庫 |
| 3 | 2 | 東大阪倉庫 |
| 4 | 2 | 神戸倉庫 |
＜customersテーブル＞
| id | warehouse_id | customer |
| 1 | 1 | 山田商会 |
| 2 | 1 | 田中屋 |
| 3 | 2 | 中川商店 |
| 4 | 2 | 川井製作所 |
| 5 | 3 | 井戸商店 |
| 6 | 3 | 戸田商会 |
| 7 | 4 | 田村屋 |
| 8 | 4 | 村山製作所 |
CSVファイルの保存
3つのシートそれぞれをCSVファイルで保存する。
- 名前を付けて保存
- ファイルの種類は”CSV UTF-8 (コンマ区切り)”
- シートごとに別々のCSVファイルに保存
BOMなしへの変換
ExcelのUTF-8はBOM付きで保存されるので、これをBOMなしのUTF-8に変換する。
たとえば、Windowsのメモ帳で読み込んでUTF-8(BOM付きではない方)で保存し直すなど。
ファイルの提供
外部サーバーの場合、FTPで所定の場所にCSVファイルをアップロード。
Vagrant仮想環境の場合、保存されたCSVファイルを共有ディレクトリーにコピーまたは移動。
MySQLでの操作
ローカルファイルを許可してログイン
MySQLにログインするのに、--enable-local-infileを指定してローカルファイルからの入力を許可。
|
1 |
[vagrant@localhost ~]$ mysql -u root -p --enable-local-infile |
MySQL内でローカルファイルの入力許可
@@local_infile変数の内容をチェックする。0の場合はローカルファイル入力が許可されていないので、SET GLOBALで許可する。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> SELECT @@local_infile; +----------------+ | @@local_infile | +----------------+ | 0 | +----------------+ 1 row in set (0.00 sec) mysql> SET GLOBAL local_infile=on; mysql> SELECT @@local_infile; +----------------+ | @@local_infile | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) |
テーブルの準備
CREATE TABLEでCSVデータを読み込むテーブルを準備する。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE branches ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); CREATE TABLE warehouses ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, branch_id INT, name VARCHAR(20), FOREIGN KEY(branch_id) REFERENCES branches(id) ); CREATE TABLE customers ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, warehouse_id INT, name VARCHAR(20), FOREIGN KEY(warehouse_id) REFERENCES warehouses(id) ); |
作成したテーブルの構造。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
mysql> DESCRIBE branches; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> DESCRIBE warehouses; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | branch_id | int | YES | MUL | NULL | | | name | varchar(20) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> DESCRIBE customers; +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | warehouse_id | int | YES | MUL | NULL | | | customer | varchar(20) | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) |
CSVファイルの読み込み
アップロードまたは共有されたCSVファイルをLOAD DATA LOCAL INFILEクエリーで読み込む。
- カンマ区切り→
FILES TERMINATED BY ',' - 改行コード→
LINES TERMINATED BY '\r\n' - 1行目が見出し行の場合→
IGNORE 1 LINES
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
LOAD DATA LOCAL INFILE "~/branches.csv" INTO TABLE branches FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; LOAD DATA LOCAL INFILE "~/warehouses.csv" INTO TABLE warehouses FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; LOAD DATA LOCAL INFILE "~/customers.csv" INTO TABLE customers FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; |
読み込み結果の確認
各テーブルデータの確認
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
mysql> SELECT * FROM branches; +----+--------------+ | id | name | +----+--------------+ | 1 | 関東支社 | | 2 | 関西支社 | +----+--------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM warehouses; +----+-----------+-----------------+ | id | branch_id | name | +----+-----------+-----------------+ | 1 | 1 | 千葉倉庫 | | 2 | 1 | 八王子倉庫 | | 3 | 2 | 東大阪倉庫 | | 4 | 2 | 神戸倉庫 | +----+-----------+-----------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM customers; +----+--------------+-----------------+ | id | warehouse_id | customer | +----+--------------+-----------------+ | 1 | 1 | 山田商会 | | 2 | 1 | 田中屋 | | 3 | 2 | 中川商店 | | 4 | 2 | 川井製作所 | | 5 | 3 | 井戸商店 | | 6 | 3 | 戸田商会 | | 7 | 4 | 田村屋 | | 8 | 4 | 村山製作所 | +----+--------------+-----------------+ 8 rows in set (0.00 sec) |
参照関係の確認
外部キーによる参照関係の確認。各外部キーと親テーブルのキーでテーブルを結合。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT branches.id, branches.name, warehouses.id, warehouses.name, customers.id, customers.name FROM customers INNER JOIN warehouses ON warehouses.id = warehouse_id INNER JOIN branches ON branches.id = branch_id; |
実行結果。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> SELECT -> branches.id, -> branches.name, -> warehouses.id, -> warehouses.name, -> customers.id, -> customers.name -> FROM customers -> INNER JOIN warehouses -> ON warehouses.id = warehouse_id -> INNER JOIN branches -> ON branches.id = branch_id; +----+--------------+----+-----------------+----+-----------------+ | id | name | id | name | id | name | +----+--------------+----+-----------------+----+-----------------+ | 1 | 関東支社 | 1 | 千葉倉庫 | 1 | 山田商会 | | 1 | 関東支社 | 1 | 千葉倉庫 | 2 | 田中屋 | | 1 | 関東支社 | 2 | 八王子倉庫 | 3 | 中川商店 | | 1 | 関東支社 | 2 | 八王子倉庫 | 4 | 川井製作所 | | 2 | 関西支社 | 3 | 東大阪倉庫 | 5 | 井戸商店 | | 2 | 関西支社 | 3 | 東大阪倉庫 | 6 | 戸田商会 | | 2 | 関西支社 | 4 | 神戸倉庫 | 7 | 田村屋 | | 2 | 関西支社 | 4 | 神戸倉庫 | 8 | 村山製作所 | +----+--------------+----+-----------------+----+-----------------+ 8 rows in set (0.00 sec) |
新規追加の確認
たとえば新たな支社を1つ追加し、AUTO_INCREMENTが機能していることを確認。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> INSERT INTO branches (name) VALUES ('九州支社'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM branches; +----+--------------+ | id | name | +----+--------------+ | 1 | 関東支社 | | 2 | 関西支社 | | 3 | 九州支社 | +----+--------------+ 3 rows in set (0.00 sec) |
外部結合してみる。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT branches.id, branches.name, warehouses.id, warehouses.name, customers.id, customers.name FROM customers RIGHT OUTER JOIN warehouses ON warehouses.id = warehouse_id RIGHT OUTER JOIN branches ON branches.id = branch_id; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
mysql> SELECT -> branches.id, -> branches.name, -> warehouses.id, -> warehouses.name, -> customers.id, -> customers.name -> FROM customers -> RIGHT OUTER JOIN warehouses -> ON warehouses.id = warehouse_id -> RIGHT OUTER JOIN branches -> ON branches.id = branch_id; +----+--------------+------+-----------------+------+-----------------+ | id | name | id | name | id | name | +----+--------------+------+-----------------+------+-----------------+ | 1 | 関東支社 | 1 | 千葉倉庫 | 1 | 山田商会 | | 1 | 関東支社 | 1 | 千葉倉庫 | 2 | 田中屋 | | 1 | 関東支社 | 2 | 八王子倉庫 | 3 | 中川商店 | | 1 | 関東支社 | 2 | 八王子倉庫 | 4 | 川井製作所 | | 2 | 関西支社 | 3 | 東大阪倉庫 | 5 | 井戸商店 | | 2 | 関西支社 | 3 | 東大阪倉庫 | 6 | 戸田商会 | | 2 | 関西支社 | 4 | 神戸倉庫 | 7 | 田村屋 | | 2 | 関西支社 | 4 | 神戸倉庫 | 8 | 村山製作所 | | 3 | 九州支社 | NULL | NULL | NULL | NULL | +----+--------------+------+-----------------+------+-----------------+ 9 rows in set (0.00 sec) |