Reputation: 4633
Row versioning for MySQL
Is there a built-in row-versioning mechanism for MySQL? Something similar to the 'timestamp' column in MS SqlServer.
Upvotes: 3
Views: 5606
Answers (3)
Reputation: 3988
A possible approach.
DROP TABLE IF EXISTS `versioned_categories`;
CREATE TABLE `versioned_categories` (
`id` INT NOT NULL AUTO_INCREMENT,
`version` INT NOT NULL DEFAULT 1,
`name` VARCHAR(45) NULL,
`createdAt` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`updatedAt` TIMESTAMP(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`id`));
DROP TRIGGER IF EXISTS `versioned_categories_BEFORE_UPDATE`;
DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER `versioned_categories_BEFORE_UPDATE` BEFORE UPDATE ON `versioned_categories` FOR EACH ROW
BEGIN
SET `NEW`.`version` = `NEW`.`version` + 1;
END$$
DELIMITER ;
TRUNCATE `versioned_categories`;
INSERT `versioned_categories`(`name`) VALUES('Richard'), ('Alan'), ('Quadling'), ('Hesper'), ('Alex'), ('Ruth'), ('Thomas'), ('Roy'), ('Oscar'), ('Leo'), ('Eshale'), ('Rose'), ('Venkuti'), ('Guzinski'), ('Guzinska');
BEGIN;
SElECT * FROM versioned_categories;
UPDATE `versioned_categories` SET `name` = replace(`name`,'a', 'A') WHERE `name` LIKE '%a%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'b', 'B') WHERE `name` LIKE '%b%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'c', 'C') WHERE `name` LIKE '%c%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'d', 'D') WHERE `name` LIKE '%d%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'e', 'E') WHERE `name` LIKE '%e%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'f', 'F') WHERE `name` LIKE '%f%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'g', 'G') WHERE `name` LIKE '%g%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'h', 'H') WHERE `name` LIKE '%h%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'i', 'I') WHERE `name` LIKE '%i%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'j', 'J') WHERE `name` LIKE '%j%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'k', 'K') WHERE `name` LIKE '%k%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'l', 'L') WHERE `name` LIKE '%l%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'m', 'M') WHERE `name` LIKE '%m%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'n', 'N') WHERE `name` LIKE '%n%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'o', 'O') WHERE `name` LIKE '%o%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'p', 'P') WHERE `name` LIKE '%p%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'q', 'Q') WHERE `name` LIKE '%q%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'r', 'R') WHERE `name` LIKE '%r%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'s', 'S') WHERE `name` LIKE '%s%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'t', 'T') WHERE `name` LIKE '%t%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'u', 'U') WHERE `name` LIKE '%u%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'v', 'V') WHERE `name` LIKE '%v%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'w', 'W') WHERE `name` LIKE '%w%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'x', 'X') WHERE `name` LIKE '%x%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'y', 'Y') WHERE `name` LIKE '%y%';
UPDATE `versioned_categories` SET `name` = replace(`name`,'z', 'Z') WHERE `name` LIKE '%z%';
SElECT * FROM `versioned_categories` ORDER BY `updatedAt`;
ROLLBACK;
The data starts out as:
| id | version | name | createdAt | updatedAt |
|---|---|---|---|---|
| 1 | 1 | Richard | 2024-12-03 18:27:12.017477 | |
| 2 | 1 | Alan | 2024-12-03 18:27:12.017477 | |
| 3 | 1 | Quadling | 2024-12-03 18:27:12.017477 | |
| 4 | 1 | Hesper | 2024-12-03 18:27:12.017477 | |
| 5 | 1 | Alex | 2024-12-03 18:27:12.017477 | |
| 6 | 1 | Ruth | 2024-12-03 18:27:12.017477 | |
| 7 | 1 | Thomas | 2024-12-03 18:27:12.017477 | |
| 8 | 1 | Roy | 2024-12-03 18:27:12.017477 | |
| 9 | 1 | Oscar | 2024-12-03 18:27:12.017477 | |
| 10 | 1 | Leo | 2024-12-03 18:27:12.017477 | |
| 11 | 1 | Eshale | 2024-12-03 18:27:12.017477 | |
| 12 | 1 | Rose | 2024-12-03 18:27:12.017477 | |
| 13 | 1 | Venkuti | 2024-12-03 18:27:12.017477 | |
| 14 | 1 | Guzinski | 2024-12-03 18:27:12.017477 | |
| 15 | 1 | Guzinska | 2024-12-03 18:27:12.017477 |
Once the updates are run:
| id | version | name | createdAt | updatedAt |
|---|---|---|---|---|
| 2 | 4 | ALAN | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.044488 |
| 10 | 4 | LEO | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.045378 |
| 1 | 7 | RICHARD | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.048258 |
| 4 | 6 | HESPER | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.054314 |
| 7 | 7 | THOMAS | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.054314 |
| 9 | 6 | OSCAR | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.054314 |
| 11 | 6 | ESHALE | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.054314 |
| 12 | 5 | ROSE | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.054314 |
| 3 | 9 | QUADLING | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.059017 |
| 6 | 5 | RUTH | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.059017 |
| 13 | 8 | VENKUTI | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.059017 |
| 5 | 5 | ALEX | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.063461 |
| 8 | 4 | ROY | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.064758 |
| 14 | 8 | GUZINSKI | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.065816 |
| 15 | 9 | GUZINSKA | 2024-12-03 18:27:12.017477 | 2024-12-03 18:27:12.065816 |
An issue I found though was case sensitivity.
If you reduce the UPDATES to just the A:
| id | version | name | createdAt | updatedAt |
|---|---|---|---|---|
| 4 | 1 | Hesper | 2024-12-03 18:33:18.338680 | |
| 5 | 2 | Alex | 2024-12-03 18:33:18.338680 | |
| 6 | 1 | Ruth | 2024-12-03 18:33:18.338680 | |
| 8 | 1 | Roy | 2024-12-03 18:33:18.338680 | |
| 10 | 1 | Leo | 2024-12-03 18:33:18.338680 | |
| 12 | 1 | Rose | 2024-12-03 18:33:18.338680 | |
| 13 | 1 | Venkuti | 2024-12-03 18:33:18.338680 | |
| 14 | 1 | Guzinski | 2024-12-03 18:33:18.338680 | |
| 1 | 2 | RichArd | 2024-12-03 18:33:18.338680 | 2024-12-03 18:33:18.361273 |
| 2 | 2 | AlAn | 2024-12-03 18:33:18.338680 | 2024-12-03 18:33:18.361273 |
| 3 | 2 | QuAdling | 2024-12-03 18:33:18.338680 | 2024-12-03 18:33:18.361273 |
| 7 | 2 | ThomAs | 2024-12-03 18:33:18.338680 | 2024-12-03 18:33:18.361273 |
| 9 | 2 | OscAr | 2024-12-03 18:33:18.338680 | 2024-12-03 18:33:18.361273 |
| 11 | 2 | EshAle | 2024-12-03 18:33:18.338680 | 2024-12-03 18:33:18.361273 |
| 15 | 2 | GuzinskA | 2024-12-03 18:33:18.338680 | 2024-12-03 18:33:18.361273 |
You'll notice Alex has an increment in version, BUT not actually changed.
The logic in the trigger needs to decide what constitutes a change.
The trigger gets called because the row matches the criteria for a change (a case insensitive string contains), but in terms of actual changes, nothing actually changed.
This is going to be specific to your requirement.
But, conceptually, a timestamp to record when (you'll notice they all have the same timestamp, so if you did, for purely arguments sake, update millions of rows, they would all have the same updatedAt) and a version number.
Upvotes: 0
Reputation: 3988
Mysql does not have a built in rowversioning mechanism. Whilst using a timestamp type may seem OK, you are going to fall foul of this for queries that update multiple rows simultaneously and take longer then the resolution of the timestamp as the value assigned is based upon the start of the change and not the end of the change.
So, if your update query affects 100 rows all rows will have the same timestamp (say a value of 2015-10-28 09:47:10.123456). But, it is possible that the rows will not be finished writing until 2015-10-28 09:47:10.654321.
If, separately, you are looking for all changed rows based upon some other timestamp, (say 2015-10-28 09:47:10.500000), you are not going to get the results you want. And depending upon your code, you may miss the 100 rows changed.
It is the fact that changes to rows now may have a timestamp in the past that you cannot simply compare 2 timestamps to get all changed rows.
Upvotes: 3
Reputation: 11382
If you add a "timestamp" field, it will update it automatically whenever you update the row - not exactly versioning though, but sounds like it might be what you are after.
Upvotes: 1
Related Questions
- How to handle versioning in sql with single query
- Versioning rows in MySQL
- Does MySQL have an equivalent of SQL Server rowversion?
- DB record versioning
- Getting mysql row version number?
- How to implement row-wise versioning using mysql + java?
- Row versioning in MySQL
- Basic version control for MySQL table
- versioning of a table
- Version Tracking with mysql