详情

导航

详情

首页手游攻略 一文完整指南MySQL常用内置函数

一文完整指南MySQL常用内置函数

佚名 2026-10-12 08:50:01

平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“一文详细解析MySQL常用内置函数”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

一、日期与时间函数

函数功能描述采用示例
NOW()拿到当前日期和时间SELECT NOW();
CURDATE()拿到当前日期SELECT CURDATE();
CURTIME()拿到当前时间SELECT CURTIME();
DATE()提取日期部分SELECT DATE(NOW());
TIME()提取时间部分SELECT TIME(NOW());
DATEDIFF()计算两个日期相差的天数SELECT DATEDIFF('2026-10-08', '2026-10-01');
DATE_ADD()在日期基础上增加指定时间间隔SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
DATE_SUB()在日期基础上减去指定时间间隔SELECT DATE_SUB(NOW(), INTERVAL 7 DAY);

拿到当前日期和时间：

select now();
+---------------------+
| now()               |
+---------------------+
| 2026-10-08 20:06:35 |
+---------------------+
1 row in set (0.00 sec)

拿到当前日期：

select curdate();
+------------+
| curdate()  |
+------------+
| 2026-10-08 |
+------------+

拿到当前时间：

select curtime();
+-----------+
| curtime() |
+-----------+
| 20:08:29  |
+-----------+

提取日期和时间：

select date(now()) 日期, time(now()) 时间;
+------------+----------+
| 日期       | 时间     |
+------------+----------+
| 2026-10-08 | 20:10:30 |
+------------+----------+

计算两个日期相差的天数：

select datediff(date(now()), '2026-10-01') res;
+------+
| res  |
+------+
|    7 |
+------+

在日期基础上增减时间间隔：

select date_add(now(), interval 2 day) res;
+---------------------+
| res                 |
+---------------------+
| 2026-10-10 20:18:42 |
+---------------------+

select date_sub(now(), interval 2 day) res;
+---------------------+
| res                 |
+---------------------+
| 2026-10-06 20:18:49 |
+---------------------+

select date_sub(now(), interval 2 year) res;
+---------------------+
| res                 |
+---------------------+
| 2024-10-08 20:19:00 |
+---------------------+

二、数学函数

函数功能描述采用示例
ABS(x)得到 x 的绝对值SELECT ABS(-10);
CEIL(x)向上取整，得到大于等于 x 的最小整数SELECT CEIL(3.14);
FLOOR(x)向下取整，得到小于等于 x 的最大整数SELECT FLOOR(3.99);
ROUND(x)四舍五入取整SELECT ROUND(3.56);
TRUNCATE(x, d)截断到小数点后 d 位，不进行四舍五入SELECT TRUNCATE(3.14159, 2);
MOD(x, y)求余数SELECT MOD(10, 3);
RAND()得到 0 到 1 之间的随机数SELECT RAND();

ABS函数：

select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+

CEIL 函数：

select ceil(3.14);
+------------+
| ceil(3.14) |
+------------+
|          4 |
+------------+

select ceil(-1.1);
+------------+
| ceil(-1.1) |
+------------+
|         -1 |
+------------+

FLOOR函数：

select floor(3.99);
+-------------+
| floor(3.99) |
+-------------+
|           3 |
+-------------+

select floor(-1.01);
+--------------+
| floor(-1.01) |
+--------------+
|           -2 |
+--------------+

ROUND函数：

select round(3.56);
+-------------+
| round(3.56) |
+-------------+
|           4 |
+-------------+

select round(3.49);
+-------------+
| round(3.49) |
+-------------+
|           3 |
+-------------+

TRUNCATE函数：

select truncate(3.14159, 2);
+----------------------+
| truncate(3.14159, 2) |
+----------------------+
|                 3.14 |
+----------------------+

select truncate(3.14159, 4);
+----------------------+
| truncate(3.14159, 4) |
+----------------------+
|               3.1415 |
+----------------------+

