MySQL函数-优快云博客

本文链接：https://blog.youkuaiyun.com/csdn_tom_168/article/details/148870533

MySQL函数是在数据库操作中用于处理数据、执行计算和转换数据格式的工具，在查询、更新和管理数据库时起着关键作用。 MySQL函数分为以下几类：

用于数值计算，支持基本运算、幂根、三角函数等。

基本运算
- ABS(x)：返回绝对值，如 ABS(-5) → 5。
- ROUND(x,d)：四舍五入到指定小数位，如 ROUND(3.1415, 2) → 3.14。
- TRUNCATE(x,d)：截断小数位，不四舍五入，如 TRUNCATE(3.99, 0) → 3。
取整函数
- CEIL(x)/CEILING(x)：向上取整，如 CEIL(3.1) → 4。
- FLOOR(x)：向下取整，如 FLOOR(3.9) → 3。
幂与根
- POWER(x,y)/POW(x,y)：计算 x 的 y 次方，如 POWER(2,3) → 8。
- SQRT(x)：平方根，如 SQRT(16) → 4。
随机数
- RAND()：生成 0~1 之间的随机浮点数，如 RAND() → 0.78234。
- RAND(seed)：指定种子值生成固定随机序列（用于测试）。
三角函数
- SIN(x)、COS(x)、TAN(x)：参数为弧度，如 SIN(PI()/2) → 1。

处理文本数据，包括拼接、截取、替换等操作。

拼接与截取
- CONCAT(str1, str2, ...)：连接多个字符串，如 CONCAT('Hello', ' ', 'World') → 'Hello World'。
- SUBSTRING(str, pos, len)：从 pos 位置开始截取长度为 len 的子串（位置从 1 开始），如 SUBSTRING('MySQL', 2, 3) → 'ySQ'。
- LEFT(str, len)/RIGHT(str, len)：分别取左/右侧 len 个字符，如 LEFT('Apple', 3) → 'App'。
大小写转换
- UPPER(str)/LOWER(str)：转换大小写，如 UPPER('mysql') → 'MYSQL'。
长度与位置
- LENGTH(str)：返回字节长度（多字节字符占多个字节），如 LENGTH('你好') → 6（UTF - 8 中一个汉字占 3 字节）。
- CHAR_LENGTH(str)：返回字符长度，如 CHAR_LENGTH('你好') → 2。
- POSITION(substr IN str)：返回子串首次出现的位置，如 POSITION('bc' IN 'abcde') → 2。
修剪与替换
- TRIM([remstr FROM] str)：删除字符串首尾的 remstr（默认删除空格），如 TRIM(' abc ') → 'abc'。
- REPLACE(str, from_str, to_str)：替换所有匹配的子串，如 REPLACE('abab', 'a', 'x') → 'xbxb'。

处理日期和时间值，支持格式化、计算和提取部分。

获取当前时间
- NOW()/CURRENT_TIMESTAMP()：返回当前日期时间，如 NOW() → 2023 - 10 - 01 14:30:25。
- CURDATE()/CURTIME()：分别返回当前日期和时间，如 CURDATE() → 2023 - 10 - 01。
提取部分
- YEAR(date)、MONTH(date)、DAY(date)：提取年、月、日，如 YEAR('2023 - 10 - 01') → 2023。
- HOUR(time)、MINUTE(time)、SECOND(time)：提取时、分、秒，如 MINUTE('14:30:25') → 30。
日期计算
- DATE_ADD(date, INTERVAL expr unit)/DATE_SUB(date, INTERVAL expr unit)：日期加减，如 DATE_ADD('2023 - 10 - 01', INTERVAL 1 DAY) → 2023 - 10 - 02。
- DATEDIFF(date1, date2)：计算天数差（date1 - date2），如 DATEDIFF('2023 - 10 - 05', '2023 - 10 - 01') → 4。
格式化
- DATE_FORMAT(date, format)：按指定格式输出日期，如 DATE_FORMAT('2023 - 10 - 01', '%Y年%m月%d日') → '2023年10月01日'。

用于逻辑判断和返回不同结果。

IF 语句
- IF(condition, value_if_true, value_if_false)：条件判断，如 IF(5 > 3, 'Yes', 'No') → 'Yes'。
NULL 值处理
- IFNULL(expr1, expr2)：若 expr1 为 NULL，则返回 expr2，如 IFNULL(NULL, 'Default') → 'Default'。
- COALESCE(val1, val2, ...)：返回第一个非 NULL 值，如 COALESCE(NULL, 1, 2) → 1。

CASE 语句

简单 CASE：

CASE expr
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

搜索 CASE：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

示例：

SELECT 
  CASE 
      WHEN score >= 90 THEN 'A'
      WHEN score >= 80 THEN 'B'
      ELSE 'C'
  END AS grade
FROM students;

用于对一组值进行计算并返回单个结果（常用于 GROUP BY 子句）。

显式转换数据类型。

CAST(expr AS type)：将 expr 转换为指定类型，如 CAST('123' AS SIGNED) → 123（字符串转整数）。
CONVERT(expr, type)：功能类似 CAST，如 CONVERT('2023 - 10 - 01', DATE) → 2023 - 10 - 01。

用于数据加密和哈希。

MD5(str)：计算 MD5 哈希值（16 字节），如 MD5('password') → '5f4dcc3b5aa765d61d8327deb882cf99'。
SHA2(str, hash_length)：计算 SHA - 2 哈希值（支持 224/256/384/512 位），如 SHA2('password', 256) → '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d154'。

返回数据库或服务器的元数据。

对查询结果的“窗口”（分组）进行计算（MySQL 8.0+ 支持）。

排名函数
- ROW_NUMBER()：为每行分配唯一排名，如 ROW_NUMBER() OVER (ORDER BY salary DESC)。
- RANK()：相同值排名相同，后续排名跳过，如 RANK() OVER (ORDER BY score DESC)。
- DENSE_RANK()：相同值排名相同，后续排名连续，如 DENSE_RANK() OVER (ORDER BY score DESC)。
聚合窗口
- SUM() OVER (PARTITION BY col ORDER BY col)：分组内累加，如计算部门累计销售额。

处理 JSON 类型数据（MySQL 5.7+ 支持）。

JSON_EXTRACT(json_doc, path)：提取 JSON 值，如 JSON_EXTRACT('{"name":"John","age":30}', '$.age') → 30。
JSON_SET(json_doc, path, val)：修改 JSON 值，如 JSON_SET('{"name":"John"}', '$.age', 30) → '{"name":"John","age":30}'。

NULL 值处理：多数函数遇到 NULL 时返回 NULL，需用 IFNULL 或 COALESCE 处理。
- 示例：IFNULL(commission, 0) 避免 commission 为 NULL 导致计算错误。
性能优化：避免在索引列上使用函数（如 WHERE YEAR(date_col) = 2023），会导致全表扫描。
- 优化方式：WHERE date_col >= '2023 - 01 - 01' AND date_col < '2024 - 01 - 01'。
兼容性：部分函数在不同版本中可能有差异，建议查阅对应版本文档。