Error [42000][1064] for generated virtual column-CodePudding

I try to update generated column with expression. It works in select

select from_unixtime(CONCAT(LEFT(jdoc->'$.ts', LENGTH(jdoc->'$.ts') - CHAR_LENGTH(jdoc->'$.sts')), jdoc->'$.sts'))  as calc_ts
from table_name

but it doesn work when i alter the table

alter table table_name
    alter column sts_date set default from_unixtime(
                CONCAT(LEFT(jdoc -> '$.ts', LENGTH(jdoc -> '$.ts') - CHAR_LENGTH(jdoc -> '$.sts')), jdoc -> '$.sts') /
                1000);

it returns me error:

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from_unixtime( CONCAT(LEFT(jdoc -> '$.ts', LENGTH(jdoc -> '$.ts'' at line 2.

what can be wrong?

CodePudding user response：

First, MySQL 5.7 doesn't support expressions for the DEFAULT clause. That feature is new as of MySQL 8.0.13. Read about it here: https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html If you are still using MySQL 5.7, you will have to upgrade to get this feature.

Second, expression defaults require an extra pair of parentheses.

Wrong:

DEFAULT expr

Right:

DEFAULT (expr)

Notice the syntax reference shows the parentheses:

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
                                                    ^    ^