3.MYSQL函数

MYSQL函数

一，字符串函数

MySQL 提供了丰富的字符串处理函数，用于对文本数据进行操作、转换和分析。以下是常用字符串函数的分类详解及示例：

---

一、基本字符串操作

1. CONCAT(str1, str2, ...)
   功能：拼接多个字符串。
   示例：

   SELECT CONCAT('Hello', ' ', 'World');  -- 输出 'Hello World'
   

2. CONCAT_WS(separator, str1, str2, ...)
   功能：用指定分隔符拼接字符串（自动跳过 NULL）。
   示例：

   SELECT CONCAT_WS('-', '2023', '10', '01');  -- 输出 '2023-10-01'
   

3. SUBSTRING(str, start, length) 或 SUBSTR()
   功能：截取子字符串。
   示例：

   SELECT SUBSTRING('MySQL', 3, 2);  -- 输出 'SQ'（索引从1开始）
   

4. LEFT(str, length) / RIGHT(str, length)
   功能：从左/右截取指定长度的子字符串。
   示例：

   SELECT LEFT('Database', 4);  -- 输出 'Data'
   

5. LENGTH(str)
   功能：返回字符串的字节数（注意字符集影响）。
   示例：

   SELECT LENGTH('中文');  -- UTF-8 下输出 6（每个汉字占3字节）
   

6. CHAR_LENGTH(str) 或 CHARACTER_LENGTH()
   功能：返回字符数（与字节数无关）。
   示例：

   SELECT CHAR_LENGTH('中文');  -- 输出 2
   

---

二、字符串搜索与替换

1. INSTR(str, substr)
   功能：返回子字符串第一次出现的位置（找不到返回0）。
   示例：

   SELECT INSTR('hello world', 'wo');  -- 输出 7
   

2. LOCATE(substr, str, [start])
   功能：类似 INSTR，可指定起始位置。
   示例：

   SELECT LOCATE('l', 'hello', 3);  -- 从第3位开始查找，输出 4
   

3. REPLACE(str, from_str, to_str)
   功能：替换字符串中的子串。
   示例：

   SELECT REPLACE('foo bar foo', 'foo', 'baz');  -- 输出 'baz bar baz'
   

4. REVERSE(str)
   功能：反转字符串。
   示例：

   SELECT REVERSE('MySQL');  -- 输出 'LQSyM'
   

---

三、字符串格式化

1. UPPER(str) / LOWER(str)
   功能：转换为大写/小写。
   示例：

   SELECT UPPER('Hello');  -- 输出 'HELLO'
   

2. TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
   功能：去除字符串两端（或单端）的指定字符（默认空格）。
   示例：

   SELECT TRIM(LEADING '0' FROM '000123');  -- 输出 '123'
   

3. LPAD(str, length, pad_str) / RPAD()
   功能：用指定字符左/右填充字符串到固定长度。
   示例：

   SELECT LPAD('7', 3, '0');  -- 输出 '007'
   

4. FORMAT(number, decimal_places)
   功能：格式化数字为字符串（千位分隔符）。
   示例：

   SELECT FORMAT(1234567.89, 2);  -- 输出 '1,234,567.89'
   

---

四、正则表达式操作（MySQL 8.0+ 增强）

1. REGEXP_LIKE(str, pattern)
   功能：检查字符串是否匹配正则表达式。
   示例：

   SELECT 'test@example.com' REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';  -- 输出 1（匹配成功）
   

2. REGEXP_REPLACE(str, pattern, replacement)
   功能：使用正则表达式替换字符串。
   示例：

   SELECT REGEXP_REPLACE('Phone: 123-456-7890', '[0-9]{3}-[0-9]{3}-[0-9]{4}', '***-***-****');  -- 输出 'Phone: ***-***-****'
   

3. REGEXP_SUBSTR(str, pattern)
   功能：提取匹配正则表达式的子字符串。
   示例：

   SELECT REGEXP_SUBSTR('Date: 2023-10-01', '[0-9]{4}-[0-9]{2}-[0-9]{2}');  -- 输出 '2023-10-01'
   

---

五、其他实用函数

