MySQLエクスポート、インポートいろいろ

#概要
データ抽出で調べていたらいくつか方法があるけど一つにまとまっている記事がなかったのでまとめてみました。
また、OUTFILE/INFILE使用時に注意することも補足としてまとめました。
※mysqlを使っています。

##mysqldump

**DB外からDBやテーブルをオプション指定して出力する**
　・データバックアップとか移動に使うことが多い。

 参照
　　[mysqldumpまとめ](https://qiita.com/PlanetMeron/items/3a41e14607a65bc9b60c)
　　[mysqldumpオプション](https://www.risewill.co.jp/blog/archives/195)

```shell:コマンド
    # dumpアウトプット例：db全体(db_nameを並べれば複数可能)
    $ mysqldump -h 127.0.0.1 -u user_name -p -P 3306 db_name > test.sql
    
    # dumpアウトプット例：特定table(table_nameを並べれば複数可能)
    $ mysqldump -h 127.0.0.1 -u user_name -p -P 3306 db_name table_name > test.sql

    # dumpインプット例：db全体
    $ mysql -h 127.0.0.1 -u user_name -p -P 3306 db_name < test.sql

    # その他例：トランザクション使用・主キーorユニークキーでソート
    # ファイル名指定し、指定DBの特定テーブルをカレントディレクトリにアウトプットしたい
    $ mysqldump -h 127.0.0.1 --single-transaction --order-by-primary
    -r test.aql -u user_name -p -P 3306 db_name table_name
```

##mysql -e "sql文"

**DB外から直接sql文を実行**
　・SQL実行結果が格納されるので、何か指定したデータが欲しい場合に使用することが多い。
　　**<font color="Red">※RDS(MySQL)環境(AWS)だと後述されるINTO OUTFILE(エクスポート)は使えないのでこれを使う</font>**
　　・[Amazon Aurora では MySQLのINTO OUTFILEが使えない](https://kishaku-kangen.blogspot.com/2017/07/amazon-aurora-mysqlinto-outfile.html)
　　・[RDS(MySQL)をCSVで出力する際にAccess denied](https://qiita.com/nishina555/items/17bd87ce37426ea49143)

```shell:コマンド
    # 出力方法例
    $ mysql -h 127.0.0.1 -p -P 3306 -e "SELECT文" db_name > ./test.csv
```

##OUTFILE/INFILE

**DB接続し、sql実行でCSVファイルのインポート・エクスポートを実行**

###使い方

参照
[MySQLでCSVファイルを使って結果出力やデータ入力を行う方法](https://proengineer.internous.co.jp/content/columnfeature/6776#section200)
[LOAD DATA INFILE構文](https://rfs.jp/sb/sql/s03/08-6.html)

```sql:SQL
    /* エクスポート */
    [SQL文] INTO OUTFILE '[フルパスファイル名]';
    /* 例 */
    SELECT * FROM db_name.table_name INTO OUTFILE '/var/lib/mysql-files/test.csv'

    /* インポート */
    LOAD DATA INFILE '[フルパスファイル名]' INTO TABLE db_name.table_name FIELDS TERMINATED BY '[区切り文字]' [OPTIONALLY] ENCLOSED BY '[フィールドを囲む文字]';
    /* 例 */
    LOAD DATA INFILE '/var/lib/mysql-files/test.csv' INTO TABLE db_name.table_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"';
```

###OUTFILE/INFILE補足
**１．ファイル格納場所はMySQLバージョンによっては指定ファイル以外読み込めないため、以下のSQLを実行してファイル格納場所を検索する必要がある**

　参照
　[LOAD DATA INFILE構文でテキストファイルからMySQLにデータをロード](https://proengineer.internous.co.jp/content/columnfeature/6776#section200)

```sql:SQL
    /* ファイル格納場所検索 */
    SELECT @@global.secure_file_priv;
```

**２．インポート時の改行コードがLFかどうかをチェック**
　　一度windowsを使用してファイルを開くと改行コードが変更されてしまう(CRLF)ので一度改行コードを戻す必要がある。
　　改行コードを戻さずにインポートした場合、データが正しくインサートされない場合がある。

　参照
　[改行コードに気をつけろ](https://qiita.com/n_s_y_m/items/d87e5c991da5ade957c1)
　[LOAD DATA INFILEで大はまりした話](https://www.infoscoop.org/blogjp/2014/07/23/about-load-data-infile/)
　[複数ファイルの改行コードを一括置換しちゃう](https://qiita.com/kouchi67/items/958e74ef792eed0c157d)

```sql:SQL
    /* 対応策例①：インポート時に改行コードを指定 */
    INFILE 'test.csv' INTO TABLE user FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
```
```shell:コマンド
    # 対応策例②：DB外で一括置換
    $ nkf -Lu --overwrite ./test.csv
```

**３．インポート時の1行目の項目を無視したい。何度も上書きしたい**
　　1行目無視：`IGNORE 1 LINES`を設定する
　　ファイル上書き：`REPLACE`を設定

　参照
　[LOAD DATA INFILEを稀によく使うので、使い方をメモ](https://kanonji.hatenadiary.com/entry/20110920/1316492030)

```sql:SQL
    /* 1行目無視 */
    LOAD DATA INFILE 'test.csv' INTO TABLE foo FIELDS TERMINATED BY ',' IGNORE 1 LINES;
    /* 同名ファイル上書 */
    LOAD DATA INFILE 'test.csv' REPLACE INTO TABLE foo FIELDS TERMINATED BY ',' IGNORE 1 LINES;
```

**４．インポート時の`LOAD DATA LOCAL INFILE`と`LOAD DATA INFILE`**
　　LOCAL利用で、実行した環境からファイル取得し、データ送信されてデータベースに格納される。
　　**<font color="Red">※RDS環境(AWS)だとLOCALを付ける必要がある</font>**

　参照
　[「load data LOCAL infile」の「LOCAL」って何？](https://ipvx.info/everyones-db/mysql/what-local-load-data-local-infile/)

```sql:SQL
    /* LOCAL記載 */
    LOAD DATA LOCAL INFILE 'test.csv' REPLACE INTO TABLE foo FIELDS TERMINATED BY ',' IGNORE 1 LINES;
```


##その他

データ出力時に番号を付けたいときとか。
[MySQLのSELECT結果に行番号を振る。](https://qiita.com/sand_bash/items/851f08a36381a4f01b75)



・必要な補足あればコメントよろしくお願いいたします。