mysql数据库的常用函数

作者

MySQL 常用函数完全指南

本文档专门讲 MySQL 里常用的各类函数：字符串、数值、日期时间、条件判断、类型转换等。每一步都配有说明和大量示例，适合零基础新手跟着练。

目录

  1. 函数是什么
  2. 字符串函数
  3. 数值函数
  4. 日期与时间函数
  5. 条件与判断函数
  6. 类型转换函数
  7. 其他常用函数
  8. 聚合函数简要回顾
  9. 函数在查询中的用法
  10. 常见错误与注意点
  11. 综合示例与速查表

1. 函数是什么

1.1 通俗理解

函数就是一段“封装好的计算规则”：你给它输入（参数），它给你输出（返回值）。
在 SQL 里，函数可以用来处理列的值、做计算、格式化显示等，例如：把姓名和班级拼成一行、把价格四舍五入、按格式显示日期。

1.2 常见用法

  • 在 SELECT 里：SELECT CONCAT(name, '-', class_name) FROM students;
  • 在 WHERE 里：WHERE YEAR(birth_date) = 2000
  • 在 ORDER BY 里：ORDER BY LENGTH(name)
  • 在 UPDATE/SET 里：SET updated_at = NOW()

下面按字符串 → 数值 → 日期时间 → 条件判断 → 类型转换的顺序介绍常用函数，并给出示例。

2. 字符串函数

2.1 CONCAT —— 拼接字符串

CONCAT(串1, 串2, …) 把多个字符串按顺序拼成一个。

SELECT CONCAT('Hello', ', ', 'World');
-- 结果：Hello, World

SELECT CONCAT(name, ' (', class_name, ')') AS 学生信息 FROM students;
-- 示例结果：张三 (高一1班)

若任一参数为 NULL，整体结果为 NULL。若想把 NULL 当空串，可用 CONCAT_WS 或 IFNULL（见后）。

CONCAT_WS(分隔符, 串1, 串2, …)：用第一个参数当分隔符拼接，NULL 会被忽略。

SELECT CONCAT_WS('-', '2025', '02', '26');
-- 结果：2025-02-26

2.2 LENGTH 与 CHAR_LENGTH

  • LENGTH(串)：字节数（中文在 utf8mb4 下通常 3～4 字节）。
  • CHAR_LENGTH(串) 或 CHARACTER_LENGTH(串)：字符个数（中文算 1 个字符）。
SELECT LENGTH('abc');           -- 3
SELECT LENGTH('中文');          -- 6（utf8mb4 下）
SELECT CHAR_LENGTH('中文');     -- 2
SELECT CHAR_LENGTH(name) AS 姓名长度 FROM students;

2.3 SUBSTRING（截取子串）

SUBSTRING(串, 起始位置, 长度) 或 SUBSTR(串, 起始位置, 长度)
位置从 1 开始；长度可省略，表示到末尾。

SELECT SUBSTRING('Hello World', 1, 5);   -- Hello
SELECT SUBSTRING('Hello World', 7);     -- World
SELECT SUBSTRING(name, 1, 1) AS 姓 FROM students;  -- 取第一个字

LEFT(串, 长度)、RIGHT(串, 长度)：从左边或右边取若干字符。

SELECT LEFT('Hello', 2);   -- He
SELECT RIGHT('Hello', 2);  -- lo

2.4 TRIM、LTRIM、RTRIM —— 去空格

  • TRIM(串)：去掉首尾空格。
  • LTRIM(串)：去掉左边空格。
  • RTRIM(串)：去掉右边空格。
SELECT TRIM('  abc  ');   -- 'abc'
SELECT LTRIM('  abc');    -- 'abc'
SELECT RTRIM('abc  ');    -- 'abc'

TRIM(字符 FROM 串)：去掉首尾指定字符（如引号）。

SELECT TRIM(BOTH '"' FROM '"hello"');  -- hello

2.5 UPPER 与 LOWER —— 大小写

  • UPPER(串)、UCASE(串)：转大写。
  • LOWER(串)、LCASE(串)：转小写。
SELECT UPPER('hello');   -- HELLO
SELECT LOWER('HELLO');   -- hello
SELECT UPPER(name) AS 姓名大写 FROM students;

2.6 REPLACE —— 替换

REPLACE(原串, 被替换的串, 新串)：把“被替换的串”全部换成“新串”。

SELECT REPLACE('abcabc', 'a', 'X');   -- XbcXbc
SELECT REPLACE(class_name, '高一', 'G1') AS 班级 FROM students;

2.7 LPAD、RPAD —— 左/右填充

LPAD(串, 总长度, 填充字符)：不足总长度时在左边用填充字符补足。
RPAD(串, 总长度, 填充字符)：在右边补足。

