今回は、MySQLのLOAD DATAコマンドでCSVファイルのデータを追加してみたので共有する。今回は、MySQLのバージョン8.0の場合について記載している。
前提条件
Windows端末上に、MySQLをインストール済であること。また、salesテーブルを作成済であること。MySQLのインストール手順は下記記事を参照のこと。なお、MySQLのバージョンは8.0とする。
やってみたこと
secure_file_privで指定されたディレクトリにファイルを配置した場合
secure_file_privで指定されたディレクトリは、MySQLデータベース内のテーブルに、ファイルからデータを読み込んだり書き出したりする際に、アクセス可能なディレクトリを示す。
MySQLのバージョン8.0の場合は、インストールした時点から、secure_file_privのディレクトリ設定がされている。他の設定変更をしないと、secure_file_privで指定したディレクトリ以外からファイルの読み込んだり書き出したりすることはできない。
1) secure_file_privの設定値を確認
SHOW VARIABLES LIKE "secure_file_priv"
2) secure_file_privの設定値となっているディレクトリに、読み込むCSVファイルを配置
なお、読み込むCSVファイルの内容は以下の通り。
3) LOAD DATAコマンドでCSVファイルのデータをMySQLデータベース内のテーブル(world.sales)に格納
LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\insert_sales.csv' INTO TABLE world.sales FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
なお、上記コマンドは、区切り文字：,(カンマ)、囲い文字：”(ダブルクォート)、文字コード：UTF-8のCSVファイルの取り込みを、1行目のヘッダ行を除いて行うコマンドである。
4) 3)で格納されたデータを確認
select * from world.sales
なお、secure_file_privで指定されたディレクトリ以外からファイルの読み込みを行おうとすると、以下のように、指定のコマンドが、現状のMySQLバージョンでは利用できない旨のエラーメッセージが表示される。
secure_file_privで指定されたディレクトリ以外にファイルを配置した場合
secure_file_privで指定されたディレクトリ以外からファイルを読み込めるようにするには、以下のように、mysqlコマンドでログインする際「–local_infile=1」というオプションを指定した上で、MySQLサーバー側の「local_infile」の値を1に設定すればよい。
1) 「–local_infile=1」というオプションを指定した上で、mysqlコマンドでログイン
2) MySQLサーバー側の「local_infile」を1に設定し、「local_infile」の値を確認
SELECT @@local_infile; SET PERSIST local_infile= 1; SELECT @@local_infile;
3) LOAD DATAコマンドでCSVファイルのデータをMySQLデータベース内のテーブル(world.sales)に格納
LOAD DATA LOCAL INFILE 'D:\\work\\insert_sales.csv' INTO TABLE world.sales FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
なお、上記コマンドは、区切り文字：,(カンマ)、囲い文字：”(ダブルクォート)、文字コード：UTF-8のCSVファイルの取り込みを、1行目のヘッダ行を除いて行うコマンドである。また、「D:\work」ディレクトリ内に、読み込むCSVファイルを配置済であるとする。
4) 3)で格納されたデータを確認
select * from world.sales
