使用VBA将Excel数据导入MySQL时主键重复报错问题求解

方案1：主键为自增字段时，省略id字段插入 #

首先确认voitures表的id字段已设置为AUTO_INCREMENT，插入语句直接去掉id字段即可，数据库会自动生成不重复的主键，不会触发重复报错。如果去掉id后仍报错，说明表内除id外还有其他唯一索引（如品牌+型号的联合唯一键），重复数据命中了其他唯一约束，可使用下面两种方案。

方案2：重复数据直接跳过，仅新增不存在的记录 #

INSERT IGNORE语法改动极小，仅比原有写法多一个关键词，遇到主键/唯一键重复时会自动跳过当前行，不会触发报错：

requete = "INSERT IGNORE INTO voitures(id,marque, modele, cv) VALUES(" & _
          TrouveTypeSql(a0) & "," & _
          TrouveTypeSql(a1) & "," & _
          TrouveTypeSql(a2) & "," & _
          TrouveTypeSql(a3) & ")"

方案3：重复数据用Excel新值覆盖数据库旧值 #

使用MySQL的ON DUPLICATE KEY UPDATE语法，命中主键/唯一键重复时，自动更新对应字段的值，不会触发报错：

requete = "INSERT INTO voitures(id,marque, modele, cv) VALUES(" & _
          TrouveTypeSql(a0) & "," & _
          TrouveTypeSql(a1) & "," & _
          TrouveTypeSql(a2) & "," & _
          TrouveTypeSql(a3) & ")" & _
          " ON DUPLICATE KEY UPDATE marque=VALUES(marque), modele=VALUES(modele), cv=VALUES(cv)"

代码优化建议 #

原有代码每次循环都打开关闭数据库连接，效率极低，建议把连接打开、关闭逻辑移到循环外，优化后的完整代码如下：

Sub test2()
    Dim I As Integer
    Dim a0 As String, a1 As String, a2 As String, a3 As String
    Const Server = "LocalHost", Port = "3306", User = "root", Password = ""
    Dim DataBase As String: DataBase = "vbamysql"
    
    With CreateObject("ADODB.Connection")
        ' 全局仅打开一次连接
        .Open "Driver={MySQL ODBC 8.0 Unicode Driver};Server=" & Server & ";Port=" & Port & ";Database=" & DataBase & ";User=" & User & ";Password=" & Password & ";"
        
        For I = 1 To 10
            a0 = ThisWorkbook.Sheets("traitement").Cells(I, 1).Value
            a1 = ThisWorkbook.Sheets("traitement").Cells(I, 2).Value
            a2 = ThisWorkbook.Sheets("traitement").Cells(I, 3).Value
            a3 = ThisWorkbook.Sheets("traitement").Cells(I, 4).Value
            
            ' 按需选择上述三种方案的SQL语句即可
            requete = "INSERT INTO voitures(id,marque, modele, cv) VALUES(" & _
                      TrouveTypeSql(a0) & "," & _
                      TrouveTypeSql(a1) & "," & _
                      TrouveTypeSql(a2) & "," & _
                      TrouveTypeSql(a3) & ")" & _
                      " ON DUPLICATE KEY UPDATE marque=VALUES(marque), modele=VALUES(modele), cv=VALUES(cv)"
            .Execute requete
        Next
        
        ' 所有插入完成后再关闭连接
        .Close
    End With
End Sub

Function TrouveTypeSql(V)
    TrouveTypeSql = Trim("" & V)
    If Trim("" & TrouveTypeSql) = "" Then TrouveTypeSql = "Null": Exit Function
    If IsDate(TrouveTypeSql) = True And InStr(TrouveTypeSql, "/") <> 0 And InStr(TrouveTypeSql, ":") <> 0 Then TrouveTypeSql = "'" & Format(TrouveTypeSql, "yyyy-mm-dd hh:mm") & "'": Exit Function
    If IsDate(TrouveTypeSql) = True And InStr(TrouveTypeSql, "/") <> 0 Then TrouveTypeSql = "'" & Format(TrouveTypeSql, "yyyy-mm-dd") & "'": Exit Function
    If IsNumeric(Replace(TrouveTypeSql, ".", ",")) = True Then TrouveTypeSql = Replace(TrouveTypeSql, ",", "."): Exit Function
    TrouveTypeSql = "'" & Replace(TrouveTypeSql, "'", "''") & "'"
End Function

内容的提问来源于stack exchange，提问作者Laventure Estivene