简介
datetime 支持的范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999。
datetime(n) 表示秒后面的6位微妙，保留/展示最高n位。
千万不要使用 timestamp 类型，因为它支持的时间范围是
1970-01-01 00:00:01.000000到2038-01-19 03:14:07.999999。范围太小了。
datetime 的取值可以是 0000-00-00 00:00:00，这个值不在其支持范围内，可以理解为和字符串类型字段的空字符串类似，代表一个空时间，但又与 null 不同。
使用 Java JDBC 获取时，默认会抛出异常。解决办法是，在 JDBC url 加上zeroDateTimeBehavior=convertToNull，遇到全0时间，转换为 Java 的 null。JDBC url 示例：
jdbc:mysql://127.0.0.1:3306/blog_db?zeroDateTimeBehavior=convertToNull
示例1: 数据插入
建表：
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
id bigint unsigned auto_increment,
created_at datetime(3) NOT NULL COMMENT '创建时间',
primary key(id)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
插入数据：
mysql> insert into test_table (created_at) values('2020-05-02 20:57:44');
mysql> insert into test_table (created_at) values('2020-05-02 20:57:44.12356');
mysql> insert into test_table (created_at) values('2020-05-02 20:57:44.12');
查询数据：
mysql> select * from test_table;
+----+-------------------------+
| id | created_at |
+----+-------------------------+
| 1 | 2020-05-02 20:57:44.000 |
| 2 | 2020-05-02 20:57:44.124 |
| 3 | 2020-05-02 20:57:44.120 |
+----+-------------------------+
示例2: 默认为当前时间
DEFAULT CURRENT_TIMESTAMP 可以让字段默认为当前时间。
建表：
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
id bigint unsigned auto_increment,
created_at_0 datetime NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
created_at_1 datetime(1) NOT NULL DEFAULT CURRENT_TIMESTAMP(1) COMMENT '创建时间',
created_at_4 datetime(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) COMMENT '创建时间',
primary key(id)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
插入数据：
mysql> insert into test_table (id) values(1);
查询：
mysql> select * from test_table;
+----+---------------------+-----------------------+--------------------------+
| id | created_at_0 | created_at_1 | created_at_4 |
+----+---------------------+-----------------------+--------------------------+
| 1 | 2020-05-02 13:05:26 | 2020-05-02 13:05:26.4 | 2020-05-02 13:05:26.4305 |
+----+---------------------+-----------------------+--------------------------+
示例3: 数据记录变化时时间自动更新
字段加上 ON UPDATE CURRENT_TIMESTAMP 属性即可。
建表：
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
id bigint unsigned auto_increment,
created_at datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
updated_at datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '修改时间', -- 数据记录变更时，时间会自动变化
primary key(id)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
插入数据：
mysql> insert into test_table (id) values(1);
查询：
mysql> select * from test_table;
+----+-------------------------+-------------------------+
| id | created_at | updated_at |
+----+-------------------------+-------------------------+
| 1 | 2020-05-02 13:49:57.362 | 2020-05-02 13:49:57.362 |
+----+-------------------------+-------------------------+
注意，必须是数据发生变化，时间才会自动发生变化。
update test_table set id=1 where id=1; 不会导致数据变化，所以时间不会变化。
update test_table set id=2 where id=1; 会导致数据发生变化，所以时间会变化。
示例4: 时区（todo）
建表：
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
id bigint unsigned auto_increment,
created_at datetime(3) NOT NULL COMMENT '创建时间',
primary key(id)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
插入数据：
mysql> insert into test_table (created_at) values('2020-05-02 20:57:44');
查询时区和数据:
mysql> select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| SYSTEM |
+-------------+
mysql> select * from test_table;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2020-05-02 20:57:44 |
+----+---------------------+
示例5: 关于 0000-00-00 00:00:00
建表：
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
id bigint unsigned auto_increment,
created_at datetime COMMENT '创建时间',
primary key(id)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
插入数据：
insert into test_table (id, created_at) values(1, '2020-05-02 20:57:44');
insert into test_table (id, created_at) values(2, '0000-00-00 00:00:00');
insert into test_table (id, created_at) values(3, null);
查询：
mysql> select * from test_table;
+----+---------------------+
| id | created_at |
+----+---------------------+
| 1 | 2020-05-02 20:57:44 |
| 2 | 0000-00-00 00:00:00 |
| 3 | NULL |
+----+---------------------+
mysql> select * from test_table where created_at is null;
+----+------------+
| id | created_at |
+----+------------+
| 3 | NULL |
+----+------------+
mysql> select * from test_table where created_at = '0000-00-00 00:00:00';
+----+---------------------+
| id | created_at |
+----+---------------------+
| 2 | 0000-00-00 00:00:00 |
+----+---------------------+
示例6: 判等与比较大小
建表：
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
id bigint unsigned auto_increment,
created_at datetime(3) COMMENT '创建时间',
primary key(id)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
插入数据：
insert into test_table (id, created_at) values(1, '2020-05-02 20:57:44');
insert into test_table (id, created_at) values(2, '2020-05-02 12:57:44');
insert into test_table (id, created_at) values(3, '0000-00-00 00:00:00');
insert into test_table (id, created_at) values(4, null);
查询所有：
mysql> select * from test_table;
+----+-------------------------+
| id | created_at |
+----+-------------------------+
| 1 | 2020-05-02 20:57:44.000 |
| 2 | 2020-05-02 12:57:44.000 |
| 3 | 0000-00-00 00:00:00.000 |
| 4 | NULL |
+----+-------------------------+
判等查询：
mysql> select * from test_table where created_at is null;
+----+------------+
| id | created_at |
+----+------------+
| 4 | NULL |
+----+------------+
mysql> select * from test_table where created_at = '0000-00-00 00:00:00';
+----+-------------------------+
| id | created_at |
+----+-------------------------+
| 3 | 0000-00-00 00:00:00.000 |
+----+-------------------------+
mysql> select * from test_table where created_at = '2020-05-02 20:57:44';
+----+-------------------------+
| id | created_at |
+----+-------------------------+
| 1 | 2020-05-02 20:57:44.000 |
+----+-------------------------+
比较查询：
mysql> select * from test_table where created_at > '0000-00-00 00:00:00';
+----+-------------------------+
| id | created_at |
+----+-------------------------+
| 1 | 2020-05-02 20:57:44.000 |
| 2 | 2020-05-02 12:57:44.000 |
+----+-------------------------+
mysql> select * from test_table where created_at >= '2020-05-02 12:57:43';
+----+-------------------------+
| id | created_at |
+----+-------------------------+
| 1 | 2020-05-02 20:57:44.000 |
| 2 | 2020-05-02 12:57:44.000 |
+----+-------------------------+
mysql> select * from test_table where created_at >= '2020-05-02 13:57:43';
+----+-------------------------+
| id | created_at |
+----+-------------------------+
| 1 | 2020-05-02 20:57:44.000 |
+----+-------------------------+
mysql> select * from test_table where created_at < '2020-05-02 13:57:43';
+----+-------------------------+
| id | created_at |
+----+-------------------------+
| 2 | 2020-05-02 12:57:44.000 |
| 3 | 0000-00-00 00:00:00.000 |
+----+-------------------------+