当前位置： 首页 > news >正文

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

news 2025/8/15 11:16:29

以下对 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 函数在实际业务场景中的协同应用，通过组合使用不同类型的函数，可以高效地完成复杂的数据处理和分析任务。

http://www.xdnf.cn/news/1298125.html

相关文章：

  • ActionChains 鼠标操作笔记
  • PyCharm 2025.2：面向工程师的 AI 工具
  • IDEA、Pycharm、DataGrip等激活破解冲突问题解决方案之一
  • C# 中 ArrayList动态数组、List＜T＞列表与 Dictionary＜T Key, T Value＞字典的深度对比
  • 20道Vue框架相关前端面试题及答案
  • OpenCV ------图像基础处理（一）
  • Elasticsearch ABAC 配置：基于患者数据的动态访问控制
  • Exif.js获取手机拍摄照片的经纬度
  • 风电功率预测实战：从数据清洗到时空建模​​
  • 机器翻译：回译与低资源优化详解
  • C# 高并发处理方式
  • 【每天一个知识点】生物的数字孪生
  • 如何选择适合工业场景的物联网网关？
  • TWINCAT+COPLEY ethercat配置
  • week1-[分支嵌套]公因数
  • Cherryusb UAC例程对接STM32 SAI播放音乐和录音(上)=＞SAI+TX+RX+DMA的配置与音频回环测试
  • C++:浅尝gdb
  • 云计算-Docker Compose 实战：从OwnCloud、WordPress、SkyWalking、Redis ，Rabbitmq等服务配置实例轻松搞定
  • Mybatis学习笔记(七)
  • 《疯狂Java讲义（第3版）》学习笔记ch4
  • 分享10个ai生成ppt网站（附ai生成ppt入口）
  • 智慧工地从工具叠加到全要素重构的核心引擎
  • 跨域及解决方案
  • AI搜索重构下的GEO优化服务商格局观察
  • 一致性哈希Consistent Hashing
  • DAY 42 Grad-CAM与Hook函数
  • JS 解构赋值语法
  • 【OpenCV】Mat详解
  • docker compose部署mysql
  • 面试题之项目中灰度发布是怎么做的