MySQL5.7关于generated column使用

一、业务场景

由于公司业务数据涉及的数据存在跨时区的场景，相关埋点日志均使用unixtime时间戳来作为日志的打点时间。统计层面则使用日期为单位统计分析数据。

遇到过的问题：业务前期数据量级不大，查询时使用mysql function from_unixtime来格式化时间戳。当数据量级达到一定程度后，频繁使用function会导致mysql server机器的CPU使用率急剧上升。

二、Generated column介绍

Generated column定义语法：

col_name data_type [GENERATED ALWAYS] AS (expr)  [VIRTUAL | STORED] [NOT NULL | NULL][UNIQUE [KEY]] [[PRIMARY] KEY][COMMENT 'string']

简单来讲，generated column主要分为以下两种类型：virtual 、stored。

1. virtual：虚拟映射的字段，通过expr表达式得到结果，实际并不存储该字段数据。（下篇文章中介绍此处用法）
2. stored：真实存在的字段，物理表中实际存在该字段数据（由expr计算而来）。

三、实例介绍(stored)

• 定义DB & Table结构：

# 创建测试DB
CREATE DATABASE `generated_test` /*!40100 DEFAULT CHARACTER SET utf8 */# 创建测试Table
CREATE TABLE `GENERATED_COLUMN_TEST` (`first_rectime` int(11) DEFAULT NULL COMMENT '第一次时间戳',`second_rectime` int(11) DEFAULT NULL COMMENT '第二次时间戳',`first_day` date GENERATED ALWAYS AS (date_format(from_unixtime(`first_rectime`),'%Y-%m-%d')) STORED COMMENT 'first_rectime对应的日期',`second_day` date GENERATED ALWAYS AS (date_format(from_unixtime(`second_rectime`),'%Y-%m-%d')) STORED COMMENT 'second_rectime对应的日期',`day_diff` int(11) GENERATED ALWAYS AS ((to_days(`first_day`) - to_days(`second_day`))) STORED COMMENT 'diff天数',`time_day_diff` int(11) GENERATED ALWAYS AS (((`first_rectime` - `second_rectime`) / 86400)) STORED COMMENT 'diff天数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='测试表';

• 向表中写入数据：

(root@192.168.1.1:3306) [generated_test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-04-04 09:48:14 |
+---------------------+
1 row in set (0.00 sec)(root@192.168.1.1:3306) [generated_test]> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1680572894 |
+-----------------------+
1 row in set (0.00 sec)(root@192.168.1.1:3306) [generated_test]> select unix_timestamp(now()) - 3 * 86400;
+-----------------------------------+
| unix_timestamp(now()) - 3 * 86400 |
+-----------------------------------+
|                        1680313704 |
+-----------------------------------+
1 row in set (0.00 sec)(root@192.168.1.1:3306) [generated_test]> insert into GENERATED_COLUMN_TEST(first_rectime,second_rectime) values(1680313704, 1680572894);
Query OK, 1 row affected (0.00 sec)(root@192.168.1.1:3306) [generated_test]> select * from GENERATED_COLUMN_TEST;
+---------------+----------------+------------+------------+----------+---------------+
| first_rectime | second_rectime | first_day  | second_day | day_diff | time_day_diff |
+---------------+----------------+------------+------------+----------+---------------+
|    1680313704 |     1680572894 | 2023-04-01 | 2023-04-04 |       -3 |            -3 |
+---------------+----------------+------------+------------+----------+---------------+
1 rows in set (0.00 sec)(root@192.168.1.1:3306) [generated_test]> update GENERATED_COLUMN_TEST set second_rectime = 1680780217 where first_rectime = 1680313704;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0(root@192.168.1.1:3306) [generated_test]> select * from GENERATED_COLUMN_TEST;
+---------------+----------------+------------+------------+----------+---------------+
| first_rectime | second_rectime | first_day  | second_day | day_diff | time_day_diff |
+---------------+----------------+------------+------------+----------+---------------+
|    1680313704 |     1680780217 | 2023-04-01 | 2023-04-06 |       -5 |            -5 |
+---------------+----------------+------------+------------+----------+---------------+
1 row in set (0.00 sec)

通过使用generated column [stored]比较优雅的解决了时间戳 -> 日期的转换问题, 而且能够同步变更到expr表达式中涉及到的字段数据。

四、业务迭代遇到的问题

• TiDB中关于generated column字段使用问题：

# 相同SQL在TiDB中创建并使用上述SQL测试：
(root@192.168.1.1:4000) [generated_test]> select * from GENERATED_COLUMN_TEST;
+---------------+----------------+------------+------------+----------+---------------+
| first_rectime | second_rectime | first_day  | second_day | day_diff | time_day_diff |
+---------------+----------------+------------+------------+----------+---------------+
|    1680313704 |     1680780217 | 2023-04-01 | 2023-04-06 |       -3 |            -5 |
+---------------+----------------+------------+------------+----------+---------------+
1 row in set (0.00 sec)

结论：在TiDB中generated column嵌套使用会出现数据内容无法同步更新的问题。