一文详解MySQL常用内置函数_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)`	返回子串首次出现的位置，未找到返回 `0`	`select instr('hello', 'll');` → `3`
`strcmp(s1, s2)`	比较两个字符串，相等返回 `0`	`select 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', 'zhangsan@qq.com'),
(2, 'si', 'li', 'lisi@163.com'),
(3, 'wu', 'wang', 'wangwu@gmail.com');

select * from student;
+----+------------+-----------+------------------+
| id | first_name | last_name | email            |
+----+------------+-----------+------------------+
|  1 | san        | zhang     | zhangsan@qq.com  |
|  2 | si         | li        | lisi@163.com     |
|  3 | wu         | wang      | wangwu@gmail.com |
+----+------------+-----------+------------------+

需求一：输出学生姓名

select concat(last_name, ' ', first_name) name from student;
+-----------+
| name      |
+-----------+
| zhang san |
| li si     |
| wang wu   |
+-----------+

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

select lower(email) from student;
+------------------+
| lower(email)     |
+------------------+
| zhangsan@qq.com  |
| lisi@163.com     |
| wangwu@gmail.com |
+------------------+

需求三：提取邮箱域名

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     | zhangsan@qq.com |
+----+------------+-----------+-----------------+

四、其他常用函数

常见函数汇总

函数	功能描述	使用示例
`if(expr, v1, v2)`	条件成立返回 `v1`，否则返回 `v2`	`select if(80 >= 60, '及格', '不及格');`
`ifnull(v1, v2)`	如果 `v1` 为 null，返回 `v2`，否则返回 `v1`	`select 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;

查询结果：

name	score
张三	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

执行逻辑：

从上到下依次判断 when 后面的条件。
当某个条件成立时，返回对应 then 后面的结果。
如果所有条件都不成立，则返回 else 指定的结果。
如果没有符合条件的分支，并且省略了 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常用内置函数的详细内容，更多关于mysql常用内置函数的资料请关注代码网其它相关文章！