Clever Geek Handbook

Find new lines in the database

I'm looking for a way to find out which rows in the database (mysql) are new (or updated), so that I can only fetch those of them and store them locally in the application (client).

My initial thought was to add a timestamp to each row and my application remembers the time of the last update, however I am concerned about changing the server clock (for example when switching from summer to winter) or the client updating during a transaction on the server - > example

Aside from temporary stamps (a pretty obvious and seemingly general idea), is there a recommended best practice for this kind of thing?

+2


source to share


5 answers


Other ideas:

  • for data that can only be pasted, the identifier is incremented (e.g. generated by a sequence or similar concept) - all you need to remember is the last identifier you copied
  • this can be extended by a column in each table that is updated from the sequence whenever it is inserted or updated (set by a trigger).
  • instead of a column for each table, it might be easier to have a central log table that collects information like this (tricky to define the releaser lines if you have compound keys)


Unless you have a compelling reason to work on such a scheme, I'll be very careful. This type of synchronization can face several tricky issues that can be harder to solve than they seem.

+2


source


Instead of timestamp, put an autoincrement column in your table and keep the largest value for that column. When you need to update, make your selection in auto_column> my_largest_value.



+1


source


Many databases use a timestamp to check for concurrency, so this would be the natural (and correct IMO) way to do it. Especially if the timestamp field is already being used to check for concurrency.

During DST changes, you need a way to invalidate the record cache on the client so that it reloads all records after the time change.

Or, as Daniel points out, just use UTC for timestamping and then you don't have to worry about time zone changes at all.

0


source


Take a look at the Timestamp column type - it automatically updates whenever you create or modify a record.

The time zone shouldn't be an issue depending on how you ask for it. It stores the value as UTC, which has no daylight saving time.

0


source


Since there is no built-in Change Data Capture in MySQL, you can implement INSERT and UPDATE and DELETE triggers to capture what you need in the audit table and retrieve data from there. The type of replication method is poor.

0


source






More articles:


All Articles