DB

MySQLのLOAD DATAコマンドでCSVファイルのデータをテーブルに追加してみた

今回は、MySQLのLOAD DATAコマンドでCSVファイルのデータを追加してみたので共有する。今回は、MySQLのバージョン8.0の場合について記載している。

前提条件

Windows端末上に、MySQLをインストール済であること。また、salesテーブルを作成済であること。MySQLのインストール手順は下記記事を参照のこと。なお、MySQLのバージョンは8.0とする。

Windows端末にMySQL、PostgreSQL、Oracleをインストールしてみた今回はいろいろな種類のデータベースを使ってみたかったので、Windows端末に、MySQL、PostgreSQL、Oracleそれぞれの...

やってみたこと

  1. secure_file_privで指定されたディレクトリにファイルを配置した場合
  2. secure_file_privで指定されたディレクトリ以外にファイルを配置した場合

 

secure_file_privで指定されたディレクトリにファイルを配置した場合

secure_file_privで指定されたディレクトリは、MySQLデータベース内のテーブルに、ファイルからデータを読み込んだり書き出したりする際に、アクセス可能なディレクトリを示す。

MySQLのバージョン8.0の場合は、インストールした時点から、secure_file_privのディレクトリ設定がされている。他の設定変更をしないと、secure_file_privで指定したディレクトリ以外からファイルの読み込んだり書き出したりすることはできない。

1) secure_file_privの設定値を確認

secure_file_privの設定値確認

2) secure_file_privの設定値となっているディレクトリに、読み込むCSVファイルを配置
読み込むCSVファイルの配置

なお、読み込むCSVファイルの内容は以下の通り。
読み込むCSVファイルの内容

3) LOAD DATAコマンドでCSVファイルのデータをMySQLデータベース内のテーブル(world.sales)に格納

loaddataコマンド
なお、上記コマンドは、区切り文字:,(カンマ)、囲い文字:”(ダブルクォート)、文字コード:UTF-8のCSVファイルの取り込みを、1行目のヘッダ行を除いて行うコマンドである。

4) 3)で格納されたデータを確認

load後のデータ確認

なお、secure_file_privで指定されたディレクトリ以外からファイルの読み込みを行おうとすると、以下のように、指定のコマンドが、現状のMySQLバージョンでは利用できない旨のエラーメッセージが表示される。
loaddataコマンドエラー

secure_file_privで指定されたディレクトリ以外にファイルを配置した場合

secure_file_privで指定されたディレクトリ以外からファイルを読み込めるようにするには、以下のように、mysqlコマンドでログインする際「–local_infile=1」というオプションを指定した上で、MySQLサーバー側の「local_infile」の値を1に設定すればよい。

1) 「–local_infile=1」というオプションを指定した上で、mysqlコマンドでログイン
secure_file_priv以外からのファイルロード処理1

2) MySQLサーバー側の「local_infile」を1に設定し、「local_infile」の値を確認

secure_file_priv以外からのファイルロード処理2

3) LOAD DATAコマンドでCSVファイルのデータをMySQLデータベース内のテーブル(world.sales)に格納

secure_file_priv以外からのファイルロード処理3
なお、上記コマンドは、区切り文字:,(カンマ)、囲い文字:”(ダブルクォート)、文字コード:UTF-8のCSVファイルの取り込みを、1行目のヘッダ行を除いて行うコマンドである。また、「D:\work」ディレクトリ内に、読み込むCSVファイルを配置済であるとする。

4) 3)で格納されたデータを確認

secure_file_priv以外からのファイルロード処理4