MySQL处理重复数据插入的处理方案_Mysql

1. 引言

在数据库操作中，处理重复数据插入是一个常见的需求。特别是在批量插入数据时，可能会遇到主键冲突或唯一键冲突（duplicate entry）的情况。如何优雅地捕获这类异常并执行相应的业务逻辑，是提高代码健壮性的关键。

本文将以一个实际的python mysql数据库操作为例，分析如何优化异常处理逻辑，使得当出现duplicate entry错误时，能够执行特定的业务方法（如更新记录状态）。同时，我们也会对比java中的类似处理方式，帮助读者更好地理解不同语言下的异常处理机制。

2. 问题背景

2.1 原始代码分析

原始代码的功能是批量插入手机号数据到mysql数据库，其核心逻辑如下：

def insert_into_mysql(phone_numbers, prefix, province, city):
    try:
        connection = get_db_connection()
        cursor = connection.cursor()
        
        data_to_insert = []
        for phone_number in phone_numbers:
            if len(phone_number) == 11:
                suffix = phone_number[-4:]
                data_to_insert.append((prefix, suffix, phone_number, province, city))
        
        cursor.executemany(insert_query, data_to_insert)
        connection.commit()
        return true
    
    except exception as e:
        print(f"插入数据失败: {e}")
        if connection:
            connection.rollback()
        return false
    
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()

2.2 存在的问题

异常处理不够精细：仅打印错误信息并回滚，没有针对特定错误（如duplicate entry）进行特殊处理。
业务逻辑耦合度低：当数据重复时，可能需要执行额外操作（如更新记录状态），但原始代码没有提供这样的扩展点。

3. 优化方案

3.1 目标

捕获特定异常：当错误信息包含duplicate entry时，执行额外逻辑（如调用update_is_deal方法）。
保持代码健壮性：确保事务回滚和资源释放不受影响。

3.2 优化后的python代码

def insert_into_mysql(phone_numbers, prefix, province, city, url=none):
    connection = none
    cursor = none
    try:
        connection = get_db_connection()
        if not connection:
            print("数据库连接失败")
            return false

        cursor = connection.cursor()
        data_to_insert = []
        
        for phone_number in phone_numbers:
            if len(phone_number) == 11:
                suffix = phone_number[-4:]
                data_to_insert.append((prefix, suffix, phone_number, province, city))

        if not data_to_insert:
            print("警告: 没有有效的手机号可插入")
            return false

        cursor.executemany(insert_query, data_to_insert)
        connection.commit()
        print(f"成功插入 {len(data_to_insert)} 条数据")
        return true

    except exception as e:
        print(f"插入数据失败: {e}")
        if connection:
            connection.rollback()
        
        # 检查是否是唯一键冲突
        if "duplicate entry" in str(e):
            if url:  # 确保url有效
                update_is_deal(url, province, city)  # 执行额外逻辑
        
        return false
    
    finally:
        if cursor:
            cursor.close()
        if connection:
            connection.close()

3.3 关键优化点

精细化异常捕获：通过检查异常信息是否包含"duplicate entry"，判断是否为唯一键冲突。
支持额外参数：新增url参数，确保update_is_deal方法可以正确执行。
事务安全：即使执行额外逻辑，仍然保证事务回滚和资源释放。

4. java对比实现

在java中，mysql的duplicate entry错误通常对应sqlintegrityconstraintviolationexception，我们可以采用类似的优化策略。

4.1 java版本优化代码

import java.sql.*;
import java.util.list;

public class phonenumberdao {
    private static final string insert_query = 
        "insert into phone_numbers (prefix, suffix, phone_number, province, city) " +
        "values (?, ?, ?, ?, ?)";

    public boolean insertintomysql(list<string> phonenumbers, string prefix, 
                                  string province, string city, string url) {
        connection connection = null;
        preparedstatement statement = null;
        
        try {
            connection = databaseutil.getconnection(); // 获取数据库连接
            connection.setautocommit(false); // 开启事务
            
            statement = connection.preparestatement(insert_query);
            
            for (string phonenumber : phonenumbers) {
                if (phonenumber.length() == 11) {
                    string suffix = phonenumber.substring(7); // 后4位
                    statement.setstring(1, prefix);
                    statement.setstring(2, suffix);
                    statement.setstring(3, phonenumber);
                    statement.setstring(4, province);
                    statement.setstring(5, city);
                    statement.addbatch(); // 加入批处理
                }
            }
            
            statement.executebatch(); // 执行批处理
            connection.commit(); // 提交事务
            return true;
            
        } catch (sqlintegrityconstraintviolationexception e) {
            // 捕获唯一键冲突异常
            system.err.println("插入数据失败（唯一键冲突）: " + e.getmessage());
            if (connection != null) {
                try {
                    connection.rollback(); // 回滚事务
                } catch (sqlexception ex) {
                    ex.printstacktrace();
                }
            }
            
            if (url != null) {
                updateisdeal(url, province, city); // 执行额外逻辑
            }
            return false;
            
        } catch (sqlexception e) {
            system.err.println("插入数据失败: " + e.getmessage());
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (sqlexception ex) {
                    ex.printstacktrace();
                }
            }
            return false;
            
        } finally {
            // 关闭资源
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (sqlexception e) {
                e.printstacktrace();
            }
        }
    }

    private void updateisdeal(string url, string province, string city) {
        // 实现更新逻辑
        system.out.println("检测到重复数据，更新状态: " + url);
    }
}

4.2 java优化点

精准捕获sqlintegrityconstraintviolationexception，而不是笼统的sqlexception。
批处理优化：使用addbatch()和executebatch()提高插入效率。
事务管理：显式控制commit()和rollback()，确保数据一致性。

5. 总结与最佳实践

5.1 关键总结

优化点	python 实现	java 实现
异常捕获	检查`str(e)`是否包含`"duplicate entry"`	捕获`sqlintegrityconstraintviolationexception`
事务管理	`connection.rollback()`	`connection.rollback()`
资源释放	`finally`块关闭连接	`finally`块关闭资源
批处理优化	`cursor.executemany()`	`addbatch()` + `executebatch()`

5.2 最佳实践

精细化异常处理：不要仅捕获exception，而应根据业务需求区分不同错误类型。
事务安全：确保异常发生时能正确回滚，避免脏数据。
资源释放：使用try-finally或try-with-resources（java）确保数据库连接关闭。
日志记录：在异常处理时记录足够的信息，便于排查问题。

6. 扩展思考

是否应该先查询再插入？
- 如果数据量较大，先查询再插入可能影响性能，直接捕获duplicate entry更高效。
如何优化update_is_deal逻辑？
- 可以引入异步处理（如消息队列），避免影响主流程性能。
是否可以用insert ignore或on duplicate key update？
- 取决于业务需求，如果需要静默忽略重复数据，可以使用这些sql语法。