MySQL - INTERVAL 运算符

MySQL - INTERVAL 操作符

什么是 INTERVAL 操作符？

MySQL 中的 INTERVAL 操作符是一个基本的关键字，用于执行日期和时间计算。它允许您从日期、时间或日期时间值中添加或减去指定的时间段。这对于从安排事件、计算截止日期到分析时间序列数据等各种应用都至关重要。

INTERVAL 提供了一种可读的、声明性的方式来表达时间持续，而不是手动计算一天中的秒数或一个月中的天数。

语法

INTERVAL expression unit

其中：

• expression: 一个值或表达式，解析为一个数字，表示时间间隔的数量。
• unit: 一个关键字，指定时间单位，例如 DAY（天）、HOUR（小时）、YEAR（年）、MONTH（月）等。INTERVAL 和 unit 都不区分大小写。

支持的单位

MySQL 支持一套全面的时间间隔单位：

单位	描述
MICROSECOND	微秒
SECOND	秒
MINUTE	分
HOUR	小时
DAY	天
WEEK	周
MONTH	月
QUARTER	季度（3个月）
YEAR	年
YEAR_MONTH	复合单位，例如 INTERVAL ‘1-6’ YEAR_MONTH (1年，6个月)
DAY_HOUR	复合单位，例如 INTERVAL ‘2 12’ DAY_HOUR (2天，12小时)

其他复合单位，如 DAY_MINUTE、HOUR_SECOND 等也可用。

执行日期和时间算术运算

INTERVAL 操作符与 + 和 - 操作符一起使用，用于加法和减法。

示例 1：向日期添加天数

SELECT '2024-01-15' + INTERVAL 10 DAY;

结果
2024-01-25

示例 2：从日期时间减去小时数

SELECT '2024-01-15 10:00:00' - INTERVAL 3 HOUR;

结果
2024-01-15 07:00:00

示例 3：使用复合单位

SELECT '2024-03-01' + INTERVAL '1-2' YEAR_MONTH; -- 添加 1 年 2 个月

结果
2025-05-01

与日期/时间函数结合使用

虽然算术运算符很常用，但使用 DATE_ADD()、DATE_SUB() 和 TIMESTAMPADD() 等专用函数通常更明确和可读。

DATE_ADD() 和 DATE_SUB() 示例

SELECT
    DATE_ADD('2024-01-31', INTERVAL 1 MONTH) AS next_month,
    DATE_SUB('2024-03-31', INTERVAL 1 MONTH) AS prev_month;

MySQL 在处理月末日期方面很智能。将 1 月 31 日添加一个月会正确地得到 2 月 29 日（在闰年）或 2 月 28 日。

next_month	prev_month
2024-02-29	2024-02-29

实际示例：订阅管理

让我们为需要续订的服务建模一个表。

CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(255) NOT NULL,
    plan VARCHAR(50),
    expiry_date DATE NOT NULL
);

INSERT INTO subscriptions (user_name, plan, expiry_date) VALUES
    ('Alice', 'Monthly', '2024-11-15'),
    ('Bob', 'Annual', '2025-01-20'),
    ('Charlie', 'Monthly', '2024-10-30'),
    ('David', 'Monthly', '2024-11-05');

现在，让我们查找所有从今天日期（CURDATE()）起 7 天内到期的订阅。

-- 假设 CURDATE() 是 '2024-10-29'
SELECT user_name, plan, expiry_date
FROM subscriptions
WHERE expiry_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY);

这个查询会选择 Charlie 和 David。

我们还可以使用 INTERVAL 在用户续订计划时更新到期日期。

-- Alice 续订她的月度计划
UPDATE subscriptions
SET expiry_date = DATE_ADD(expiry_date, INTERVAL 1 MONTH)
WHERE user_name = 'Alice';

在客户端应用程序中使用 INTERVAL

从客户端应用程序使用 INTERVAL 很简单。您可以直接构建查询字符串（如果时间间隔是静态的）或为时间间隔值使用参数。

PHP (PDO)
Node.js (mysql2/promise)
Java (JDBC)
Python (mysql-connector-python)




```php
<?php
// 假设 $pdo 是一个已连接的 PDO 对象

// 按动态月数续订订阅
$userName = 'Bob';
$monthsToAdd = 12; // 来自“年度”计划购买

$sql = "UPDATE subscriptions
        SET expiry_date = DATE_ADD(expiry_date, INTERVAL ? MONTH)
        WHERE user_name = ?";

$stmt = $pdo->prepare($sql);
$stmt->execute([$monthsToAdd, $userName]);

echo "Subscription for {$userName} renewed successfully.";
?>

main.js

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

async function renewSubscription() {
    let connection;
    try {
        connection = await mysql.createConnection({ /* connection config */ });

        const userName = 'Bob';
        const monthsToAdd = 12;

        const sql = 'UPDATE subscriptions SET expiry_date = DATE_ADD(expiry_date, INTERVAL ? MONTH) WHERE user_name = ?';
        const [result] = await connection.execute(sql, [monthsToAdd, userName]);

        if (result.affectedRows > 0) {
            console.log(`Subscription for ${userName} renewed successfully.`);
        } else {
            console.log(`User ${userName} not found.`);
        }
    } catch (error) {
        console.error('Error renewing subscription:', error);
    } finally {
        if (connection) await connection.end();
    }
}

renewSubscription();

SubscriptionManager.java

import java.sql.*;

public class SubscriptionManager {
    // 假设 DB_URL, USER, PASS 已定义
    public static void main(String[] args) {
        String sql = "UPDATE subscriptions SET expiry_date = DATE_ADD(expiry_date, INTERVAL ? MONTH) WHERE user_name = ?";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            int monthsToAdd = 12;
            String userName = "Bob";

            pstmt.setInt(1, monthsToAdd);
            pstmt.setString(2, userName);

            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                System.out.println("Subscription for " + userName + " renewed successfully.");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

renew_script.py

import mysql.connector

def renew_subscription(user_name, months_to_add):
    try:
        conn = mysql.connector.connect(/* connection config */)
        cursor = conn.cursor()

        sql = ("UPDATE subscriptions "
               "SET expiry_date = DATE_ADD(expiry_date, INTERVAL %s MONTH) "
               "WHERE user_name = %s")

        cursor.execute(sql, (months_to_add, user_name))
        conn.commit()

        if cursor.rowcount > 0:
            print(f"Subscription for {user_name} renewed successfully.")
        else:
            print(f"User {user_name} not found.")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    renew_subscription('Bob', 12)

## 重要考量

- **时区：** MySQL 的日期和时间函数可以是时区感知的，但这取决于服务器和客户端的时区设置。对于全球性应用，最佳实践是将所有日期时间存储为 UTC，并在应用层进行本地时区转换。
- **闰年和月末：** 使用 `MONTH` 或 `YEAR` 间隔时，MySQL 会正确处理闰年和月份天数不同等复杂情况。将 `INTERVAL 1 MONTH` 添加到 `2024-01-31` 会正确得到 `2024-02-29`。
- **歧义：** 对单位的使用要小心。`INTERVAL 30 DAY` 并非总是与 `INTERVAL 1 MONTH` 相同。前者总是精确添加 30 天，而后者会根据具体月份进行调整。