【MySQL/MariaDB】"LOAD DATA INFILE"で発生する"Invalid ut8mb4 character string"エラーの原因と解決策
MySQL/MariaDB で LOAD DATA INFILE コマンドを使用してデータをロードする場合、"Invalid ut8mb4 character string" エラーが発生することがあります。これは、ロードしようとしているデータに、MySQL/MariaDB がサポートしていない UTF-8 文字が含まれていることを示しています。
原因
このエラーは、以下のいずれかの原因によって発生します。
- ロード対象ファイルのエンコーディングが間違っている: ロード対象ファイルのエンコーディングが UTF-8 でない場合、このエラーが発生する可能性があります。ファイルが別のエンコーディングで保存されている場合は、適切なエンコーディングに変換してから再度ロードする必要があります。
- テーブルの文字セットが UTF-8 でない: ロード対象のテーブルの文字セットが UTF-8 でない場合、このエラーが発生する可能性があります。テーブルの文字セットを UTF-8 に変更する必要があります。
解決策
このエラーを解決するには、以下のいずれかの方法を実行する必要があります。
ロード対象ファイルのエンコーディングが UTF-8 でない場合は、適切なエンコーディングに変換する必要があります。テキストエディタやエンコーディング変換ツールを使用して、ファイルのエンコーディングを変更できます。
テーブルの文字セットを UTF-8 に変更する
ロード対象のテーブルの文字セットが UTF-8 でない場合は、以下のコマンドを使用して変更できます。
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;
LOAD DATA INFILE コマンドを実行するクライアントの文字セットが UTF-8 でない場合は、以下のコマンドを使用して変更できます。
SET NAMES utf8mb4;
エスケープシーケンスを使用する
一部の特殊文字は、LOAD DATA INFILE コマンドでエスケープする必要があります。たとえば、バックスラッシュ (\) は \\ としてエスケープする必要があります。
IGNORE オプションを使用する
LOAD DATA INFILE コマンドに IGNORE オプションを指定すると、無効な文字を含む行を無視してデータを読み込むことができます。
- 上記の解決策に加えて、エラーメッセージに表示されている具体的な文字列を確認することで、問題の原因を特定できる場合があります。
- 問題が解決しない場合は、MySQL/MariaDB のフォーラムやコミュニティで助けを求めることができます。
-- テーブルを作成する
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
city VARCHAR(255) NOT NULL
);
-- ロード対象のCSVファイル
LOAD DATA LOCAL INFILE '/path/to/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, email, city)
IGNORE 1 LINES;
説明
このコードは、以下のことを行います。
customersという名前のテーブルを作成します。このテーブルには、id、name、email、cityという 4 つの列があります。/path/to/customers.csvという名前の CSV ファイルからデータをロードします。- CSV ファイルの各行はカンマ (
,) で区切られていることを指定します。 id、name、email、cityの順序で、CSV ファイルの各列からデータをロードします。- CSV ファイルの最初の行を無視します。これは、通常、CSV ファイルのヘッダー行を含むためです。
補足
- このコードは、MySQL 5.7.7 以降と MariaDB 10.1 以降で使用できます。
- ロード対象の CSV ファイルのエンコーディングが UTF-8 であることを確認してください。
- テーブルの
city列のデータ型がVARCHARであることを確認してください。データ型が異なる場合は、LOAD DATA INFILEコマンドの(id, name, email, city)部分を適切なデータ型に変更する必要があります。
その他の解決策
以下のコマンドを使用して、クライアントとサーバーの文字セットを UTF-8 に設定できます。
SET CHARACTER_SET_CLIENT = utf8mb4;
SET CHARACTER_SET_RESULTS = utf8mb4;
このコマンドを実行すると、LOAD DATA INFILE コマンドでエラーが発生する可能性がなくなります。
iconv コマンドを使用して、ロード対象ファイルのエンコーディングを変換してから、LOAD DATA INFILE コマンドを実行できます。
iconv -f original_encoding -t utf8mb4 input.csv > output.csv
LOAD DATA LOCAL INFILE 'output.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, email, city)
IGNORE 1 LINES;
original_encodingでエンコードされたinput.csvファイルを読み込みます。- ファイルの内容を UTF-8 に変換します。
- 変換された内容を
output.csvファイルに書き込みます。 output.csvファイルをLOAD DATA INFILEコマンドを使用してロードします。
Temporary table を使用する
以下の方法で、一時テーブルを使用してデータをロードできます。
-- 一時テーブルを作成する
CREATE TEMPORARY TABLE tmp_customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
city VARCHAR(255) NOT NULL
) ENGINE = MEMORY;
-- ロード対象のCSVファイルを一時テーブルにロード
LOAD DATA LOCAL INFILE '/path/to/customers.csv'
INTO TABLE tmp_customers
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, email, city)
IGNORE 1 LINES;
-- 一時テーブルからデータを `customers` テーブルに挿入する
INSERT INTO customers (id, name, email, city)
SELECT id, name, email, city FROM tmp_customers;
-- 一時テーブルを削除する
DROP TEMPORARY TABLE tmp_customers;
この方法は、ロード対象のファイルが大きすぎる場合に役立ちます。
mysqlimport ツールを使用して、CSV ファイルからデータをロードできます。
mysqlimport -u username -p password -h hostname database_name customers.csv
- MySQL サーバーに接続します。
customers.csvファイルの内容をcustomersテーブルにロードします。
注意事項
mysqlimportツールは、MySQL クライアントに付属しています。mysqlimportツールを使用するには、MySQL サーバーへのアクセス権限が必要です。
mysql mariadb load-data-infile
