Mysql数据类型---TIMESTAMP

最新推荐文章于 2024-06-13 10:37:43 发布

risen16

最新推荐文章于 2024-06-13 10:37:43 发布

阅读量1w

点赞数 2

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/risen16/article/details/77875923

版权

mysql 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

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

risen16

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Mysql数据类型---TIMESTAMP

标签（空格分隔）： MYSQLTIMESTAMP 类型使用 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字段用于 INSE
复制链接

扫一扫