MySQL数据库：内置函数

目录

一. 日期相关函数

二. 字符串相关函数

三. 常用数学函数

四. 其它函数

---

一. 日期相关函数

表1. 日期相关函数

函数	功能
current_date()	获取当前的日期 ( 年-月-日 )，格式为'yyyy-mm-dd'
current_time()	获取当前时间 ( 时-分-秒 )，格式为'hh:mm:ss'
current_timestamp()	获取当前时间戳，前端显示格式为'yyyy-mm-dd HH:ii:ss'
date(datetime)	返回datetime中的日期部分（去掉时间部分）
date_add(date, interval d_val)	从日期类型数据中加上一定的时间， 增加时间的单位可以是year / month / day / hour / minute / second
date_sub(date, interval d_val)	从日期类型数据中减去一定的时间， 减去时间的单位可以是year / month / day / hour / minute / second
datediff(date1, date2)	计算两个日期之间的差值，单位是天
now()	获取当前时间

日期类型数据之间可以进行比较，时间靠后的数据 > 时间靠前的数据。

• 获取当前的日期、时间和时间戳

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2023-12-23     |
+----------------+
1 row in set (0.00 sec)

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 19:35:44       |
+----------------+
1 row in set (0.00 sec)

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-12-23 19:35:49 |
+---------------------+
1 row in set (0.00 sec)

• 截取datetime类型参数中的日期部分

mysql> select date('2023-11-23 22:23:05');
+-----------------------------+
| date('2023-11-23 22:23:05') |
+-----------------------------+
| 2023-11-23                  |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2023-12-23  |
+-------------+
1 row in set (0.00 sec)

mysql> select date(current_timestamp());
+---------------------------+
| date(current_timestamp()) |
+---------------------------+
| 2023-12-23                |
+---------------------------+
1 row in set (0.00 sec)

• 获取当前的日期和时间

可以通过时间戳来获取，也可以直接调用now函数获取。

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-12-23 19:39:03 |
+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2023-12-23 19:39:05 |
+---------------------+
1 row in set (0.00 sec)

• 计算在当前时间now()加上和减去特定时间后的时间

-- 当前时间向后2年
mysql> select date_add(now(), interval 2 year);
+----------------------------------+
| date_add(now(), interval 2 year) |
+----------------------------------+
| 2025-12-23 19:39:58              |
+----------------------------------+
1 row in set (0.00 sec)

-- 当前时间向后2个月
mysql> select date_add(now(), interval 2 month);
+-----------------------------------+
| date_add(now(), interval 2 month) |
+-----------------------------------+
| 2024-02-23 19:40:03               |
+-----------------------------------+
1 row in set (0.01 sec)

-- 当前时间向后35天
mysql> select date_add(now(), interval 35 day);
+----------------------------------+
| date_add(now(), interval 35 day) |
+----------------------------------+
| 2024-01-27 19:40:19              |
+----------------------------------+
1 row in set (0.01 sec)

-- 当前时间向后60min
mysql> select date_add(now(), interval 60 minute);
+-------------------------------------+
| date_add(now(), interval 60 minute) |
+-------------------------------------+
| 2023-12-23 20:40:33                 |
+-------------------------------------+
1 row in set (0.00 sec)

-- 当前时间向前1000秒
mysql> select date_sub(now(), interval 1000 second);
+---------------------------------------+
| date_sub(now(), interval 1000 second) |
+---------------------------------------+
| 2023-12-23 19:24:11                   |
+---------------------------------------+
1 row in set (0.00 sec)

-- 当前时间向前30天
mysql> select date_sub(now(), interval 30 day);
+----------------------------------+
| date_sub(now(), interval 30 day) |
+----------------------------------+
| 2023-11-23 19:41:04              |
+----------------------------------+
1 row in set (0.00 sec)

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

mysql> select datediff('2023-12-20','2023-02-07') as GapDays;
+---------+
| GapDays |
+---------+
|     316 |
+---------+
1 row in set (0.00 sec)

mysql> select datediff('2023-02-07','2023-12-20') as GapDays;
+---------+
| GapDays |
+---------+
|    -316 |
+---------+
1 row in set (0.00 sec)

二. 字符串相关函数

表2. 字符串操作相关函数

