监视MySQL表中的新数据 [英] Monitor New Data in MySQL Table

问题描述

我想知道表中是否有新行.如何在 VB.Net 中做到这一点?

I want to know if there's new row in my table. How can I do that in VB.Net ?

示例Insert Query已触发，则MessageBox将 提示告诉我表中插入了新数据.

Example An Insert Query has triggered then a MessageBox will prompt telling me that there's new data inserted in my table.

如果可能的话，有人可以解释并告诉我我该怎么做? (如果有代码:3 ，那会更好)

If its possible can someone explain and tell me how can I do it? (It would be better if there is code :3)

推荐答案

您的问题仍然不清楚.仅监视一种类型的更改-根据标题监视新数据"(INSERT)-比检测任何更改(按照注释中的I want to detect the Changes in my table更简单)要复杂得多.

Your question remains unclear. Monitoring only one type of change - "new data" (INSERT) as per the title - is more complicated than detecting any change (as per I want to detect the Changes in my table in comments which is simpler).

MySql提供了获取表校验和的方法:

MySql provides the means to get the checksum of a table:

checksum table TABLE_NAME [QUICK | EXTENDED]

InnoDB和MyISAM表的MySQL Workbench结果:

MySQL Workbench results for an InnoDB and MyISAM table:

通过监视这些返回值的更改，您可以检测到任何更改.但请注意:

By watching for changes in those return values, you can detect any change. But note:

• 该表必须已使用Checksum = 1选项创建
• QUICK选项在版本5.7.2(IIRC和当前社区版本为5.7.14)之前的InnoDB表上不起作用.

• The table must have been created with the Checksum = 1 option
• The QUICK option does not work on InnoDB tables prior to version 5.7.2 (IIRC and the current Community version is 5.7.14).

幸运的是，如果您未指定选项，MySQL似乎会选择最快的选项，它将返回一个值.因此，在计时器上按表跟踪更改变得很容易:

Luckily, if you do not specify an option, MySQL seems to pick the fastest one which will return a value. So, it becomes easy to track changes by table on a Timer:

' Track last checksum by table
Friend Class TableItem
    Public Property Name As String
    Public Property CheckSum As Int64

    Public Sub New(n As String)
        Name = n
        CheckSum = 0
    End Sub
End Class
' a list of them to track more than one table:
Private Tables As List(Of TableItem)

初始化:

Timer1.Enabled = True

Tables = New List(Of TableItem)
Tables.Add(New TableItem("Sample"))
Tables.Add(New TableItem("SampleISAM"))

计时器滴答事件:

' Note: cannot use Parameters for table or col names
Dim sql = "CHECKSUM TABLE {0} "

Using dbcon As New MySqlConnection(mySQLConnStr)
    dbcon.Open()
    Using cmd As New MySqlCommand(sql, dbcon)
        ' loop thru collection, polling one at a time
        For Each tbl As TableItem In Tables
            cmd.CommandText = String.Format(sql, tbl.Name)

            Using rdr As MySqlDataReader = cmd.ExecuteReader()
                If rdr.Read Then
                    Dim thisResult = rdr.GetInt64(1)

                    ' ignore the first result
                    If tbl.CheckSum = 0 Then
                        tbl.CheckSum = thisResult
                        Return
                    End If
                    ' save the last non-zed value
                    If tbl.CheckSum <> thisResult Then
                        tbl.CheckSum = thisResult
                        ' method to do something when changed:
                        TableChanged(tbl.Name)
                    End If

                End If
            End Using
        Next
    End Using
End Using

我的操作方法只是将更改报告到列表框:

My do something method us just reporting the changes to a listbox:

Private Sub TableChanged(tbl As String)
    lb.Items.Add(String.Format("Table {0} changed {1}", tbl,
                               DateTime.Now.ToString("HH:mm:ss.ffffff")))
End Sub

要实际上仅监视诸如INSERTS之类的内容，您需要使用某种日志表.添加一个触发器，该触发器将使用时间戳记和操作代码(插入"，删除")来更新该表.然后只需检查时间戳记是否有更改，也许可以过滤掉非监视动作.

To actually watch for something like only INSERTS, you'd need to use some sort of log table. Add a trigger which updates that table with a TimeStamp and maybe action code ("insert", "delete"). Then just check the TimeStamp for changes, perhaps filtering out non-watch actions.

特别是一个可以监视多个表或某些更改事件的版本，可以更好地用作类.计时器代码可以封装，并且可以引发表更改事件.

Particularly a version to watch multiple tables or certain change events will work better as a class. The timer code can be encapsulated and it could raise events for a table changes.

• MySQL v.5.6 13.7.2.3 CHECKSUM表语法

这篇关于监视MySQL表中的新数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！