SELECT LPAD('5', 3, '0');    -- 005
SELECT RPAD('ab', 5, '*');   -- ab***

2.8 INSTR、LOCATE —— 查找位置

  • INSTR(串, 子串)：子串第一次出现的位置（从 1 开始），没有则 0。
  • LOCATE(子串, 串 [, 起始位置])：同上，参数顺序不同。
SELECT INSTR('hello world', 'o');     -- 5
SELECT LOCATE('o', 'hello world');    -- 5
SELECT LOCATE('三', name) FROM students WHERE name = '张三';  -- 2

2.9 其他字符串函数（了解）

  • REVERSE(串)：反转字符串。
  • REPEAT(串, 次数)：重复若干次。
  • SPACE(n)：n 个空格的字符串。
SELECT REVERSE('abc');      -- cba
SELECT REPEAT('ab', 3);     -- ababab

3. 数值函数

3.1 ROUND —— 四舍五入

ROUND(数 [, 小数位数])：四舍五入；小数位数省略则保留整数。

SELECT ROUND(3.14159);       -- 3
SELECT ROUND(3.14159, 2);    -- 3.14
SELECT ROUND(3.14159, 4);    -- 3.1416
SELECT ROUND(score, 1) AS 分数 FROM scores;  -- 保留一位小数

3.2 FLOOR 与 CEIL / CEILING

  • FLOOR(数)：向下取整（小于等于该数的最大整数）。
  • CEIL(数)、CEILING(数)：向上取整。
SELECT FLOOR(3.7);   -- 3
SELECT CEIL(3.2);    -- 4
SELECT CEILING(3.0); -- 3

3.3 TRUNCATE —— 截断（不四舍五入）

TRUNCATE(数, 小数位数)：直接截断到指定位数，不四舍五入。

SELECT TRUNCATE(3.14159, 2);  -- 3.14
SELECT TRUNCATE(3.999, 2);    -- 3.99

3.4 ABS、MOD、符号

  • ABS(数)：绝对值。
  • MOD(被除数, 除数)：取余，同 %。
  • SIGN(数)：正数 1，负数 -1，零 0。
SELECT ABS(-10);     -- 10
SELECT MOD(10, 3);   -- 1
SELECT 10 % 3;       -- 1
SELECT SIGN(-5);     -- -1

3.5 POW、SQRT、RAND

  • POW(底, 指数)、POWER(底, 指数)：幂运算。
  • SQRT(数)：平方根（非负）。
  • RAND()：0～1 之间随机小数；RAND(种子) 可固定随机序列。
SELECT POW(2, 3);    -- 8
SELECT SQRT(16);     -- 4
SELECT RAND();       -- 随机数，如 0.123...

4. 日期与时间函数

4.1 取当前日期时间

  • NOW()：当前日期+时间，如 2025-02-26 14:30:00。
  • CURDATE()、CURRENT_DATE()：当前日期，如 2025-02-26。
  • CURTIME()、CURRENT_TIME()：当前时间，如 14:30:00。
  • CURRENT_TIMESTAMP()：同 NOW()。
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();

4.2 从日期时间里取部分

  • YEAR(日期)、MONTH(日期)、DAY(日期)：年、月、日。
  • HOUR(时间)、MINUTE(时间)、SECOND(时间)：时、分、秒。
  • DATE(日期时间)：只取日期部分。
  • TIME(日期时间)：只取时间部分。
SELECT YEAR('2025-02-26');           -- 2025
SELECT MONTH('2025-02-26');          -- 2
SELECT DAY('2025-02-26');            -- 26
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());
SELECT DATE(NOW());   -- 2025-02-26
SELECT TIME(NOW());   -- 14:30:00

4.3 日期加减：DATE_ADD、DATE_SUB

DATE_ADD(日期, INTERVAL 数 单位)、DATE_SUB(日期, INTERVAL 数 单位)
单位如：DAY、MONTH、YEAR、HOUR、MINUTE、SECOND、WEEK 等。

SELECT DATE_ADD('2025-02-26', INTERVAL 7 DAY);    -- 2025-03-05
SELECT DATE_ADD('2025-02-26', INTERVAL 1 MONTH);  -- 2025-03-26
SELECT DATE_SUB('2025-02-26', INTERVAL 10 DAY);   -- 2025-02-16
SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR);

简写：日期 + INTERVAL 数 单位、日期 – INTERVAL 数 单位。

SELECT '2025-02-26' + INTERVAL 1 DAY;  -- 2025-02-27