1. GROUP_CONCAT(expr [ORDER BY ...] [SEPARATOR sep])
   功能：将多行结果拼接为单个字符串（常用于聚合查询）。
   示例：

   SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM users;  -- 输出 'Alice, Bob, Charlie'
   

2. SUBSTRING_INDEX(str, delim, count)
   功能：按分隔符截取部分字符串。
   示例：

   SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);  -- 输出 'www.mysql'
   

3. STRCMP(str1, str2)
   功能：比较字符串（返回 -1, 0, 1）。
   示例：

   SELECT STRCMP('apple', 'banana');  -- 输出 -1（按字典序比较）
   

---

六、注意事项

1. 字符串索引从1开始，而非0。
2. 字符集影响结果：如 LENGTH() 在 UTF-8 和 Latin1 中的表现不同。
3. NULL处理：多数函数遇到 NULL 参数会返回 NULL。
4. 性能优化：避免在大数据集上频繁使用正则表达式或复杂字符串操作。

---

示例综合应用

-- 格式化电话号码：将 '1234567890' 转为 '(123) 456-7890'
SELECT CONCAT(
    '(', 
    SUBSTRING(phone, 1, 3), 
    ') ', 
    SUBSTRING(phone, 4, 3), 
    '-', 
    SUBSTRING(phone, 7)
) AS formatted_phone
FROM contacts;

-- 提取邮箱域名
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain FROM users;

---

二，数值函数

MySQL 提供了丰富的数值处理函数，用于数学计算、数据格式化、随机数生成等场景。以下是常用数值函数的分类详解及示例：

---

一、基础数学运算

1. ABS(x)
   功能：返回绝对值。
   示例：

   SELECT ABS(-10);  -- 输出 10
   

2. CEIL(x) 或 CEILING(x)
   功能：向上取整（返回不小于 x 的最小整数）。
   示例：

   SELECT CEIL(3.2);  -- 输出 4
   

3. FLOOR(x)
   功能：向下取整（返回不大于 x 的最大整数）。
   示例：

   SELECT FLOOR(3.8);  -- 输出 3
   

4. ROUND(x, [d])
   功能：四舍五入到指定小数位数（默认为 0）。
   示例：

   SELECT ROUND(3.1415, 2);  -- 输出 3.14
   SELECT ROUND(3.5);       -- 输出 4
   

5. TRUNCATE(x, d)
   功能：直接截断到指定小数位数（不四舍五入）。
   示例：

   SELECT TRUNCATE(3.1415, 2);  -- 输出 3.14
   SELECT TRUNCATE(3.999, 0);   -- 输出 3
   

---

二、算术运算与符号处理

1. MOD(x, y)
   功能：取模运算（返回 x 除以 y 的余数）。
   示例：

   SELECT MOD(10, 3);  -- 输出 1
   

2. POW(x, y) 或 POWER(x, y)
   功能：计算 x 的 y 次方。
   示例：

   SELECT POW(2, 3);  -- 输出 8
   

3. SQRT(x)
   功能：返回 x 的平方根。
   示例：

   SELECT SQRT(16);  -- 输出 4
   

4. SIGN(x)
   功能：返回数值的符号（正数返回 1，负数返回 -1，0 返回 0）。
   示例：

   SELECT SIGN(-5);  -- 输出 -1
   

---

三、对数与指数函数

1. EXP(x)
   功能：返回 e 的 x 次方（自然指数）。
   示例：

   SELECT EXP(1);  -- 输出约 2.71828
   

2. LN(x)
   功能：返回自然对数（以 e 为底）。
   示例：

   SELECT LN(EXP(2));  -- 输出 2
   

3. LOG([base], x)
   功能：返回指定底数的对数（若省略 base，则为自然对数）。
   示例：

   SELECT LOG(2, 8);  -- 输出 3（即 log₂8）
   

4. LOG10(x)
   功能：返回以 10 为底的对数。
   示例：

   SELECT LOG10(1000);  -- 输出 3
   

---

四、三角函数

1. SIN(x)
   功能：计算正弦值（x 为弧度）。
   示例：

   sSELECT SIN(PI()/2);  -- 输出 1
   

