SQL详细语法教程（三）mysql的函数知识

以下对 MySQL 中这几类函数进行超详细拆解，从基础语法、原理到复杂场景应用，帮你吃透这些函数：

一、字符串函数（String Functions）

字符串函数用于处理文本数据，从简单拼接、截取到复杂正则操作都能覆盖，是处理业务文本（如用户姓名、地址、日志内容）的核心工具。

1. 拼接与拆分

• CONCAT(str1, str2, ...)

  • 作用：拼接多个字符串，任意参数为NULL则结果为NULL。
  • 深度用法：结合COALESCE处理NULL，如CONCAT(COALESCE(name, '匿名'), '@', domain)，避免拼接出NULL。
  • 示例：

    SELECT CONCAT('MySQL', ' ', '8.4') AS full_version; 
    -- 结果：MySQL 8.4
    

• CONCAT_WS(separator, str1, str2, ...)

  • 作用：用separator拼接字符串，自动忽略NULL参数，适合拼接带分隔符的场景（如拼接地址）。
  • 底层逻辑：遍历参数，跳过NULL后用分隔符连接。
  • 示例：

    SELECT CONCAT_WS(', ', '北京', NULL, '朝阳区') AS address; 
    -- 结果：北京, 朝阳区
    

2. 长度与位置

• LENGTH(str) vs CHAR_LENGTH(str)

  • LENGTH：返回字符串字节长度，受字符集影响（如UTF-8中 1 个汉字占 3 字节）。
  • CHAR_LENGTH：返回字符个数，与字符集无关（1 个汉字算 1 个字符）。
  • 示例：

    SELECT LENGTH('Hello世界') AS byte_len,   -- 结果：9（H e l l o + 世界(3*2)）CHAR_LENGTH('Hello世界') AS char_len; -- 结果：7
    

• POSITION(substr IN str)

  • 作用：返回子串substr在str中首次出现的位置（从 1 开始计数），无匹配则返回 0。
  • 对比LOCATE(substr, str)：语法不同（LOCATE更灵活，支持pos参数指定起始位置）。
  • 示例：

    SELECT POSITION('SQL' IN 'MySQL8.4') AS pos; -- 结果：3（M y SQL...）
    

3. 截取与替换

• SUBSTRING(str, pos, len)（SUBSTR是别名）

  • 语法细节：pos为起始位置（1 开始），len可选（默认截取到末尾）；pos为负数时从末尾倒数。
  • 复杂场景：截取动态长度内容（如日志的前 100 字符）。
  • 示例：

    SELECT SUBSTRING('2025-08-14 12:00:00', 1, 10) AS date_part; -- 结果：2025-08-14
    SELECT SUBSTRING('HelloWorld', -5) AS last_five; -- 结果：World（从倒数第5位开始）
    

• REPLACE(str, old_sub, new_sub)

  • 深度用法：批量替换文本（如清洗脏数据中的非法字符）。
  • 对比正则替换（REGEXP_REPLACE）：REPLACE是精确替换，正则支持模糊匹配（需 MySQL 8.0+）。
  • 示例：

    SELECT REPLACE('MySQL5.7', '5.7', '8.4') AS updated; -- 结果：MySQL8.4
    

4. 格式化与转换

• TRIM([direction [remstr] FROM] str)

  • 细分用法：
    • TRIM(str)：默认去除两端空格。
    • TRIM(LEADING '0' FROM '00123')：去除开头的0。
    • TRIM(TRAILING '.' FROM '123.')：去除末尾的.。
  • 示例：

    SELECT TRIM('  MySQL  ') AS trimmed; -- 结果：MySQL
    SELECT TRIM(LEADING 'x' FROM 'xxTestxx') AS leading_trim; -- 结果：Testxx
    

• UPPER/LOWER(str)（UCASE/LCASE是别名）

  • 注意点：对非 ASCII 字符（如ß→SS）的转换依赖字符集规则，部分字符可能无法完美转换。
  • 业务场景：统一用户名格式（如存储时转小写）。
  • 示例：

    SELECT UPPER('mysql') AS upper_case, LOWER('MySQL') AS lower_case; 
    -- 结果：MYSQL, mysql
    

二、数值函数（Numeric Functions）

数值函数用于数学计算、统计分析，从简单加减到复杂金融计算（如利息、汇率）都离不开它们。