函数	功能
charset(str)	获取字符串str的字符编码集。
concat(str1, str2, str3, ...)	将若干个字符串进行拼接。
instr(str, subStr)	返回subStr在字符串中首次出现的位置下标，注意此时下标从1开始，如果str中找不到字符串subStr，那么就返回0。
ucase(str)	将小写字母全部转换为大写字母，不对非英文字母字符做处理。
lcase(str)	将大写字母全部转换为小写字母，不对非英文字母字符做处理。
left(str, length)	从最左边开始，截取str的前length个字符，如果length超过str的字符数，那么就获取全部的str字符。
right(str, length)	从最右边开始，截取str的后length个字符，如果length超过str的字符数，那么就获取全部的str字符。
length(str)	计算字符串str的长度，注意是以字节数为单位，在utf8编码中，一个C/C++字符占一个字节，一个汉字占3个字节。
replace(str, subStr, replaceStr)	将str中的子串subStr，全部替换为replaceStr，注意是全部替换，而不是仅替换第一次出现的subStr。
strcmp(str1, str2)	按照字典序比较str1和str2的大小，功能与C语言库函数strcmp完全相同，如果str1>str2返回1，str1<str2返回-1，str1=str2返回0。
substring(str, pos, [length])	从字符串str的pos下标处开始截取长度为length的子串，下标从1开始，length如果省略或长度过大就截取到字符串末尾。
ltrim(str)	截掉字符串str左边的空格。
rtrim(str)	截掉字符串str右边的空格。
trim(str)	截掉字符串str左边和右边的空格，中间空格不受影响。

• 获取字符串编码集

默认情况下，MySQL数据库采用utf8编码集。

+------------------+
| charset('hello') |
+------------------+
| utf8             |
+------------------+
1 row in set (0.00 sec)

• 拼接字符串

concat函数，无论传给这个函数多少个字符串，都能进行拼接，如果只传递一个字符串，就返回这个字符串本身。

mysql> select concat('aa');
+--------------+
| concat('aa') |
+--------------+
| aa           |
+--------------+
1 row in set (0.00 sec)

mysql> select concat('aa','bbb','cc');
+-------------------------+
| concat('aa','bbb','cc') |
+-------------------------+
| aabbbcc                 |
+-------------------------+
1 row in set (0.00 sec)

• 在字符串中查找特定子串

-- 'bc'在'abckmbcc'中首次出现在下标为2的位置处
mysql> select instr('abckmbcc','bc');
+------------------------+
| instr('abckmbcc','bc') |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.00 sec)

-- 'hello'在'abckmbcc'中没有出现
mysql> select instr('abckmbcc','hello');
+---------------------------+
| instr('abckmbcc','hello') |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

• 大小写字母之间的转换

mysql> select ucase('hello%%abc');
+---------------------+
| ucase('hello%%abc') |
+---------------------+
| HELLO%%ABC          |
+---------------------+
1 row in set (0.00 sec)

mysql> select lcase('HELLO%%ABC');
+---------------------+
| lcase('HELLO%%ABC') |
+---------------------+
| hello%%abc          |
+---------------------+
1 row in set (0.00 sec)

• 在字符串的左端和右端截取子串

mysql> select left('abcdef', 4);
+-------------------+
| left('abcdef', 4) |
+-------------------+
| abcd              |
+-------------------+
1 row in set (0.01 sec)

mysql> select left('abcdef', 10);
+--------------------+
| left('abcdef', 10) |
+--------------------+
| abcdef             |
+--------------------+
1 row in set (0.00 sec)

mysql> select right('abcdef', 4);
+--------------------+
| right('abcdef', 4) |
+--------------------+
| cdef               |
+--------------------+
1 row in set (0.00 sec)

mysql> select right('abcdef', 10);
+---------------------+
| right('abcdef', 10) |
+---------------------+
| abcdef              |
+---------------------+
1 row in set (0.00 sec)

• 用length()函数计算字符串长度

length = 3 * 汉字数 + 1 * C/Cpp字符数

-- 2个汉字，4个C++字符，总共10字节
mysql> select length('中国2023');
+----------------------+
| length('中国2023')   |
+----------------------+
|                   10 |
+----------------------+
1 row in set (0.00 sec)

• ?替换字符串中的指定子串

