data,time,datatime

mysql> create table t_time (d date,t time,dt datetime);

Query OK, 0 rows affected (0.21 sec)

mysql> desc t_time

    -> ;

+-------+----------+------+-----+---------+-------+

| Field | Type     | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| d     | date     | YES  |     | NULL    |       |

| t     | time     | YES  |     | NULL    |       |

| dt    | datetime | YES  |     | NULL    |       |

+-------+----------+------+-----+---------+-------+

3 rows in set (0.01 sec)

mysql> insert into t_time values(now(),now(),now());

Query OK, 1 row affected, 1 warning (0.06 sec)

mysql> commit;

Query OK, 0 rows affected (0.06 sec)

mysql> select * from t_time;

+------------+----------+---------------------+

| d          | t        | dt                  |

+------------+----------+---------------------+

| 2020-08-07 | 13:35:05 | 2020-08-07 13:35:05 |

+------------+----------+---------------------+

1 row in set (0.02 sec)

timestamp

当参数explicit_defaults_for_timestamp为1或者off时，所建的表中字段类型为timestamp类型时，当插入为null时 ，系统会字段给该字段添加当前时间到timstamp字段，如果update该字段就跟他新成要插入的数据。

mysql> show variables like 'explicit%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| explicit_defaults_for_timestamp | OFF   |

+---------------------------------+-------+

1 row in set (0.02 sec)

mysql> insert into t_tim values (null);

Query OK, 1 row affected (0.01 sec)

mysql> commit;

Query OK, 0 rows affected (0.07 sec)

mysql> create table t_tim (id1 timestamp);

Query OK, 0 rows affected (0.65 sec)

mysql> insert into t_tim values (null);

Query OK, 1 row affected (0.01 sec)

mysql> commit;

Query OK, 0 rows affected (0.05 sec)

mysql> select * from t_tim;

+---------------------+

| id1                 |

+---------------------+

| 2020-08-07 13:53:43 |

+---------------------+

1 row in set (0.00 sec)

mysql> desc t_tim;

+-------+-----------+------+-----+-------------------+-----------------------------+

| Field | Type      | Null | Key | Default           | Extra                       |

+-------+-----------+------+-----+-------------------+-----------------------------+

| id1   | timestamp | NO   |     | C URRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+-------+-----------+------+-----+-------------------+-----------------------------+

mysql> show create table t_tim

    -> \G;

*************************** 1. row ***************************

       Table: t_tim

Create Table: CREATE TABLE `t_tim` (

  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.02 sec)

当explicit_defaults_for_timestamp=on时

mysql> show variables like 'explicit%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| explicit_defaults_for_timestamp | ON    |

+---------------------------------+-------+

1 row in set (0.12 sec)

mysql> create table t_tim (id1 timestamp);

Query OK, 0 rows affected (0.20 sec)

mysql> desc t_tim;

+-------+-----------+------+-----+---------+-------+

| Field | Type      | Null | Key | Default | Extra |

+-------+-----------+------+-----+---------+-------+

| id1   | timestamp | YES  |     | NULL    |       |

+-------+-----------+------+-----+---------+-------+

1 row in set (0.02 sec)

mysql> insert into t_tim values (null);

Query OK, 1 row affected (0.01 sec)

mysql> commit;

Query OK, 0 rows affected (0.05 sec)

mysql> select * from t_tim;

+------+

| id1  |

+------+

| NULL |

+------+

1 row in set (0.01 sec)

当一个表有两列都是timestamp 类型，第二个默认为 0000-00-00 00:00:00

mysql>  set session sql_mode='';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> alter table t_tim add a timestamp;

Query OK, 0 rows affected (0.93 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t_tim \G;

*************************** 1. row ***************************

       Table: t_tim

Create Table: CREATE TABLE `t_tim` (

  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `a` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR: 

No query specified

mysql> INSERT INTO T_TIM VALUES (NULL,NULL);

Query OK, 1 row affected (0.01 sec)

mysql> TRUNCATE TABLE T_TIM;

Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO T_TIM VALUES (NULL,NULL);

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM T_TIM;

+---------------------+---------------------+

| id1                 | a                   |

+---------------------+---------------------+

| 2020-08-07 14:16:29 | 2020-08-07 14:16:29 |

+---------------------+---------------------+

1 row in set (0.00 sec)

mysql> DESC T_TIM;

+-------+-----------+------+-----+---------------------+-----------------------------+

| Field | Type      | Null | Key | Default             | Extra                       |

+-------+-----------+------+-----+---------------------+-----------------------------+

| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |

| a     | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |

+-------+-----------+------+-----+---------------------+-----------------------------+

2 rows in set (0.00 sec)

mysql> INSERT INTO T_TIM (ID1) VALUES(NULL);

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM T_TIM; 默认为0000

+---------------------+---------------------+

| id1                 | a                   |

+---------------------+---------------------+

 |

| 2020-08-07 14:19:09 | 0000-00-00 00:00:00 |

+---------------------+---------------------+

3 rows in set (0.00 sec)

mysql> ALTER TABLE T_TIM ADD I INT;

Query OK, 0 rows affected (0.57 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> TRUNCATE TABLE T_TIM;

Query OK, 0 rows affected (0.18 sec)

mysql> INSERT INTO T_TIM (I) VALUES(1);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM T_TIM;

+---------------------+---------------------+------+

| id1                 | a                   | I    |

+---------------------+---------------------+------+

| 2020-08-07 14:20:25 | 0000-00-00 00:00:00 |    1 |

+---------------------+---------------------+------+

1 row in set (0.02 sec)

timestamp 和时区相关

mysql> create table t8 (

    -> id1 timestamp not null default current_timestamp,

    -> id2 datetime default null 

    -> )

    -> ;

Query OK, 0 rows affected (0.62 sec)

mysql> show variables like '%time_zone%'

    -> ;

+------------------+--------+

| Variable_name    | Value  |

+------------------+--------+

| system_time_zone | CST    |

| time_zone        | SYSTEM |

+------------------+--------+

2 rows in set (0.06 sec)

system是指默认时区和主机时区相同

mysql> select * from t8;

Empty set (0.01 sec)

mysql> insert into t8 values(now(),now());

Query OK, 1 row affected (0.03 sec)

mysql> select * from t8;

+---------------------+---------------------+

| id1                 | id2                 |

+---------------------+---------------------+

| 2020-08-07 14:36:34 | 2020-08-07 14:36:34 |

+---------------------+---------------------+

1 row in set (0.01 sec)

更改时区为东九区

mysql> set time_zone='+9:00';

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t8;

+---------------------+---------------------+

| id1                 | id2                 |

+---------------------+---------------------+

| 2020-08-07 15:36:34 | 2020-08-07 14:36:34 |

+---------------------+---------------------+

1 row in set (0.01 sec)

当改为东九区时 则id1的值别东八区快一个小时，产生误差

所以当服务器的时区不同时，所见表中timpstamp则不同