MySQLでINSERT時に重複をスキップする方法 – データ重複回避完全ガイド

データベース・SQL

この記事では、MySQLでINSERT文を実行する際に、既存データとの重複を避けてスキップする方法を詳しく解説します。

3つの主要な手法の特徴と使い分けから、実践的な活用例まで幅広くカバーしていきます。

スポンサーリンク

重複データ問題の背景

なぜ重複スキップが必要か

データベースへのデータ挿入では、以下のような場面で重複チェックが重要になります：

よくある重複発生シーン

  • バッチ処理での大量データ登録
  • 複数のシステムから同時データ投入
  • 外部システムからのデータ同期
  • ユーザーの重複送信による二重登録
  • データマイグレーション時の重複回避

重複による問題

  • アプリケーションエラーの発生
  • データ整合性の破綻
  • パフォーマンスの低下
  • 不正確な集計結果

MySQLの制約とエラー

一意制約違反時のエラー

-- 通常のINSERT文で重複が発生した場合
INSERT INTO users (id, email) VALUES (1, 'test@example.com');
-- Error: Duplicate entry '1' for key 'PRIMARY'

このようなエラーを回避するために、MySQLでは複数の解決手法が提供されています。

サンプルテーブルの準備

実際のコード例を理解するために、以下のサンプルテーブルを使用します：

-- ユーザーテーブルの作成
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(50) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 初期データの挿入
INSERT INTO users (id, email, name, age) VALUES
(1, 'tanaka@example.com', '田中太郎', 28),
(2, 'sato@example.com', '佐藤花子', 32),
(3, 'suzuki@example.com', '鈴木一郎', 45);

-- テーブル内容の確認
SELECT * FROM users;

初期データの状態

+----+--------------------+-----------+-----+---------------------+
| id | email              | name      | age | created_at          |
+----+--------------------+-----------+-----+---------------------+
|  1 | tanaka@example.com | 田中太郎  |  28 | 2024-01-15 10:30:00 |
|  2 | sato@example.com   | 佐藤花子  |  32 | 2024-01-15 10:30:01 |
|  3 | suzuki@example.com | 鈴木一郎  |  45 | 2024-01-15 10:30:02 |
+----+--------------------+-----------+-----+---------------------+

方法1：INSERT IGNORE文

基本構文と動作

基本構文

INSERT IGNORE INTO テーブル名 (列名1, 列名2, ...)
VALUES (値1, 値2, ...);

動作原理

  • 一意制約（PRIMARY KEY、UNIQUE）に違反する場合、そのレコードを無視
  • エラーを発生させずに処理を継続
  • 重複しないレコードのみが挿入される

実用例

単一レコードの挿入

-- 既存IDでの挿入試行（スキップされる）
INSERT IGNORE INTO users (id, email, name, age)
VALUES (1, 'new_tanaka@example.com', '新田中', 30);

-- 新しいIDでの挿入（成功する）
INSERT IGNORE INTO users (id, email, name, age)
VALUES (4, 'yamada@example.com', '山田次郎', 35);

-- 結果確認
SELECT * FROM users WHERE id IN (1, 4);

実行結果

+----+--------------------+-----------+-----+---------------------+
| id | email              | name      | age | created_at          |
+----+--------------------+-----------+-----+---------------------+
|  1 | tanaka@example.com | 田中太郎  |  28 | 2024-01-15 10:30:00 |
|  4 | yamada@example.com | 山田次郎  |  35 | 2024-01-15 10:35:00 |
+----+--------------------+-----------+-----+---------------------+

複数レコードの一括挿入

INSERT IGNORE INTO users (id, email, name, age) VALUES
(1, 'duplicate1@example.com', '重複1', 25),  -- スキップ（IDが重複）
(5, 'watanabe@example.com', '渡辺三郎', 40), -- 挿入成功
(2, 'duplicate2@example.com', '重複2', 27),  -- スキップ（IDが重複）
(6, 'ito@example.com', '伊藤四郎', 50);      -- 挿入成功

-- 影響を受けた行数の確認
-- Query OK, 2 rows affected, 2 warnings (0.01 sec)

重複チェックの詳細動作

-- EMAIL列での重複チェック
INSERT IGNORE INTO users (id, email, name, age)
VALUES (10, 'tanaka@example.com', '別の田中', 40);
-- スキップされる（emailが重複）

-- 複合的な重複チェック
INSERT IGNORE INTO users (id, email, name, age) VALUES
(7, 'unique1@example.com', 'ユニーク1', 30),
(8, 'sato@example.com', 'ユニーク2', 35),     -- emailで重複
(9, 'unique3@example.com', 'ユニーク3', 40);
-- 1番目と3番目のみ挿入される

メリットとデメリット

メリット

  • シンプルで理解しやすい構文
  • 高いパフォーマンス
  • バッチ処理に適している
  • エラーハンドリングが不要