2. COS(x)
   功能：计算余弦值。
   示例：

   SELECT COS(0);  -- 输出 1
   

3. TAN(x)
   功能：计算正切值。
   示例：

   SELECT TAN(PI()/4);  -- 输出约 1
   

4. PI()
   功能：返回圆周率 π 的值（约 3.141593）。
   示例：

   SELECT PI();  -- 输出 3.141593
   

---

五、随机数生成

1. RAND([seed])
   功能：生成 0 到 1 之间的随机浮点数（可指定种子）。
   示例：

   SELECT RAND();          -- 输出随机数，如 0.5487
   SELECT FLOOR(RAND()*100);  -- 生成 0~99 的随机整数
   

---

六、聚合函数（数值统计）

1. SUM(expr)
   功能：计算列的总和。
   示例：

   SELECT SUM(salary) FROM employees;
   

2. AVG(expr)
   功能：计算列的平均值。
   示例：

   SELECT AVG(score) FROM exams;
   

3. MAX(expr) / MIN(expr)
   功能：返回列的最大值/最小值。
   示例：

   SELECT MAX(temperature) FROM weather_data;
   

---

七、数值格式化

1. FORMAT(x, d)
   功能：将数值格式化为易读字符串（添加千位分隔符，保留 d 位小数）。
   示例：

   SELECT FORMAT(1234567.89, 2);  -- 输出 '1,234,567.89'
   

---

八、注意事项

1. 精度问题：浮点运算可能存在精度丢失（如 0.1 + 0.2 ≠ 0.3），建议使用 DECIMAL 类型处理精确计算（如财务数据）。
2. NULL 处理：若参数为 NULL，大多数函数返回 NULL。
3. 溢出处理：超出范围的值可能导致错误或截断。
4. 版本差异：部分函数（如 ROUND 的行为）可能因 MySQL 版本不同而有细微差异。

---

综合应用示例

-- 计算圆的面积（保留两位小数）
SELECT ROUND(PI() * POW(radius, 2), 2) AS area FROM circles;

-- 生成 1~100 的随机整数
SELECT FLOOR(1 + RAND() * 100) AS random_number;

-- 统计订单总金额（精确到分）
SELECT SUM(TRUNCATE(price * quantity, 2)) AS total FROM orders;

---

三，日期函数

MySQL 提供了丰富的日期和时间处理函数，用于操作、格式化、计算日期和时间数据。以下是常用日期函数的分类详解及示例：

---

一、获取当前日期和时间

1. NOW()
   功能：返回当前日期和时间（格式为 YYYY-MM-DD HH:MM:SS）。
   示例：

   SELECT NOW();  -- 输出 '2023-10-01 14:30:45'
   

2. CURDATE()
   功能：返回当前日期（格式为 YYYY-MM-DD）。
   示例：

   SELECT CURDATE();  -- 输出 '2023-10-01'
   

3. CURTIME()
   功能：返回当前时间（格式为 HH:MM:SS）。
   示例：

   SELECT CURTIME();  -- 输出 '14:30:45'
   

4. SYSDATE()
   功能：与 NOW() 类似，但 SYSDATE() 返回函数执行时的时间（NOW() 返回语句开始执行的时间）。
   示例：

   SELECT SYSDATE();
   

---

二、日期计算与调整

1. DATE_ADD(date, INTERVAL expr unit)
   功能：向日期添加指定的时间间隔。
   支持单位：DAY, MONTH, YEAR, HOUR, MINUTE, SECOND 等。
   示例：

   SELECT DATE_ADD('2023-10-01', INTERVAL 7 DAY);  -- 输出 '2023-10-08'
   SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR);      -- 当前时间加2小时
   

2. DATE_SUB(date, INTERVAL expr unit)
   功能：从日期减去指定的时间间隔。
   示例：

   SELECT DATE_SUB('2023-10-01', INTERVAL 3 MONTH);  -- 输出 '2023-07-01'
   

3. DATEDIFF(date1, date2)
   功能：返回两个日期之间的天数差（date1 - date2）。
   示例：

   SELECT DATEDIFF('2023-10-10', '2023-10-01');  -- 输出 9
   

