如果需要表示年月日时分秒,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)