4.4 日期差：DATEDIFF、TIMESTAMPDIFF

  • DATEDIFF(日期1, 日期2)：日期1 – 日期2，结果是天数（只比日期，不管时间）。
  • TIMESTAMPDIFF(单位, 时间1, 时间2)：时间2 – 时间1，按单位（DAY、MONTH、YEAR、HOUR 等）返回数值。
SELECT DATEDIFF('2025-03-01', '2025-02-26');  -- 3
SELECT DATEDIFF(CURDATE(), '2000-01-01');     -- 多少天
SELECT TIMESTAMPDIFF(YEAR, '2000-01-01', '2025-02-26');  -- 25
SELECT TIMESTAMPDIFF(DAY, birth_date, CURDATE()) AS 天数 FROM 某表;

4.5 格式化：DATE_FORMAT、STR_TO_DATE

DATE_FORMAT(日期, 格式串)：把日期按格式转成字符串。
常用占位符：%Y 四位年，%m 月，%d 日，%H 时，%i 分，%s 秒，%W 星期名。

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');              -- 2025-02-26
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日');           -- 2025年02月26日
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');     -- 2025-02-26 14:30:00

STR_TO_DATE(字符串, 格式串)：按格式把字符串转成日期。

SELECT STR_TO_DATE('2025-02-26', '%Y-%m-%d');
SELECT STR_TO_DATE('26/02/2025', '%d/%m/%Y');

4.6 星期、季度等（了解）

  • DAYOFWEEK(日期)：1=周日，2=周一……7=周六。
  • DAYNAME(日期)：星期英文名。
  • QUARTER(日期)：季度 1～4。
  • LAST_DAY(日期)：该月最后一天。
SELECT DAYNAME('2025-02-26');   -- Wednesday
SELECT QUARTER('2025-02-26');   -- 1
SELECT LAST_DAY('2025-02-26');  -- 2025-02-28

5. 条件与判断函数

5.1 IF

IF(条件, 值1, 值2)：条件为真返回值1，否则返回值2。

SELECT IF(1 > 0, '是', '否');   -- 是
SELECT IF(score >= 60, '及格', '不及格') AS 结果 FROM scores;
SELECT IF(gender = '男', 'M', 'F') AS 性别码 FROM students;

5.2 IFNULL 与 NULLIF

  • IFNULL(值, 替代值)：若“值”为 NULL，返回“替代值”，否则返回“值”。
  • NULLIF(值1, 值2)：若值1=值2 返回 NULL，否则返回值1。
SELECT IFNULL(score, 0) AS 分数 FROM scores;   -- 把 NULL 显示为 0
SELECT IFNULL(nickname, name) AS 显示名 FROM users;
SELECT NULLIF(1, 1);   -- NULL
SELECT NULLIF(1, 2);   -- 1

5.3 COALESCE

COALESCE(值1, 值2, 值3, …)：返回第一个非 NULL 的值。

SELECT COALESCE(NULL, NULL, 3, 4);   -- 3
SELECT COALESCE(phone, email, '无') AS 联系方式 FROM users;

5.4 CASE 表达式

简单 CASE：按某列等于哪个值分支。

SELECT name,
  CASE class_name
    WHEN '高一1班' THEN '1班'
    WHEN '高一2班' THEN '2班'
    ELSE '其他'
  END AS 班级简写
FROM students;

搜索 CASE：按条件分支（更灵活）。

SELECT name, score,
  CASE
    WHEN score >= 90 THEN '优秀'
    WHEN score >= 60 THEN '及格'
    ELSE '不及格'
  END AS 等级
FROM scores;

6. 类型转换函数

6.1 CAST

CAST(值 AS 类型)：把值转成指定类型。类型如：SIGNED（整数）、DECIMAL(10,2)、CHAR、DATE、DATETIME。

SELECT CAST('123' AS SIGNED);           -- 123
SELECT CAST(3.14 AS DECIMAL(5,2));     -- 3.14
SELECT CAST(NOW() AS DATE);            -- 只取日期
SELECT CAST('2025-02-26' AS DATE);

6.2 CONVERT

CONVERT(值, 类型) 或 CONVERT(值 USING 字符集)：转换类型或字符集。

SELECT CONVERT('123', SIGNED);
SELECT CONVERT('中文' USING utf8mb4);

7. 其他常用函数

7.1 GREATEST、LEAST

GREATEST(值1, 值2, …)：取最大。
LEAST(值1, 值2, …)：取最小。

SELECT GREATEST(1, 5, 3);   -- 5
SELECT LEAST(1, 5, 3);      -- 1

7.2 与 NULL 相关

前面已讲 IFNULL、COALESCE、NULLIF；再强调：与 NULL 做运算或比较，结果多为 NULL，用 IFNULL 等可避免显示或计算异常。

8. 聚合函数简要回顾