デメリット

  • 重複理由の詳細がわからない
  • 部分的な挿入失敗が警告でしか確認できない
  • データの更新はできない

方法2：INSERT … ON DUPLICATE KEY UPDATE

基本構文と動作

基本構文

INSERT INTO テーブル名 (列名1, 列名2, ...)
VALUES (値1, 値2, ...)
ON DUPLICATE KEY UPDATE
    列名1 = VALUES(列名1),
    列名2 = VALUES(列名2);

動作原理

  • 重複がない場合：通常のINSERTを実行
  • 重複がある場合：指定されたUPDATE文を実行
  • 常に1つの結果（INSERT または UPDATE）が得られる

実用例

重複時に更新を行う場合

INSERT INTO users (id, email, name, age)
VALUES (1, 'updated_tanaka@example.com', '更新田中', 29)
ON DUPLICATE KEY UPDATE
    email = VALUES(email),
    name = VALUES(name),
    age = VALUES(age);

-- 結果確認
SELECT * FROM users WHERE id = 1;

実行結果

+----+---------------------------+-----------+-----+---------------------+
| id | email                     | name      | age | created_at          |
+----+---------------------------+-----------+-----+---------------------+
|  1 | updated_tanaka@example.com| 更新田中  |  29 | 2024-01-15 10:30:00 |
+----+---------------------------+-----------+-----+---------------------+

重複時にスキップする場合

-- 実質的なスキップ（何も更新しない）
INSERT INTO users (id, email, name, age)
VALUES (2, 'new_sato@example.com', '新佐藤', 33)
ON DUPLICATE KEY UPDATE id = id;
-- または
ON DUPLICATE KEY UPDATE id = VALUES(id);

-- 元のデータが維持される
SELECT * FROM users WHERE id = 2;

条件付き更新

INSERT INTO users (id, email, name, age)
VALUES (3, 'conditional@example.com', '条件付き', 50)
ON DUPLICATE KEY UPDATE
    email = CASE 
        WHEN age < VALUES(age) THEN VALUES(email)
        ELSE email 
    END,
    name = CASE 
        WHEN age < VALUES(age) THEN VALUES(name)
        ELSE name 
    END,
    age = GREATEST(age, VALUES(age));

高度な活用例

カウンターの更新

-- アクセスカウンターテーブル
CREATE TABLE page_views (
    page_id INT PRIMARY KEY,
    view_count INT DEFAULT 1,
    last_viewed TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ページビューの記録（重複時はカウント増加）
INSERT INTO page_views (page_id, view_count)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE 
    view_count = view_count + 1,
    last_viewed = CURRENT_TIMESTAMP;

在庫管理での活用

-- 在庫テーブル
CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    quantity INT DEFAULT 0,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 在庫の追加（既存の場合は加算）
INSERT INTO inventory (product_id, quantity)
VALUES (101, 50)
ON DUPLICATE KEY UPDATE 
    quantity = quantity + VALUES(quantity);

VALUES()関数の詳細

VALUES()関数の使用例

INSERT INTO users (id, email, name, age)
VALUES (10, 'values_test@example.com', 'VALUES関数テスト', 35)
ON DUPLICATE KEY UPDATE
    email = VALUES(email),           -- 新しい値
    name = CONCAT(name, ' (更新)'),   -- 既存値を変更
    age = GREATEST(age, VALUES(age)); -- 大きい方を選択

方法3：INSERT SELECT + NOT EXISTS

基本構文と動作

基本構文

INSERT INTO テーブル名 (列名1, 列名2, ...)
SELECT 値1, 値2, ...
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 
    FROM テーブル名 
    WHERE 条件
);

動作原理

  • サブクエリで重複チェックを実施
  • 重複がない場合のみINSERTを実行
  • より複雑な条件での重複判定が可能

実用例

単純な重複チェック

INSERT INTO users (id, email, name, age)
SELECT 11, 'not_exists@example.com', 'NOT EXISTS', 40
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE id = 11
);

複数条件での重複チェック

INSERT INTO users (id, email, name, age)
SELECT 12, 'complex@example.com', '複雑チェック', 45
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM users 
    WHERE email = 'complex@example.com' 
       OR (name = '複雑チェック' AND age = 45)
);

条件付きチェック

-- 年齢が30歳以上の同名ユーザーがいない場合のみ挿入
INSERT INTO users (id, email, name, age)
SELECT 13, 'conditional@example.com', '田中太郎', 25
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM users 
    WHERE name = '田中太郎' AND age >= 30
);

複雑な業務ロジックでの活用

関連テーブルとの整合性チェック

-- 部署テーブル
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    active BOOLEAN DEFAULT TRUE
);

-- 社員テーブル
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    dept_id INT,
    emp_name VARCHAR(50),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- アクティブな部署にのみ社員を追加
