返回文章列表 →

Note

函数

记录 MySQL 常用内置函数的语法和示例

发布于 更新于

函数

函数可以出现在 SELECT、WHERE、ORDER BY、UPDATE 等语句中，常用于处理字符串、数值和日期，或者根据条件生成不同的结果。

本文介绍四类常用函数：

  • 字符串函数：拼接、转换、填充、清理或截取字符串。
  • 数值函数：取整、求余、生成随机数或保留指定小数位。
  • 日期函数：获取当前日期时间、提取日期部分或进行日期计算。
  • 流程控制函数：根据条件选择返回结果。

字符串函数

字符串函数用于处理字符类型的数据。常用函数如下：

函数 作用
CONCAT(s1, s2, ..., sn) 按参数顺序拼接字符串；任意参数为 NULL 时返回 NULL
LOWER(str) 将字符串转换为小写
UPPER(str) 将字符串转换为大写
LPAD(str, len, padstr) 在字符串左侧重复填充 padstr，使结果达到 len 个字符
RPAD(str, len, padstr) 在字符串右侧重复填充 padstr，使结果达到 len 个字符
TRIM(str) 删除字符串两端的空格
SUBSTRING(str, start[, len]) 从 start 位置开始截取字符串；指定 len 时最多返回 len 个字符

说明

  1. LOWER 和 UPPER 的转换结果受字符集和排序规则影响，对 BINARY、VARBINARY、BLOB 等二进制字符串不会执行大小写转换。

  2. LPAD 和 RPAD 的长度按字符计算。如果原字符串已经超过 len，结果会被截短为前 len 个字符，而不是保持原值不变。

  3. TRIM(str) 默认只删除两端的空格，也可以使用完整语法删除指定前缀或后缀：

    TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
    • BOTH：同时处理两端，是默认方式。
    • LEADING：只处理开头。
    • TRAILING：只处理结尾。
    • remstr：需要删除的字符串；省略时删除空格。

  4. SUBSTRING 的第一个字符位置是 1。start 为负数时从字符串末尾反向定位，为 0 时返回空字符串。SUBSTR 和 MID 是它的同义写法。

数值函数

数值函数用于计算和转换数值。常用函数如下：

函数 作用
CEIL(x)、CEILING(x) 返回不小于 x 的最小整数，即向上取整
FLOOR(x) 返回不大于 x 的最大整数，即向下取整
MOD(x, y) 返回 x 除以 y 的余数；y 为 0 时返回 NULL
RAND([seed]) 返回满足 0 <= value < 1 的伪随机浮点数
ROUND(x[, y]) 将 x 四舍五入到 y 位小数；省略 y 时默认取 0 位小数

说明

  1. MOD(x, y) 还可以写成 x % y 或 x MOD y。
  2. RAND(seed) 可以指定种子；使用相同的常量种子能够生成可重复的随机数序列，便于测试。
  3. ROUND 的第二个参数也可以是负数，此时会对小数点左侧的位数进行舍入。例如，ROUND(125, -1) 返回 130。对于精确数值和浮点近似数，恰好位于中间值时所采用的舍入规则可能不同；金额等需要确定精度的数据应使用 DECIMAL，避免依赖 FLOAT 或 DOUBLE 的近似结果。

日期函数

日期函数用于获取、提取和计算日期时间。常用函数如下：

函数 作用
CURDATE() 返回当前日期
CURTIME([fsp]) 返回当前时间；fsp 可以指定 0～6 位小数秒精度
NOW([fsp]) 返回当前日期和时间；fsp 可以指定 0～6 位小数秒精度
YEAR(date) 返回日期中的年份
MONTH(date) 返回日期中的月份
DAY(date) 返回日期是当月的第几天，是 DAYOFMONTH 的同义写法
DATE_ADD(date, INTERVAL expr unit) 将指定时间间隔加到日期或日期时间上
DATEDIFF(date1, date2) 返回 date1 - date2 的天数，只使用参数的日期部分

说明

  1. DATE_ADD 常用的时间单位包括 YEAR、MONTH、DAY、HOUR、MINUTE 和 SECOND。返回值类型会根据传入值和时间单位确定。

  2. DATEDIFF 的参数顺序会影响结果：第一个日期晚于第二个日期时返回正数，早于第二个日期时返回负数。即使参数包含时间部分，计算时也只比较日期部分。

  3. CURDATE、CURTIME 和 NOW 使用当前会话时区，并在一条语句开始执行时求值；同一条语句中多次调用会得到一致的当前时间结果。

流程控制函数

