CSVファイルをDBにインポートする

## 概要
容量の大きいCSVファイルをMySQLに流し込む処理を作ることがあり、調べた内容をメモしておく。

## ミドルウェア構成
dockerで Apache + PHP + MadiaDB のローカル環境を作ってテストしてみる。

```yml:docker-compose.yml
version: '3'
services:
  httpd:
    image: local/apache:2.4
    build: ./docker/httpd/
    ports:
      - '8080:80'
    volumes:
      - app:/var/www/www.example.com:cached

  php:
    image: local/php:7.3
    build:
      context: ./
      dockerfile: ./docker/php/Dockerfile
    ports:
      - '9000:9000'
    volumes:
      - app:/var/www/www.example.com:cached
    depends_on:
      - mariadb

  mariadb:
    image: local/mariadb:10.2
    build: ./docker/mariadb/
    environment:
      - MYSQL_DATABASE=mysql
      - MYSQL_ROOT_PASSWORD=password
    ports:
      - '3306:3306'
    volumes:
      - mariadb-data:/var/lib/mysql

volumes:
  mariadb-data:
  app:
    driver_opts:
      type: none
      device: ${PWD}/
      o: bind
```

## アプリケーションについて
インポートコマンドを作る際の方針は以下の通りです。

* Laravelのコマンドとして構築する
* 一時テーブルを作って、そちらにデータを一括流し込みする
* `Load Data Local Infile`を使ってインポートする  
※ PHPで１行ごと読んで処理していると時間かかるため

## 準備
Laravelで `LOAD DATA LOCAL INFILE` を使うには、DBとLaravelの設定が必要なようです。

```my.cnf
[server]
local_infile=true
``` 

```php:config/database.php
'mysql' => [
    'driver'      => 'mysql',
    'host'        => env('DB_HOST', 'mariadb'),
    'port'        => env('DB_PORT', '3306'),
    'database'    => env('DB_DATABASE', 'example'),
    'username'    => env('DB_USERNAME', 'mysql'),
    'password'    => env('DB_PASSWORD', 'password'),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset'     => 'utf8mb4',
    'collation'   => 'utf8mb4_unicode_ci',
    'prefix'      => '',
    'strict'      => true,
    'engine'      => null,
    'options'     => [
        PDO::ATTR_EMULATE_PREPARES   => true,
        PDO::MYSQL_ATTR_LOCAL_INFILE => true,  // ← ここ!!
    ],
],
```

## 実装してみる
まず、コマンドを追加する。

```bash
$ php artisan make:command ImportCsv
```

サンプルなので、コマンドにすべて直書きしたいと思います。

```php
<?php
namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

class ImportCsv extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'import:csv';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'import data from csv file.';

    public function handle()
    {
        $this->info('start');

        // ファイルを指定する
        $file = '/var/www/www.example.com/hoge.csv';

        // 一時テーブルを作成する
        $this->createTemporaryTable();

        // 一次テーブルにCSVファイルのデータを流し込む
        $this->importData($file);

        // その他登録処理などを記述する
        // ...

        $this->info('complete');
    }

    private function createTemporaryTable()
    {
        Schema::create('tmp_hoge', function (Blueprint $table) {
            $table->bigIncrements('id')->comment('id');
            $table->string('title', 50)->comment('タイトル');
            $table->string('description', 255)->comment('説明');
            $table->integer('price')->comment('金額');
            $table->integer('tax_rate')->comment('税率');
            $table->string('memo')->comment('備考');
            $table->temporary();
        });
    }

    private function importData(string $file)
    {
        // エンコード指定 (sjis,cp932, utf8)
        DB::statement("SET CHARACTER_SET_DATABASE=cp932;"); 
        DB::statement("
            LOAD DATA LOCAL INFILE ?
            INTO TABLE `tmp_hoge`
            FIELDS TERMINATED BY ?
            ENCLOSED BY ?
            LINES TERMINATED BY ?
            (@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26,@27,@28,@29,@30)
            SET
              title=@1,
              description=@2,
              price=@5,
              tax_rate=@6,
              memo=@30
            ;
        ", [
            $file,
            ',',
            '"',
            "\r\n"
        ]);
    }
}
```

実行するときは、こんな感じでしょうか。

```bash
$ php artisan import:csv
```

こんな感じでインポートできました。

## まとめ
ローカルのdocker環境で、800万件のデータを取り込んで見たときは、
だいたい90秒(1分30秒)ぐらいだったでしょうか。

入力検証などはできないかったりするので、その点はデメリットです。
しかし、信頼できるデータをまとめて大量に流し込む場合には便利かもしれませんね。

## 追記 2021/11/05
「 **SJIS** 」のエンコードだと、機種依存文字が「？」になって取り込めないので、
「 **CP932** 」のエンコードの方が良さそうに思われます。

## 参考サイト
* [LaravelでLOAD DATA LOCAL INFILEできない](https://qiita.com/hondy12345/items/ca5fdc4573292f3fcf5d)
* [Creating Temporary table in laravel/lumen and insert data](https://stackoverflow.com/questions/47493155/creating-temporary-table-in-laravel-lumen-and-insert-data)
* [MySQLの”LOAD DATA INFILE”などでCSVファイル入出力時の文字コード、権限などの注意点](http://wordpress.honobono-life.info/svrmng/mysql%E3%81%AEload-data-infile%E3%82%B3%E3%83%9E%E3%83%B3%E3%83%89%E3%81%AA%E3%81%A9%E3%81%A7csv%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E5%85%A5%E5%87%BA%E5%8A%9B%E6%99%82%E3%81%AE%E6%96%87%E5%AD%97/)
* [MysqlでCSVから特定列だけインポートする](http://jigsaw.hatenablog.jp/entry/2013/06/12/113016)
* [【MySQL】LOAD DATA INFILE するときのファイルの文字コード](https://www.softel.co.jp/blogs/tech/archives/2293)
* [mysql で sjis の csv をインポートする(CP932)](https://moriya.xrea.jp/tdiary/20210228.html)
* [MySQLのsjisとcp932の違い](https://blog.tmtms.net/entry/201805/mysql-sjis-cp932)
* [MySQLにおける機種依存文字の扱い](https://smiu2it.blog.ss-blog.jp/2009-04-16)

以上