INSERT INTO employees (emp_id, dept_id, emp_name)
SELECT 1001, 10, '新入社員'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM employees WHERE emp_id = 1001
) AND EXISTS (
    SELECT 1 FROM departments 
    WHERE dept_id = 10 AND active = TRUE
);

REPLACE INTOとの比較

REPLACE INTO文の動作

基本構文

REPLACE INTO テーブル名 (列名1, 列名2, ...)
VALUES (値1, 値2, ...);

動作の違い

-- REPLACEの場合：削除→挿入
REPLACE INTO users (id, email, name, age)
VALUES (1, 'replace@example.com', 'REPLACE', 50);

-- ON DUPLICATE KEY UPDATEの場合：更新のみ
INSERT INTO users (id, email, name, age)
VALUES (1, 'update@example.com', 'UPDATE', 51)
ON DUPLICATE KEY UPDATE
    email = VALUES(email),
    name = VALUES(name),
    age = VALUES(age);

重要な違い

項目REPLACE INTOON DUPLICATE KEY UPDATE
動作DELETE → INSERTUPDATE
AUTO_INCREMENTリセットされる保持される
トリガーDELETE + INSERTUPDATE のみ
パフォーマンスやや低い高い

パフォーマンス比較と最適化

性能テストの実施

大量データでの性能比較

-- テスト用の大きなテーブル作成
CREATE TABLE performance_test (
    id INT PRIMARY KEY,
    data VARCHAR(100),
    value INT,
    INDEX idx_data (data)
);

-- 初期データの準備
INSERT INTO performance_test (id, data, value)
SELECT 
    n,
    CONCAT('data_', n),
    n * 10
