第二十章日期时间与条件函数

本章目标

通过本章学习，你将能够：

掌握 MySQL 中所有核心日期时间函数的用法和适用场景
理解 NOW() 与 SYSDATE() 的关键区别及其对复制的影响
熟练使用 DATE_FORMAT 和 STR_TO_DATE 进行日期格式化与解析
掌握 DATE_ADD/DATE_SUB 和 INTERVAL 进行日期计算
熟练使用 DATEDIFF、TIMESTAMPDIFF 进行日期差值计算
掌握 IF、IFNULL、NULLIF、COALESCE 和 CASE 条件表达式
能够在复杂业务查询中灵活组合日期与条件函数

前置知识

在学习本章之前，你需要：

理解 MySQL 中的日期时间数据类型：DATE、TIME、DATETIME、TIMESTAMP
知道 TIMESTAMP 的范围限制（1970-01-01 到 2038-01-19）与时区敏感性
理解 NULL 的三值逻辑（TRUE、FALSE、UNKNOWN）
了解基本的 SQL SELECT 语法和 WHERE 条件过滤
理解字符串与日期之间的隐式转换规则

20.1 获取当前日期时间

20.1.1 NOW() 与 SYSDATE() —— 重要区别

sql

-- NOW()：返回语句开始执行时的日期时间（在语句执行期间保持不变）
SELECT NOW();               -- 结果: 2024-10-15 14:30:25
SELECT NOW() + 0;           -- 结果: 20241015143025（数字形式）

-- SYSDATE()：返回实际系统时间（每次调用都重新获取）
SELECT SYSDATE();           -- 结果: 2024-10-15 14:30:25
SELECT SYSDATE() + 0;       -- 结果: 20241015143025（数字形式）

-- 核心区别演示：在一条语句中调用多次
SELECT NOW(), SLEEP(2), NOW();
-- 两个 NOW() 返回相同时间！（语句开始时的快照）

SELECT SYSDATE(), SLEEP(2), SYSDATE();
-- 两个 SYSDATE() 返回不同时间！（相差约 2 秒）

-- 对复制的影响：
-- STATEMENT 复制模式：NOW() 在主从库上返回相同值（安全）
-- STATEMENT 复制模式：SYSDATE() 在主从库上可能不同（危险！）
-- 解决方法：设置 sysdate-is-now=1 或在 ROW 格式下复制

-- 实际应用：记录创建时间和更新时间
INSERT INTO orders (created_at, updated_at) VALUES (NOW(), NOW());
UPDATE orders SET updated_at = NOW() WHERE id = 1;

20.1.2 CURDATE() 和 CURTIME()

sql

-- CURDATE() / CURRENT_DATE()：当前日期（不含时间）
SELECT CURDATE();           -- 结果: 2024-10-15
SELECT CURRENT_DATE();      -- 结果: 2024-10-15（标准 SQL 语法）
SELECT CURDATE() + 0;       -- 结果: 20241015

-- CURTIME() / CURRENT_TIME()：当前时间（不含日期）
SELECT CURTIME();           -- 结果: 14:30:25
SELECT CURRENT_TIME();      -- 结果: 14:30:25（标准 SQL 语法）
SELECT CURTIME() + 0;       -- 结果: 143025

-- 实际应用：查询当天的数据
SELECT * FROM orders WHERE DATE(created_at) = CURDATE();

-- 更高效的方式（可以利用索引）：
SELECT * FROM orders
WHERE created_at >= CURDATE()
  AND created_at < CURDATE() + INTERVAL 1 DAY;

20.2 日期时间提取函数

20.2.1 DATE(expr) 和 TIME(expr)

sql

-- DATE：提取日期部分
SELECT DATE('2024-10-15 14:30:25');      -- 结果: 2024-10-15
SELECT DATE(NOW());                       -- 结果: 当前日期

-- TIME：提取时间部分
SELECT TIME('2024-10-15 14:30:25');      -- 结果: 14:30:25
SELECT TIME(NOW());                       -- 结果: 当前时间

-- 实际应用：按日期分组统计
SELECT DATE(created_at) AS order_date, COUNT(*) AS order_count
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_date DESC;

20.2.2 各时间组件提取函数

sql

-- 年、月、日
SELECT
    YEAR('2024-10-15 14:30:25')    AS yr,       -- 2024
    MONTH('2024-10-15 14:30:25')   AS mon,      -- 10
    DAY('2024-10-15 14:30:25')     AS dy;        -- 15