1. 基础运算

• ABS(x)：返回绝对值，支持整数、小数。

  • 示例：SELECT ABS(-12.3) AS abs_val; -- 结果：12.3

• MOD(x, y)（%是别名）

  • 细节：MOD返回与x同符号的余数，且y不能为 0（否则报错）。
  • 对比x % y：功能等价，但MOD是函数语法，%是运算符。
  • 示例：

    SELECT MOD(10, 3) AS mod_val, 10 % 3 AS mod_op; -- 结果：1, 1
    SELECT MOD(-10, 3) AS neg_mod; -- 结果：-1（因-10 = (-4)*3 + 2？不，实际计算：-10 = (-3)*3 + (-1)，所以余数-1）
    

2. 取整与舍入

• CEILING(x)/FLOOR(x)

  • CEILING：返回不小于 x 的最小整数（向上取整）。
  • FLOOR：返回不大于 x 的最大整数（向下取整）。
  • 示例：

    SELECT CEILING(3.14) AS ceil_val, FLOOR(3.99) AS floor_val; 
    -- 结果：4, 3
    SELECT CEILING(-3.14) AS neg_ceil; -- 结果：-3（因-3 ≥ -3.14）
    

• ROUND(x, d)

  • 规则：d是小数位数（默认 0，即取整）；d>0时保留d位小数，d<0时对整数部分舍入（如ROUND(1234.56, -2)→1200）。
  • 对比TRUNCATE(x, d)：ROUND是四舍五入，TRUNCATE是直接截断（如TRUNCATE(3.99, 1)→3.9）。
  • 示例：

    SELECT ROUND(3.14159, 2) AS round_2, ROUND(1234.567, -2) AS round_neg; 
    -- 结果：3.14, 1200
    

3. 幂与开方

• POW(x, y)/POWER(x, y)：返回x的y次幂（y为小数时计算开方，如POW(4, 0.5)→2）。

  • 示例：SELECT POW(2, 3) AS pow_val; -- 结果：8

• SQRT(x)：返回x的平方根（x≥0，否则返回NULL）。

  • 扩展：SQRT等价于POW(x, 0.5)，但更直观。
  • 示例：SELECT SQRT(16) AS sqrt_val; -- 结果：4

4. 随机数与进制转换

• RAND([seed])

  • 细节：seed为可选参数（整数），相同seed生成相同随机序列；无seed时每次结果不同。
  • 业务场景：生成验证码、随机抽样。
  • 示例：

    SELECT RAND() AS rand_val, RAND(123) AS fixed_rand; 
    -- 固定seed=123时，每次执行fixed_rand结果相同
    

• CONV(num, from_base, to_base)

  • 功能：将num从from_base进制转为to_base进制（支持 2~36 进制，超过 36 需自定义映射）。
  • 限制：num为字符串时，字符需符合from_base规则（如二进制只能是0/1）。
  • 示例：

    SELECT CONV('1010', 2, 10) AS bin_to_dec; -- 结果：10（二进制1010→十进制10）
    SELECT CONV('A', 16, 2) AS hex_to_bin; -- 结果：1010（十六进制A→二进制1010）
    

三、日期函数（Date/Time Functions）

日期函数是处理时间数据的核心，涉及业务逻辑（如订单时效、报表统计）、索引优化（合理用DATE类型）等。

1. 获取当前时间

• NOW()/SYSDATE()

  • 区别：
    • NOW()：执行 SQL 语句时开始的时间点（语句内所有NOW()值相同）。
    • SYSDATE()：返回实际执行到该函数时的时间（语句内多次调用可能不同）。
  • 示例：

    SELECT NOW() AS now_time, SYSDATE() AS sys_time, SLEEP(2), -- 休眠2秒NOW() AS now_time_after, SYSDATE() AS sys_time_after; 
    -- now_time和now_time_after相同，sys_time和sys_time_after差约2秒
    

• CURDATE()/CURTIME()

  • 简化版：CURDATE()返回YYYY-MM-DD，CURTIME()返回HH:MM:SS（带秒精度）。
  • 对比DATE(NOW())：功能等价，但CURDATE更简洁。
  • 示例：

    SELECT CURDATE() AS today, CURTIME(6) AS now_time; -- 结果：2025-08-14, 12:34:56.789012（假设当前时间）
    

