MySQL高级函数之：`LAST_DAY()`：其在获取月份最后一天时的应用。

MySQL高级函数 LAST_DAY()：获取月份最后一天的应用

大家好，今天我们要深入探讨MySQL中的一个实用高级函数：LAST_DAY()。虽然这个函数看起来简单直接，但它在实际开发中却有着广泛的应用场景，尤其是在处理日期相关的业务逻辑时。本次讲座将从LAST_DAY()的基本语法入手，逐步深入到各种实际应用，并辅以代码示例，帮助大家充分掌握这个函数的用法。

1. LAST_DAY() 函数的基本语法

LAST_DAY() 函数的作用是返回包含指定日期的月份的最后一天。其基本语法如下：

LAST_DAY(date)

其中，date 参数可以是以下几种类型：

• 一个日期或日期时间表达式（例如：'2023-10-15', '2023-10-15 10:00:00'）
• 一个日期或日期时间类型的列名
• 其他返回日期或日期时间值的函数

LAST_DAY() 函数返回一个 DATE 类型的值，表示该月份的最后一天。

简单示例：

SELECT LAST_DAY('2023-10-15'); -- 返回 '2023-10-31'
SELECT LAST_DAY('2024-02-10'); -- 返回 '2024-02-29' (闰年)
SELECT LAST_DAY('2023-12-31'); -- 返回 '2023-12-31'

2. LAST_DAY() 函数的常见应用场景

LAST_DAY() 函数在实际开发中可以应用于多种场景，以下列举一些常见的例子：

2.1. 计算月末日期：

这是 LAST_DAY() 函数最直接的应用。可以用于生成报表、统计数据等需要按月汇总的场景。

例如，假设我们有一个 orders 表，记录了订单信息，其中包含 order_date 字段记录订单日期。我们需要查询每个月有多少订单。

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (order_date, amount) VALUES
('2023-10-05', 100.00),
('2023-10-15', 150.00),
('2023-10-25', 200.00),
('2023-11-01', 120.00),
('2023-11-10', 180.00),
('2023-12-05', 250.00);

SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,  -- 获取年月
    COUNT(*) AS order_count
FROM
    orders
GROUP BY
    month
ORDER BY
    month;

如果我们需要知道每个月的最后一天日期，可以这样查询：

SELECT DISTINCT DATE_FORMAT(LAST_DAY(order_date), '%Y-%m-%d') AS last_day_of_month
FROM orders
ORDER BY last_day_of_month;

2.2. 生成日期范围：

LAST_DAY() 函数可以结合其他日期函数，生成一个包含整个月的日期范围。例如，生成一个月的每日报表。

假设我们需要生成 2023 年 10 月的每日报表，可以使用如下 SQL：

WITH RECURSIVE date_series AS (
    SELECT '2023-10-01' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_series
    WHERE dt < LAST_DAY('2023-10-01')
)
SELECT dt
FROM date_series
ORDER BY dt;

这段 SQL 使用了递归 CTE (Common Table Expression) 来生成一个日期序列，从 2023-10-01 开始，每天递增一天，直到 2023 年 10 月的最后一天。 LAST_DAY('2023-10-01') 确保了序列在 10 月份结束。

2.3. 计算年龄：

虽然计算年龄通常使用 TIMESTAMPDIFF 或 DATE_DIFF，但在某些特殊情况下，LAST_DAY() 也可以辅助计算。例如，确定一个人是否已经满某个年龄（以月份的最后一天为截止）。

假设我们有一个 users 表，包含 birthday 字段记录用户的生日，我们需要查询所有在 2023 年 10 月 31 日之前满 18 岁的用户。

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    birthday DATE NOT NULL
);

INSERT INTO users (username, birthday) VALUES
('Alice', '2005-10-31'),
('Bob', '2005-11-01'),
('Charlie', '2000-05-15');

SELECT *
FROM users
WHERE birthday <= DATE_SUB(LAST_DAY('2023-10-01'), INTERVAL 18 YEAR);

在这个例子中，DATE_SUB(LAST_DAY('2023-10-01'), INTERVAL 18 YEAR) 计算出 2023 年 10 月 31 日的 18 年前的那一天，然后与用户的生日进行比较。

2.4. 处理账单周期：

在订阅服务或者账单系统中，经常需要处理账单周期。LAST_DAY() 可以用于确定账单周期的结束日期。

例如，假设我们的账单周期是按月计算的，每个月的最后一天是账单截止日期。我们可以使用 LAST_DAY() 来计算每个用户的账单截止日期。

假设我们有一个 subscriptions 表，包含 start_date 字段记录订阅开始日期，我们需要计算每个订阅的下一个账单截止日期。

CREATE TABLE subscriptions (
    subscription_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    start_date DATE NOT NULL
);

INSERT INTO subscriptions (user_id, start_date) VALUES
(1, '2023-10-15'),
(2, '2023-11-01');

