MySQL虚拟字段把日期转成yyyyMMdd的int类型数据

需求描述

表中有一个字段创建日期create_date(datetime)，需要再新建一个虚拟字段create_day(int)，显示创建的天，如今天：20210720，方面程序的调用和统计，create_day是和create_date关联的，不需要新增一个普通的字段，然后特别的录入，只要根据create_date自动计算出来就可以了。

表结构如下：

mysql> desc t_account;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| create_date | datetime     | YES  |     | NULL    |                |
| update_date | datetime     | YES  |     | NULL    |                |
| no          | varchar(128) | NO   | UNI | NULL    |                |
| password    | varchar(255) | YES  |     | NULL    |                |
| name        | varchar(255) | YES  |     | NULL    |                |
| mobile      | varchar(255) | YES  |     | NULL    |                |
| disabled    | int          | NO   |     | 0       |                |
+-------------+--------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

实现

我们利用MySQL的日期函数(year/month/dayofmonth)，提出出年月日，然后创建出虚拟字段就可以了

mysql> ALTER TABLE `jmgr`.`t_account` 
ADD COLUMN `create_day` int GENERATED ALWAYS AS ((((10000 * year(`create_date`)) + (100 * month(`create_date`))) + dayofmonth(`create_date`))) VIRTUAL NULL AFTER `create_date`;

mysql> desc t_account;
+-------------+--------------+------+-----+---------+-------------------+
| Field       | Type         | Null | Key | Default | Extra             |
+-------------+--------------+------+-----+---------+-------------------+
| id          | int          | NO   | PRI | NULL    | auto_increment    |
| create_date | datetime     | YES  |     | NULL    |                   |
| create_day  | int          | YES  |     | NULL    | VIRTUAL GENERATED |
| update_date | datetime     | YES  |     | NULL    |                   |
| no          | varchar(128) | NO   | UNI | NULL    |                   |
| password    | varchar(255) | YES  |     | NULL    |                   |
| name        | varchar(255) | YES  |     | NULL    |                   |
| mobile      | varchar(255) | YES  |     | NULL    |                   |
| disabled    | int          | NO   |     | 0       |                   |
+-------------+--------------+------+-----+---------+-------------------+
12 rows in set (0.01 sec)

现在多了一个create_day的虚拟字段，插入数据的时候，不用给create_day设定数据，select的时候，会自动根据create_date的日期计算出来了，很方便。

当然如果SQL中直接查也是可以的：

select id, create_date, 
((((10000 * year(create_date)) + (100 * month(create_date))) + dayofmonth(create_date))) as create_day,
name from t_account