database

MySQL 时间字段类型¶

MySQL 提供专用时间类型，各有明确的存储空间和语义：

1. 时间类型对比¶

2. DATETIME vs TIMESTAMP 关键区别¶

TIMESTAMP

• 存储 UTC 时间，按会话时区自动转换
• 受 2038 年问题影响
• 支持自动初始化和更新：DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

DATETIME

• 存储绝对时间，无时区概念
• 范围更广，适合未来日期
• 5.6+ 版本支持微秒精度

3. 建表示例¶

CREATE TABLE user_activity (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    
    -- 记录创建和更新时间（自动）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- 预约时间（未来时间，用 DATETIME）
    appointment_time DATETIME NOT NULL,
    
    -- 用户出生日期
    birth_date DATE,
    
    -- 每日提醒时间
    reminder_time TIME,
    
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

4. 常用时间函数¶

-- 获取当前时间
SELECT NOW();           -- '2024-01-02 15:30:45'
SELECT CURDATE();       -- '2024-01-02'
SELECT CURTIME();       -- '15:30:45'
SELECT CURRENT_TIMESTAMP;

-- 时间计算
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 3 HOUR);
SELECT NOW() + INTERVAL 1 MONTH;

-- 格式化输出
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

-- 提取部分
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());

5. 时区处理¶

-- 查看当前时区
SELECT @@global.time_zone, @@session.time_zone;

-- 设置会话时区
SET time_zone = '+08:00';
SET time_zone = 'Asia/Shanghai';

-- 存储时统一用 UTC
INSERT INTO events (event_time) VALUES (UTC_TIMESTAMP());

6. 最佳实践¶

推荐：

• 记录日志用 TIMESTAMP - 自动管理时区
• 未来时间用 DATETIME - 避免 2038 限制
• 生日/纪念日用 DATE - 节省空间
• 始终建立索引 - 时间字段是高频查询条件

避免：

• 混用 DATETIME 和 TIMESTAMP 导致时区混乱
• 在 WHERE 中对时间字段使用函数（导致索引失效）
• 使用字符串类型存储时间

-- 正确：利用索引
SELECT * FROM orders WHERE created_at >= '2024-01-01';

-- 错误：索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';