MySQL 函数

函数是指一段可以直接被另一段程序调用的程序或代码。MySQL 内置函数按用途分为四类：字符串函数、数值函数、日期函数、流程函数。典型用途：由入职日期算入职天数、按分数值判定等级、生成随机验证码、工号补零等。

一、字符串函数

函数	功能
`CONCAT(S1, S2, ... Sn)`	字符串拼接，将 S1、S2…Sn 拼接成一个字符串
`LOWER(str)`	将字符串 str 全部转为小写
`UPPER(str)`	将字符串 str 全部转为大写
`LPAD(str, n, pad)`	左填充，用字符串 pad 对 str 的左边填充，达到 n 个字符长度
`RPAD(str, n, pad)`	右填充，用字符串 pad 对 str 的右边填充，达到 n 个字符长度
`TRIM(str)`	去掉字符串头部和尾部的空格（中间空格不去）
`SUBSTRING(str, start, len)`	返回字符串 str 从 start 位置起的 len 个长度的子串

SELECT CONCAT('hello', ' mysql');   -- hello mysql
SELECT UPPER('hello');              -- HELLO
SELECT LPAD('01', 5, '-');          -- ---01
SELECT RPAD('01', 5, '-');          -- 01---
SELECT TRIM(' hello mysql ');       -- hello mysql
SELECT SUBSTRING('hello mysql', 1, 5);  -- hello

-- 案例：企业工号统一为 5 位，不足 5 位的在前面补 0
UPDATE emp SET workno = LPAD(workno, 5, '0');

二、数值函数

函数	功能
`CEIL(x)`	向上取整
`FLOOR(x)`	向下取整
`MOD(x, y)`	返回 x / y 的模（取余）
`RAND()`	返回 0 ~ 1 内的随机数
`ROUND(x, y)`	求参数 x 四舍五入的值，保留 y 位小数

SELECT CEIL(1.2);        -- 2
SELECT FLOOR(1.2);       -- 1
SELECT MOD(3, 4);        -- 3
SELECT RAND();           -- 0 ~ 1 之间的随机数
SELECT ROUND(2.345, 2);  -- 2.35

-- 案例：生成一个 6 位数的随机验证码
SELECT LPAD(ROUND(RAND() * 1000000), 6, '0');

三、日期函数

函数	功能
`CURDATE()`	返回当前日期
`CURTIME()`	返回当前时间
`NOW()`	返回当前日期和时间
`YEAR(date)` / `MONTH(date)` / `DAY(date)`	获取指定 date 的年 / 月 / 日
`DATE_ADD(date, INTERVAL expr type)`	返回一个日期/时间值加上一个时间间隔 expr 后的时间值
`DATEDIFF(date1, date2)`	返回起始时间 date1 和结束时间 date2 之间的天数

SELECT CURDATE();                                -- 当前日期
SELECT CURTIME();                                -- 当前时间
SELECT NOW();                                    -- 当前日期 + 时间
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());    -- 当前的年、月、日
SELECT DATE_ADD(NOW(), INTERVAL 70 MONTH);       -- 当前时间 + 70 个月
SELECT DATEDIFF(NOW(), '1995-08-29');            -- 距今的天数

-- 案例：查询所有员工的入职天数，并按入职天数倒序排序
SELECT name, DATEDIFF(NOW(), entrydate) AS entrydays FROM emp ORDER BY entrydays DESC;

四、流程函数

流程函数可以在 SQL 语句中实现条件筛选，提高语句效率。

函数	功能
`IF(value, t, f)`	如果 value 为 true，则返回 t，否则返回 f
`IFNULL(value1, value2)`	如果 value1 不为空，返回 value1，否则返回 value2
`CASE WHEN [val1] THEN [res1] ... ELSE [default] END`	如果 val1 为 true，返回 res1，… 否则返回 default 默认值
`CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END`	如果 expr 的值等于 val1，返回 res1，… 否则返回 default 默认值

SELECT IF(TRUE, 'ok', 'error');       -- ok
SELECT IFNULL(NULL, 'default');       -- default
SELECT IFNULL('ok', 'default');       -- ok

-- 案例：工作地址 北京/上海 → 一线城市，其它 → 二线城市
SELECT
    name,
    CASE workaddress WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END AS '工作地址'
FROM emp;

-- 案例：按分数段展示等级（>= 85 优秀，>= 60 及格，否则不及格）
CREATE TABLE score(
    id       INT         COMMENT 'ID',
    name     VARCHAR(20) COMMENT '姓名',
    math     INT         COMMENT '数学',
    english  INT         COMMENT '英语',
    chinese  INT         COMMENT '语文'
) COMMENT '学员成绩表';

INSERT INTO score (id, name, math, english, chinese)
VALUES (1, 'Tom', 67, 88, 95), (2, 'Rose', 23, 66, 90), (3, 'Jack', 56, 98, 76);

SELECT
    id,
    name,
    CASE WHEN math >= 85 THEN '优秀' WHEN math >= 60 THEN '及格' ELSE '不及格' END '数学',
    CASE WHEN english >= 85 THEN '优秀' WHEN english >= 60 THEN '及格' ELSE '不及格' END '英语',
    CASE WHEN chinese >= 85 THEN '优秀' WHEN chinese >= 60 THEN '及格' ELSE '不及格' END '语文'
FROM score;

五、速查总结

分类	函数
字符串函数	`CONCAT`、`LOWER`、`UPPER`、`LPAD`、`RPAD`、`TRIM`、`SUBSTRING`
数值函数	`CEIL`、`FLOOR`、`MOD`、`RAND`、`ROUND`
日期函数	`CURDATE`、`CURTIME`、`NOW`、`YEAR`、`MONTH`、`DAY`、`DATE_ADD`、`DATEDIFF`
流程函数	`IF`、`IFNULL`、`CASE [...] WHEN ... THEN ... ELSE ... END`

常用组合：LPAD(ROUND(RAND() * 1000000), 6, '0') 生成 6 位随机验证码。

MySQL 函数

一、字符串函数

二、数值函数

三、日期函数

四、流程函数

五、速查总结

相关