-- DAYOFMONTH：同 DAY
SELECT DAYOFMONTH('2024-10-15');    -- 结果: 15

-- DAYOFWEEK：一周中的第几天（1=周日, 2=周一, ..., 7=周六）
SELECT DAYOFWEEK('2024-10-15');     -- 结果: 3（周二）

-- WEEKDAY：一周中的第几天（0=周一, 1=周二, ..., 6=周日）
SELECT WEEKDAY('2024-10-15');       -- 结果: 1（周二）

-- DAYOFYEAR：一年中的第几天（1-366）
SELECT DAYOFYEAR('2024-10-15');     -- 结果: 289

-- QUARTER：季度（1-4）
SELECT QUARTER('2024-10-15');       -- 结果: 4

-- WEEK：一年中的第几周
SELECT WEEK('2024-10-15');          -- 结果: 41（取决于模式参数）

-- 时、分、秒
SELECT
    HOUR('14:30:25')    AS hr,       -- 14
    MINUTE('14:30:25')  AS min,      -- 30
    SECOND('14:30:25')  AS sec;       -- 25

-- MICROSECOND：微秒
SELECT MICROSECOND('14:30:25.123456');  -- 结果: 123456

-- DAYNAME / MONTHNAME：获取名称字符串
SELECT
    DAYNAME('2024-10-15')   AS day_name,   -- Tuesday
    MONTHNAME('2024-10-15') AS month_name;  -- October

-- 实际应用：季节性分析
SELECT
    QUARTER(created_at) AS quarter,
    SUM(amount) AS total_sales
FROM orders
GROUP BY QUARTER(created_at), YEAR(created_at);

-- 按星期统计
SELECT
    DAYNAME(created_at) AS weekday,
    COUNT(*) AS order_count
FROM orders
GROUP BY DAYOFWEEK(created_at), DAYNAME(created_at)
ORDER BY DAYOFWEEK(created_at);

20.3 日期格式化函数

20.3.1 DATE_FORMAT(date, format) —— 完整格式说明

DATE_FORMAT 是最强大的日期格式化函数，支持丰富的格式占位符。

sql

-- 常用格式占位符速查表：
-- %Y = 四位数年份 (2024)
-- %y = 两位数年份 (24)
-- %M = 完整月份名 (January-December)
-- %m = 两位数月份 (01-12)
-- %c = 数字月份 (1-12，无前导零)
-- %d = 两位数日期 (01-31)
-- %e = 数字日期 (1-31，无前导零)
-- %H = 24小时制小时 (00-23)
-- %h = 12小时制小时 (01-12)
-- %i = 分钟 (00-59)
-- %s = 秒 (00-59)
-- %p = AM/PM
-- %W = 完整星期名 (Sunday-Saturday)
-- %a = 缩写星期名 (Sun-Sat)
-- %j = 一年中的第几天 (001-366)
-- %U = 一年中的第几周，周日为第一天 (00-53)
-- %u = 一年中的第几周，周一为第一天 (00-53)
-- %T = 24小时制时间 (hh:mm:ss)
-- %r = 12小时制时间 (hh:mm:ss AM/PM)
-- %% = 字面量百分号

sql

-- 常见日期格式化示例
SELECT DATE_FORMAT('2024-10-15', '%Y-%m-%d');       -- 2024-10-15
SELECT DATE_FORMAT('2024-10-15', '%Y年%m月%d日');   -- 2024年10月15日
SELECT DATE_FORMAT('2024-10-15', '%d/%m/%Y');       -- 15/10/2024
SELECT DATE_FORMAT('2024-10-15', '%W, %M %e, %Y');  -- Tuesday, October 15, 2024

-- 时间格式化
SELECT DATE_FORMAT('2024-10-15 14:30:25', '%H:%i:%s');      -- 14:30:25
SELECT DATE_FORMAT('2024-10-15 14:30:25', '%h:%i:%s %p');   -- 02:30:25 PM
SELECT DATE_FORMAT('2024-10-15 14:30:25', '%T');             -- 14:30:25

-- 中文格式
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时%i分%s秒');

-- 实际应用：报表中的日期格式化
SELECT
    order_id,
    DATE_FORMAT(created_at, '%Y-%m-%d') AS order_date,
    DATE_FORMAT(created_at, '%Y年第%U周') AS week_label,
    CONCAT('¥', FORMAT(amount, 2)) AS formatted_amount
