Mysql数据类型---TIMESTAMP

来源：互联网发布：mysql union or in 编辑：程序博客网时间：2024/06/09 16:08

标签（空格分隔）： 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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)

阅读全文

0 0