2. 日期计算与差值

• DATE_ADD(date, INTERVAL expr unit)（ADDDATE是别名）

  • 灵活用法：支持复杂时间运算（如+1 YEAR 2 MONTHS 3 DAYS）。
  • 常用单位：YEAR/MONTH/DAY/HOUR/MINUTE/SECOND，还支持WEEK（周）、QUARTER（季度）等。
  • 示例：

    SELECT DATE_ADD('2025-08-14', INTERVAL 1 DAY) AS tomorrow; -- 结果：2025-08-15
    SELECT ADDDATE('2025-08-14', INTERVAL 30 MINUTE) AS later; -- 结果：2025-08-14 00:30:00（假设当前是0点）
    

• DATEDIFF(date1, date2)

  • 规则：返回date1 - date2的天数差（仅算日期部分，忽略时间）。
  • 对比TIMESTAMPDIFF(unit, start, end)：支持更细粒度（如小时、分钟差），且结果是end - start的差值。
  • 示例：

    SELECT DATEDIFF('2025-08-20', '2025-08-14') AS days_diff; -- 结果：6
    SELECT TIMESTAMPDIFF(HOUR, '2025-08-14 10:00', '2025-08-15 12:00') AS hour_diff; -- 结果：26（24+2）
    

3. 日期格式化

• DATE_FORMAT(date, format)
  • 格式符：
    • %Y（4 位年）、%y（2 位年）、%m（月份 01-12）、%c（月份 1-12）、%d（日 01-31）。
    • %H（24 小时制 00-23）、%h（12 小时制 01-12）、%i（分钟 00-59）、%s（秒 00-59）。
  • 复杂场景：生成带时区的时间字符串（需结合CONVERT_TZ）。
  • 示例：

    SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS formatted; 
    -- 结果：2025-08-14 12:34:56（假设当前时间）
    SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y') AS verbose; 
    -- 结果：Thursday, August 14, 2025（英文星期、月份）

4. 时间提取与转换

• EXTRACT(unit FROM date)

  • 功能：提取日期的部分字段（如年、月、日、小时等）。
  • 对比YEAR(date)/MONTH(date)：EXTRACT更灵活，支持组合单位（如EXTRACT(YEAR_MONTH FROM date)返回YYYYMM格式）。
  • 示例：

    SELECT EXTRACT(YEAR FROM '2025-08-14 12:00:00') AS year, EXTRACT(HOUR_MINUTE FROM '2025-08-14 12:34:56') AS hour_min; 
    -- 结果：2025, 1234（小时+分钟，无分隔）
    

• STR_TO_DATE(str, format)

  • 反向操作：将字符串按format转为日期类型（需严格匹配格式，否则返回NULL）。
  • 业务场景：处理用户输入的非标准日期（如'25/08/14'转DATE）。
  • 示例：

    SELECT STR_TO_DATE('14-08-2025', '%d-%m-%Y') AS converted_date; 
    -- 结果：2025-08-14（注意格式符要和字符串匹配）
    

四、流程函数（Control Flow Functions）

流程函数用于条件判断、分支逻辑，类似编程语言中的if-else、switch-case，是实现业务逻辑（如权限控制、数据分组）的关键。

1. 简单条件判断

• IF(expr, true_val, false_val)
  • 底层逻辑：expr为真（非NULL、非 0）则返回true_val，否则返回false_val。
  • 嵌套用法：IF可多层嵌套（但建议复杂逻辑用CASE，更清晰）。
  • 示例：

    SELECT score, IF(score >= 60, '及格', '不及格') AS pass_status 
    FROM students; 
    -- 根据score判断是否及格
    

2. 多分支条件（CASE语句）- 搜索CASE高级用法

CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2 ...] ELSE default END（搜索CASE）

• 灵活条件判断：支持任意布尔条件（非=比较），如范围判断、NULL检查。
• 性能与可读性：复杂逻辑下，搜索CASE比嵌套IF更易维护，且执行计划更清晰（MySQL 能优化条件顺序）。
• 示例：订单状态多条件处理

  SELECT order_status, CASE WHEN order_status = 0 THEN '未支付'WHEN order_status = 1 AND pay_time > NOW() - INTERVAL 1 HOUR THEN '支付中（1小时内）'WHEN order_status = 1 THEN '支付中（超时）'WHEN order_status = 2 AND refund_time IS NULL THEN '已完成（未退款）'ELSE '其他状态'END AS status_desc
  FROM orders;
  

  这里通过CASE处理了状态值 + 时间范围 + 关联字段（refund_time） 的多层逻辑，比嵌套IF简洁数倍。

