How to set "On Update" for uuid()

Question

The syntax to define an update value for timestamp is the following:

CREATE TABLE Example1 (
    ...
    `RowVersion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ...
);

I can change the datatype from timestamp to GUID colum by:

`RowVersion` CHAR(36) NOT NULL DEFAULT 'uuid()'

But I can not write:

 `RowVersion` CHAR(36) NOT NULL DEFAULT 'uuid()' ON UPDATE 'uuid()',

How am I able to set a new uuid() when the row is updated without create a trigger?

It makes sense to update a TIMESTAMP - if you with to record the latest change for example, but why (on earth) would you want to update a UUID? Normally, they are only used for PRIMARY KEYs - otherwise they are just anonymous bits - they have no meaning outside of their randomness and therefore updating them makes no sense. There is no meaningful difference between one UUID and another. — Vérace, Apr 11 '16 at 12:31
It makes sense to replace the type Timestamp to GUID for many reasons (google for more info). Therefor I want to add the same logic to auto update the field value on row update. Exactly the same logic that timestamp allows, but apply it into guid. — Dryadwoods, Apr 11 '16 at 12:51
I didn't say that it didn't make sense to change TIMESTAMP to UUID/GUID - if you want some random value. But, AFAICS, most of stuff that I found appears to be about disks - and unless I'm mistaken, you get that from the disk itself, no? Not an expert here, but you wouldn't be just generating one to replace the old one - you'd be taking one from some disk, so you would be updating an old disk ID with a new one? Under what circumstances do you want to randomly change a UUID to another one (with the possible exception of tests on probability and randomness itself)? — Vérace, Apr 11 '16 at 13:03
@Vérace I appreciate your feedback and I thank you for trying to help. I want to use this field to add a "RowVersion" logic (concurrency). And TimeStamp is an old field and has many disadvantages and I simply want to use a guid instead. And my question I think is clear on just trying to figure out if I can avoid using a trigger just to update this field on row update. — Dryadwoods, Apr 11 '16 at 13:15

score 2 · Accepted Answer · edited Apr 13 '17 at 12:42

The specification really makes very little sense. Having a "rowversion" is fine and ok. But what makes sense to use for a column that is a "rowversion" is either a timestamp (so we can compare the 2 values in 2 rows to see which is earlier and which is later) or an incrementing (integer or not) value. GUIDs are not incrementing and makes zero sense to be used in this way.

Further, not even the first code sample will work. First because you would need to use the function uuid(), not the string 'uuid()':

`RowVersion` CHAR(36) NOT NULL DEFAULT uuid()

Even in that case though, it wouldn't work because MySQL does not allow anything but constants in the DEFAULT value for column (with one exception, see below). From the documentation, MySQL, CREATE TABLE Syntax:

The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP or DATETIME column. See ...

Check the answers in the questions: MySQL - Alter table to automatically put in a UUID and How can I get MySQL to use the value returned by a function as a default?, for some ways to put the UUID values in the column.

But in any case, this does not solve the "rowversion" problem. Whether you use triggers, procedures or modify your statements to update the UUID values, I don't see how those can be used as rowversions.

How to set "On Update" for uuid()

1 Answers1