SELECT
    subscription_id,
    start_date,
    LAST_DAY(start_date) AS next_billing_date
FROM
    subscriptions;

如果账单周期的计算方式是从 start_date 往后按月计算，那么计算下一个账单截止日期可以这样写：

SELECT
    subscription_id,
    start_date,
    LAST_DAY(DATE_ADD(start_date, INTERVAL 1 MONTH)) AS next_billing_date
FROM
    subscriptions;

2.5. 数据分区：

在大型数据库中，为了提高查询效率，经常会对数据进行分区。按月分区是一种常见的策略，LAST_DAY() 可以用于确定每个月的数据应该存储在哪个分区中。

例如，假设我们有一个 logs 表，包含 log_time 字段记录日志时间，我们需要按月对数据进行分区。

在创建分区表时，可以使用如下 SQL：

CREATE TABLE logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    log_time DATETIME NOT NULL,
    message TEXT
)
PARTITION BY RANGE (TO_DAYS(log_time)) (
    PARTITION p202310 VALUES LESS THAN (TO_DAYS('2023-11-01')),
    PARTITION p202311 VALUES LESS THAN (TO_DAYS('2023-12-01')),
    PARTITION p202312 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

这里，我们使用了 TO_DAYS() 函数将日期转换为天数，然后使用 PARTITION BY RANGE 对数据进行分区。每个分区存储一个月的数据。 LAST_DAY() 函数可以用来动态生成这些分区边界。 例如，可以写一个存储过程，根据给定的年份和月份，自动创建相应的分区。

2.6. 财务报表：

在生成财务报表时，经常需要计算月末余额、月度收入等指标。LAST_DAY() 函数可以用于确定月末日期，从而方便进行计算。

例如，假设我们有一个 transactions 表，包含 transaction_date 字段记录交易日期，amount 字段记录交易金额。我们需要计算每个月的月末余额。

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO transactions (transaction_date, amount) VALUES
('2023-10-05', 100.00),
('2023-10-15', -50.00),
('2023-11-01', 200.00),
('2023-11-15', -100.00);

SELECT
    DATE_FORMAT(LAST_DAY(transaction_date), '%Y-%m') AS month,
    SUM(amount) AS monthly_balance
FROM
    transactions
GROUP BY
    month
ORDER BY
    month;

3. LAST_DAY() 函数与其他日期函数的结合使用

LAST_DAY() 函数可以与其他日期函数结合使用，实现更复杂的功能。

3.1. DATE_ADD() / DATE_SUB():

如前所述，DATE_ADD() 和 DATE_SUB() 可以与 LAST_DAY() 结合，计算相对于月末日期的其他日期。例如，计算上个月的月末日期：

SELECT LAST_DAY(DATE_SUB('2023-10-15', INTERVAL 1 MONTH)); -- 返回 '2023-09-30'

3.2. DATE_FORMAT():

DATE_FORMAT() 可以将 LAST_DAY() 返回的日期格式化为特定的字符串。

SELECT DATE_FORMAT(LAST_DAY('2023-10-15'), '%Y年%m月%d日'); -- 返回 '2023年10月31日'

3.3. CURDATE() / NOW():

CURDATE() 返回当前日期，NOW() 返回当前日期和时间。可以将它们与 LAST_DAY() 结合，计算本月的月末日期。

SELECT LAST_DAY(CURDATE()); -- 返回本月的月末日期
SELECT LAST_DAY(NOW());    -- 返回本月的月末日期

3.4. MONTH() / YEAR():

可以使用 MONTH() 和 YEAR() 函数提取日期中的月份和年份，然后结合 LAST_DAY() 构建特定月份的月末日期。

SELECT LAST_DAY(MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL MONTH(CURDATE())-1 MONTH);

这条语句略微复杂，但展示了如何使用 YEAR() 和 MONTH() 获取当前年份和月份，然后使用 MAKEDATE 构建当年的第一天，再使用 DATE_ADD 增加相应的月份数，最后使用 LAST_DAY 获取该月的最后一天。 这种方法在需要动态构建日期时非常有用。

4. LAST_DAY() 函数的注意事项

• LAST_DAY() 函数只接受日期或日期时间类型的值作为参数。如果传递其他类型的值，MySQL 会尝试进行隐式转换，但可能会导致错误或不可预测的结果。
• LAST_DAY() 函数返回的是 DATE 类型的值，不包含时间部分。
• LAST_DAY() 函数在处理闰年时会自动进行调整，返回正确的月末日期。
• 如果 date 参数为 NULL，则 LAST_DAY() 函数返回 NULL。

5. 示例：生成过去12个月的月末日期

这是一个更复杂的例子，展示了如何使用 LAST_DAY() 函数生成过去 12 个月的月末日期。

WITH RECURSIVE date_series AS (
    SELECT LAST_DAY(CURDATE()) AS dt
    UNION ALL
    SELECT LAST_DAY(DATE_SUB(dt, INTERVAL 1 MONTH))
    FROM date_series
    WHERE dt > DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
)
SELECT dt
FROM date_series
ORDER BY dt DESC;

这个 SQL 语句使用递归 CTE 生成一个日期序列，从当前月份的月末日期开始，每次减去一个月，直到 12 个月前。 LAST_DAY() 函数确保每次迭代都得到该月的月末日期。

6. LAST_DAY()在报表系统中的实际应用

假设我们正在开发一个报表系统，需要生成月度用户活跃度报告。 我们有一个user_activity表，记录了用户的活动信息，包含user_id和activity_date字段。

CREATE TABLE user_activity (
    activity_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    activity_date DATE NOT NULL
);

INSERT INTO user_activity (user_id, activity_date) VALUES
(1, '2023-10-05'),
(2, '2023-10-15'),
(1, '2023-11-01'),
(3, '2023-11-10'),
(2, '2023-12-05'),
(1, '2023-12-20');

我们需要生成一个报表，显示每个月有多少活跃用户（即当月至少有一次活动的用户）。

SELECT
    DATE_FORMAT(LAST_DAY(activity_date), '%Y-%m') AS month,
    COUNT(DISTINCT user_id) AS active_users
FROM
    user_activity
GROUP BY
    month
ORDER BY
    month;

在这个查询中，LAST_DAY(activity_date) 用于确保我们按月进行分组，即使 activity_date 在月份中变化。 DATE_FORMAT 用于将日期格式化为 YYYY-MM 形式，方便阅读。 COUNT(DISTINCT user_id) 用于计算每个月的活跃用户数。

7. 使用LAST_DAY()进行数据清洗

在数据仓库项目中，有时需要对历史数据进行清洗。 假设一个电商平台将每天的销售额记录在daily_sales表中。 但是由于早期系统设计不规范，有些数据记录的日期是错误的，例如将2月份的数据记录为3月1日。 为了更正这些错误，我们可以使用LAST_DAY()来判断数据是否合理，并将错误的日期更正为当月的最后一天。

CREATE TABLE daily_sales (
    sale_id INT PRIMARY KEY AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO daily_sales (sale_date, amount) VALUES
('2023-02-28', 100.00),
('2023-03-01', 150.00),  -- 错误的日期，应该是2月的最后一天
('2023-03-15', 200.00);

-- 更新错误的日期
UPDATE daily_sales
SET sale_date = LAST_DAY(DATE_SUB(sale_date, INTERVAL 1 MONTH))
WHERE MONTH(sale_date) = 3 AND DAY(sale_date) = 1 AND MONTH(LAST_DAY(DATE_SUB(sale_date, INTERVAL 1 MONTH))) = 2;

SELECT * FROM daily_sales;

在这个例子中，我们首先创建了一个包含错误日期的daily_sales表。 然后，我们使用UPDATE语句找到sale_date是3月1日，且上一个月的最后一天是2月的数据，并将其更正为2月的最后一天。

8. LAST_DAY()在金融系统中的应用

在金融系统中，经常需要计算利息或者罚息。 假设一个银行需要对逾期贷款收取罚息，罚息的计算截止日期是每个月的最后一天。 银行有一个loans表，记录了贷款信息，包含loan_id、loan_amount、interest_rate和due_date字段。

CREATE TABLE loans (
    loan_id INT PRIMARY KEY AUTO_INCREMENT,
    loan_amount DECIMAL(10, 2) NOT NULL,
    interest_rate DECIMAL(5, 2) NOT NULL,
    due_date DATE NOT NULL
);

INSERT INTO loans (loan_amount, interest_rate, due_date) VALUES
(10000.00, 0.05, '2023-10-15'),
(20000.00, 0.06, '2023-11-01');

我们需要计算截止到当前月份最后一天，每个贷款的逾期天数。

SELECT
    loan_id,
    DATEDIFF(LAST_DAY(CURDATE()), due_date) AS overdue_days
FROM
    loans
WHERE
    due_date < LAST_DAY(CURDATE());

在这个查询中，LAST_DAY(CURDATE()) 用于获取当前月份的最后一天，然后使用DATEDIFF函数计算due_date和当前月份最后一天之间的天数差，从而得到逾期天数。

总结: 掌握LAST_DAY()，更高效地处理时间逻辑

本次讲座我们深入探讨了 MySQL 中的 LAST_DAY() 函数。从基本语法到实际应用，再到与其他日期函数的结合使用，相信大家对 LAST_DAY() 函数有了更深入的理解。它在处理月末日期、生成日期范围、计算年龄、处理账单周期、数据分区、财务报表等方面都有着广泛的应用。 希望大家能够灵活运用 LAST_DAY() 函数，提高开发效率，解决实际问题。 掌握LAST_DAY()函数，可以更有效地处理数据库中的日期和时间信息，特别是在需要进行月度数据分析、报表生成或财务计算的场景下。熟练使用LAST_DAY()能使SQL语句更加简洁明了。