Mysql替换功能问题

Question

我想用替换功能更新我的表，但结果不是我的预期结果，我该如何解决这个问题？

假设我有一个 LONGTEXT 列(using_id)，其值如下 1,2,3,4,5,6,7,8,9,10,11,

现在我想删除'1'，所以我这样写:-

 Dim query As String = "UPDATE curr_id SET using_id = REPLACE(using_id,'" & $"{g_currTicketID}," & "','')"

但是结果变成了2,3,4,5,6,7,8,9,10,1 我怎样才能保留我的'11,'？

预期结果:2,3,4,5,6,7,8,9,10,11,

Public Sub TicketStatusUpdate(p_str As String)
        'Login => GET NEXT ID > UPDATE USING ID > UPDATE NEXT ID
        'Sales => RELEASE > UPDATE USED ID > GET NEXT ID > UPDATE USING ID > UPDATE NEXT ID

        Select Case p_str
            Case "UPDATE USED ID"
                TicketStatusUpdate("RELEASE")

                Dim query As String = "UPDATE curr_id SET used_id = CONCAT(used_id, '" & $"{g_currTicketID}," & "')"
                Using cmd = New MySqlCommand(query, conn)
                    cmd.ExecuteNonQuery()
                End Using

                TicketStatusUpdate("GET NEXT ID")

            Case "GET NEXT ID"
                Dim query_select = "SELECT * FROM curr_id WHERE DATE(curr_date) = '" & Format(DateTime.Today.Date, "yyyy-MM-dd") & "' "
                Dim reader As MySqlDataReader
                Dim IsNewDay As Boolean = False

                Using cmd = New MySqlCommand(query_select, conn)
                    reader = cmd.ExecuteReader
                    reader.Read()
                    If reader.HasRows Then
                        g_currTicketID = reader.GetInt32("next_id")
                    Else
                        g_currTicketID = 1
                        IsNewDay = True
                    End If
                    reader.Close()
                End Using

                If IsNewDay Then TicketStatusUpdate("RESET")
                TicketStatusUpdate("UPDATE USING ID")
                TicketStatusUpdate("UPDATE NEXT ID")

            Case "UPDATE USING ID"
                Dim query As String = "UPDATE curr_id SET using_id = CONCAT(using_id, '" & $"{g_currTicketID}," & "')"
                Using cmd = New MySqlCommand(query, conn)
                    cmd.ExecuteNonQuery()
                End Using

            Case "UPDATE NEXT ID"
                Dim query1 As String = "SELECT * FROM curr_id"
                Dim str_usingID As String = ""
                Dim str_usedID As String = ""

                Dim reader As MySqlDataReader
                Using cmd = New MySqlCommand(query1, conn)
                    reader = cmd.ExecuteReader
                    reader.Read()
                    str_usingID = reader.GetString("using_id").ToString
                    str_usedID = reader.GetString("used_id").ToString
                    reader.Close()
                End Using

                Dim str_allID As String = (str_usingID + str_usedID).TrimEnd(",")
                Dim strArray As String() = str_allID.Split(",")
                Dim intArray As Integer() = Array.ConvertAll(strArray, Function(s) Int32.Parse(s))
                Array.Sort(Of Integer)(intArray)

                Dim nextID As Integer = FirstMissing(intArray)

                Dim query2 As String = "UPDATE curr_id SET next_id = '" & nextID & "'"
                Using cmd = New MySqlCommand(query2, conn)
                    cmd.ExecuteNonQuery()
                End Using

            Case "RELEASE"
                Dim query As String = "UPDATE curr_id SET using_id = REPLACE(using_id,'" & $"{g_currTicketID}," & "','')"
                Using cmd = New MySqlCommand(query, conn)
                    cmd.ExecuteNonQuery()
                End Using

            Case "RESET"
                Dim query As String = "UPDATE curr_id SET next_id='',used_id='',using_id=''"
                Using cmd = New MySqlCommand(query, conn)
                    cmd.ExecuteNonQuery()
                End Using
        End Select
    End Sub

 Private Function FirstMissing(sequence() As Integer) As Integer
        Dim seq = sequence
        Dim firstMissingNumer = Int32.MinValue

        For i = 1 To Math.Min(seq.Last, seq.Count)
            If seq(i - 1) <> i Then
                firstMissingNumer = i
                Exit For
            End If
        Next

        If firstMissingNumer = Int32.MinValue Then
            Return seq.Max + 1
        Else
            Return firstMissingNumer
        End If
    End Function

当我注销应用程序时，它将调用 TicketStatusUpdate("RELEASE") 以从 using_id 列中删除 g_currTicketID。

示例:

g_currTicketID = 1
using_id = 1,11,21,31,

当 TicketStatusUpdate("RELEASE") 调用时，它会从 using_id 中删除所有 '1,'，因此结果将变成 1,2,3 这不是我想要的结果，我只想删除 '1,'并保留“11,21,31”，

Answer 1

您可以尝试同时替换值和边界字符，而不是仅替换值(在您的情况下，逗号“，”是边界字符)。
示例:

如果 g_currTicketID = 1，using_id = 1,11,21,31，则

replaceValue = CONCAT(',',g_currTicketID,',') = ',1,'
stringToReplace = CONCAT(',',using_id,',') = ',1,11,21,31,'
using_id = TRIM(BOTH ',' FROM REPLACE(stringToReplace, replaceValue, ','))

你的更新语句可能是这样的

Dim query As String = "UPDATE curr_id SET using_id = TRIM(BOTH ',' FROM REPLACE(CONCAT(',' ,using_id, ','), '," & $"{g_currTicketID}," & "', ','))"