【初心者向け】MySQLでCSVファイルを楽々インポート：NULL値の扱い方も解説

MySQLでCSVデータからNULL値を読み込む方法：LOAD DATA INFILE構文

MySQLでCSVファイルからデータをロードする際、NULL値を適切に処理することは重要です。LOAD DATA INFILE構文を使用することで、CSVファイル内の空文字（""）や区切り文字のみを含む行をNULL値として認識し、データベーステーブルにロードすることができます。

手順

1. テーブル作成

まず、ロードするCSVデータに対応するテーブルを作成する必要があります。テーブル定義には、各列のデータ型と、NULL値を許容するかどうかを明記する必要があります。

CREATE TABLE your_table_name (
  column1 datatype NOT NULL,
  column2 datatype,
  column3 datatype NULL
);

1. CSVファイルの準備

CSVファイルは、MySQLが認識できる形式である必要があります。具体的には、以下の点に注意が必要です。

• 各列はカンマ（,）で区切られていること
• テキストデータは二重引用符（""）で囲まれていること
• NULL値を表すセルは空文字（""）または区切り文字のみを含むこと

1. LOAD DATA INFILE構文の使用

LOAD DATA INFILE構文を使用して、CSVファイルをMySQLテーブルにロードします。この構文には、以下のオプションを指定することができます。

• LOCAL INFILE 'filepath': ロードするCSVファイルのパスを指定します。
• FIELDS TERMINATED BY ',': 各列の区切り文字を指定します。デフォルトはカンマ（,）です。
• IGNORE 1 LINES: 最初の1行を無視します。これは、CSVファイルにヘッダー行が含まれている場合に便利です。
• SETNULLVALUES '': 空文字（""）をNULL値として扱います。

LOAD DATA INFILE 'path/to/your_file.csv'
INTO TABLE your_table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
SET `NULL` VALUES ''

補足事項

• 上記の手順は、MySQL 5.7以降で利用可能です。古いバージョンのMySQLでは、異なる構文が必要になる場合があります。
• 特殊文字を含むCSVデータを読み込む場合は、CHARACTER SET句を使用して適切な文字セットを指定する必要があります。
• 大量のデータをロードする場合は、LOAD DATA INFILEよりも高速なmysqlimportツールを使用することを検討してください。

例

以下の例では、data.csvという名前のCSVファイルからデータをmy_tableテーブルにロードします。data.csvファイルには、以下の内容が含まれています。

id,name,age
1,Alice,30
2,Bob,25
3,,22

CREATE TABLE my_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age INT NULL
);

LOAD DATA INFILE 'data.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
SET `NULL` VALUES ''

このSQLを実行すると、以下のデータがmy_tableテーブルにロードされます。

id | name       | age
----+------------+-----
1  | Alice     | 30
2  | Bob       | 25
3  | NULL       | 22

注意事項

• LOAD DATA INFILE構文を使用する場合は、十分な権限を持っていることを確認してください。
• CSVファイルが破損している場合、エラーが発生する可能性があります。
• 大量のデータをロードする場合は、パフォーマンスに影響を与える可能性があります。

CSVファイル data.csv

id,name,age,city
1,Alice,30,New York
2,Bob,25,Chicago
3,,22,
4,David,50,Los Angeles
5,Emily,,35

CREATE TABLE my_table (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age INT,
  city VARCHAR(255)
);

読み込み処理

LOAD DATA INFILE 'path/to/data.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, name, age, city)
SET @tmp_age = NULLIF(@tmp_age, ''),
    age = @tmp_age,
    city = NULLIF(@tmp_city, '')
;

解説

1. ファイルパス指定: LOAD DATA INFILE 'path/to/data.csv' で、読み込むCSVファイルのパスを指定します。
2. テーブル指定: INTO TABLE my_table で、データをロードするテーブルを指定します。
3. 区切り文字設定: FIELDS TERMINATED BY ',' で、各列の区切り文字をカンマ（,）に設定します。
4. ヘッダー行無視: IGNORE 1 LINES で、CSVファイルの最初の行をヘッダー行として無視します。
5. 列名マッピング: (id, name, age, city) で、CSVファイルの列とテーブルの列をマッピングします。
6. NULL値処理: SET @tmp_age = NULLIF(@tmp_age, ''), age = @tmp_age, city = NULLIF(@tmp_city, '') で、以下の処理を行います。
   • NULLIF(@tmp_age, ''): 空文字（""）を NULL に変換します。
   • age = @tmp_age: 変換結果を age 列に格納します。
7. ;: SQLクエリを終了します。

補足

• このコードは、MySQL 5.7以降で使用できます。

MySQLでCSVデータからNULL値を読み込む方法は、LOAD DATA INFILE構文以外にもいくつかあります。以下に、代替方法として考えられる2つの方法を紹介します。

方法1：INSERTステートメントを使用する

INSERTステートメントを使用して、CSVファイルを逐行読み込み、各行を個別にレコードとして挿入することができます。この方法では、NULL値を明示的に処理する必要があり、コードが煩雑になる可能性があります。

SET @filepath = 'path/to/data.csv';
SET @row_num = 1;

SELECT
  @col1 := LINE_CONTAINS(@filepath, ',', @row_num),
  @col2 := LINE_CONTAINS(@filepath, ',', @row_num + 1),
  @col3 := LINE_CONTAINS(@filepath, ',', @row_num + 2),
  @col4 := LINE_CONTAINS(@filepath, ',', @row_num + 3)
;

INSERT INTO my_table (id, name, age, city)
VALUES (
  @col1,
  @col2,
  IFNULL(@col3, NULL),
  IFNULL(@col4, NULL)
);

SET @row_num = @row_num + 4;

WHILE @col1 IS NOT NULL;

REPEAT
  SELECT
    @col1 := LINE_CONTAINS(@filepath, ',', @row_num),
    @col2 := LINE_CONTAINS(@filepath, ',', @row_num + 1),
    @col3 := LINE_CONTAINS(@filepath, ',', @row_num + 2),
    @col4 := LINE_CONTAINS(@filepath, ',', @row_num + 3)
  ;

  INSERT INTO my_table (id, name, age, city)
  VALUES (
    @col1,
    @col2,
    IFNULL(@col3, NULL),
    IFNULL(@col4, NULL)
  );

  SET @row_num = @row_num + 4;
UNTIL @col1 IS NULL;

方法2：外部ライブラリを使用する

MySQL用のライブラリの中には、CSVファイルの読み込みとNULL値の処理を容易にするものがあります。以下に、2つのライブラリの例を紹介します。

これらのライブラリは、LOAD DATA INFILE構文よりも柔軟性と使いやすさに優れている可能性があります。

各方法の比較

どの方法が最適かは、データ量、データ形式、および開発者のスキルレベルによって異なります。シンプルなケースであれば、LOAD DATA INFILE構文が適切です。より複雑なケースや、柔軟性と使いやすさを求める場合は、INSERTステートメントまたは外部ライブラリを検討してください。