批量向collateralrpt表插入数据时出现MySqlException列数与值数不匹配报错

现有实现 #

你当前用于批量同步 collateral 表查询结果的VB代码如下：

repNo = MainForm.StaffMixname.Text.Substring(0, 3) & DateTime.Now.ToString("yyyyMMddhhmmss")
MetroGrid5.DataSource = Nothing
Dim ds As DataSet = New DataSet
Dim Query As String = "SELECT ci.seq_id, CONCAT(ci.lastname, ci.firstname) AS fullname, ci.amountApplied, ci.province, co.kind, co.specifications, co.regOwner, co.location FROM clientinformation ci LEFT JOIN collateraloffered co ON ci.seq_id=co.seq_id WHERE co.kind IS NOT NULL AND ci.province = '" & MetroComboBox8.Text & "' AND ci.seq_id BETWEEN '" & convertedstrFrom.ToString("yyMMdd") & "%' AND '" & convertedstrTo.ToString("yyMMdd") & "%'"
Dim fetch As New MySqlDataAdapter(Query, connect)
fetch.Fill(ds, "collateral")
MetroGrid5.DataSource = ds.Tables("collateral")
If MetroGrid5.Rows.Count > 0 Then
    Dim cm As New MySqlCommand

    With cm
        .Connection = connect
        For i As Integer = 0 To MetroGrid5.RowCount - 1
            .CommandText = _
                "INSERT INTO collateralrpt Values('" & repNo & _
                "', '" & MetroGrid5.Rows(i).Cells("seq_id").Value & _
                "', '" & MetroGrid5.Rows(i).Cells("fullname").Value & _
                "', '" & MetroGrid5.Rows(i).Cells("amountApplied").Value & _
                "', '" & MetroGrid5.Rows(i).Cells("kind").Value & _
                "', '" & MetroGrid5.Rows(i).Cells("specifications").Value & _
                "', '" & MetroGrid5.Rows(i).Cells("regOwner").Value & _
                "', '" & MetroGrid5.Rows(i).Cells("location").Value & _
                "', '" & MetroGrid5.Rows(i).Cells("province").Value & "')"
            .ExecuteNonQuery()
        Next
    End With
    cm.Dispose()
    cm = Nothing
    With connect
        .Close()
        .Dispose()
    End With
Else
    MsgBox("No Data!")
End If

运行后触发报错：MySqlException: Column count doesn't match value count at row 1

触发报错的核心原因 #

• 未指定插入列名，依赖全列顺序匹配
  代码中使用的INSERT INTO collateralrpt Values(...)语法，要求传入的 值 数量、顺序 必须和collateralrpt表定义的所有列完全一致。只要表的实际列数多于你传入的9个值，或者列顺序和预期不一致，就会直接触发列数不匹配的错误。
• 硬拼字符串导致的SQL语法截断
  目前代码是直接把DataGrid单元格的内容拼接进SQL语句，如果单元格值中包含单引号（比如人名中的撇号、地址中的特殊符号），会直接截断字符串边界，把后续拼接的内容识别成SQL语法片段，最终打乱整体传入值的数量，触发语法层面的列数不匹配报错。

修复建议 #

• 写插入语句时显式定义要插入的列，让传入值和列一一对应，不再依赖表的默认列顺序：

  INSERT INTO collateralrpt (报表号列, 业务主键列, 客户姓名列, 申请金额列, 抵押物类型列, 抵押物规格列, 登记权属人列, 抵押物位置列, 省份列)
  VALUES (...)
  

• 替换硬拼字符串的写法为参数化查询，提前定义SQL和参数占位符，循环中只给参数赋值，从根源上避免单引号截断、SQL注入的问题。
• 整批插入时绑定事务，保证多笔数据要么全部入库、要么全部回滚，避免中途异常产生脏数据。

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