4. TIMESTAMPDIFF(unit, start_date, end_date)
   功能：返回两个日期之间的差值（以指定单位表示）。
   支持单位：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND。
   示例：

   SELECT TIMESTAMPDIFF(YEAR, '2000-05-15', CURDATE());  -- 计算年龄（年）
   

---

三、日期部分提取

1. YEAR(date)
   功能：提取年份。
   示例：

   SELECT YEAR('2023-10-01');  -- 输出 2023
   

2. MONTH(date) / DAY(date)
   功能：提取月份/日。
   示例：

   SELECT MONTH('2023-10-01');  -- 输出 10
   SELECT DAY('2023-10-01');    -- 输出 1
   

3. HOUR(time) / MINUTE(time) / SECOND(time)
   功能：提取时间的小时、分钟、秒。
   示例：

   SELECT HOUR('14:30:45');  -- 输出 14
   

4. DAYOFWEEK(date)
   功能：返回星期几（1=周日，2=周一，…，7=周六）。
   示例：

   SELECT DAYOFWEEK('2023-10-01');  -- 输出 1（周日）
   

5. DAYNAME(date)
   功能：返回星期名称（如 Monday）。
   示例：

   SELECT DAYNAME('2023-10-01');  -- 输出 'Sunday'
   

6. LAST_DAY(date)
   功能：返回月份的最后一天。
   示例：

   SELECT LAST_DAY('2023-02-15');  -- 输出 '2023-02-28'
   

---

四、日期格式化与解析

1. DATE_FORMAT(date, format)
   功能：按指定格式显示日期。
   常用格式符：

   • %Y：4位年份
   • %m：月份（01-12）
   • %d：日（01-31）
   • %H：小时（00-23）
   • %i：分钟（00-59）
   • %s：秒（00-59）
   • %W：星期名称（如 Sunday）
     示例：

   SELECT DATE_FORMAT(NOW(), '%Y/%m/%d %H:%i:%s');  -- 输出 '2023/10/01 14:30:45'
   

2. STR_TO_DATE(str, format)
   功能：将字符串转换为日期（需指定格式）。
   示例：

   SELECT STR_TO_DATE('01-10-2023', '%d-%m-%Y');  -- 输出 '2023-10-01'
   

---

五、其他实用函数

1. UNIX_TIMESTAMP([date])
   功能：返回当前或指定日期的时间戳（秒级，从1970-01-01起）。
   示例：

   SELECT UNIX_TIMESTAMP('2023-10-01 00:00:00');  -- 输出 1696118400
   

2. FROM_UNIXTIME(timestamp, [format])
   功能：将时间戳转换为日期格式。
   示例：

   SELECT FROM_UNIXTIME(1696118400);  -- 输出 '2023-10-01 00:00:00'
   

3. TIMEDIFF(time1, time2)
   功能：返回两个时间的差值（格式为 HH:MM:SS）。
   示例：

   SELECT TIMEDIFF('18:00:00', '14:30:00');  -- 输出 '03:30:00'
   

---

六、注意事项

1. 日期合法性：无效日期（如 2023-02-30）可能返回 NULL 或警告。
2. 时区处理：
   • 默认使用系统时区，可通过 SET time_zone = '+08:00'; 调整。
   • CONVERT_TZ(dt, from_tz, to_tz) 函数可转换时区。
3. 日期范围：
   • DATE 支持范围：1000-01-01 到 9999-12-31。
   • TIMESTAMP 支持范围：1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。
4. 性能优化：避免在 WHERE 条件中对日期列使用函数（可能无法使用索引）。

---

七、综合示例

-- 1. 计算员工年龄（精确到年）
SELECT name, TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age FROM employees;

-- 2. 生成最近7天的日期范围
SELECT DATE_SUB(CURDATE(), INTERVAL n DAY) AS date
FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) nums;

-- 3. 格式化日期为中文显示
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时%i分') AS formatted_date;  -- 输出 '2023年10月01日 14时30分'

---

四，流程控制函数

MySQL 的流程控制函数允许在查询中实现条件判断和逻辑分支，类似于编程语言中的 if-else 或 switch-case 结构。以下是常用流程控制函数的详细说明及示例：

