update自更新字段添加新字段是否会更新旧数据
验证：一张具备自更新字段的表，如果新增一个字段，原表数据的update_time字段是否会更新？
1、创建一张自更新字段的表
代码语言：javascript复制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字段值随即更新
代码语言：javascript复制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字段是否会更新？
答案是不会更新。
