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

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

##mysqldump

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

参照
　　mysqldumpまとめ
　　mysqldumpオプション

コマンド

    # 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実行結果が格納されるので、何か指定したデータが欲しい場合に使用することが多い。
　　※RDS(MySQL)環境(AWS)だと後述されるINTO OUTFILE(エクスポート)は使えないのでこれを使う
　　・Amazon Aurora では MySQLのINTO OUTFILEが使えない
　　・RDS(MySQL)をCSVで出力する際にAccess denied

コマンド

    # 出力方法例
    $ mysql -h 127.0.0.1 -p -P 3306 -e "SELECT文" db_name > ./test.csv

##OUTFILE/INFILE

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

###使い方

参照
MySQLでCSVファイルを使って結果出力やデータ入力を行う方法
LOAD DATA INFILE構文

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にデータをロード

SQL

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

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

　参照
　改行コードに気をつけろ
　LOAD DATA INFILEで大はまりした話
　複数ファイルの改行コードを一括置換しちゃう

SQL

    /* 対応策例①：インポート時に改行コードを指定 */
    INFILE 'test.csv' INTO TABLE user FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';

コマンド

    # 対応策例②：DB外で一括置換
    $ nkf -Lu --overwrite ./test.csv

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

　参照
　LOAD DATA INFILEを稀によく使うので、使い方をメモ

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利用で、実行した環境からファイル取得し、データ送信されてデータベースに格納される。
　　※RDS環境(AWS)だとLOCALを付ける必要がある

　参照
　「load data LOCAL infile」の「LOCAL」って何？

SQL

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

##その他

データ出力時に番号を付けたいときとか。
MySQLのSELECT結果に行番号を振る。

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