mysqlでLOAD DATA LOCAL INFILEを使用してmysqlにデータをインポートすると文字化けする

Question

CSVファイルをmysqlでLOAD DATA LOCAL INFILEを使用してmysqlにデータをインポートすると文字化けします。
原因が分からなくて困っています。

CSV

220,武蔵野三鷹,042220,0422,20,東日本電信電話株式会社,使用中,

220,武蔵野三鷹,042221,0422,21,東日本電信電話株式会社,使用中,
220,武蔵野三鷹,042222,0422,22,東日本電信電話株式会社,使用中,

MYSQL

*************************** 36740. row ***************************

            id: 54489

number_kukaku_code: '266'
ma: '小笠原'
number: '049989'
area_code: '04998'
city_code: '9'
company:
use_flg: '未使用
etc: NULL
created_at: NULL
updated_at: NULL
36740 rows in set (0.04 sec)

CSV文字コード

file 000697546.csv

000697546.csv: UTF-8 Unicode (with BOM) text, with CRLF line terminators

my.conf

#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8mb4
skip-character-set-client-handshake
[client]
default-character-set=utf8mb4

show variables like "chara%";

mysql> show variables like "chara%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

テーブル文字コード

mysql> show table status from denwa\G

*************************** 1. row ***************************
Name: area_codes
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 37050
Avg_row_length: 156
Data_length: 5783552
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 57338
Create_time: 2020-12-13 15:04:48
Update_time: 2020-12-13 16:04:03
Check_time: NULL
Collation: utf8mb4_unicode_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************

実行SQL

LOAD DATA LOCAL INFILE "/home/root/000697546.csv"

INTO TABLE area_codes
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(@1,@2,@3,@4,@5,@6,@7,@8)
SET number_kukaku_code=@1, ma=@2, number=@3,area_code=@4, city_code=@5,company=@6, use_flg=@7, etc=@8;

よろしくお願いいたします・

Comments

[yambejp]

投入したというcsvと文字化けしたという行データが
まったく違うものではないですか？
これだとちょっと判断がつかないですが・・・

[gomengo]

全部文字化けしてしまっていたので、そのようにしてしまいました。
申し訳ございません。

Answer 1

「LOAD DATA LOCAL INFILE」構文には「character set」句が指定できるので
指定してみてはどうでしょうか？

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.2.6 LOAD DATA INFILE 構文

Answer 2

Laravelでプルグラムを使用してインポートすることで解消しました。

根本的な解決にななっていないのですが。

{
    // アップロードファイルのファイルパスを取得
    $file_path[] = '/home/root/000697549.csv';
    $file_path[] = '/home/root/000697549.csv';
    $file_path[] = '/home/root/000697551.csv';
    $file_path[] = '/home/root/000697548.csv';
    $file_path[] = '/home/root/000697550.csv';
    $file_path[] = '/home/root/000697552.csv';
    // CSV取得
    foreach ($file_path as $key => $value) {
    $file =new \SplFileObject($value);
    $file->setFlags(
        \SplFileObject::READ_CSV |           // CSV 列として行を読み込む
        \SplFileObject::READ_AHEAD |       // 先読み/巻き戻しで読み出す。
        \SplFileObject::SKIP_EMPTY |         // 空行は読み飛ばす
        \SplFileObject::DROP_NEW_LINE    // 行末の改行を読み飛ばす
      );
    // 一行ずつ処理
    foreach($file as $line)
    {
        $data[] = [
            "number_kukaku_code"=> $line[0],
            "ma"   => $line[1],
            "number"    => $line[2],
            "area_code"=> $line[3],
            "city_code"=> $line[4],
            "company"=> $line[5],
            "use_flg"=> $line[6],
            "etc"=> $line[7],
        ];
    }

    AreaCode::insert($data);
    $data = [];
}
    //var_dump($data);
    exit;
}
php