【MySQL LOAD DATA】Duplicate entryエラーの解消

はじめに

• MySQLでテーブルにデータをインポートしようとしたところ、エラーが出てつまずきました。解消方法を残します。

やりたいこと

• MySQLで作成したテーブルに、データをインポート(LOAD DATA)したい。
• LOAD DATAステートメントを実行する際、『Duplicate entry ‘〇〇' for keyエラー』を解消したい。

開発環境

• Docker Desktop for Windows v4.19.0
• MySQL バージョン: 8.0.32

実行したSQLステートメント

LOAD DATA INFILE '/var/lib/mysql-files/page_views'
     INTO TABLE page_views
          FIELDS TERMINATED BY ' '
          LINES TERMINATED BY '\n'
          (@var1, @var2, @var3, @var4)
      SET domain_code = @var1,
          page_title = @var2,
          count_views = @var3,
          total_response_size = @var4;

エラー内容

ERROR 1062 (23000): Duplicate entry 'ace-Ôn_Keuë' for key 'page_views.PRIMARY'

• 『page_views』テーブルの PRIMARY キーにおいて、重複したエントリ「'ace-Ôn_Keuë'」が挿入されようとしてエラーになっているようです。

調査

以下はインポートしたいデータの一部を抜粋したものです

ace Ôn_Keue 10 0
ace Ôn_Keuë 2 0 
incubator.m Wt/ace/Ôn_Keue 1 0
incubator.m Wt/ace/Ôn_Keuë 1 0

エラー文とデータと一部より、以下のことがわかりました

• eとëが区別されていなさそう（Ôn_KeueとÔn_Keuë）。

対策

対策その１ - 文字セットと照合順序の指定を変更する

• デフォルトの照合を 『utf8mb4_0900_bin』 に変更します。

utf8mb4のデフォルトの照合はバージョンで異なります。
　・MySQL5.7: utf8mb4_general_ci
　・MySQL8.0: utf8mb4_0900_ai_ci
今回はバージョン8.0以降なので、デフォルトが 『utf8mb4_0900_ai_ci』 でした。

utf8mb4_0900_ai_ci では、大/小文字が明示的に区別されず、アクセントも区別されません。
【公式】照合の命名規則より

照合順序の変更方法（1~4のどれかを適宜使用）

1. サーバーレベルで変更

• MySQLサーバーのデフォルトの照合順序を指定(my.cnfファイル)

[mysqld]
collation-server=utf8mb4_0900_bin

2. データベースレベルで変更

CREATE DATABASE log_analysis COLLATE utf8mb4_0900_bin;

3. テーブルレベルで変更

CREATE TABLE page_views(
    domain_code VARCHAR(20)  NOT NULL,
    page_title  VARCHAR(300) NOT NULL,
    )COLLATE utf8mb4_0900_bin;

4. カラムレベルで変更

CREATE TABLE page_views(
    domain_code VARCHAR(20)  NOT NULL COLLATE utf8_unicode_ci,
    page_title  VARCHAR(300) NOT NULL COLLATE utf8_unicode_ci,
    );

対策その１－結果

• eとëが区別されたようですが、ほかの箇所でまたエラーが出ました。
• 今回は大文字と小文字が区別されていなさそうです。（ACDCとAcdc）

ERROR 1062 (23000): Duplicate entry 'en-ACDC' for key 'page_views.PRIMARY'

照合順序を『utf8mb4_0900_bin』に変更したので大文字と小文字も区別される予測だったのですが、結果はエラーが返ってきてしまいました。原因はまだ解明していません…。

対策その２ - LOAD DATAコマンドにIGNOREを追加する

LOAD DATA INFILE '/var/lib/mysql-files/page_views' IGNORE

IGNORE キーワードを使うとどうなる？
　→ ロード中に重複エラーが発生しても、処理を中断せず次の行に進みます。

対策その２－結果

• 成功した…？ データは無事インポートされました。ですが、１行スキップされているのが気になります。

Query OK, 5124895 rows affected, 5 warnings (37.35 sec)
Records: 5124896  Deleted: 0  Skipped: 1  Warnings: 5

• Warningsをみると、やはり『en-ACDC』が重複とみなされスキップされているようです。

SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1062 | Duplicate entry 'en-ACDC' for key 'page_views.PRIMARY' |
| Warning | 1048 | Column 'total_response_size' cannot be null            |
| Warning | 1048 | Column 'total_response_size' cannot be null            |
| Warning | 1048 | Column 'total_response_size' cannot be null            |
| Warning | 1048 | Column 'total_response_size' cannot be null            |
+---------+------+--------------------------------------------------------+

おわりに

• 対策その１とその２を実行してデータをインポートすることができました。Warningが出ていましたが、テーブルにはしっかりデータが反映されていたので今回はこれをもって解決としました。

以上、ここまで読んでくださりありがとうございました☺