ON UPDATE CURRENT_TIMESTAMP定义没有显式时间戳的更新将导致当前时间戳值的更新。
您可以使用ALTER命令从列中删除ON UPDATE CURRENT_TIMESTAMP。
语法如下ALTER TABLE yourTableName
CHANGE yourTimeStampColumnName yourTimeStampColumnName timestamp NOT
NULL default CURRENT_TIMESTAMP;
为了理解上述语法,让我们创建一个表。创建表的查询如下mysql> create table removeOnUpdateCurrentTimeStampDemo
- > (
- > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
- > Name varchar(20),
- > UserUpdateTimestamp timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
- > );
使用DESC命令检查表的描述。
查询如下mysql> desc removeOnUpdateCurrentTimeStampDemo;
以下是输出+---------------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+-------------------+-----------------------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(20) | YES | | NULL | |
| UserUpdateTimestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------------+-------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.04 sec)
现在查看更新CURRENT_TIMESTAMP上的Extra字段。删除ON UPDATE CURRENT_TIMESTAMP的查询如下:mysql> alter table removeOnUpdateCurrentTimeStampDemo
- > change UserUpdateTimestamp UserUpdateTimestamp timestamp NOT NULL default CURRENT_TIMESTAMP;
Records: 0 Duplicates: 0 Warnings: 0
再次检查表的描述。
查询如下mysql> desc removeOnUpdateCurrentTimeStampDemo;
以下是输出+---------------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+-------------------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(20) | YES | | NULL | |
| UserUpdateTimestamp | timestamp | NO | | CURRENT_TIMESTAMP | |
+---------------------+-------------+------+-----+-------------------+----------------+
3 rows in set (0.00 sec)
如果要删除默认的CURRENT_TIMESTAMP,则查询如下mysql> alter table removeOnUpdateCurrentTimeStampDemo
- > change UserUpdateTimestamp UserUpdateTimestamp timestamp NOT NULL;
Records: 0 Duplicates: 0 Warnings: 0
再次检查表的描述。
查询如下mysql> desc removeOnUpdateCurrentTimeStampDemo;
以下是输出+---------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(20) | YES | | NULL | |
| UserUpdateTimestamp | timestamp | NO | | NULL | |
+---------------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
现在看上面的示例输出,我们删除了ON UPDATE CURRENT TIMESTAMP。