关于MySQL的timestamp类型

╰+哭是因爲堅強的太久メ 2022-02-16 12:08 228阅读 0赞

如果需要表示年月日时分秒，datetime和timestamp都是可以选择的数据类型。但是，  
1.从存储空间上，datetime占8 bytes，timestamp占4bytes；  
2.从取值范围上，datetime:1000-01-01 00:00:00至9999-12-31 23:59:59，timestamp: 1970-01-01 00:00:01.000000至2038-01-19 03:14:07.999999。

对于timestamp，有以下需要注意的地方：  
1.第一个timestamp列会默认加上`DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`属性，

mysql> create table test (col1 timestamp,col2 datetime);
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `col1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `col2` datetime DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)

2.如果需要将timestamp内容显示为数据，将timestamp列+0即可，datetime类型同理，

mysql> select col1 from test;
    +---------------------+
    | col1                |
    +---------------------+
    | 2019-04-22 13:46:56 |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> select col1+0 from test;
    +----------------+
    | col1+0         |
    +----------------+
    | 20190422134656 |
    +----------------+
    1 row in set (0.00 sec)

3.如果timestamp列超过有效范围，在不是严格模式下，会有warnings，并将值转换为零值，

mysql> set sql_mode='';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> insert into test(col1) values ('9999-12-31 12:00:00');
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> show warnings;
    +---------+------+-----------------------------------------------+
    | Level   | Code | Message                                       |
    +---------+------+-----------------------------------------------+
    | Warning | 1264 | Out of range value for column 'col1' at row 1 |
    +---------+------+-----------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select col1 from test;
    +---------------------+
    | col1                |
    +---------------------+
    | 2019-04-22 13:47:54 |
    | 0000-00-00 00:00:00 |
    +---------------------+
    2 rows in set (0.00 sec)

4.在5.7中，增加第二个timestamp列时，默认值为0值，同样可以将默认值设置为CURRENT\_TIMESTAMP，

mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `col1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `col2` datetime DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> alter table test add col3 timestamp;
    Query OK, 0 rows affected (0.05 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `col1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `col2` datetime DEFAULT NULL,
      `col3` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> alter table test modify col3 timestamp default CURRENT_TIMESTAMP;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> show create table test\G
    *************************** 1. row ***************************
           Table: test
    Create Table: CREATE TABLE `test` (
      `col1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      `col2` datetime DEFAULT NULL,
      `col3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)

5.timestamp与时区相关，当插入日期时，会先转换为本地时区后存放，从数据库取出时，也会将日期转换本地时区后显示。

mysql> select col1 from test;
    +---------------------+
    | col1                |
    +---------------------+
    | 2019-04-22 13:47:54 |
    | 0000-00-00 00:00:00 |
    +---------------------+
    2 rows in set (0.00 sec)
    
    mysql> show variables like 'time_zone';
    +---------------+--------+
    | Variable_name | Value  |
    +---------------+--------+
    | time_zone     | SYSTEM |
    +---------------+--------+
    1 row in set (0.00 sec)
    
    mysql> set time_zone='+9:00';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select col1 from test;
    +---------------------+
    | col1                |
    +---------------------+
    | 2019-04-22 14:47:54 |
    | 0000-00-00 00:00:00 |
    +---------------------+
    2 rows in set (0.00 sec)