第七章：常用函数与操作符 —— SQL 的魔法工具箱

核心摘要：
SQL 的强大不仅在于查数据，还在于处理数据。
本章将为你打开 MySQL 的内置函数库，从字符串处理的字节陷阱，到日期计算的千年虫问题，再到流程控制的逻辑艺术。
我们不仅教你怎么用，更会告诉你哪些函数会让索引失效，以及时区对时间函数的致命影响。

环境准备：
为了确保本章的所有示例（特别是字符串处理和报表生成）都能直接运行，我们需要先完善数据模型。本章我们将引入 users 表，并对 orders 表进行扩展。

---

7.0 环境准备 (Data Setup)

请务必执行以下 SQL，以构建完整的测试环境。我们将创建用户表，并给订单表增加备注字段。

USE shop_biz;

-- 1. 创建并初始化 users 表
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) CHARSET=utf8;

INSERT INTO users (user_id, username, email) VALUES 
(101, 'admin_zhang', 'zhang@example.com'),
(102, 'user_li', 'li@example.com'),
(103, 'adm_monitor', 'monitor@sys.com'), -- 用于测试前缀匹配
(104, 'guest_wang', NULL); -- 用于测试 NULL 处理

-- 2. 扩展 orders 表 (增加 user_note 字段用于字符串函数演示)
-- 如果你沿用第六章的表，需要执行 ALTER；如果是新环境，直接用下面的 CREATE
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2),
    user_note VARCHAR(255) COMMENT '用户备注',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) CHARSET=utf8;

INSERT INTO orders (order_id, user_id, total_amount, user_note, created_at) VALUES 
(1, 101, 5999.00, '  发顺丰快递  ', '2023-01-01 10:00:00'), -- 前后有空格，用于测试 TRIM
(2, 101, 13598.00, NULL, '2023-01-02 11:00:00'),
(3, 102, 3999.00, '请周末配送', '2023-01-03 12:00:00');

---

7.1 字符串函数 (String Functions)

处理文本数据是开发中最常见的需求。但要注意：MySQL 中的字符串函数通常是大小写不敏感的（取决于 Collation）。

7.1.1 拼接与截取

1. CONCAT(s1, s2, ...)
   • 作用：将多个字符串连接成一个。
   • 坑点：只要有一个参数是 NULL，结果就是 NULL！

   -- 尝试拼接用户名和备注
   SELECT CONCAT('User:', username, ' Note:', NULL) FROM users WHERE user_id = 101; 
   -- 结果：NULL
   

   • 解决：使用 CONCAT_WS(separator, s1, s2) (With Separator)，它会自动跳过 NULL。

   SELECT CONCAT_WS(' ', 'User:', username, NULL) FROM users WHERE user_id = 101; 
   -- 结果：'User: admin_zhang'
   

2. SUBSTRING(str, pos, len)
   • 作用：截取字符串。
   • 注意：MySQL 的下标从 1 开始，不是 0！

   SELECT SUBSTRING('Hello World', 1, 5); -- 结果：'Hello'
   

7.1.2 长度计算的字节陷阱

• LENGTH(str)：返回字符串的字节数。
• CHAR_LENGTH(str)：返回字符串的字符数。

实战演示（UTF8 编码下，一个汉字占 3 字节）：

SELECT LENGTH('中国');       -- 结果：6 (3+3)
SELECT CHAR_LENGTH('中国');  -- 结果：2

场景：如果你的数据库字段定义是 VARCHAR(10)，指的是 10 个字符，不是字节。但如果你在应用层限制输入长度，请务必区分字节和字符。

7.1.3 清理与转换

• TRIM(str): 去除首尾空格。
• UPPER(str) / LOWER(str): 大小写转换。

-- 清理订单备注中的空格，并转大写（虽然后者对中文没用）
SELECT order_id, TRIM(user_note) AS clean_note 
FROM orders 
WHERE order_id = 1;
-- 结果：'发顺丰快递' (原本前后有空格)

性能警示：
不要在 WHERE 条件的左侧使用函数！

-- 极慢：会导致索引失效，全表扫描
-- 即使 username 上有索引，因为对它做了 LEFT 操作，数据库必须把每一行都拿出来算一遍
SELECT * FROM users WHERE LEFT(username, 3) = 'adm';

-- 极快：走索引范围查询
SELECT * FROM users WHERE username LIKE 'adm%';

---

7.2 数值函数 (Numeric Functions)

虽然复杂的数学计算建议在应用层（Java/Python）做，但基本的统计计算还得靠 SQL。

1. 取整与四舍五入

   • CEIL(x): 向上取整 (Ceiling) -> CEIL(1.1) = 2
   • FLOOR(x): 向下取整 (Floor) -> FLOOR(1.9) = 1
   • ROUND(x, d): 四舍五入保留 d 位小数 -> ROUND(1.58, 1) = 1.6