MOD函数：

select mod(10, 3);
+------------+
| mod(10, 3) |
+------------+
|          1 |
+------------+

RAND函数：

生成 0 ~ 1 中的随机数
select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.9467393354948045 |
+--------------------+

生成 0 ~ 100 中的随机数
select rand() * 100;
+------------------+
| rand() * 100     |
+------------------+
| 58.6048947261692 |
+------------------+

生成 0 ~ 100 中的随机整数
select truncate(rand() * 100, 0);
+---------------------------+
| truncate(rand() * 100, 0) |
+---------------------------+
|                        69 |
+---------------------------+

补充：MySQL 中进制转换函数

函数功能描述采用示例
BIN(n)将十进制整数转换为二进制字符串SELECT BIN(10); → 1010
OCT(n)将十进制整数转换为八进制字符串SELECT OCT(10); → 12
HEX(n)将数字转换为十六进制字符串，也兼容字符串的十六进制编码表示SELECT HEX(255); → FF
CONV(n, from_base, to_base)将数字字符串从指定进制转换为另一种进制（2～36 进制）SELECT CONV('1010', 2, 10); → 10

三、字符串函数

函数功能描述采用示例
CHARSET(s)查看字符串的字符集SELECT CHARSET('abc');
CONCAT(s1, s2, ...)将多个字符串拼接成一个字符串SELECT CONCAT('Hello', 'World');
LENGTH(s)得到字符串的字节长度SELECT LENGTH('abc'); → 3
UPPER(s)将字符串转换为大写SELECT UPPER('hello'); → HELLO
LOWER(s)将字符串转换为小写SELECT LOWER('HELLO'); → hello
SUBSTRING(s, pos, len)从指定位置开始截取指定长度的字符串SELECT SUBSTRING('abcdef', 2, 3); → bcd
LEFT(s, n)拿到字符串左侧的 n 个字符SELECT LEFT('abcdef', 3); → abc
RIGHT(s, n)拿到字符串右侧的 n 个字符SELECT RIGHT('abcdef', 3); → def
TRIM(s)去除字符串首尾的空格SELECT TRIM(' hello '); → hello
LTRIM(s)去除字符串左侧的空格SELECT LTRIM(' hello'); → hello
RTRIM(s)去除字符串右侧的空格SELECT RTRIM('hello '); → hello
REPLACE(s, from, to)将字符串中的指定子串替换为新字符串SELECT REPLACE('hello', 'l', 'x'); → hexxo
INSTR(s, sub)从实现思路看，得到子串首次出现的位置，未找到得到 0SELECT INSTR('hello', 'll'); → 3
STRCMP(s1, s2)比较两个字符串，相等得到 0SELECT STRCMP('abc', 'abc'); → 0

查看字符串字符集：

select charset('abc');
+----------------+
| charset('abc') |
+----------------+
| utf8           |
+----------------+

拼接字符串：

select concat('hello', 'world');
+--------------------------+
| concat('hello', 'world') |
+--------------------------+
| helloworld               |
+--------------------------+

结合项目来看，这里需留意，如果参与拼接的任意一个参数为 NULL，最后结构也会是 NULL。

select concat('hello', NULL, 'world') res;
+------+
| res  |
+------+
| NULL |
+------+

截取子串：

select substring('abcdefg', 1);
+-------------------------+
| substring('abcdefg', 1) |
+-------------------------+
| abcdefg                 |
+-------------------------+

select substring('abcdefg', 1, 2);
+----------------------------+
| substring('abcdefg', 1, 2) |
+----------------------------+
| ab                         |
+----------------------------+

字符串函数的实际应用

CREATE TABLE student (
    id INT PRIMARY KEY,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    email VARCHAR(100)
);

INSERT INTO student VALUES
(1, 'San', 'Zhang', '[email protected]'),
(2, 'Si', 'Li', '[email protected]'),
(3, 'Wu', 'Wang', '[email protected]');

