MySQL - 今日日期

在 MySQL 中处理日期

有效地管理日期和时间是数据库管理中的一项基本技能。MySQL 提供了强大的数据类型和函数来准确存储、检索和操作时间数据。

常见错误：将日期存储为文本

一种常见的反模式（anti-pattern）是将日期存储在 VARCHAR 或 TEXT 列中。务必不惜一切代价避免这种情况。使用正确的日期/时间数据类型可确保数据完整性，允许进行正确的排序和计算，并更有效地利用存储空间。

选择正确的数据类型

MySQL 提供了几种用于时间值的数据类型。最常见的有：

• DATE：存储日期值（‘YYYY-MM-DD’）。适用于生日、事件日期等。范围：‘1000-01-01’ 到 ‘9999-12-31’。
• DATETIME：存储日期和时间（‘YYYY-MM-DD HH:MM:SS’）。适用于记录特定时刻，如订单创建时间。它不感知时区。
• TIMESTAMP：也存储日期和时间。但是，它在存储时会将值从当前会话的时区转换为 UTC，并在检索时再转换回会话的时区。这使其非常适合跨不同时区使用的应用程序。其范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC。

获取当前日期和时间

MySQL 提供了几个函数来从服务器获取当前日期和时间。

• CURDATE() 或 CURRENT_DATE()：以 DATE 值返回当前日期。
• NOW() 或 SYSDATE()：以 DATETIME 或 TIMESTAMP 值返回当前日期和时间。
• UTC_TIMESTAMP()：返回当前的协调世界时（UTC）日期和时间。

查询示例

SELECT CURDATE() AS today_date;
-- 结果：2023-10-27

SELECT NOW() AS current_datetime;
-- 结果：2023-10-27 10:30:00

实际示例：订单表

让我们创建一个表来存储订单信息，并使用适当的数据类型。

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL, -- 订单创建日期
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 自动记录创建时间
    amount DECIMAL(10, 2)
);

现在，让我们插入一些数据。我们将为此使用 MySQL 的日期函数。

INSERT INTO orders (customer_id, order_date, amount)
VALUES
    -- 今天下的订单
    (101, CURDATE(), 199.99),

    -- 昨天下的订单，使用日期算术
    (102, DATE_SUB(CURDATE(), INTERVAL 1 DAY), 49.50),

    -- 下个月的订单
    (101, DATE_ADD(CURDATE(), INTERVAL 1 MONTH), 120.00);

要检索订单，您只需查询该表：

SELECT order_id, customer_id, order_date, created_at, amount FROM orders;

在客户端程序中处理日期

当在应用程序中获取日期/时间值时，现代数据库驱动程序通常会将它们转换为编程语言的本机日期/时间对象，而不仅仅是字符串。

Node.js（使用 mysql2）

const mysql = require('mysql2/promise');

async function getOrders() {
  // ...（连接设置）
  const [rows] = await connection.execute('SELECT order_id, order_date FROM orders');
  rows.forEach(row => {
    // 'order_date' 字段是一个 JavaScript Date 对象
    console.log(`Order ID: ${row.order_id}, Date: ${row.order_date.toDateString()}`);
  });
  // ...（连接关闭）
}
getOrders();

Python（使用 mysql-connector-python）

import mysql.connector
import datetime

# ...（连接设置）
with connection.cursor(dictionary=True) as cursor:
    cursor.execute("SELECT order_id, order_date FROM orders")
    for row in cursor.fetchall():
        # 'order_date' 字段是一个 Python datetime.date 对象
        order_date: datetime.date = row['order_date']
        print(f"Order ID: {row['order_id']}, Date: {order_date.strftime('%Y-%m-%d')}")
# ...（连接关闭）