mysql> select replace('bcXXaaXXmmXXc','XX','HELLO');
+---------------------------------------+
| replace('bcXXaaXXmmXXc','XX','HELLO') |
+---------------------------------------+
| bcHELLOaaHELLOmmHELLOc                |
+---------------------------------------+
1 row in set (0.00 sec)

• 替换特定的子串

-- 从下标为2的位置开始，取长度为4的子串
mysql> select substring('abcdef',2,4);
+-------------------------+
| substring('abcdef',2,4) |
+-------------------------+
| bcde                    |
+-------------------------+
1 row in set (0.00 sec)

-- 从下标为2的位置开始，取长度为20的子串
-- 由于长度20会超过字符串末尾，那么就截取到末尾字符
mysql> select substring('abcdef',2,20);
+--------------------------+
| substring('abcdef',2,20) |
+--------------------------+
| bcdef                    |
+--------------------------+
1 row in set (0.00 sec)

-- 截取从下标2开始到末尾字符的子串
mysql> select substring('abcdef',2);
+-----------------------+
| substring('abcdef',2) |
+-----------------------+
| bcdef                 |
+-----------------------+
1 row in set (0.00 sec)

• 去除字符串左边和右边位置的空格

mysql> select ltrim('   hello   world          ') as ans;
+-------------------------+
| ans                     |
+-------------------------+
| hello   world           |
+-------------------------+
1 row in set (0.01 sec)

mysql> select rtrim('   hello   world          ') as ans;
+------------------+
| ans              |
+------------------+
|    hello   world |
+------------------+
1 row in set (0.00 sec)

mysql> select trim('   hello   world          ') as ans;
+---------------+
| ans           |
+---------------+
| hello   world |
+---------------+
1 row in set (0.00 sec)

三. 常用数学函数

表2. 数学函数

函数	功能
abs(number)	求number的绝对值。
bin(decimal_number)	将十进制数转换为其二进制表示，注意对于小数并不是直接返回其对应二进制，而是输出其向0取整后的二进制表示。
hex(decimal_number)	将十进制数转换为16进制，对浮点数的处理规则与bin相同。
conv(number, from_base, to_base)	进制转换，将number转换为目标进制表示，from_base为数据原本的进制，to_base为目标进制。
ceil(number)	向上取整。
floor(number)	向下取整。
format(number, places)	格式化数据，保留places位精度。
rand()	随机生成位于[0.0, 1.0)的浮点数。
mod(number, denominator)	取模运算。

• 对整数和浮点数求绝对值

mysql> select abs(123);
+----------+
| abs(123) |
+----------+
|      123 |
+----------+
1 row in set (0.00 sec)

mysql> select abs(-123);
+-----------+
| abs(-123) |
+-----------+
|       123 |
+-----------+
1 row in set (0.00 sec)

mysql> select abs(-123.1234);
+----------------+
| abs(-123.1234) |
+----------------+
|       123.1234 |
+----------------+
1 row in set (0.00 sec)

• 调用bin函数获取正整数、负整数和浮点数对应的二进制表示

mysql> select bin(-1);
+------------------------------------------------------------------+
| bin(-1)                                                          |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111111 | -- -1的二进制补码为全1
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select bin(10);
+---------+
| bin(10) |
+---------+
| 1010    |
+---------+
1 row in set (0.00 sec)

mysql> select bin(3.56);
+-----------+
| bin(3.56) |
+-----------+
| 11        |  -- 返回3的二进制表示，小数部分被忽略
+-----------+
1 row in set (0.00 sec)

• 使用conv函数进行进制转换

-- 十进制数30转为16进制表示
mysql> select conv(30,10,16);
+----------------+
| conv(30,10,16) |
+----------------+
| 1E             |
+----------------+
1 row in set (0.00 sec)

-- 十进制数9转为2进制表示
mysql> select conv(9,10,2);
+--------------+
| conv(9,10,2) |
+--------------+
| 1001         |
+--------------+
1 row in set (0.00 sec)

-- 十进制数38转为20进制表示
mysql> select conv(38,10,20);
+----------------+
| conv(38,10,20) |
+----------------+
| 1I             |
+----------------+
1 row in set (0.00 sec)

• 向下取整和向上取整

在实际的项目中，常用的取整方式有三种：零向取整、向上取整、向下取整。

mysql> select ceil(3.1);
+-----------+
| ceil(3.1) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