FROM (
    SELECT a.N + b.N * 10 + c.N * 100 + 1 as n
    FROM 
    (SELECT 0 as N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a,
    (SELECT 0 as N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b,
    (SELECT 0 as N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
) numbers
WHERE n <= 1000;

インデックスの最適化

適切なインデックス設計

-- 重複チェック対象列にインデックス
CREATE INDEX idx_users_email ON users(email);

-- 複合インデックスの活用
CREATE INDEX idx_users_name_age ON users(name, age);

-- NOT EXISTS クエリの最適化
EXPLAIN SELECT * FROM users 
WHERE NOT EXISTS (
    SELECT 1 FROM users u2 
    WHERE u2.email = 'test@example.com'
);

エラーハンドリングと監視

ワーニングの確認

INSERT IGNORE のワーニング確認

INSERT IGNORE INTO users (id, email, name, age) VALUES
(1, 'warning_test@example.com', 'ワーニングテスト', 30);

-- ワーニング内容の確認
SHOW WARNINGS;

出力例

+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY'|
+---------+------+---------------------------------------+

影響行数の取得

ROW_COUNT()関数の活用

INSERT IGNORE INTO users (id, email, name, age)
VALUES (20, 'rowcount@example.com', 'RowCount', 35);

SELECT ROW_COUNT() as affected_rows;
-- 結果: 1 (成功) または 0 (スキップ)

詳細な結果の取得

-- プロシージャでの実装例
DELIMITER //

CREATE PROCEDURE insert_with_result(
    IN p_id INT,
    IN p_email VARCHAR(100),
    IN p_name VARCHAR(50),
    IN p_age INT,
    OUT p_result VARCHAR(20)
)
BEGIN
    DECLARE row_count INT;
    
    INSERT IGNORE INTO users (id, email, name, age)
    VALUES (p_id, p_email, p_name, p_age);
    
    SET row_count = ROW_COUNT();
    
    IF row_count = 1 THEN
        SET p_result = 'INSERTED';
    ELSE
        SET p_result = 'SKIPPED';
    END IF;
END //

DELIMITER ;

-- 使用例
CALL insert_with_result(21, 'procedure@example.com', 'プロシージャ', 40, @result);
SELECT @result;

実際の業務での活用パターン

データ移行での活用

他システムからのデータ移行

-- 移行元データの取得と重複回避挿入
INSERT IGNORE INTO users (id, email, name, age)
SELECT 
    old_id,
    old_email,
    old_name,
    old_age
FROM legacy_users
WHERE migration_status = 'pending';

-- 移行結果の確認
SELECT 
    COUNT(*) as total_legacy,
    (SELECT COUNT(*) FROM users) as current_users
FROM legacy_users;

API連携での重複回避

外部APIからのデータ取り込み

-- APIから取得したデータの安全な登録
INSERT INTO users (id, email, name, age)
VALUES (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
    name = CASE 
        WHEN last_updated < VALUES(last_updated) THEN VALUES(name)
        ELSE name 
    END,
    age = CASE 
        WHEN last_updated < VALUES(last_updated) THEN VALUES(age)
        ELSE age 
    END,
    last_updated = GREATEST(last_updated, VALUES(last_updated));

ログデータの重複排除

アクセスログの重複排除

CREATE TABLE access_logs (
    user_id INT,
    page_url VARCHAR(255),
    access_date DATE,
    access_count INT DEFAULT 1,
    PRIMARY KEY (user_id, page_url, access_date)
);

-- 同日同ページアクセスは集約
INSERT INTO access_logs (user_id, page_url, access_date, access_count)
VALUES (1, '/product/123', CURDATE(), 1)
ON DUPLICATE KEY UPDATE
    access_count = access_count + 1;

トランザクション制御との組み合わせ

安全なバッチ処理

START TRANSACTION;

-- 複数テーブルへの整合性を保った挿入
INSERT IGNORE INTO users (id, email, name, age)
VALUES (30, 'transaction@example.com', 'トランザクション', 35);

INSERT IGNORE INTO user_profiles (user_id, profile_data)
VALUES (30, 'プロフィールデータ');

-- 両方成功した場合のみコミット
COMMIT;

エラー時のロールバック

DELIMITER //

CREATE PROCEDURE safe_user_insert(
    IN p_id INT,
    IN p_email VARCHAR(100),
    IN p_name VARCHAR(50),
    IN p_age INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    INSERT INTO users (id, email, name, age)
    VALUES (p_id, p_email, p_name, p_age)
    ON DUPLICATE KEY UPDATE
        email = VALUES(email),
        name = VALUES(name),
        age = VALUES(age);
    
    -- 関連テーブルへの挿入
    INSERT INTO user_logs (user_id, action, timestamp)
    VALUES (p_id, 'USER_CREATED_OR_UPDATED', NOW());
    
    COMMIT;
END //

DELIMITER ;

よくある質問

どの方法が最も高速ですか？

パフォーマンス順序（一般的な場合）：

  1. INSERT IGNORE：最も高速
  2. ON DUPLICATE KEY UPDATE：中程度
  3. NOT EXISTS：条件によっては低速

ただし、テーブルサイズやインデックス設計により変動するため、実際の環境での測定が重要です。

複数の一意制約がある場合はどうなりますか？

INSERT IGNORE の場合：

-- PRIMARY KEY と UNIQUE制約の両方がある場合
INSERT IGNORE INTO users (id, email, name, age)
VALUES (1, 'existing@example.com', 'テスト', 30);
-- どちらかの制約に違反した場合、レコード全体がスキップ

ON DUPLICATE KEY UPDATE の場合： どの一意制約に違反したかに関わらず、UPDATE句が実行されます。

外部キー制約がある場合の注意点は？

制約違反時の動作：

-- 存在しない部署IDを指定した場合
INSERT IGNORE INTO employees (emp_id, dept_id, emp_name)
VALUES (1001, 999, '新入社員');  -- dept_id=999が存在しない
-- 外部キー制約違反により、このレコードもスキップされる

推奨対応： 事前に参照整合性をチェックするか、NOT EXISTS パターンで条件を明示的に記述する。

AUTO_INCREMENTカラムでの注意点は？

INSERT IGNORE の場合：

CREATE TABLE auto_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(50) UNIQUE
);

INSERT IGNORE INTO auto_test (data) VALUES
('データ1'),  -- id=1で挿入
('データ2'),  -- id=2で挿入  
('データ1'),  -- スキップされるが、id=3は消費される
('データ3');  -- id=4で挿入

-- 結果：id=1,2,4 が使用され、3は欠番になる

この動作を避けたい場合は、事前重複チェックを推奨します。

まとめ

MySQLでINSERT時の重複をスキップする方法は、用途に応じて適切に選択することが重要です。

重要なポイント

  • INSERT IGNORE：シンプルで高速、バッチ処理に最適
  • ON DUPLICATE KEY UPDATE：更新も含めた柔軟な制御が可能
  • NOT EXISTS：複雑な条件指定や他DB互換性重視時に使用

使い分けの指針

用途推奨方法理由
大量データの高速挿入INSERT IGNOREパフォーマンス重視
データの更新も含めたいON DUPLICATE KEY UPDATE柔軟性が高い
複雑な重複判定NOT EXISTS条件指定の自由度
他DBとの互換性重視NOT EXISTS標準SQL準拠
カウンター更新ON DUPLICATE KEY UPDATE加算処理に最適

ベストプラクティス

  1. インデックス設計：重複チェック対象列への適切なインデックス
  2. パフォーマンステスト：実際のデータ量での性能測定
  3. エラーハンドリング：ワーニングや影響行数の適切な監視
  4. トランザクション制御：整合性が重要な場合の適切な制御

Youtube

神話・歴史・伝承の解説チャンネル

スポンサーリンク

コメント

タイトルとURLをコピーしました