How to define @@ system_time_zone as the default value for a column of an existing table in MySQL?

Question

I am trying to define @@ system_time_zone as the default value for the column that I am trying to add to an existing table. eg.

ALTER TABLE T1 ADD COLUMN TIME_ZONE CHAR(64) NOT NULL DEFAULT @@system_time_zone; 

This causes a MySQL syntax error. I tried to include @@ system_time_zone in single quotes. I now divide this into two, as follows:

 ALTER TABLE T1 ADD COLUMN TIME_ZONE CHAR(64) NOT NULL; UPTATE T1 SET TIME_ZONE=@ @system_time_zone; 

This works, but obviously this is not an ideal solution. I tried searching Google / StackOverFlow, but to no avail. Can someone tell me if there is a proper syntax that can achieve this / any other alternative with which this can be achieved?

Answer 1

This is a limitation in MySQL:

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression

But @@ and @ are expressions that are used to get values ​​for system or local variables. And they are not permanent. They can be changed at runtime.

Your approach is great, but if you really want to use a system variable, you can use dynamic SQL, for example:

 set @q = concat('alter table t1 add column time_zone char(64) not null default ', quote(@@system_time_zone)); prepare stmt from @q; execute stmt; deallocate prepare stmt; 

In @lanzz's tip: In the last code block, @@system_time_zone becomes a constant instead of constant variables. And if the time zone is changed in the future, the default value for this column will remain unchanged.