FROM orders;

-- 生成文件名（带时间戳）
SELECT CONCAT('report_', DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'), '.csv') AS filename;
-- 结果: report_20241015_143025.csv

20.3.2 TIME_FORMAT(time, format)

TIME_FORMAT 专门用于格式化时间值，只接受时间相关的格式占位符。

sql

-- 基本用法
SELECT TIME_FORMAT('14:30:25', '%H:%i');         -- 14:30
SELECT TIME_FORMAT('14:30:25', '%h:%i %p');       -- 02:30 PM
SELECT TIME_FORMAT('14:30:25', '%r');              -- 02:30:25 PM
SELECT TIME_FORMAT('14:30:25', '%T');              -- 14:30:25

-- 注意：使用日期占位符（如 %Y, %m）会返回 NULL 或 0！
SELECT TIME_FORMAT('14:30:25', '%Y-%m-%d');        -- 结果: 0000-00-00

20.3.3 STR_TO_DATE(str, format)

将字符串按照指定格式解析为日期值。这是 DATE_FORMAT 的逆操作。

sql

-- 基本用法
SELECT STR_TO_DATE('2024-10-15', '%Y-%m-%d');      -- 结果: 2024-10-15
SELECT STR_TO_DATE('15/10/2024', '%d/%m/%Y');      -- 结果: 2024-10-15
SELECT STR_TO_DATE('2024年10月15日', '%Y年%m月%d日'); -- 结果: 2024-10-15

-- 解析带时间的日期
SELECT STR_TO_DATE('2024-10-15 14:30:25', '%Y-%m-%d %H:%i:%s');
-- 结果: 2024-10-15 14:30:25

-- 解析不完整的日期（缺失部分自动补默认值）
SELECT STR_TO_DATE('2024-10', '%Y-%m');             -- 结果: 2024-10-00（日为 0）
SELECT STR_TO_DATE('14:30', '%H:%i');               -- 结果: 0000-00-00 14:30:00

-- 实际应用：清洗导入数据
-- 导入的数据中日期格式为 'MM/DD/YYYY'
UPDATE imported_users
SET birth_date = STR_TO_DATE(birth_str, '%m/%d/%Y')
WHERE birth_str IS NOT NULL AND birth_str != '';

-- 验证转换结果
SELECT
    birth_str,
    STR_TO_DATE(birth_str, '%m/%d/%Y') AS converted
FROM imported_users
WHERE STR_TO_DATE(birth_str, '%m/%d/%Y') IS NULL
  AND birth_str IS NOT NULL;
-- 返回转换失败的行，可能是格式不符合预期

20.4 日期运算函数

20.4.1 DATE_ADD 和 DATE_SUB

sql

-- DATE_ADD(date, INTERVAL expr unit)：日期加法
SELECT DATE_ADD('2024-10-15', INTERVAL 1 DAY);         -- 2024-10-16
SELECT DATE_ADD('2024-10-15', INTERVAL 1 MONTH);       -- 2024-11-15
SELECT DATE_ADD('2024-10-15', INTERVAL 1 YEAR);        -- 2025-10-15
SELECT DATE_ADD('2024-10-15', INTERVAL 2 WEEK);         -- 2024-10-29

-- DATE_SUB(date, INTERVAL expr unit)：日期减法
SELECT DATE_SUB('2024-10-15', INTERVAL 1 DAY);         -- 2024-10-14
SELECT DATE_SUB('2024-10-15', INTERVAL 3 MONTH);       -- 2024-07-15

-- DATE_ADD 也可以做减法（使用负数）
SELECT DATE_ADD('2024-10-15', INTERVAL -1 DAY);        -- 2024-10-14

-- 时间运算
SELECT DATE_ADD('2024-10-15 14:30:25', INTERVAL 2 HOUR);     -- 2024-10-15 16:30:25
SELECT DATE_ADD('2024-10-15 14:30:25', INTERVAL '1:30' HOUR_MINUTE);  -- 2024-10-15 16:00:25
SELECT DATE_ADD('2024-10-15 14:30:25', INTERVAL '1:30:45' HOUR_SECOND);  -- 2024-10-15 16:01:10

20.4.2 INTERVAL 完整单位列表

sql

-- 完整 INTERVAL 单位
-- 单一单位：
-- MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

-- 复合单位（格式为 'X:Y' 或 'X:Y:Z'）：
-- SECOND_MICROSECOND    'SECONDS.MICROSECONDS'
-- MINUTE_MICROSECOND    'MINUTES:SECONDS.MICROSECONDS'
-- MINUTE_SECOND         'MINUTES:SECONDS'
-- HOUR_MICROSECOND      'HOURS:MINUTES:SECONDS.MICROSECONDS'
-- HOUR_SECOND           'HOURS:MINUTES:SECONDS'
-- HOUR_MINUTE           'HOURS:MINUTES'
-- DAY_MICROSECOND       'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
-- DAY_SECOND            'DAYS HOURS:MINUTES:SECONDS'
-- DAY_MINUTE            'DAYS HOURS:MINUTES'
-- DAY_HOUR              'DAYS HOURS'
-- YEAR_MONTH            'YEARS-MONTHS'

-- 复合单位使用示例
SELECT DATE_ADD('2024-10-15', INTERVAL '1-6' YEAR_MONTH);
-- 结果: 2026-04-15（加 1 年 6 个月）

SELECT DATE_ADD('2024-10-15 10:00:00', INTERVAL '2 4:30' DAY_MINUTE);
-- 结果: 2024-10-17 14:30:00（加 2 天 4 小时 30 分钟）

SELECT DATE_ADD('2024-10-15 10:00:00', INTERVAL '3:45:30' HOUR_SECOND);
-- 结果: 2024-10-15 13:45:30（加 3 小时 45 分 30 秒）

20.4.3 EXTRACT(unit FROM date)

EXTRACT 与 DATE_ADD 使用相同的 unit 值，专门用于提取部分。

sql

-- 基本用法
SELECT EXTRACT(YEAR FROM '2024-10-15');            -- 2024
SELECT EXTRACT(MONTH FROM '2024-10-15');           -- 10
SELECT EXTRACT(DAY FROM '2024-10-15');             -- 15
SELECT EXTRACT(HOUR FROM '2024-10-15 14:30:25');   -- 14
SELECT EXTRACT(MINUTE FROM '2024-10-15 14:30:25'); -- 30

-- 复合提取
SELECT EXTRACT(YEAR_MONTH FROM '2024-10-15');      -- 202410
SELECT EXTRACT(DAY_HOUR FROM '2024-10-15 14:30:25');  -- 1514
SELECT EXTRACT(HOUR_MINUTE FROM '2024-10-15 14:30:25');  -- 1430

-- EXTRACT 与单独函数的对比（完全等价）
SELECT
    EXTRACT(YEAR FROM created_at) AS yr1,
    YEAR(created_at) AS yr2;
-- 两者结果相同，EXTRACT 是标准 SQL 语法

20.5 日期差值函数

20.5.1 DATEDIFF(date1, date2)

返回 date1 - date2 的天数差。只比较日期部分，忽略时间部分。

sql

-- 基本用法
SELECT DATEDIFF('2024-10-20', '2024-10-15');     -- 结果: 5
SELECT DATEDIFF('2024-10-15', '2024-10-20');     -- 结果: -5
SELECT DATEDIFF('2024-10-15 23:59:59', '2024-10-16 00:00:01');  -- 结果: -1

-- 实际应用：计算用户注册天数
SELECT
    username,
    DATEDIFF(NOW(), registered_at) AS days_since_register
FROM users;

-- 计算订单发货延迟
SELECT
    order_id,
    DATEDIFF(shipped_at, created_at) AS processing_days
FROM orders
WHERE shipped_at IS NOT NULL;

-- 查找超过 30 天未登录的用户
SELECT username, last_login_at
FROM users
WHERE DATEDIFF(NOW(), last_login_at) > 30;

20.5.2 TIMEDIFF(time1, time2)

返回 time1 - time2 的时间差，格式为 HH:MM:SS。注意参数是 TIME 类型或可以转换为 TIME 的字符串。

sql

-- 基本用法
SELECT TIMEDIFF('14:30:00', '10:00:00');           -- 结果: 04:30:00
SELECT TIMEDIFF('10:00:00', '14:30:00');           -- 结果: -04:30:00

-- 注意：TIMEDIFF 的范围是 -838:59:59 到 838:59:59
-- 超出范围会返回 NULL！

-- 实际应用：计算工作时长
SELECT
    emp_id,
    TIMEDIFF(check_out, check_in) AS work_duration
FROM attendance;

-- 计算两个 DATETIME 之间的时间差（使用 CAST）
SELECT TIMEDIFF(
    CAST('2024-10-15 14:30:00' AS TIME),
    CAST('2024-10-15 10:00:00' AS TIME)
);  -- 结果: 04:30:00

20.5.3 TIMESTAMPDIFF(unit, start, end) —— 更灵活的差值计算

TIMESTAMPDIFF 是最灵活的日期差值函数，可以按任意单位计算差值。

sql

-- 语法：TIMESTAMPDIFF(unit, start, end)
-- unit 支持：YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND

-- 计算天数差
SELECT TIMESTAMPDIFF(DAY, '2024-10-15', '2024-10-20');    -- 结果: 5

-- 计算小时差
SELECT TIMESTAMPDIFF(HOUR, '2024-10-15 10:00', '2024-10-15 14:30');  -- 结果: 4

-- 计算分钟差
SELECT TIMESTAMPDIFF(MINUTE, '2024-10-15 10:00', '2024-10-15 14:30');  -- 结果: 270

-- 计算月数差
SELECT TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-10-15');   -- 结果: 9

-- 计算年龄（周岁）
SELECT
    name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM users;

-- 实际应用：查找处理超时的工单（超过 4 小时）
SELECT ticket_id, created_at, resolved_at,
       TIMESTAMPDIFF(HOUR, created_at, resolved_at) AS resolution_hours
FROM tickets
WHERE TIMESTAMPDIFF(HOUR, created_at, resolved_at) > 4;

-- 计算两个日期之间完整经过的月数
SELECT TIMESTAMPDIFF(MONTH, '2024-01-31', '2024-02-29');  -- 结果: 0
-- 注意：未满一个完整月，所以返回 0

20.5.4 DATEDIFF 与 TIMESTAMPDIFF 对比

函数	精度	灵活性	比较方式
DATEDIFF	天	低（只能算天数）	date1 - date2
TIMESTAMPDIFF	多种单位	高	end - start（注意顺序！）
TIMEDIFF	时间	中（TIME 类型）	time1 - time2

20.6 UNIX 时间戳函数

sql

-- UNIX_TIMESTAMP([date])：日期 → Unix 时间戳（1970-01-01 至今的秒数）
SELECT UNIX_TIMESTAMP();                             -- 当前时间戳
SELECT UNIX_TIMESTAMP('2024-10-15 14:30:25');        -- 某个日期的秒数

-- FROM_UNIXTIME(unix_timestamp [, format])：Unix 时间戳 → 日期
SELECT FROM_UNIXTIME(1697377825);                    -- 时间戳转日期
SELECT FROM_UNIXTIME(1697377825, '%Y-%m-%d %H:%i:%s');  -- 带格式转换
SELECT FROM_UNIXTIME(1697377825, '%Y年%m月%d日');

-- 实际应用：存储和查询时间范围
-- 假设某些系统以 Unix 时间戳存储时间
SELECT * FROM event_logs
WHERE unix_time >= UNIX_TIMESTAMP('2024-01-01')
  AND unix_time < UNIX_TIMESTAMP('2025-01-01');

-- 注意：MySQL 的 UNIX_TIMESTAMP 是秒级精度（10 位）
-- 毫秒级（13 位）需要除以 1000
SELECT FROM_UNIXTIME(1697377825123 / 1000);

20.7 条件函数

20.7.1 IF(expr, true_val, false_val)

内联 if-else 表达式，类似于编程语言中的三元运算符。

sql

-- 基本用法
SELECT IF(10 > 5, 'yes', 'no');              -- 结果: yes
SELECT IF(10 < 5, 'yes', 'no');              -- 结果: no

-- NULL 作为条件：NULL 被视为 FALSE
SELECT IF(NULL, 'yes', 'no');                -- 结果: no

-- 实际应用：条件标记
SELECT
    product_name,
    stock_quantity,
    IF(stock_quantity < 10, '库存不足', '库存充足') AS stock_status
FROM products;

-- 性别转换
SELECT
    name,
    IF(gender = 1, '男', '女') AS gender_text
FROM users;

-- 嵌套 IF（不推荐超过 2 层，复杂场景用 CASE）
SELECT
    score,
    IF(score >= 90, '优秀',
       IF(score >= 60, '及格', '不及格')) AS grade
FROM exam_results;

20.7.2 IFNULL(expr, replacement)

如果 expr 为 NULL，返回 replacement；否则返回 expr。这是处理 NULL 最常用的函数。

sql

-- 基本用法
SELECT IFNULL(NULL, 'default value');        -- 结果: default value
SELECT IFNULL('not null', 'default');        -- 结果: not null
SELECT IFNULL(NULL, 0);                      -- 结果: 0

-- 实际应用：计算时用 0 代替 NULL
SELECT
    product_name,
    price * IFNULL(discount_rate, 0) AS discount_amount
FROM products;

-- 累计求和（NULL 视为 0）
SELECT
    user_id,
    IFNULL(bonus_a, 0) + IFNULL(bonus_b, 0) AS total_bonus
FROM salaries;

-- 显示时的默认值
SELECT
    name,
    IFNULL(nickname, '未设置昵称') AS display_name
FROM users;

20.7.3 NULLIF(expr1, expr2)

如果 expr1 = expr2，返回 NULL；否则返回 expr1。常用于防止除零错误和其他需要将特定值转为 NULL 的场景。

sql

-- 基本用法
SELECT NULLIF(10, 10);                       -- 结果: NULL（相等则返回 NULL）
SELECT NULLIF(10, 20);                       -- 结果: 10（不等则返回第一个参数）

-- 核心用途：防止除零错误
-- 错误写法（会报错）：
-- SELECT amount / 0 FROM orders;

-- 正确写法：
SELECT amount / NULLIF(quantity, 0) AS unit_price
FROM order_items;
-- 当 quantity=0 时，NULLIF 返回 NULL → amount/NULL = NULL（不报错）

-- 防止求平均时除零
SELECT
    SUM(score) / NULLIF(COUNT(*), 0) AS avg_score
FROM student_scores;

-- 将空字符串转为 NULL
SELECT NULLIF(TRIM(name), '') AS clean_name
FROM imported_data;

-- 实际应用：标记零值为未知
SELECT
    product_name,
    NULLIF(price, 0) AS valid_price  -- 价格为 0 视为数据缺失
FROM products;

20.7.4 COALESCE(val1, val2, ...)

返回参数列表中第一个非 NULL 值。如果所有参数都是 NULL，则返回 NULL。

sql

-- 基本用法
SELECT COALESCE(NULL, NULL, 'third', 'fourth');   -- 结果: third
SELECT COALESCE(NULL, 0, 100);                     -- 结果: 0（0 不是 NULL）
SELECT COALESCE(NULL, NULL, NULL);                  -- 结果: NULL

-- 实际应用：多级默认值
SELECT
    name,
    COALESCE(mobile, phone, emergency_contact, '无联系方式') AS contact
FROM users;
-- 优先使用手机号，没有则用电话，再没有则用紧急联系人，都没有则显示"无联系方式"

-- 多列求最大值（处理 NULL）
SELECT
    id,
    GREATEST(COALESCE(score1, 0), COALESCE(score2, 0), COALESCE(score3, 0)) AS best_score
FROM student_scores;

-- COALESCE vs IFNULL
-- COALESCE 可接受多个参数，返回第一个非 NULL
-- IFNULL 只接受两个参数
-- COALESCE 是 SQL 标准，IFNULL 是 MySQL 特有
SELECT COALESCE(NULL, NULL, 'value');   -- 结果: value
-- SELECT IFNULL(NULL, NULL, 'value');  -- 语法错误！IFNULL 只接受 2 个参数

20.7.5 CASE 表达式

CASE 是 SQL 中最强大的条件表达式，有两种语法形式。

简单 CASE（Simple CASE）：

sql

-- 语法：
-- CASE value
--     WHEN compare_value1 THEN result1
--     WHEN compare_value2 THEN result2
--     [ELSE default_result]
-- END

-- 示例：状态码转换
SELECT
    order_id,
    CASE status
        WHEN 0 THEN '待支付'
        WHEN 1 THEN '已支付'
        WHEN 2 THEN '已发货'
        WHEN 3 THEN '已完成'
        WHEN 4 THEN '已取消'
        ELSE '未知状态'
    END AS status_text
FROM orders;

-- 星期几的中文显示
SELECT
    CASE DAYOFWEEK(NOW())
        WHEN 1 THEN '星期日'
        WHEN 2 THEN '星期一'
        WHEN 3 THEN '星期二'
        WHEN 4 THEN '星期三'
        WHEN 5 THEN '星期四'
        WHEN 6 THEN '星期五'
        WHEN 7 THEN '星期六'
    END AS weekday_cn;

搜索 CASE（Searched CASE）：

sql

-- 语法：
-- CASE
--     WHEN condition1 THEN result1
--     WHEN condition2 THEN result2
--     [ELSE default_result]
-- END

-- 示例：成绩等级
SELECT
    student_name,
    score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM exam_results;

CASE 高级应用：

sql

-- 1. 条件聚合（行转列/透视）
SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    SUM(CASE WHEN status = 1 THEN amount ELSE 0 END) AS paid_amount,
    SUM(CASE WHEN status = 2 THEN amount ELSE 0 END) AS refund_amount,
    COUNT(CASE WHEN status = 1 THEN 1 END) AS paid_count,
    COUNT(CASE WHEN status = 2 THEN 1 END) AS refund_count
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m');

-- 2. ORDER BY 中的条件排序
SELECT * FROM products
ORDER BY
    CASE
        WHEN category = '电子产品' THEN 1
        WHEN category = '家居用品' THEN 2
        ELSE 3
    END,
    price DESC;

-- 3. UPDATE 中的条件更新
UPDATE employees
SET bonus = CASE
    WHEN performance = 'A' THEN salary * 0.2
    WHEN performance = 'B' THEN salary * 0.1
    WHEN performance = 'C' THEN salary * 0.05
    ELSE 0
END;

-- 4. WHERE 中的动态条件（配合存储过程）
SELECT * FROM products
WHERE price > CASE
    WHEN @price_level = 'high' THEN 1000
    WHEN @price_level = 'medium' THEN 500
    WHEN @price_level = 'low' THEN 100
    ELSE 0
END;

20.8 综合实战示例

sql

-- 场景：用户消费行为分析报表
SELECT
    u.id AS user_id,
    CONCAT_WS(' ', u.first_name, u.last_name) AS full_name,

    -- 注册天数
    DATEDIFF(NOW(), u.registered_at) AS days_since_register,

    -- 首单距今天数
    DATEDIFF(NOW(), MIN(o.created_at)) AS days_since_first_order,

    -- 末单距今天数
    DATEDIFF(NOW(), MAX(o.created_at)) AS days_since_last_order,

    -- 用户生命周期（月）
    TIMESTAMPDIFF(MONTH, MIN(o.created_at), MAX(o.created_at)) AS lifecycle_months,

    -- 总消费金额
    COALESCE(SUM(o.amount), 0) AS total_spent,

    -- 平均订单金额
    ROUND(COALESCE(AVG(o.amount), 0), 2) AS avg_order_amount,

    -- 用户等级
    CASE
        WHEN COALESCE(SUM(o.amount), 0) >= 10000 THEN 'VIP'
        WHEN COALESCE(SUM(o.amount), 0) >= 5000 THEN 'Gold'
        WHEN COALESCE(SUM(o.amount), 0) >= 1000 THEN 'Silver'
        ELSE '普通用户'
    END AS user_level,

    -- 是否沉睡用户（超过 90 天未下单）
    IF(DATEDIFF(NOW(), MAX(o.created_at)) > 90, '是', '否') AS is_dormant,

    -- 注册月份
    DATE_FORMAT(u.registered_at, '%Y年%m月') AS register_month

FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id
HAVING total_spent > 0
ORDER BY total_spent DESC;

常见错误

NOW() 与 SYSDATE() 混淆导致复制问题
- 错误：在存储过程或函数中大量使用 SYSDATE()，在 STATEMENT 复制模式下主从数据不一致。
- 原因：SYSDATE() 返回实际执行时间，NOW() 返回语句开始时间。
- 解决：优先使用 NOW()；如必须用 SYSDATE()，设置 sysdate-is-now=1 或使用 ROW 格式复制。
DATEDIFF 和 TIMESTAMPDIFF 参数顺序混淆
- 错误：DATEDIFF('2024-01-01', '2024-01-10') 返回 -9 而非预期的 9。
- 原因：DATEDIFF(date1, date2) = date1 - date2；TIMESTAMPDIFF(unit, start, end) = end - start。
- 解决：记住 DATEDIFF 是"第一个减第二个"，TIMESTAMPDIFF 是"第二个减第一个"。
STR_TO_DATE 格式不匹配导致返回 NULL
- 错误：STR_TO_DATE('10/15/2024', '%Y-%m-%d') 返回 NULL。
- 原因：字符串格式（MM/DD/YYYY）与指定格式（YYYY-MM-DD）不匹配。
- 解决：仔细确认源数据的日期格式，使用正确的格式占位符。
DATE_FORMAT 中使用 TIME 类型的日期占位符
- 错误：使用 TIME_FORMAT('14:30:25', '%Y-%m-%d') 返回 '0000-00-00'。
- 原因：TIME_FORMAT 只能使用时间相关的占位符。
- 解决：时间格式化使用 TIME_FORMAT，日期时间格式化使用 DATE_FORMAT。
忽略 TIMESTAMPDIFF 的"未满"行为
- 错误：TIMESTAMPDIFF(YEAR, '2023-12-31', '2024-01-01') 期望返回 1，实际返回 0。
- 原因：TIMESTAMPDIFF 计算的是"完整经过"的单位数，两个日期之间未满一个完整年。
- 解决：如需按日历年份差，使用 YEAR(end) - YEAR(start)。

本章练习

日期格式化练习：假设一个表中 date_str 列存储了多种格式的日期字符串（'2024/10/15', '15-10-2024', 'October 15, 2024', '2024年10月15日'），编写查询使用 STR_TO_DATE 统一转换为 DATE 类型，并找出转换失败的记录。
用户留存分析：基于 users 表（registered_at）和 orders 表（user_id, created_at），编写查询计算：每日注册用户数、次日留存率（注册后第二天有订单的比例）、7 日留存率、30 日留存率。
订单时效分析：基于 orders 表（created_at, paid_at, shipped_at, delivered_at），计算每笔订单的：支付耗时（分钟）、发货耗时（小时）、配送耗时（天），并标记出每个环节超时的订单（支付 > 30 分钟、发货 > 24 小时、配送 > 7 天）。
使用 CASE 进行数据透视：有一张销售表 sales(product_id, sale_date, quantity)，使用 CASE 和 SUM 将数据透视成：产品 ID、Q1 销量、Q2 销量、Q3 销量、Q4 销量、全年总销量。
NULL 处理综合练习：创建一张包含大量 NULL 值的测试表，练习使用 IFNULL、COALESCE、NULLIF 进行各种场景的数据清洗和计算（除零保护、默认值填充、空字符串转 NULL、多列取非空值等）。
日期范围查询优化：编写查询统计某个时间范围内的数据。要求使用正确的日期范围写法（>= 开始日期 AND < 结束日期+1天）以确保能利用索引，并说明为什么 DATE(created_at) BETWEEN '2024-01-01' AND '2024-01-31' 不能有效利用索引。

第二十章 日期时间与条件函数 ​

本章目标 ​

前置知识 ​

20.1 获取当前日期时间 ​

20.1.1 NOW() 与 SYSDATE() —— 重要区别 ​

20.1.2 CURDATE() 和 CURTIME() ​

20.2 日期时间提取函数 ​

20.2.1 DATE(expr) 和 TIME(expr) ​

20.2.2 各时间组件提取函数 ​

20.3 日期格式化函数 ​

20.3.1 DATE_FORMAT(date, format) —— 完整格式说明 ​

20.3.2 TIME_FORMAT(time, format) ​

20.3.3 STR_TO_DATE(str, format) ​

20.4 日期运算函数 ​

20.4.1 DATE_ADD 和 DATE_SUB ​

20.4.2 INTERVAL 完整单位列表 ​

20.4.3 EXTRACT(unit FROM date) ​

20.5 日期差值函数 ​

20.5.1 DATEDIFF(date1, date2) ​

20.5.2 TIMEDIFF(time1, time2) ​

20.5.3 TIMESTAMPDIFF(unit, start, end) —— 更灵活的差值计算 ​

20.5.4 DATEDIFF 与 TIMESTAMPDIFF 对比 ​

20.6 UNIX 时间戳函数 ​

20.7 条件函数 ​

20.7.1 IF(expr, true_val, false_val) ​

20.7.2 IFNULL(expr, replacement) ​

20.7.3 NULLIF(expr1, expr2) ​

20.7.4 COALESCE(val1, val2, ...) ​

20.7.5 CASE 表达式 ​

20.8 综合实战示例 ​

常见错误 ​

本章练习 ​