mysql> select ceil(-3.1);
+------------+
| ceil(-3.1) |
+------------+
|         -3 |
+------------+
1 row in set (0.00 sec)

mysql> select ceil(-3.9);
+------------+
| ceil(-3.9) |
+------------+
|         -3 |
+------------+
1 row in set (0.00 sec)

mysql> select floor(-4.2);
+-------------+
| floor(-4.2) |
+-------------+
|          -5 |
+-------------+
1 row in set (0.00 sec)

• 格式化数据

mysql> select format(1.234567,3);
+--------------------+
| format(1.234567,3) |
+--------------------+
| 1.235              |
+--------------------+
1 row in set (0.00 sec)

mysql> select format(1.234567,10);
+---------------------+
| format(1.234567,10) |
+---------------------+
| 1.2345670000        |
+---------------------+
1 row in set (0.01 sec)

mysql> select format(1.234567,0);
+--------------------+
| format(1.234567,0) |
+--------------------+
| 1                  |
+--------------------+
1 row in set (0.00 sec)

• 随机生成数据

通过rand()可以随机生成位于[0.0, 1.0)之间的数据，如果希望生成随机范围的数据，可以通过rand配合乘法与加减法来实现，通过配合format(... , 0)可以获取整数。如下代码，实现了通过rand()获取[0,10)、[5,15)以及[-3.0, 8) 范围内的整数。

-- 生成[0.0, 10.0)范围内的数据
mysql> select format(10 * rand(), 2);
+------------------------+
| format(10 * rand(), 2) |
+------------------------+
| 9.08                   |
+------------------------+
1 row in set (0.00 sec)

-- 生成[5.0, 15.0)范围内的数据
mysql> select format(10 * rand() + 5, 2);
+----------------------------+
| format(10 * rand() + 5, 2) |
+----------------------------+
| 5.56                       |
+----------------------------+
1 row in set (0.00 sec)

-- 生成[-3.0, 8.0)范围内的数据
mysql> select format(11 * rand() - 3, 2);
+----------------------------+
| format(11 * rand() - 3, 2) |
+----------------------------+
| 3.08                       |
+----------------------------+
1 row in set (0.00 sec)

• 取模运算

取模运算的规则：假设计算A % B，那么计算结果res的符号（正负）应与A相同，并且计算结果的绝对值为：abs(res) = abs(A) % abs(B)。

如：(-10) % 3 = -1，10 % (-3) = 1。

mysql> select mod(-10, 3);
+-------------+
| mod(-10, 3) |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

mysql> select mod(10, -3);
+-------------+
| mod(10, -3) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select mod(10, 3);
+------------+
| mod(10, 3) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

四. 其它函数

表4. 其它函数

函数	功能
user()	查看当前使用数据库的用户。
database()	查看当前使用的数据库。
row_count()	查看上一个操作影响的行数，-1表示影响0行数据。
md5(str)	获取字符串对应的md5码。
password(str)	对字符串进行加密以作为密码来使用。
ifnull(val1, val2)	如果val1为null返回val2，否则返回val1，类似三目运算符。

• 查看当前的用户、数据库和上次操作影响的行数

mysql> select user();
+--------+
| user() |
+--------+
| root@  |  -- 当前使用数据库的是超级管理员
+--------+
1 row in set (0.00 sec)

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

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 |  -- 上次操作影响0行数据
+-------------+
1 row in set (0.00 sec)

• 获取字符串的md5码以及对字符串加密

mysql> system clear;
mysql> select md5('abcdef');
+----------------------------------+
| md5('abcdef')                    |
+----------------------------------+
| e80b5017098950fc58aad83c8c14978e |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select password('abcdef');
+-------------------------------------------+
| password('abcdef')                        |
+-------------------------------------------+
| *C2D24DCA38E9E862098B85BF0AB35CAA52803797 |
+-------------------------------------------+
1 row in set, 1 warning (0.01 sec)

• 调用ifnull模拟三目运算符

对于ifnull(val1, val2)，如果val1和val2都为null，那么ifnull也会返回null。

mysql> select ifnull(100, 200);
+------------------+
| ifnull(100, 200) |
+------------------+
|              100 |
+------------------+
1 row in set (0.00 sec)

mysql> select ifnull(null, 200);
+-------------------+
| ifnull(null, 200) |
+-------------------+
|               200 |
+-------------------+
1 row in set (0.01 sec)

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