mysql - 避免从 excel 用户表单将空值更新到 mysql 的最佳方法是什么？

Question

我正在使用用户表单来更新 mysql 中的表，如果用户尝试使用空值更新或更新相同的值，我需要避免更新，我想在代码中进行验证，但它会根据表的大小(目前几乎 60 列)是一个长代码，这是我会尝试的，有什么更好的解决方案吗？

Sub UserForm_Initialize()

    'Connection code to get data from table...

    TextBox1OldValue = rs.Fields("Column1")
    TextBox2OldValue = rs.Fields("Column2")
    ...
End Sub

Sub MySQLTest_Click()

   'Connection code....

    sqlQuery = "UPDATE table SET "

    If TextBox1.Value <> "" And TextBox1.Value <> TextBox1OldValue Then
        sqlQuery = sqlQuery & "Column1='" & TextBox1.Value
    End If

    If TextBox2.Value <> "" And TextBox2.Value <> TextBox2OldValue Then
        sqlQuery = sqlQuery & "', Column2='" & TextBox2.Value
    End If
    ...
End Sub

Answer 1

最好的方法是始终将相同的语句发送到 MySQL 数据库，并且仅根据用户输入更改参数值。
这样做的好处是，文本框中的单引号不会使查询崩溃:这被称为 SQL 注入(inject)漏洞，并且没有任何手动解决方法可以修补它。参数化语句并让服务器以一种比任何本地客户端解决方案可能实现的方式更安全的方式处理参数值。然后服务器缓存并重用下一次调用的执行计划，因为它每次都接收相同的语句。
所以关键是要采取“这个值改变了吗？”逻辑服务器端......在结构化查询语言中 - 不是 Visual Basic。我们可以使用 ifnull WHERE 中的 MySQL 函数子句将条件逻辑从 VBA 和 EXCEL.EXE 主机进程中移出...并移到服务器上:

'each ? represents an ordinal parameter
Dim sql As String
sql = "UPDATE table AS a " & _
      "SET a.Column1 = ?, " & _
      "    a.Column2 = ? " & _
      "WHERE a.Column1 <> ifnull(?, a.Column1) " & _
      "  OR a.Column2 <> ifnull(?, a.Column2);"

我们不会去替换这些 ?带有用户输入的问号。相反，我们将创建一个 Command , 附加 Parameter反对命令的 Parameters集合，然后 Execute命令。
我们可以内联完成所有这些，但将其移至自己的过程范围感觉恰到好处。像这样的东西:

'parameters are Variant because other types cannot hold a NULL value.
Private Sub UpdateThing(ByVal conn As ADODB.Connection, ByVal Value1 As Variant, ByVal Value2 As Variant, ByVal Value3 As Variant)

    'local const ensures the string is a compile-time constant:
    '(stops compiling if we attempt to concatenate user inputs)
    Const sql As String = _
        "UPDATE table AS a " & _
        "SET a.Column1 = ?, " & _
        "    a.Column2 = ? " & _
        "WHERE a.Column1 <> ifnull(?, a.Column1) " & _
        "  OR a.Column2 <> ifnull(?, a.Column2);"

    'create a new command
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    
    'wire it up
    Set cmd.ActiveConnection = conn
    cmd.CommandType = adCommandText
    cmd.CommandText = sql
    
    'add the parameter values in the order they must appear in the query.
    cmd.Parameters.Append cmd.CreateParameter(Type:=..., Value:=Value1)
    cmd.Parameters.Append cmd.CreateParameter(Type:=..., Value:=Value2)
    cmd.Parameters.Append cmd.CreateParameter(Type:=..., Value:=Value3)
    
    'run with it
    cmd.Execute
    'Note: Command.Execute method returns a Recordset object. Useful for parameterized SELECT :)

End Sub

你会想要 ...对于 Type参数为 ADODB 数据类型，适用于它所针对的相应服务器端列值，您需要 ...对于 Value参数可能是 Variant局部变量或参数，以便您可以有条件地使其 Null基于给定的文本框是空的还是包含一个值。
现在客户端决定是传递文本框的内容，还是传递 null。值(value)。
更好的解决方案是将 SQL 字符串文字从代码中移出并作为存储过程放到 MySQL 服务器上 - ADODB.Command需要一些小调整，因为一个 VBA 过程最多可以有 40 个参数，对于 60 个参数，你需要一个 ParamArray那里。
所以调用站点可能看起来像..

UpdateThing conn, IIf(TextBox1.Value = "", Null, TextBox1.Value), IIf(TextBox2.Value = "", Null, TextBox2.Value), IIf(TextBox3.Value = "", Null, TextBox3.Value), _
                  IIf(TextBox4.Value = "", Null, TextBox4.Value), IIf(TextBox5.Value = "", Null, TextBox5.Value), IIf(TextBox6.Value = "", Null, TextBox6.Value), ...

现在是那些的 60 倍......我想要一个函数，所以我只需要指定 TextBoxN.Value一次，你明白了。请记住，VBA 停止编译跨越 20 多行物理代码的逻辑代码行，因此每行至少需要 3 个参数值。