select * from student;
+----+------------+-----------+------------------+
| id | first_name | last_name | email            |
+----+------------+-----------+------------------+
|  1 | San        | Zhang     | [email protected]  |
|  2 | Si         | Li        | [email protected]     |
|  3 | Wu         | Wang      | [email protected] |
+----+------------+-----------+------------------+

需求一：输出学生姓名

select concat(last_name, ' ', first_name) name from student;
+-----------+
| name      |
+-----------+
| Zhang San |
| Li Si     |
| Wang Wu   |
+-----------+

需求二：将所有邮箱转换为小写

select lower(email) from student;
+------------------+
| lower(email)     |
+------------------+
| [email protected]  |
| [email protected]     |
| [email protected] |
+------------------+

需求三：提取邮箱域名

select substring(email, instr(email, '@') + 1) suffix from student;
+-----------+
| suffix    |
+-----------+
| QQ.COM    |
| 163.COM   |
| GMAIL.COM |
+-----------+

需求四：查询采用 QQ 邮箱的学生

select * from student where substring(email, instr(email, '@') + 1) = 'qq.com';
+----+------------+-----------+-----------------+
| id | first_name | last_name | email           |
+----+------------+-----------+-----------------+
|  1 | San        | Zhang     | [email protected] |
+----+------------+-----------+-----------------+

四、其他常用函数

常用函数汇总

函数功能描述采用示例
IF(expr, v1, v2)条件成立得到 v1，否则得到 v2SELECT IF(80 >= 60, '及格', '不及格');
IFNULL(v1, v2)如果 v1 为 NULL，得到 v2，否则得到 v1SELECT IFNULL(NULL, 0);
COALESCE(v1, ...)得到第一个非 NULL 的参数SELECT COALESCE(NULL, NULL, 10);
DATABASE()得到当前采用的数据库名称SELECT DATABASE();
USER()得到当前连接的用户名和主机信息SELECT USER();
VERSION()得到 MySQL 服务器版本SELECT VERSION();
PASSWORD()对用户数据进行加密SELECT PASSWORD(s);

落到代码里，其中 IF()、IFNULL()、COALESCE() 常用来条件判断以及 NULL 值处理，在实际的数据查询过程中最为常用。

条件判断与 NULL 处理

（1）IF() 函数

IF() 函数用来根据条件表达式的结果，得到不同的值。

基本语法：

IF(expr, value1, value2);
  • expr：条件表达式。
  • value1：当条件成立时得到的值。
  • value2：当条件不成立时得到的值。

比如：

select if(80 >= 60, '及格', '不及格') res;
+--------+
| res    |
+--------+
| 及格   |
+--------+

在这个场景下，在 MySQL 里，IF() 将非零且非 NULL 的数值条件视为真，将零或 NULL 视为假。

比如：

SELECT IF(1, 'true', 'false');     -- true
SELECT IF(0, 'true', 'false');     -- false
SELECT IF(NULL, 'true', 'false');  -- false

（2）IFNULL() 函数

在这个场景下，IFNULL() 常用来处理 NULL 值。当第一个参数为 NULL 时，得到第二个参数；否则得到第一个参数。

基本语法：

IFNULL(value1, value2);

比如：

SELECT IFNULL(NULL, 100);  -- 100
SELECT IFNULL(10, 100);    -- 10

在这个场景下，真实开发场景下，某些字段可能允许存储 NULL，如果想要在查询时采用指定的默认值，就能够采用 IFNULL()。

假设存在如下所示学生信息表：

CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    score INT
);

INSERT INTO student VALUES
(1, '张三', 90),
(2, '李四', NULL),
(3, '王五', 80);

查询学生成绩时，如果成绩为 NULL，希望显示为 0：

SELECT name, IFNULL(score, 0) AS score FROM student;

查询结果：

namescore
张三90
李四0
王五80

