mysql - 将创建时间，修改时间列添加到mysql中的现有表

Question

我有一张包含一些记录的表格。现在我想向其中添加“创建”和“更新”列。我正在使用这样的查询

ALTER TABLE employee ADD COLUMN updated TIMESTAMP NOT NULL DEFAULT 
    CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

但这是创建值全为零的列。我希望它是当前时间戳。

示例:

mysql> select * from employee;  
+----+------+  
| id | name |  
+----+------+  
|  1 | a    |  
|  2 | b    |  
|  3 | c    |  
+----+------+  
3 rows in set (0.00 sec)  

mysql> alter table employee add column updated timestamp not null default current_timestamp on update current_timestamp;  
Query OK, 3 rows affected (0.20 sec)  
Records: 3  Duplicates: 0  Warnings: 0  

mysql> select * from employee;  
+----+------+---------------------+  
| id | name | updated             |  
+----+------+---------------------+  
|  1 | a    | 0000-00-00 00:00:00 |  
|  2 | b    | 0000-00-00 00:00:00 |  
|  3 | c    | 0000-00-00 00:00:00 |  
+----+------+---------------------+  
3 rows in set (0.00 sec)  

mysql> update employee set name='aa' where id='1';  
Query OK, 1 row affected (0.05 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  

mysql> select * from employee;  
+----+------+---------------------+  
| id | name | updated             |  
+----+------+---------------------+  
|  1 | aa   | 2015-12-10 15:41:44 |  
|  2 | b    | 0000-00-00 00:00:00 |  
|  3 | c    | 0000-00-00 00:00:00 |  
+----+------+---------------------+  
3 rows in set (0.00 sec)  

mysql> alter table employee add column city varchar(10) not null default 'banglore';  
Query OK, 3 rows affected (0.17 sec)  
Records: 3  Duplicates: 0  Warnings: 0  

mysql> select * from employee;  
+----+------+---------------------+----------+  
| id | name | updated             | city     |  
+----+------+---------------------+----------+  
|  1 | aa   | 2015-12-10 15:41:44 | banglore |  
|  2 | b    | 0000-00-00 00:00:00 | banglore |  
|  3 | c    | 0000-00-00 00:00:00 | banglore |  
+----+------+---------------------+----------+  
3 rows in set (0.00 sec)

似乎 DEFAULT CURRENT_TIMESTAMP 不起作用

Answer 1

ALTER TABLE `employee` ADD `COLUMN` 
TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP