MySQL 管理运维：妙用 DATETIME 数据类型的默认值

MySQL 管理运维：妙用 DATETIME 数据类型的默认值

ShawnYan Lv.6
  2023-05-08 21:58 2023-05-08 21:58 Created   2023-05-08 21:58 2023-05-08 21:58 Updated      1.2k Words  6 Mins  

MySQL 中的日期 DATE 和时间 TIME 类型在数据库设计和数据处理中扮演着至关重要的角色。
日期和时间数据类型允许我们存储日期、时间或日期时间值，为数据查询、排序和计算提供了便利。
在 MySQL 中，有多种日期和时间类型，如：DATE, TIME, DATETIME, TIMESTAMP 和 YEAR。

本文将以 DATETIME 数据类型为线索，介绍几种常用的时间类型的默认值用法，并介绍一则案例。

1. 默认值为 0 或 NULL

DATETIME 设置为 0 表示无效时间格式，默认情况下 MySQL 并不允许这种情况，如果这样设定会报错。

1
2
3
4
5
6
7
8
9
(root@localhost) [yandb]> create table t (
    -> a int, 
    -> d1 datetime, 
    -> d2 datetime default 0,
    -> d3 datetime default null
    -> );
ERROR 1067 (42000): Invalid default value for 'd2'
Error (Code 1292): Incorrect datetime value: '0' for column 'd2' at row 1
Error (Code 1067): Invalid default value for 'd2'

这是因为 sql_mode 中设定了 NO_ZERO_DATE 模式，这里为了测试，先临时取消这个设定。

1
2
3
4
5
6
7
8
9
10
(root@localhost) [yandb]> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(root@localhost) [yandb]> set sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

创建测试表，包含了三个日期时间类型的列：d1、d2 和 d3。

1
2
3
4
5
6
create table t (
a int, 
d1 datetime, -- 等价于默认值为 NULL
d2 datetime default 0, -- 等价于默认值为 '0000-00-00 00:00:00'
d3 datetime default null
);

插入测试数据并查看：

1
2
3
4
5
6
7
8
9
10
11
(root@localhost) [yandb]> insert t (a) select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

(root@localhost) [yandb]> table t;
+------+------+---------------------+------+
| a    | d1   | d2                  | d3   |
+------+------+---------------------+------+
|    1 | NULL | 0000-00-00 00:00:00 | NULL |
+------+------+---------------------+------+
1 row in set (0.00 sec)

2. 默认值为当前时间

有两种常用的内置方法来获取当前时间，NOW() 和 CURRENT_TIMESTAMP，两者为同义词。

创建测试表：

1
2
3
4
5
create table t (
a int,
d1 datetime default now(),
d2 datetime default CURRENT_TIMESTAMP
);
1
2
3
4
5
6
7
8
9
(root@localhost) [yandb]> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int DEFAULT NULL,
  `d1` datetime DEFAULT CURRENT_TIMESTAMP,
  `d2` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

3. 默认值为当前时间，加上间隔时间

某些使用场景需要获得当前时间，并加上一个日期间隔，比如物品过保日期。

设定默认值的方法为：DEFAULT (CURRENT_DATE + INTERVAL 3 MONTH)

测试：

1
2
3
4
5
6
7
create table t (
a int,
d1 datetime default now(),
d2 datetime DEFAULT (CURRENT_TIMESTAMP + INTERVAL 3 MONTH)
);

insert t (a) select 1;

输出：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
(root@localhost) [yandb]> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int DEFAULT NULL,
  `d1` datetime DEFAULT CURRENT_TIMESTAMP,
  `d2` datetime DEFAULT ((now() + interval 3 month))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

(root@localhost) [yandb]> table t;
+------+---------------------+---------------------+
| a    | d1                  | d2                  |
+------+---------------------+---------------------+
|    1 | 2023-05-08 11:05:43 | 2023-08-08 11:05:43 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

4. 默认值为当前时间，并自动更新

时间类型默认值后可以加上 ON UPDATE CURRENT_TIMESTAMP 子句，当有新数据更新时，可自动更新字段为当前时间戳。

