Recording Record version ID in MySQL

It can be useful to record the update count of a row. This way you can know how many times a row has been updated. This is even more useful if you have a binlog cdc pipeline and are tracking the order of updates and need to ledger them in a history table (having purely timestamps doesn't indicate if you've missed an update in the middle - if you notice a gap then that means there was a missed record, however deletions are harder to track).

We use MariaDB System versioning to simulate the history table but you could also use maxwells daemon CDC or debezium to view binary log entries:

DROP TABLE notes;

CREATE TABLE notes (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    v_id int DEFAULT 1, -- INVISIBLE,
    body text,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB WITH SYSTEM VERSIONING;

CREATE TRIGGER before_v_id_notes BEFORE UPDATE ON notes
   FOR EACH ROW SET NEW.v_id = OLD.v_id + 1;

INSERT INTO notes SET body="Hello World!";
SELECT * FROM notes;

id, v_id, body, updated_at
1,1,Hello World!,2022-02-20 10:32:39

UPDATE notes SET body = "Hello World! My name is Anil" WHERE id=1;
SELECT * FROM notes;

id, v_id, body, updated_at
1,2,Hello World! My name is Anil,2022-02-20 10:33:09

Now we can view the full history table:

SELECT *, ROW_START, ROW_END FROM notes FOR SYSTEM_TIME ALL;

id, v_id, body, updated_at, ROW_START, ROW_END
1,1,Hello World!,                2022-02-20 10:32:39,2022-02-20 10:32:39.173108,2022-02-20 10:33:09.371880
1,2,Hello World! My name is Anil,2022-02-20 10:33:09,2022-02-20 10:33:09.371880,2038-01-19 03:14:07.999999

Generally I've found having an id auto increment, v_id, updated_at timestamps to be generally quite useful when you think about audit - in addition having an updated_by is generally useful as well.