update自更新字段添加新字段是否会更新旧数据
\
验证：一张具备自更新字段的表，如果新增一个字段，原表数据的update_time字段是否会更新？
\
1、创建一张自更新字段的表
\
CREATE TABLE bohaitest(
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
saler_id MEDIUMINT(11) NOT NULL DEFAULT '0' COMMENT 'ID',
assigned_cust_num INT(10) NOT NULL DEFAULT '0' COMMENT '已分配会员数',
assign_type TINYINT(3) NOT NULL DEFAULT '0' COMMENT '分配类型',
flight_no varchar(15) NOT NULL DEFAULT '' COMMENT '航',
add_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
mark TINYINT(3) NOT NULL DEFAULT '0' COMMENT '删除标志(0:有效;1:删除)',
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='测试表';
\
insert into bohaitest (saler_id) values (100);
insert into bohaitest (saler_id) values (200);
insert into bohaitest (saler_id) values (300);
\
mysql> select * from bohaitest;
+----+----------+-------------------+-------------+-----------+---------------------+---------------------+------+
| id | saler_id | assigned_cust_num | assign_type | flight_no | add_time | update_time | mark |
+----+----------+-------------------+-------------+-----------+---------------------+---------------------+------+
| 1 | 100 | 0 | 0 | | 2022-04-28 17:55:12 | 2022-04-28 17:55:12 | 0 |
| 2 | 200 | 0 | 0 | | 2022-04-28 17:55:43 | 2022-04-28 17:55:43 | 0 |
| 3 | 300 | 0 | 0 | | 2022-04-28 17:55:44 | 2022-04-28 17:55:44 | 0 |
+----+----------+-------------------+-------------+-----------+---------------------+---------------------+------+
\
2、对该表新增字段。
ALTER TABLE bohaitest
ADD COLUMN ent_wei_flag TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' AFTER flight_no;
\
mysql> select * from bohaitest;
+----+----------+-------------------+-------------+-----------+--------------+---------------------+---------------------+------+
| id | saler_id | assigned_cust_num | assign_type | flight_no | ent_wei_flag | add_time | update_time | mark |
+----+----------+-------------------+-------------+-----------+--------------+---------------------+---------------------+------+
| 1 | 100 | 0 | 0 | | 0 | 2022-04-28 17:55:12 | 2022-04-28 17:55:12 | 0 |
| 2 | 200 | 0 | 0 | | 0 | 2022-04-28 17:55:43 | 2022-04-28 17:55:43 | 0 |
| 3 | 300 | 0 | 0 | | 0 | 2022-04-28 17:55:44 | 2022-04-28 17:55:44 | 0 |
+----+----------+-------------------+-------------+-----------+--------------+---------------------+---------------------+------+
3 rows in set (0.00 sec)
注意：历史update_time字段值并没有更新。
3.更新某条记录，对应update_time字段值随即更新
mysql> update bohaitest set ent_wei_flag = 1 where saler_id=100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
\
mysql> select * from bohaitest;
+----+----------+-------------------+-------------+-----------+--------------+---------------------+---------------------+------+
| id | saler_id | assigned_cust_num | assign_type | flight_no | ent_wei_flag | add_time | update_time | mark |
+----+----------+-------------------+-------------+-----------+--------------+---------------------+---------------------+------+
| 1 | 100 | 0 | 0 | | 1 | 2022-04-28 17:55:12 | 2022-04-28 18:01:18 | 0 |
| 2 | 200 | 0 | 0 | | 0 | 2022-04-28 17:55:43 | 2022-04-28 17:55:43 | 0 |
| 3 | 300 | 0 | 0 | | 0 | 2022-04-28 17:55:44 | 2022-04-28 17:55:44 | 0 |
。
验证：一张具备自更新字段的表，如果新增一个字段，原表数据的update_time字段是否会更新？
答案是不会更新。
\
\
\
\
\
\
\
\
\
\
