mysql - 在整个表中搜索一个单词 - MySQL

Question

目前正在使用以下代码在整个表格中搜索单词，

    Dim searchKey As String = "Sample"
    Dim mysql As String = "SELECT * FROM myTable " & _
                          "WHERE col1 LIKE '%" & searchKey & "%' " & _
                          " OR col2 LIKE '%" & searchKey & "%' " & _
                          " OR col3 LIKE '%" & searchKey & "%' " & _
                          " OR col4 LIKE '%" & searchKey & "%' "

但是当列数增加时，查询变得更加困难。所以有人可以建议我一些替代方法来做同样的事情。

Answer 1

使用循环以编程方式构建字符串。在此示例中，您只需将列添加到数组中。

Dim searchKey As String = "Sample"
Dim array() As String = {"col1", "col2", "col3", "col4"}
Dim b As Boolean = True

Dim mysql As String = "SELECT * FROM myTable WHERE "

For Each str As String In array
    If b Then
        mysql = mysql & str + " LIKE '%" & searchKey & "%'"
    Else
        mysql = mysql & " OR " & str & " LIKE '%" & searchKey & "%'"
    End If
    b = False

Next

Debug.WriteLine(mysql)

输出>>

SELECT * FROM myTable WHERE col1 LIKE '%Sample%' OR col2 LIKE '%Sample%' OR col3 LIKE '%Sample%' OR col4 LIKE '%Sample%'