CODEWITHSUNDEEP
mysqlgenerated-columns
MRtecno98
MRtecno98

Reputation: 86

Can i use local variables in a generated column expression in MySQL?

I want to create a generated column in MySQL as follows:

ALTER TABLE example ADD uuid uuid AS
        (UNHEX(INSERT(
            INSERT(@hash:=MD5(username),
                   13, 2, HEX(CONV(SUBSTR(@hash, 13, 2), 16, 10) & 0x0f | 0x30)),
            17, 2, HEX(CONV(SUBSTR(@hash, 17, 2), 16, 10) & 0x3f | 0x80))))

This computation only depends on the username field and is deterministic, yet MySQL won't allow it as a generated column because it uses local variables. I don't want to compute the hash three times because its a waste of resources so are there any workarounds around this and if not could someone explain the reason for this limitation?

Upvotes: 0

Views: 63

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562871

MariaDB allows variables in a generated column expression (this question was initially tagged both mysql and mariadb, though the text of the post mentions only mysql).

MariaDB and MySQL are different products. MariaDB forked from MySQL in 2010, so features introduced since then are sometimes implemented in different ways. You shouldn't assume MariaDB and MySQL are compatible, or even close variations of the same implementation.

MySQL does not allow variables in a generated column expression.

Nor can you use user-defined functions in a generated column expression (I don't know about MariaDB on this detail; I encourage you to check the documentation).

The reason is that the value of a variable (or definition of a function) could change from one invocation of the expression to the next. So the expression is not deterministic.

The example you show, that assigns the variable within the expression, might override any previous definition of the variable, but that doesn't matter. The general case is that expressions involving variables could be non-deterministic, and it's difficult to ensure that the expression is safe. So MySQL implements generated columns to disallow variables.

A workaround is to write a trigger to calculate your expression instead of a generated column. You can use a greater variety of code in the trigger, including using local variables.

Upvotes: 0

Related Questions