需留意：IFNULL() 只是对查询结果进行处理，同时不会修改数据表中实际存储的 NULL 值。

（3）NULLIF() 函数

在这个场景下，NULLIF() 用来比较两个表达式，如果二者相等，则得到 NULL；否则得到第一个表达式的值。

基本语法：

NULLIF(value1, value2);

比如：

SELECT NULLIF(10, 10);  -- NULL
SELECT NULLIF(10, 20);  -- 10

从逻辑上能够将其理解为：

CASE
    WHEN value1 = value2 THEN NULL
    ELSE value1
END

一个常用的应用场景是避免除零错误。

SELECT 100 / NULLIF(0, 0);
-- NULL

在这个场景下，除数为 0 时，NULLIF(0, 0) 得到 NULL，最后除法运算结果也为 NULL，从而避免直接采用零作为除数。在实际 MySQL 中，除零本身的报错或警告行为还与 SQL 模式及执行场景有关。

（4）COALESCE() 函数

在这个场景下，COALESCE() 用来从多个参数中依次查找，得到第一个非 NULL 的参数。

基本语法：

COALESCE(value1, value2, ...);

比如：

SELECT COALESCE(NULL, NULL, 100, 200);
-- 100

若所有参数均为 NULL，则得到 NULL。

SELECT COALESCE(NULL, NULL, NULL);
-- NULL

它与 IFNULL() 的作用比较相似，但存在一个明显的区别：

  • IFNULL()：只能接收两个参数。
  • COALESCE()：能够接收多个参数，依次得到第一个非 NULL 的值。

理解这一步时，所以，当需从多个候选值中选择有效数据时，采用 COALESCE() 更加便于。

CASE WHEN 条件表达式

注：CASE 属于 SQL 条件表达式，同时不是普通的内置函数。

基本语法：

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

执行逻辑：

  1. 从上到下依次判断 WHEN 后面的条件。
  2. 某个条件成立时，得到对应 THEN 后面的结果。
  3. 若所有条件都不成立，则得到 ELSE 指定的结果。
  4. 若没有符合条件的分支，并且省略了 ELSE，则得到 NULL。

假设需根据学生的成绩划分等级：

  • 成绩大于等于 90：优秀。
  • 成绩大于等于 80：良好。
  • 成绩大于等于 60：及格。
  • 成绩小于 60：不及格。
  • 成绩为 NULL：暂无成绩。
SELECT
    name,
    score,
    CASE
        WHEN score IS NULL THEN '暂无成绩'
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade
FROM student;

此外，CASE 还兼容另一种写法，适合对同一个表达式进行多个等值判断：

CASE gender
    WHEN '男' THEN 'Male'
    WHEN '女' THEN 'Female'
    ELSE 'Unknown'
END

结合项目来看，这种形式就相当于依次比较 gender 与各个 WHEN 后面的值是否相等。

数据库信息函数

（1）DATABASE() 函数

拿到当前正在采用的数据库：

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

若当前没有选择任何数据库，则得到 NULL。

（2）USER() 函数

拿到当前连接时采用的用户名以及客户端主机信息：

mysql> select user();
+--------+
| user() |
+--------+
| lt@    |
+--------+

（3）VERSION() 函数

拿到当前 MySQL 服务器的版本信息：

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.44    |
+-----------+

（4）PASSWORD() 函数

对用户私密数据进行加密：

select password('1234');
+-------------------------------------------+
| password('1234')                          |
+-------------------------------------------+
| *A4B6157319038724E3560894F7F932C8886EBFCF |
+-------------------------------------------+

实际处理时，总的来说，MySQL常用内置函数适合结合实际项目边做边理解。先抓住核心思路，再逐步补上细节和边界处理，最后效果会更稳定，也更容易复用。

相关资讯
点击查看更多
游戏推荐
推荐专题
热门阅读
推荐下载

Copyright©2010-2019 . All rights reserved | 波波三国游戏官网