3. CASE与聚合函数联动（分组条件统计）

核心场景：按动态条件分组统计，比如 “统计不同分数段的人数”。

• 示例：成绩分段统计

  SELECTCASE WHEN score >= 90 THEN 'A'WHEN score >= 80 THEN 'B'WHEN score >= 60 THEN 'C'ELSE 'D'END AS grade_level,COUNT(*) AS student_count
  FROM exam_scores
  GROUP BY grade_level;
  

  原理：CASE先为每行数据打标签（A/B/C/D），再通过GROUP BY按标签聚合，实现动态分组统计。

4. IFNULL/COALESCE - 空值兜底逻辑

• IFNULL(expr, replacement)：若expr为NULL，返回replacement；否则返回expr。
• COALESCE(expr1, expr2, ...)：返回第一个非NULL的参数，支持多参数（比IFNULL更灵活）。
• 底层区别：
  • IFNULL是 MySQL 特有函数，仅支持 2 个参数。
  • COALESCE是 SQL 标准函数，支持任意多参数，且在跨数据库迁移时更兼容。
• 示例：用户昵称兜底

  SELECT -- 优先用nickname，无则用username，还无则用'匿名用户'COALESCE(nickname, username, '匿名用户') AS display_name 
  FROM users;
  

5. NULLIF(expr1, expr2) - 空值等价判断

• 作用：若expr1 = expr2，返回NULL；否则返回expr1。常用于 “消除无效值” 场景（如订单金额为 0 时置为NULL）。
• 示例：清洗无效金额

  SELECT order_id,NULLIF(total_amount, 0) AS valid_amount -- 金额为0时返回NULL
  FROM orders;

补充：前几类函数的 “底层原理 + 高级优化 + 异常场景”

一、字符串函数 - 底层与优化

1. 字符集影响：

   • LENGTH依赖字符集（如UTF-8中CHAR_LENGTH('中文')=2，但LENGTH('中文')=6）。
   • 优化建议：若需 “字符个数”，优先用CHAR_LENGTH；若需 “存储长度”，用LENGTH。

2. 索引与字符串函数：

   • 危险操作：WHERE LOWER(username) = 'admin'会破坏索引（函数包裹字段）。
   • 优化方案：
     • 存储时统一大小写（如入库转小写），用WHERE username = 'admin'。
     • 或用函数索引（MySQL 8.0 + 支持）：ALTER TABLE users ADD INDEX idx_username (LOWER(username));

3. 超长字符串截断：

   • 异常场景：SUBSTRING(str, pos, len)中pos超过字符串长度，返回空字符串（而非报错）。
   • 防御性写法：结合CHAR_LENGTH判断，如：

     SELECT IF(CHAR_LENGTH(str) >= pos, SUBSTRING(str, pos, len), NULL) AS safe_substr;
     

二、数值函数 - 精度与性能

1. 浮点型精度陷阱：

   • ROUND(0.1 + 0.2, 1)结果是0.3（符合预期），但ROUND(0.3 - 0.1, 1)可能因二进制存储误差出现0.20000000000000001。
   • 解决方案：对金融场景，用DECIMAL类型（精确存储）替代FLOAT/DOUBLE，或用ROUND+CAST强制转换。

2. MOD函数的负数逻辑：

   • 原理：MOD(x, y)公式为 x - y * FLOOR(x / y)，所以MOD(-10, 3)结果是-1（因-10 = (-4)*3 + 2？不，实际计算：FLOOR(-10/3) = -4，所以-10 - 3*(-4) = 2？哦，这里之前的示例错误！正确计算：

     SELECT MOD(-10, 3); -- 结果：2（因为 -10 = (-4)*3 + 2 → 余数2）
     SELECT MOD(10, -3); -- 结果：-2（10 = (-4)*(-3) + (-2) → 余数-2）
     

   • 结论：MOD余数符号与x无关，取决于y的符号？不，实际是余数与x同符号（MySQL 官方文档定义：MOD(x, y)返回x - y * FLOOR(x / y)，所以符号由x决定）。