这种机制对于跟踪记录的创建时间和最后修改时间非常有用。您不需要在插入或更新记录时手动设置这些时间戳，MySQL 会自动为你处理。

1
2
3
4
5
6
create table t (
a int primary key,
d datetime DEFAULT now() ON UPDATE CURRENT_TIMESTAMP
);

replace t(a) select 1;

输出：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
(root@localhost) [yandb]> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int NOT NULL,
  `d` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

(root@localhost) [yandb]> table t;
+---+---------------------+
| a | d                   |
+---+---------------------+
| 1 | 2023-05-08 13:34:03 |
+---+---------------------+
1 row in set (0.00 sec)

(root@localhost) [yandb]> replace t(a) select 1;
Query OK, 2 rows affected (0.01 sec)
Records: 1  Duplicates: 1  Warnings: 0

(root@localhost) [yandb]> table t;
+---+---------------------+
| a | d                   |
+---+---------------------+
| 1 | 2023-05-08 13:34:45 |
+---+---------------------+
1 row in set (0.00 sec)

5. 案例

假设我们有一个名为 orders 的表，该表记录了用户的订单信息。表中包含以下字段：order_id（订单ID）、user_id（用户ID）、product_id（产品ID）、quantity（数量）和 order_date（订单日期）。

为了记录订单最后更新时间，可以在该表中增加 update_at 字段，设定默认值为当前时间戳，并开启自动更新。

1
2
3
4
5
6
7
8
create table orders (
order_id int primary key,
user_id int,
product_id int,
quantity int,
order_date datetime,
update_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

输出：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
(root@localhost) [yandb]> insert orders (order_id,user_id,product_id,quantity,order_date) values (1,1,1,1,curdate());
Query OK, 1 row affected (0.01 sec)

(root@localhost) [yandb]> table orders;
+----------+---------+------------+----------+---------------------+---------------------+
| order_id | user_id | product_id | quantity | order_date          | update_at           |
+----------+---------+------------+----------+---------------------+---------------------+
|        1 |       1 |          1 |        1 | 2023-05-08 00:00:00 | 2023-05-08 13:50:33 |
+----------+---------+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)

(root@localhost) [yandb]> replace orders (order_id,user_id,product_id,quantity,order_date) values (1,1,1,2,curdate());
Query OK, 2 rows affected (0.00 sec)

(root@localhost) [yandb]> table orders;
+----------+---------+------------+----------+---------------------+---------------------+
| order_id | user_id | product_id | quantity | order_date          | update_at           |
+----------+---------+------------+----------+---------------------+---------------------+
|        1 |       1 |          1 |        2 | 2023-05-08 00:00:00 | 2023-05-08 13:51:18 |
+----------+---------+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)

小结

上述 DATETIME 数据类型的默认值各有用途和适用场景。
了解这些默认值和如何设置它们对于正确设计数据库和数据模型至关重要。
最后也给出一个实用案例，巧用默认值设定也可以简化数据处理过程。

logo.jpg
  • Title: MySQL 管理运维：妙用 DATETIME 数据类型的默认值
  • Author: ShawnYan
  • Created at: 2023-05-08 21:58:00
  • Updated at: 2023-05-08 21:58:00
  • Link: https://shawnyan.cn/2023/mysql/mysql-8-case-datetime-default/
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐阅读
MySQL 管理运维：删除列导致索引变化 MySQL 管理运维：删除列导致索引变化 MySQL 管理运维：这类 VALUES() 已被弃用 MySQL 管理运维：这类 VALUES() 已被弃用 TiDB 7.x 源码编译之 TiDB Server 篇，及新特性详解（文末彩蛋） TiDB 7.x 源码编译之 TiDB Server 篇，及新特性详解（文末彩蛋）
推荐阅读
MySQL 管理运维：删除列导致索引变化 MySQL 管理运维：删除列导致索引变化 MySQL 管理运维：这类 VALUES() 已被弃用 MySQL 管理运维：这类 VALUES() 已被弃用
 Comments
On this page
  1. 1. 默认值为 0 或 NULL
  2. 2. 默认值为当前时间
  3. 3. 默认值为当前时间，加上间隔时间
  4. 4. 默认值为当前时间，并自动更新
  5. 5. 案例
  6. 小结