MySQL：datetime 类型

#MySQL 笔记

简介

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 |
+----+-------------------------+

( 本文完 )