标签（空格分隔）： MYSQL
TIMESTAMP 类型使用 4 个字节，范围是 ‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’ 。
MySQL以’YYYY-MM-DD HH:MM:SS’格式显示TIMESTAMP值，显示宽度固定为19个字符。如果想要获得数字值，应在TIMESTAMP列添加+0。
TIMESTAMP字段用于 INSERT 或 UPDATE 操作时记录日期和时间。如果你不分配一个值，表中的第一个TIMESTAMP字段自动设置为最近操作的日期和时间。也可以通过分配一个NULL值，将 TIMESTAMP 列设置为当前的日期和时间。
TIMESTAMP 字段定义
你可以使用当前的时间戳去初始化值和自动更新，或者是其中之一，也可以都不是。比如，你在定义的时候可以指定自动更新，但并不初始化。
1. 如果定义时 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 子句都有，列值为默认使用当前的时间戳，并且自动更新
CREATE TABLE tb1(
id INTEGER,
t1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
mysql> DESC tb1;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| t1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)
mysql> INSERT INTO tb1(id) values(1);
Query OK, 1 row affected (0.10 sec)
mysql> select * from tb1;
+------+---------------------+
| id | t1 |
+------+---------------------+
| 1 | 2017-08-30 12:07:00 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> UPDATE tb1 SET id=2 WHERE id=1;
Query OK, 1 row affected (0.20 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb1;
+------+---------------------+
| id | t1 |
+------+---------------------+
| 2 | 2017-08-30 12:07:55 |
+------+---------------------+
1 row in set (0.00 sec)2. 如果不使用 DEFAULT 或 ON UPDATE 子句，那么它等同于 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
CREATE TABLE tb2(
id INTEGER,
t1 TIMESTAMP
);
mysql> DESC tb2;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| t1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)3 如果只有 DEFAULT CURRENT_TIMESTAMP 子句，而没有 ON UPDATE 子句，列值默认为当前时间戳但不自动更新
CREATE TABLE tb3(
id INTEGER,
t1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
mysql> DESC tb3;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-------+
| id | int(11) | YES | | NULL | |
| t1 | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-----------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)4. 如果没有 DEFAULT 子句，但是有 ON UPDATE CURRENT_TIMESTAMP 子句， 字段将自动更新，列默认为 NULL。
timestamp 字段默认不能为 NULL，必须明确指定该字段接受 NULL，否则会报错
mysql> CREATE TABLE tb4(
-> id INTEGER,
-> t1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> );
ERROR 1067 (42000): Invalid default value for 't1'mysql> CREATE TABLE tb4(
-> id INTEGER,
-> t1 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected (0.23 sec)
mysql> DESC tb4;
+-------+-----------+------+-----+---------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-----------------------------+
| id | int(11) | YES | | NULL | |
| t1 | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+---------+-----------------------------+
2 rows in set (0.00 sec)
mysql> INSERT tb4(id) values(1);
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM tb4;
+------+------+
| id | t1 |
+------+------+
| 1 | NULL |
+------+------+
1 row in set (0.00 sec)
mysql> UPDATE tb4 SET id=2 WHERE id=1;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM tb4;
+------+---------------------+
| id | t1 |
+------+---------------------+
| 2 | 2017-08-30 12:21:13 |
+------+---------------------+
1 row in set (0.00 sec)5 设置一个常量为默认值
如果有一个常量 DEFAULT，该列会有一个默认值，而且不会自动初始化为当前时间戳。如果该列还有一个 ON UPDATE CURRENT_TIMESTAMP 子句，这个时间戳会自动更新，否则该列有一个默认的常量但不会自动更新。
常量可以为 null,或者 1970-01-01 00:00:00 到 2037-12-31 23:59:59 之间的一个值，其他值都会报错。
设置默认值为 0，报错
mysql> CREATE TABLE tb5(
-> id INTEGER,
-> t1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
-> );
ERROR 1067 (42000): Invalid default value for 't1'设置默认值为 0000-00-00 00:00:00，报错
mysql> CREATE TABLE tb5(
-> id INTEGER,
-> t1 TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP
-> );
ERROR 1067 (42000): Invalid default value for 't1'设置默认值为 1970-01-02 00:00:00，正确
mysql> CREATE TABLE tb5(
-> id INTEGER,
-> t1 TIMESTAMP NULL DEFAULT '1970-01-02 00:00:00' ON UPDATE CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected (0.34 sec)
mysql> DESC tb5;
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| t1 | timestamp | YES | | 1970-01-02 00:00:00 | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+---------------------+-----------------------------+
2 rows in set (0.00 sec)设置默认值为 NULL，必须明确指定该字段接受 NULL,否则报错。
mysql> CREATE TABLE tb5_2(
-> id INTEGER,
-> t1 TIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
-> );
ERROR 1067 (42000): Invalid default value for 't1'
mysql> CREATE TABLE tb5_2(
-> id INTEGER,
-> t1 TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected (0.36 sec)
mysql> DESC tb5_2
-> ;
+-------+-----------+------+-----+---------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-----------------------------+
| id | int(11) | YES | | NULL | |
| t1 | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+---------+-----------------------------+
2 rows in set (0.00 sec)
mysql> DESC tb5_2;
+-------+-----------+------+-----+---------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-----------------------------+
| id | int(11) | YES | | NULL | |
| t1 | timestamp | YES | | NULL | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+---------+-----------------------------+
2 rows in set (0.00 sec)应用示例
mysql> ALTER TABLE news MODIFY `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (1.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc news;
+------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(50) | NO | | NULL | |
| thumb | varchar(250) | YES | | NULL | |
| content | text | YES | | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)mysql> ALTER TABLE `news` MODIFY `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc news;
+------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(50) | NO | | NULL | |
| thumb | varchar(250) | YES | | NULL | |
| content | text | YES | | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | |
| updated_at | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