流程控制函数或表达式可以根据条件返回不同结果，常用于状态转换、空值处理和数据分级。

函数或表达式 作用
IF(condition, value_if_true, value_if_false) 条件为真时返回第二个参数，否则返回第三个参数
IFNULL(value1, value2) value1 不为 NULL 时返回 value1，否则返回 value2
CASE WHEN condition THEN result ... ELSE default END 按顺序判断条件，返回第一个成立条件对应的结果
CASE expression WHEN value THEN result ... ELSE default END 将表达式依次与各个值比较，返回第一个相等值对应的结果

说明

  1. IFNULL 只判断 NULL，不会把 0 或空字符串 '' 当作空值。例如，IFNULL('', '默认值') 返回的仍然是空字符串。

  2. 两种 CASE 都会从上到下匹配，并在找到第一个满足条件的分支后返回结果。如果没有分支匹配，存在 ELSE 时返回其结果；省略 ELSE 时返回 NULL。

示例

数据准备

DROP TABLE IF EXISTS emp;

CREATE TABLE emp (
    id INT PRIMARY KEY COMMENT '编号',
    workno VARCHAR(10) COMMENT '工号',
    name VARCHAR(10) COMMENT '姓名',
    gender CHAR(1) COMMENT '性别',
    age TINYINT UNSIGNED COMMENT '年龄',
    idcard CHAR(18) COMMENT '身份证号',
    workaddress VARCHAR(50) COMMENT '工作地址',
    entrydate DATE COMMENT '入职时间'
) COMMENT = '员工表';

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES
    (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'),
    (2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),
    (3, '3', '韦一笑', '男', 38, '123456789012345671', '上海', '2005-08-01'),
    (4, '4', '赵敏', '女', 18, '123456789012345672', '北京', '2009-12-01'),
    (5, '5', '小昭', '女', 16, '12345678901234567X', '上海', '2007-07-01'),
    (6, '6', '杨逍', '男', 28, NULL, '杭州', '2006-01-01'),
    (7, '7', '周芷若', '女', 24, '123456789012345674', '杭州', '2010-05-01'),
    (8, '8', '张三丰', '男', 88, NULL, '武汉', '1980-01-01'),
    (9, '9', '灭绝师太', '女', 40, '123456789012345676', '北京', '1990-01-01'),
    (10, '10', '范遥', '男', 35, '123456789012345677', '西安', '2008-03-01');

字符串函数

CONCAT

拼接员工姓名和工作地址：

SELECT name,
       CONCAT(name, '（', workaddress, '）') AS employee_info
FROM emp;

如果参与拼接的值可能为 NULL，可以先使用 IFNULL 提供替代值：

SELECT name,
       CONCAT(name, '：', IFNULL(idcard, '未填写')) AS identity_info
FROM emp;
LOWER

生成小写形式的员工代码：

SELECT name,
       LOWER(CONCAT('EMP-', workno)) AS lowercase_code
FROM emp;
UPPER

生成大写形式的员工代码：

SELECT name,
       UPPER(CONCAT('emp-', workno)) AS uppercase_code
FROM emp;
LPAD

在工号左侧补 0，将查询结果统一显示为 5 位：

SELECT workno,
       LPAD(workno, 5, '0') AS padded_workno
FROM emp;

这里只转换查询结果，不会修改表中保存的 workno。

RPAD

在工号右侧补 -，将查询结果统一显示为 5 个字符：

SELECT workno,
       RPAD(workno, 5, '-') AS padded_workno
FROM emp;
TRIM

删除字符串两端的空格，并演示删除指定前后缀：

SELECT TRIM('  Hello MySQL  ') AS trimmed_text;

SELECT TRIM(BOTH 'x' FROM 'xxxMySQLxxx') AS trimmed_marker;

第一个查询返回 Hello MySQL，第二个查询返回 MySQL。

SUBSTRING

截取身份证号前 6 个字符：

SELECT name,
       SUBSTRING(idcard, 1, 6) AS idcard_prefix
FROM emp
WHERE idcard IS NOT NULL;

使用负数位置截取工号的最后一个字符：

SELECT workno,
       SUBSTRING(workno, -1) AS last_character
FROM emp;

数值函数

CEIL

将员工年龄除以 10 后向上取整：

SELECT name,
       age,
       CEIL(age / 10.0) AS rounded_up
FROM emp;
FLOOR

将员工年龄除以 10 后向下取整：

SELECT name,
       age,
       FLOOR(age / 10.0) AS rounded_down
FROM emp;

对于负数，向上和向下仍然按照数轴方向取整：

SELECT CEIL(-1.23) AS rounded_up,
       FLOOR(-1.23) AS rounded_down;

结果分别为 -1 和 -2。

MOD

根据年龄除以 2 的余数判断奇偶：

SELECT name,
       age,
       MOD(age, 2) AS remainder
FROM emp;

偶数年龄的余数为 0，奇数年龄的余数为 1。

RAND

查看一个 [0, 1) 范围内的随机数：

SELECT RAND() AS random_value;

从员工表中随机查询一名员工：

SELECT id, name
FROM emp
ORDER BY RAND()
LIMIT 1;

逻辑执行过程：

  1. FROM emp 读取员工表的全部记录。
  2. RAND() 为每条记录生成一个 [0, 1) 范围内的随机数，例如：
员工 随机值
柳岩 0.72
张无忌 0.13
韦一笑 0.48
  1. ORDER BY RAND() 按随机值排序，此时张无忌排在第一位
  2. LIMIT 1 只返回排序后的第一条记录

组合 RAND、FLOOR 和 LPAD 生成由 6 个数字字符组成的演示代码：

SELECT LPAD(FLOOR(RAND() * 1000000), 6, '0') AS demo_code;

这里使用 FLOOR 可以保证随机整数范围是 0～999999，再由 LPAD 补足前导零。该写法只适合学习和普通随机展示，不能作为真实身份验证代码的安全生成方案。

ROUND

将员工年龄除以 3 的结果保留两位小数：

SELECT name,
       age,
       ROUND(age / 3, 2) AS rounded_value
FROM emp;

对小数点左侧的十位进行舍入：

SELECT ROUND(125, -1) AS rounded_to_tens;

结果为 130。

日期函数

CURDATE

查询当前日期：

SELECT CURDATE() AS today;
CURTIME

查询当前时间，并保留 3 位小数秒：

SELECT CURTIME(3) AS current_time_value;
NOW

查询当前日期和时间：

SELECT NOW() AS current_datetime_value;
YEAR

提取员工入职年份：

SELECT name,
       entrydate,
       YEAR(entrydate) AS entry_year
FROM emp;
MONTH

提取员工入职月份：

SELECT name,
       entrydate,
       MONTH(entrydate) AS entry_month
FROM emp;
DAY

提取员工入职日期是当月的第几天：

SELECT name,
       entrydate,
       DAY(entrydate) AS entry_day
FROM emp;
DATE_ADD

计算每名员工入职满一年的日期：

SELECT name,
       entrydate,
       DATE_ADD(entrydate, INTERVAL 1 YEAR) AS first_anniversary
FROM emp;

也可以给当前时间增加指定间隔：

SELECT DATE_ADD(NOW(), INTERVAL 70 MONTH) AS seventy_months_later;
DATEDIFF

计算两段固定日期之间的天数：

SELECT DATEDIFF('2021-12-01', '2021-10-01') AS days_between;

结果为 61。如果交换两个参数，结果为 -61。

查询所有员工的入职天数，并按入职天数降序排列：

SELECT name,
       entrydate,
       DATEDIFF(CURDATE(), entrydate) AS employment_days
FROM emp
ORDER BY employment_days DESC, id ASC;

流程控制函数

IF

根据性别生成员工类别：

SELECT name,
       gender,
       IF(gender = '男', '男员工', '女员工') AS employee_type
FROM emp;
IFNULL

身份证号为 NULL 时显示“未填写”：

SELECT name,
       IFNULL(idcard, '未填写') AS idcard
FROM emp;

对比 NULL 和空字符串的处理结果：

SELECT IFNULL(NULL, '默认值') AS null_result,
       IFNULL('', '默认值') AS empty_string_result;

null_result 返回 默认值，empty_string_result 返回空字符串。

搜索 CASE

搜索 CASE 适合判断范围或编写彼此不同的条件。下面根据年龄划分年龄段：

SELECT name,
       age,
       CASE
           WHEN age < 20 THEN '20岁以下'
           WHEN age < 40 THEN '20至39岁'
           ELSE '40岁及以上'
       END AS age_group
FROM emp;

条件按顺序判断，因此 WHEN age < 40 实际处理的是已经排除 20 岁以下员工后的 20～39 岁。

简单 CASE

简单 CASE 适合将同一个表达式与多个确定值比较。下面将北京和上海标记为一线城市，其余地址标记为其他城市：

SELECT name,
       workaddress,
       CASE workaddress
           WHEN '北京' THEN '一线城市'
           WHEN '上海' THEN '一线城市'
           ELSE '其他城市'
       END AS city_category
FROM emp;