---

一、基础条件判断

1. IF(condition, value_if_true, value_if_false)

功能：根据条件返回两个值中的一个。
示例：

-- 判断成绩是否及格
SELECT name, IF(score >= 60, '及格', '不及格') AS result FROM exams;

应用场景：

• 简单的二选一逻辑。
• 数据分类标记（如是否有效、是否激活）。

---

2. IFNULL(expr1, expr2)

功能：若 expr1 为 NULL，返回 expr2，否则返回 expr1。
示例：

-- 处理可能为 NULL 的字段
SELECT product, IFNULL(stock, 0) AS stock_qty FROM inventory;

应用场景：

• 替换 NULL 值为默认值（如 0、空字符串）。
• 避免计算时因 NULL 导致错误。

---

3. NULLIF(expr1, expr2)

功能：若 expr1 = expr2，返回 NULL，否则返回 expr1。
示例：

-- 避免除零错误
SELECT revenue / NULLIF(users, 0) AS revenue_per_user FROM metrics;

应用场景：

• 防止无效运算（如除以零）。
• 隐藏重复值（将特定值转为 NULL）。

---

二、多条件分支

1. CASE 表达式

功能：实现多条件分支逻辑，支持两种语法形式：
语法 1（值匹配）：

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

语法 2（条件判断）：

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

示例：

-- 根据分数划分等级（值匹配）
SELECT name,
    CASE FLOOR(score / 10)
        WHEN 9 THEN 'A'
        WHEN 8 THEN 'B'
        WHEN 7 THEN 'C'
        ELSE 'D'
    END AS grade
FROM exams;

-- 根据订单金额分类（条件判断）
SELECT order_id,
    CASE
        WHEN amount > 1000 THEN '高价值'
        WHEN amount BETWEEN 500 AND 1000 THEN '中价值'
        ELSE '低价值'
    END AS category
FROM orders;

应用场景：

• 数据分桶（如价格区间、年龄分组）。
• 动态字段生成（如状态描述、优先级标签）。

---

三、流程控制函数对比

函数/表达式	适用场景	特点
IF()	简单二选一逻辑	语法简洁，适合快速判断
CASE	多分支复杂逻辑	灵活性高，支持条件表达式和值匹配
IFNULL()	处理 NULL 默认值	专用于 NULL 替换
NULLIF()	特定值转 NULL 或避免错误	用于防御性编程

---

四、注意事项

1. 性能优化：
   • 避免在 WHERE 子句中使用流程控制函数（可能无法利用索引）。
   • 复杂逻辑尽量在应用层处理，减少数据库计算压力。
2. 可读性：
   • 嵌套过多 IF 或 CASE 会降低可维护性，建议拆分逻辑或添加注释。
3. 兼容性：
   • IF 是 MySQL 特有语法，其他数据库（如 PostgreSQL）使用 CASE 替代。
   • CASE 表达式是 SQL 标准，跨数据库兼容性更好。
4. NULL 处理：
   • 注意 CASE 中未覆盖的条件可能返回 NULL，建议始终包含 ELSE 子句。

---

五、综合应用示例

场景：用户活跃状态标记

SELECT user_id,
    CASE
        WHEN last_login >= CURDATE() - INTERVAL 7 DAY THEN '活跃'
        WHEN last_login >= CURDATE() - INTERVAL 30 DAY THEN '一般'
        ELSE '沉默'
    END AS activity_status,
    IFNULL(email, '未绑定') AS email_status
FROM users;

输出示例：

+---------+-----------------+--------------+
| user_id | activity_status | email_status |
+---------+-----------------+--------------+
| 1001    | 活跃            | user@example.com |
| 1002    | 一般            | 未绑定        |
+---------+-----------------+--------------+

---

六、最佳实践

1. 明确默认值：始终为 CASE 表达式提供 ELSE 分支，避免意外 NULL。
2. 测试边界条件：确保分支覆盖所有可能值（如数值区间的端点）。
3. 优先使用 CASE：复杂逻辑使用 CASE 更易维护，而非嵌套多个 IF。

---