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?

Answer 1

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.