垦荒学园
  • 中文

      • MySQL处理重复数据

      概述

      重复数据可能导致数据完整性问题和性能问题。MySQL提供了多种方法来查找、预防和处理表中的重复记录。

      重复类型

      完全重复：所有列相同 部分重复：部分列相同 主键重复：重复的主键 唯一键重复：重复的唯一约束 业务重复：逻辑重复（同一人多次）

      预防重复

      主键约束

      -- Prevent exact row duplicates

-- Insert attempt with duplicate primary key fails
-- Error: Duplicate entry for key 'PRIMARY'

      唯一约束

      -- Prevent duplicate values in specific columns

-- Insert duplicate email fails
-- Error: Duplicate entry for key 'email'

      复合唯一约束

      -- Prevent combination duplicates

-- Insert duplicate user_id + email_type fails
-- Error: Duplicate entry for key 'user_id'

      查找重复

      GROUP BY方法

      -- Find duplicates by specific column

-- Find all columns that are duplicates

      子查询方法

      -- Find duplicate records

-- Show one instance per duplicate

      自连接方法

      -- Find duplicate records using self-join

-- Count duplicates per value

      删除重复

      使用自连接删除

      -- Keep the lowest ID for each duplicate

-- Check deleted count

      使用子查询删除

      -- Delete duplicates, keep first occurrence

-- Delete duplicates, keep highest ID

      使用临时表删除

      -- Create temporary table with unique records

-- Truncate original table

-- Insert unique records

      INSERT IGNORE / INSERT IGNORE

      跳过重复错误

      -- Ignore duplicate errors

-- Check affected rows

      REPLACE INTO / REPLACE INTO

      替换现有记录

      -- Replace if duplicate key exists

-- Equivalent to DELETE + INSERT
-- If id=1 exists, delete it, then insert new record

      ON DUPLICATE KEY UPDATE / ON DUPLICATE KEY UPDATE

      重复时更新

      -- Update if duplicate key exists

-- Insert with multiple values

      高级ON DUPLICATE KEY UPDATE

      -- Increment counter on duplicate

-- Conditional update

      实用示例

      示例1：清理联系人列表

      -- Find duplicate emails

-- Remove duplicates, keep most recent

      示例2：合并重复记录

      -- Merge duplicate users

-- Create table for merged records

-- Insert merged records

-- Delete old duplicates

      示例3：导入数据

      -- Import with duplicate handling

-- Import unique records

-- Or update existing records

      示例4：每日去重

      -- Create deduplicated view

-- Use view instead of table

      重复预防策略

      数据库设计

      -- Add unique constraints

-- Add composite unique for business logic

      应用程序逻辑

      -- Check before insert
-- Step 1: Check exists

-- Step 2: Insert if not exists

      预防的触发器

      -- Prevent duplicate inserts

      监控重复

      重复检测查询

      -- Find duplicates across all tables

      定期重复检查

      -- Create procedure to check duplicates
    

-- Run regularly

      最佳实践

      选择去重方法

      -- Use INSERT IGNORE for:
-- - Simple insert operations
-- - Want to skip duplicates silently

-- Use REPLACE for:
-- - Complete row replacement
-- - Need to delete old record completely

-- Use ON DUPLICATE KEY UPDATE for:
-- - Update existing records
-- - Merge data
-- - Keep history with update timestamp

      性能考虑

      -- Large deduplication operations
-- Use temporary tables for better performance

-- Add indexes to temporary table

-- Delete in batches

      小结

      在MySQL中处理重复涉及：

      预防：主键、唯一约束 检测：GROUP BY、自连接、子查询 删除：带连接的DELETE、临时表 插入处理：INSERT IGNORE、REPLACE、ON DUPLICATE KEY UPDATE 监控：定期检查数据质量

      根据数据完整性需求和性能需求选择适当的方法。

      上一个：序列

      下一个：SQL注入