2. 随机数

   • RAND(): 返回 0 到 1 之间的随机浮点数。
   • 坑点：ORDER BY RAND() 性能极差！不要用它来随机抽取数据（会把所有行加载到内存排序）。

---

7.3 日期时间函数 (Date and Time Functions) —— 最复杂的领域

时间处理是 Bug 的重灾区，主要源于格式和时区。

7.3.1 获取当前时间

• NOW(): 返回当前日期和时间 (YYYY-MM-DD HH:MM:SS)。在语句开始执行时就固定了。
• SYSDATE(): 返回函数执行时的实时时间。
  • 区别：如果一条 SQL 执行了 2 秒，NOW() 在这 2 秒内所有行是一样的，SYSDATE() 可能会变。推荐使用 NOW()。

7.3.2 日期格式化 (Format)

DATE_FORMAT(date, format) 是报表统计的神器。

-- 将订单创建时间转换为 '2023年01月' 格式
SELECT order_id, DATE_FORMAT(created_at, '%Y年%m月') AS month_str
FROM orders;

7.3.3 日期计算

1. DATE_ADD(date, INTERVAL expr unit)

   -- 推算 30 天后的时间（常用于会员过期计算）
   SELECT DATE_ADD(NOW(), INTERVAL 30 DAY);
   

2. DATEDIFF(date1, date2)

   • 计算两个日期相差的天数（date1 - date2）。

7.3.4 时区问题 (Timezone)

MySQL 的 TIMESTAMP 类型会受时区影响，而 DATETIME 不会。
最佳实践：建议服务器和数据库统一设置为 UTC 或者业务所在地时区（如 Asia/Shanghai），并在应用层处理时区转换。

---

7.4 流程控制函数 (Flow Control) —— SQL 中的 IF-ELSE

7.4.1 IF(expr1, expr2, expr3)

逻辑：如果 expr1 为真，返回 expr2，否则返回 expr3。

-- 检查订单是否有备注
SELECT order_id, IF(user_note IS NOT NULL, '有', '无') AS has_note
FROM orders;

7.4.2 IFNULL(expr1, expr2)

逻辑：如果 expr1 不是 NULL，返回 expr1，否则返回 expr2。
必用场景：聚合求和时防止返回 NULL。

-- 如果某个用户没有任何订单，SUM 结果是 NULL。用 IFNULL 转为 0。
SELECT IFNULL(SUM(total_amount), 0) FROM orders WHERE user_id = 99999;

7.4.3 CASE WHEN —— 复杂的条件判断

这是 SQL 中最强大的逻辑控制器。

-- 对订单金额进行分级
SELECT order_id, total_amount,
    CASE 
        WHEN total_amount > 10000 THEN '大额订单'
        WHEN total_amount > 1000 THEN '普通订单'
        ELSE '小额订单'
    END AS amount_level
FROM orders;

---

7.5 JSON 函数 (MySQL 5.7+ 特性)

MySQL 5.7 引入了原生的 JSON 类型。虽然我们这里的表没有定义 JSON 字段，但可以直接演示函数的用法。

-- 模拟从 JSON 字符串中提取数据
SELECT JSON_EXTRACT('{"id": 1, "name": "iPhone"}', '$.name'); 
-- 结果："iPhone"

---

7.6 综合实战：生成复杂的订单报表

需求：
生成一份报表，包含：订单号、下单日期（格式：2023-01-01）、订单金额、金额等级（>5000为大额）、是否有备注（去空格后判断长度）。

SELECT 
    order_id,
    DATE_FORMAT(created_at, '%Y-%m-%d') AS order_date,
    IFNULL(total_amount, 0) AS amount, -- 防止金额为 NULL
    
    -- 金额分级
    CASE 
        WHEN total_amount > 5000 THEN '大额订单'
        ELSE '普通订单'
    END AS amount_level,
    
    -- 备注状态：先 TRIM 去空格，再看 LENGTH 是否大于 0
    IF(LENGTH(TRIM(user_note)) > 0, '有备注', '无备注') AS has_note
    
FROM orders;

执行结果预期：

1. order_id=1: 大额订单, 有备注 (因为 user_note 是 ’ 发顺丰快递 ', TRIM 后有长度)
2. order_id=2: 大额订单, 无备注 (user_note 是 NULL, LENGTH 是 NULL, IF 判断为 False - 注：严格来说 LENGTH(NULL) 返回 NULL，在 IF 中 NULL 被视为 False，显示无备注，符合逻辑)
3. order_id=3: 普通订单, 有备注

通过本章，你手中的 SQL 武器库已经非常丰富了。下一章，我们将进入数据库领域的深水区：索引与性能优化。