mysql日期类型默认值

基础概念

MySQL中的日期类型主要包括DATE、DATETIME和TIMESTAMP。这些类型用于存储日期和时间值。

• DATE：存储格式为YYYY-MM-DD，范围从1000-01-01到9999-12-31。
• DATETIME：存储格式为YYYY-MM-DD HH:MM:SS，范围从1000-01-01 00:00:00到9999-12-31 23:59:59。
• TIMESTAMP：存储格式也为YYYY-MM-DD HH:MM:SS，但范围从1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC。

默认值

在MySQL中，日期类型的默认值取决于列的定义和表的创建方式。

创建表时指定默认值

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date_column DATE DEFAULT '2023-01-01',
    datetime_column DATETIME DEFAULT CURRENT_TIMESTAMP,
    timestamp_column TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

在这个例子中：

• date_column的默认值是2023-01-01。
• datetime_column的默认值是插入记录时的当前时间。
• timestamp_column的默认值也是插入记录时的当前时间，并且在每次更新记录时会自动更新为当前时间。

修改已有表的默认值

ALTER TABLE example
MODIFY date_column DATE DEFAULT '2024-01-01';

优势

1. 灵活性：日期类型提供了多种格式，可以根据需要选择合适的类型。
2. 自动更新：TIMESTAMP类型可以在插入或更新记录时自动更新时间戳。
3. 存储效率：日期类型在数据库中占用的空间相对较小，适合存储大量日期和时间数据。

类型

• DATE
• DATETIME
• TIMESTAMP

应用场景

• 日志记录：记录系统或应用的运行日志，包括开始和结束时间。
• 订单管理：记录订单的创建和完成时间。
• 用户活动跟踪：记录用户的登录、注册、操作等时间点。

常见问题及解决方法

问题：为什么TIMESTAMP类型的默认值在插入时不更新？

原因：可能是由于表的explicit_defaults_for_timestamp系统变量设置为ON。当这个变量为ON时，TIMESTAMP列的默认值和自动更新行为需要显式指定。

解决方法：

SET GLOBAL explicit_defaults_for_timestamp = OFF;

或者在创建表时显式指定：

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp_column TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

问题：为什么日期格式不正确？

原因：可能是由于插入的数据格式不正确，或者数据库的字符集和排序规则不支持某些日期格式。

解决方法：

确保插入的数据格式正确，并检查数据库的字符集和排序规则：

ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

参考链接

• MySQL官方文档 - 日期和时间类型
• MySQL官方文档 - 修改表的默认值