Generated MySQL Columns and Changing Values

I was speaking at PHP[Tek] this week on the JSON Data Type and using generated columns. JSON columns can not be indexed but data from a JSON column can be extracted via a generated column and that column can be indexed. All was going well until someone asked me about modifying data in a generated column. Was it possible?

I blinked hard. I have not tried that! I had not seen any mention of that in the documentation. So I had to admit that I did not know and would have to try that.

The Test

mysql> CREATE TABLE gentest (a INT, b INT AS (a + 1) STORED, INDEX(b));
...
mysql> INSERT INTO gentest VALUES (1),(2),(3),(4);
...

So now we have a table with data to test. So lets try to modify the value of one of the generated columns.

mysql> UPDATE gentest SET b = 9 WHERE a = 1;

And what did the server do?

It returned an error and told me The value specified for generated column 'b' in table 'gentest' is not allowed.

Lesson Learned

So now I know that the server will not allow you to mangle, er, change the value of generated columns away from their definition. And yes, I find the same thing with both the VIRTUAL and STORED versions of generated columns.