常用聚合函数（详细见《聚合与分组》文档）：

  • *COUNT()、COUNT(列)、COUNT(DISTINCT 列)**：计数。
  • SUM(列)：求和。
  • AVG(列)：平均。
  • MAX(列)、MIN(列)：最大、最小。

示例：

SELECT COUNT(*) FROM students;
SELECT AVG(score) FROM scores WHERE subject = '语文';
SELECT MAX(score), MIN(score) FROM scores;

9. 函数在查询中的用法

9.1 在 SELECT 里

对列做处理、起别名：

SELECT name, UPPER(name) AS 姓名大写, CONCAT(name, '-', class_name) AS 信息 FROM students;
SELECT name, ROUND(score, 1) AS 分数, IF(score >= 60, '及格', '不及格') AS 等级 FROM scores;

9.2 在 WHERE 里

按“处理后的值”筛选：

SELECT * FROM students WHERE CHAR_LENGTH(name) = 2;
SELECT * FROM scores WHERE YEAR(created_at) = 2025;
SELECT * FROM students WHERE class_name = '高一1班' AND age > 17;

9.3 在 ORDER BY 里

按“函数结果”排序：

SELECT * FROM students ORDER BY LENGTH(name);
SELECT * FROM scores ORDER BY IFNULL(score, 0) DESC;

9.4 在 UPDATE 里

用函数赋新值：

UPDATE 某表 SET updated_at = NOW();
UPDATE 某表 SET name = UPPER(name);

10. 常见错误与注意点

10.1 字符串用单引号

函数参数是字符串时要用单引号：CONCAT('a','b')，不要写成 CONCAT(a,b)（会当列名）。

10.2 日期格式

日期建议用 ‘YYYY-MM-DD’ 或 ‘YYYY-MM-DD HH:MM:SS’，避免歧义；STR_TO_DATE 的格式串要和字符串一致。

10.3 NULL 参与运算

NULL + 数、NULL 与比较 结果多为 NULL；需要默认值时用 IFNULL(列, 0) 等。

10.4 除零

除以 0 可能报错或得到 NULL，写条件时注意 WHERE 分母 0 或用 IF 判断。

10.5 中文字符长度

LENGTH 按字节，CHAR_LENGTH 按字符；显示“几个字”用 CHAR_LENGTH。

11. 综合示例与速查表

11.1 综合示例（可直接在库里试）

-- 字符串
SELECT CONCAT(name, '(', class_name, ')') AS 信息 FROM students LIMIT 3;
SELECT UPPER(name), LENGTH(name), CHAR_LENGTH(name) FROM students LIMIT 3;
SELECT SUBSTRING(name, 1, 1) AS 姓 FROM students;

-- 数值
SELECT ROUND(85.456, 1), FLOOR(85.9), CEIL(85.1);
SELECT IFNULL(score, 0) AS 分数 FROM scores LIMIT 5;

-- 日期
SELECT NOW(), CURDATE(), CURTIME();
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i');
SELECT DATEDIFF('2025-12-31', CURDATE()) AS 距年末天数;

-- 条件
SELECT name, score, IF(score >= 60, '及格', '不及格') AS 等级 FROM scores LIMIT 5;
SELECT name, CASE WHEN age >= 18 THEN '成年' ELSE '未成年' END AS 年龄组 FROM students;

11.2 常用函数速查表

类别 函数 示例 / 说明
字符串 CONCAT CONCAT(a,b) 拼接
LENGTH / CHAR_LENGTH 字节数 / 字符数
SUBSTRING SUBSTRING(串,1,3) 截取
LEFT / RIGHT 左/右取若干字符
TRIM / LTRIM / RTRIM 去空格
UPPER / LOWER 大小写
REPLACE 替换
LPAD / RPAD 左/右填充
数值 ROUND 四舍五入
FLOOR / CEIL 下/上取整
TRUNCATE 截断
ABS / MOD 绝对值 / 取余
日期 NOW / CURDATE / CURTIME 当前日期时间/日期/时间
YEAR / MONTH / DAY 年/月/日
DATE_ADD / DATE_SUB 日期加减
DATEDIFF / TIMESTAMPDIFF 日期差
DATE_FORMAT / STR_TO_DATE 格式化/解析
条件 IF IF(条件, 值1, 值2)
IFNULL / COALESCE 处理 NULL
CASE WHEN 多分支判断
类型 CAST CAST(值 AS 类型)

把字符串、数值、日期、条件、类型转换等常用函数过一遍后，在写 SELECT、WHERE、ORDER BY、UPDATE 时就可以按需选用。建议在库里对 students、scores 等表多写几条带函数的查询，巩固用法。

访问数: 14

发表评论