三、日期函数 - 索引与时区

1. 日期索引优化：

   • 坏案例：WHERE DATE(create_time) = '2025-08-14'会使create_time索引失效（函数包裹字段）。
   • 好方案：

     -- 改写为：
     WHERE create_time >= '2025-08-14 00:00:00' AND create_time < '2025-08-15 00:00:00'
     

     利用左闭右开区间，让create_time索引直接生效。

2. 时区问题：

   • NOW()返回的是数据库服务器时区时间（由system_time_zone和time_zone参数控制）。
   • 跨时区业务：用CONVERT_TZ转换时区，如：

     SELECT CONVERT_TZ(NOW(), 'SYSTEM', 'Asia/Shanghai') AS shanghai_time;
     

四、流程函数 - 执行顺序与短路逻辑

1. CASE的短路特性：

   • MySQL 中，CASE一旦匹配到第一个WHEN条件，会立即停止后续判断（短路执行）。
   • 应用：优先判断 “高优先级条件”（如WHEN order_status = -1 THEN '已删除'放最前，避免无效计算）。

2. IF与CASE的性能对比：

   • 简单条件（如IF(score>60, '及格', '不及格')）：IF略快（少一层语法解析）。
   • 复杂条件（3 个分支以上）：CASE更优（执行计划更清晰，且短路逻辑更彻底）。

实战案例

题目：电商订单数据统计分析

假设你有一个电商订单表orders，结构如下：

CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT,user_id INT NOT NULL,user_name VARCHAR(50) NOT NULL,product_name VARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL,quantity INT NOT NULL,create_time DATETIME NOT NULL,pay_time DATETIME NULL,status TINYINT NOT NULL COMMENT '0-未支付,1-已支付,2-已取消'
);

请编写一条 SQL 语句，实现以下分析需求：

1. 计算每个订单的总金额（单价 × 数量），并四舍五入保留 2 位小数
2. 提取订单创建日期（格式：YYYY 年 MM 月 DD 日）
3. 判断订单支付状态文本描述（已支付 / 未支付 / 已取消）
4. 计算订单从创建到支付的时长（分钟），未支付订单显示 "未支付"
5. 生成用户订单标识（格式：用户 ID_用户名_订单 ID）
6. 按订单创建日期分组，统计每天的总订单数、总销售金额

代码示例：

-- 电商订单数据统计分析解决方案
SELECT -- 5. 生成用户订单标识（字符串函数：CONCAT）CONCAT(user_id, '_', user_name, '_', order_id) AS user_order_id,-- 2. 提取订单创建日期（日期函数：DATE_FORMAT）DATE_FORMAT(create_time, '%Y年%m月%d日') AS create_date,-- 3. 判断订单支付状态文本描述（流程函数：CASE）CASE WHEN status = 1 THEN '已支付'WHEN status = 2 THEN '已取消'ELSE '未支付'END AS status_desc,-- 1. 计算订单总金额并四舍五入（数值函数：ROUND）ROUND(price * quantity, 2) AS total_amount,-- 4. 计算支付时长（日期函数：TIMESTAMPDIFF + 流程函数：IFNULL）IFNULL(TIMESTAMPDIFF(MINUTE, create_time, pay_time), '未支付') AS pay_duration_minutes,-- 6. 按日期分组统计（聚合函数）COUNT(order_id) OVER (PARTITION BY DATE(create_time)) AS daily_order_count,SUM(ROUND(price * quantity, 2)) OVER (PARTITION BY DATE(create_time)) AS daily_total_sales
FROM orders
ORDER BY create_time;-- 补充说明：
-- 1. 使用CONCAT函数拼接用户ID、用户名和订单ID，生成唯一标识
-- 2. 利用DATE_FORMAT将日期转换为中文格式
-- 3. 通过CASE语句将数字状态转换为可读性更高的文本
-- 4. 结合TIMESTAMPDIFF计算时间差，并用IFNULL处理未支付订单的NULL值
-- 5. 使用窗口函数（OVER + PARTITION BY）实现按日期分组统计
-- 6. ROUND函数确保金额计算结果保留2位小数，符合财务数据要求

这个案例展示了各类 MySQL 函数在实际业务场景中的协同应用，通过组合使用不同类型的函数，可以高效地完成复杂的数据处理和分析任务。