When creating a virtual column, if the virtual column is timestamp, the value of the virtual column does not change when the time zone changes, and the mysql version changes.
mariadb10.3：
|
create table test_timestamp (`create_timestamp` timestamp(3) NOT NULL COMMENT '记录创建时间',`create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` )) VIRTUAL COMMENT '记录创建时间');
|
|
insert into test_timestamp (`create_timestamp`) value (1569495326.861);
|
|
|
mysql> select * from test_timestamp;
|
+------------------+-------------------------+
|
| create_timestamp | create_time |
|
+------------------+-------------------------+
|
| 1569495326861 | 2019-09-26 10:55:26.861 |
|
+------------------+-------------------------+
|
1 row in set (0.01 sec)
|
|
mysql> set time_zone = "+01:00";
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> select * from test_timestamp;
|
+------------------+-------------------------+
|
| create_timestamp | create_time |
|
+------------------+-------------------------+
|
| 1569495326861 | 2019-09-26 10:55:26.861 |
|
+------------------+-------------------------+
|
1 row in set (0.00 sec)
|
|
mysql5.7：
mysql> create table test_timestamp (`create_timestamp` bigint(20) NOT NULL COMMENT '记录创建时间',
|
-> `create_time` timestamp(3) GENERATED ALWAYS AS (from_unixtime(`create_timestamp` / 1000)) VIRTUAL COMMENT '记录创建时间');
|
Query OK, 0 rows affected (0.31 sec)
|
|
mysql> insert into test_timestamp (`create_timestamp`) value (1569495326861);
|
Query OK, 1 row affected (0.03 sec)
|
|
mysql> select * from test_timestamp;
|
+------------------+-------------------------+
|
| create_timestamp | create_time |
|
+------------------+-------------------------+
|
| 1569495326861 | 2019-09-26 10:55:26.861 |
|
+------------------+-------------------------+
|
1 row in set (0.01 sec)
|
|
mysql> set time_zone = "+01:00";
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> select * from test_timestamp;
|
+------------------+-------------------------+
|
| create_timestamp | create_time |
|
+------------------+-------------------------+
|
| 1569495326861 | 2019-09-26 11:55:26.861 |
|
+------------------+-------------------------+
|
1 row in set (0.00 sec)
|
Bug
Critical
