1. So Easy | 一文搞定MySQL数据类型

MySQL 8.0参考手册对DATETIME和TIMESTAMP做如下解释：

TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). For any TIMESTAMP or DATETIME column in a table, you can assign the current timestamp as the default value, the auto-update value, or both:

• An auto-initialized column is set to the current timestamp for inserted rows that specify no value for the column.
• An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

意思是TIMESTAMP和 DATETIME列可以自动初始化并更新为当前日期和时间（即当前时间戳）。

1. 使用DEFAULT CURRENT_TIMESTAMP和时 ON UPDATE CURRENT_TIMESTAMP，该列具有其默认值的当前时间戳，并自动更新为当前时间戳。

   CREATE TABLE t1 (
   ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
   );
   

2. 如果有DEFAULT子句但没有ON UPDATE CURRENT_TIMESTAMP子句，则该列具有给定的默认值，并且不会自动更新为当前时间戳。

   CREATE TABLE t1 (
   ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   dt DATETIME DEFAULT CURRENT_TIMESTAMP
   );
   

对于记录添加、更新时间用TIMESTAMP还是DATETIME，可根据具体需求而定，TIMESTAMP占用空间少，但是范围有限制。不过TIMESTAMP的范围也足够我用，因此我建表的SQL通常如下：

CREATE TABLE `table_name` (
  `table_name_id` INT NOT NULL AUTO_INCREMENT COMMENT '数据库自增标识',

  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`table_name_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='表名注释'