深入浅出MySQL-04-【常用函数】-EW帮帮网

前言

环境：

Window11
MySQL-8.0.35

1.字符串函数

最常用的一种函数。

注意：在 MySQL 中，字符串的下标（或称为索引）是从 1 开始的，而不是从 0 开始。

函数	功能
CONCAT(s1, s2, …, sn)	连接s1, s2, …, sn 为一个字符串
INSERT(str, x, y, instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr
LOWER(str)	将字符串str中的所有字符转换为小写
UPPER(str)	将字符串str中的所有字符转换为大写
LEFT(str, x)	返回字符串str最左边的x个字符
RIGHT(str, x)	返回字符串str最右边的x个字符
LPAD(str, n, pad)	用字符串pad对str最左边进行填充，直到长度为n个字符长度
RPAD(str, n, pad)	用字符串pad对str最右边进行填充，直到长度为n个字符长度
LTRIM(str)	去掉字符串str左侧的空格
RTRIM(str)	去掉字符串str右侧的空格
REPEAT(str, x)	返回str重复x次的结果
REPLACE(str, a, b)	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1, s2)	比较字符串s1和s2
TRIM(str)	去掉字符串行尾和行头的空格
SUBSTRING(str, x, y)	返回从字符串str的x位置起y个字符长度的字符串

CONCAT(s1, s2, …, sn)函数，把传入的字符串连接成为一个字符串。但是如果其中有NULL，结果就是NULL，因为任何字符串和NULL进行连接的结果都是NULL。

mysql> select concat('aa','bb','cc'), concat('aa','bb', null);
+------------------------+-------------------------+
| concat('aa','bb','cc') | concat('aa','bb', null) |
+------------------------+-------------------------+
| aabbcc                 | NULL                    |
+------------------------+-------------------------+
1 row in set (0.01 sec)

INSERT(str, x, y, instr)函数，将字符串str从第x位置开始，y个字符长的字串替换为字符串instr。

// 第6个位置也就是 H 开始，5个字符的长度 Hello 替换为 MySQL
mysql> select insert('12345Hello', 6, 5, 'MySQL');
+-------------------------------------+
| insert('12345Hello', 6, 5, 'MySQL') |
+-------------------------------------+
| 12345MySQL                          |
+-------------------------------------+
1 row in set (0.01 sec)

LOWER(str) 和 UPPER(str)函数，把字符串转换为小写或者大写。

mysql> select lower('我是大写转小写ABC'), upper('我是小写转大写abc');
+----------------------------+----------------------------+
| lower('我是大写转小写ABC') | upper('我是小写转大写abc') |
+----------------------------+----------------------------+
| 我是大写转小写abc          | 我是小写转大写ABC          |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

LEFT(str, x) 和 RIGTH(str, x)函数，分别是返回字符串最左边的x字符和最右边的x个字符，如果第二个参数为NULL，那么将不返回任何字符串，返回NULL。

mysql> select left('abcdef', 5), right('abcdef', 5), left('abcdef', null);
+-------------------+--------------------+----------------------+
| left('abcdef', 5) | right('abcdef', 5) | left('abcdef', null) |
+-------------------+--------------------+----------------------+
| abcde             | bcdef              | NULL                 |
+-------------------+--------------------+----------------------+
1 row in set (0.01 sec)

LPAD(str, n, pad) 和 RPAD(str, n, pad)函数，分别用字符串pad 对 str 最左边和最右边进行填充，直到长度为n个字符长度。

mysql> select lpad('abc', 5, 00), lpad('abc', 5, 'hellomysql'), rpad('abc', 5, 0), rpad('abc', 5, 'hellomysql');
+--------------------+------------------------------+-------------------+------------------------------+
| lpad('abc', 5, 00) | lpad('abc', 5, 'hellomysql') | rpad('abc', 5, 0) | rpad('abc', 5, 'hellomysql') |
+--------------------+------------------------------+-------------------+------------------------------+
| 00abc              | heabc                        | abc00             | abche                        |
+--------------------+------------------------------+-------------------+------------------------------+
1 row in set (0.01 sec)

LTRIM(str) 和 RTRIM(str)函数，分别去掉字符串str左侧和右侧的空格。

mysql> select ltrim('   |abc|   '), rtrim('   |abc|   ');
+----------------------+----------------------+
| ltrim('   |abc|   ') | rtrim('   |abc|   ') |
+----------------------+----------------------+
| |abc|                |    |abc|             |
+----------------------+----------------------+
1 row in set (0.00 sec)

REPEAT(str, x)函数，返回str重复x次的结果。

mysql> select repeat('HelloMySQL==', 5);
+--------------------------------------------------------------+
| repeat('HelloMySQL==', 5)                                    |
+--------------------------------------------------------------+
| HelloMySQL==HelloMySQL==HelloMySQL==HelloMySQL==HelloMySQL== |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

REPLACE(str, a, b)函数，用字符串b替换字符串str中所有出现的字符串a。

mysql> select replace('abbaaccaaadd', 'a', '==');
+------------------------------------+
| replace('abbaaccaaadd', 'a', '==') |
+------------------------------------+
| ==bb====cc======dd                 |
+------------------------------------+
1 row in set (0.00 sec)

STRCMP(s1, s2)函数，比较字符串s1和s2的ASCII码值的大小，如果s1<s2，返回-1。如果s1=s2，返回0。如果s1>s2，返回1.

mysql> select strcmp('a','b'), strcmp('a','a'),strcmp('b','a');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('a','a') | strcmp('b','a') |
+-----------------+-----------------+-----------------+
|              -1 |               0 |               1 |
+-----------------+-----------------+-----------------+
1 row in set (0.01 sec)

SUBSTRING(str, x, y)函数，返回从字符串str中的第x位置起y个字符长度的字符串。

mysql> select substring('abcdefg', '2', 5), substring('你好我是张三', 3, 4);
+------------------------------+---------------------------------+
| substring('abcdefg', '2', 5) | substring('你好我是张三', 3, 4) |
+------------------------------+---------------------------------+
| bcdef                        | 我是张三                        |
+------------------------------+---------------------------------+
1 row in set (0.00 sec)

2.数值函数

处理数值方面的运算。

函数	功能
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最小整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x, y)	返回x/y的模, 等价于前面运算符章节中的%
RAND()	返回0~1内的随机值
ROUND(x, y)	返回参数x的四舍五入的有y位小数的值
TRUNCATE(x, y)	返回数字x截断为y位小数的结果

ABS(x)函数，返回x的绝对值。

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

CEIL(x)函数，返回大于x的最小整数值。

mysql> select ceil(-0.8), ceil(-1.5), ceil(0.8), ceil(1.5);
+------------+------------+-----------+-----------+
| ceil(-0.8) | ceil(-1.5) | ceil(0.8) | ceil(1.5) |
+------------+------------+-----------+-----------+
|          0 |         -1 |         1 |         2 |
+------------+------------+-----------+-----------+
1 row in set (0.01 sec)

FLOOR(x)函数，返回小于x的最大整数值。和上面的CEIL正好相反。

mysql> select floor(-0.8), floor(-1.5), floor(0.8), floor(1.5);
+-------------+-------------+------------+------------+
| floor(-0.8) | floor(-1.5) | floor(0.8) | floor(1.5) |
+-------------+-------------+------------+------------+
|          -1 |          -2 |          0 |          1 |
+-------------+-------------+------------+------------+
1 row in set (0.00 sec)

MOD(x, y)函数，返回 x/y 的模，等价于前面符号章节的%操作。x和y的值任何一个为NULL，结果就是NULL。

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

RAND()函数，返回0~1的随机值。

mysql> select rand(), rand(), rand();
+--------------------+--------------------+--------------------+
| rand()             | rand()             | rand()             |
+--------------------+--------------------+--------------------+
| 0.5018647653068088 | 0.8444691345509786 | 0.7167514075681115 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

RAND函数可以取任意指定范围内的随机数，例如需要0~100之间的任意随机整数，可以如下操作：

mysql> select ceil(rand()*100), ceil(rand()*100),  ceil(rand()*100);
+------------------+------------------+------------------+
| ceil(rand()*100) | ceil(rand()*100) | ceil(rand()*100) |
+------------------+------------------+------------------+
|                6 |               11 |               36 |
+------------------+------------------+------------------+
1 row in set (0.00 sec)

ROUND(x, y)函数，返回参数x的四舍五入的有y位小数的值。如果是整数，将会保留y位数量的0。如果不写y，则默认y就是0，也就是将四舍五入后取整。

mysql> select round(1.456, 2), round(1.456), round(1.456, 4), round(1), round(1.456, 0), round(1,3);
+-----------------+--------------+-----------------+----------+-----------------+------------+
| round(1.456, 2) | round(1.456) | round(1.456, 4) | round(1) | round(1.456, 0) | round(1,3) |
+-----------------+--------------+-----------------+----------+-----------------+------------+
|            1.46 |            1 |           1.456 |        1 |               1 |          1 |
+-----------------+--------------+-----------------+----------+-----------------+------------+
1 row in set (0.00 sec)

TRUNCATE(x, y)函数，返回数字x截断为y位小数的结果。注意和上面ROUND的区别，这里是截断不进行四舍五入操作。

mysql> select truncate(1.456, 2), truncate(1.456, 4), truncate(1, 2);
+--------------------+--------------------+----------------+
| truncate(1.456, 2) | truncate(1.456, 4) | truncate(1, 2) |
+--------------------+--------------------+----------------+
|               1.45 |              1.456 |              1 |
+--------------------+--------------------+----------------+
1 row in set (0.00 sec)

3.日期和时间函数

常用的日期函数如下：

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
UNIX_TIMESTAMP(date)	返回日期date的UNIX时间戳
FROM_UNIXTIME	返回UNIX时间戳的日期值
WEEK(date)	返回日期date为一年中的第几周
YEAR(date)	返回日期date的年份
HOUR(time)	返回时间time的小时值
MINUTE(time)	返回时间time的分钟值
MONTH(date)	返回日期date为一年中的第几个月
MONTHNAME(date)	返回日期date的月份名
DATE_FORMAT(date, fmt)	返回按照字符串fmt格式化日期date后的值
DATE_ADD(date, INTERVAL expr type)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr, expr2)	返回起始时间expr和结束日期expr2之间的天数

CURDATE()函数，返回当前日期，只包含年、月、日。
CURTIME()函数，返回当前时间，只包含时、分、秒。
NOW()函数，返回当前日期和时间，年、月、日、时、分、秒都包含。

mysql> select curdate(), curtime(), now();
+------------+-----------+---------------------+
| curdate()  | curtime() | now()               |
+------------+-----------+---------------------+
| 2024-04-24 | 16:00:26  | 2024-04-24 16:00:26 |
+------------+-----------+---------------------+
1 row in set (0.01 sec)

UNIX_TIMESTAMP(date)函数，返回日期date的UNIX时间戳。

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1713946688 |
+-----------------------+
1 row in set (0.01 sec)

FROM_UNIXTIME(unixtime)函数，返回UNIXTIME时间戳的日期值，和上面的UNIX_TIMESTAMP(date)函数互为逆操作。

mysql> select unix_timestamp(now()), now();
+-----------------------+---------------------+
| unix_timestamp(now()) | now()               |
+-----------------------+---------------------+
|            1713946819 | 2024-04-24 16:20:19 |
+-----------------------+---------------------+
1 row in set (0.00 sec)

// 对比上面unix_timestamp的值，互为逆操作
mysql> select from_unixtime(1713946819);
+---------------------------+
| from_unixtime(1713946819) |
+---------------------------+
| 2024-04-24 16:20:19       |
+---------------------------+
1 row in set (0.00 sec)

WEEK(date) 和 YEAR(date)函数，分别返回参数date是一年中的第几周和哪一年。

mysql> select week(now()), year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
|          16 |        2024 |
+-------------+-------------+
1 row in set (0.00 sec)

HOUR(time) 和 MINUTE(time)函数，分别返回参数time的小时和分钟。

mysql> select now(), hour(now()), minute(now());
+---------------------+-------------+---------------+
| now()               | hour(now()) | minute(now()) |
+---------------------+-------------+---------------+
| 2024-04-24 16:23:27 |          16 |            23 |
+---------------------+-------------+---------------+
1 row in set (0.01 sec)

MONTHNAME(date)函数，返回参数date的英文月份名称。
MONTH(date)函数，返回参数date为一年中的第几个月。

mysql> select now(), monthname(now()), month(now());
+---------------------+------------------+--------------+
| now()               | monthname(now()) | month(now()) |
+---------------------+------------------+--------------+
| 2024-04-24 16:25:18 | April            |            4 |
+---------------------+------------------+--------------+
1 row in set (0.00 sec)

DATE_FORMAT(date, fmt)函数，按照字符串fmt格式化日期date，fmt的格式符如下：
- %Y：四位数的年份
- %y：两位数的年份
- %m：两位数的月份（01-12）
- %c：月份（1-12）
- %d：两位数的日（01-31）
- %e：日（1-31）
- %H：两位数的小时（00-23）
- %h 或 %I：两位数的小时（01-12）
- %i：两位数的分钟（00-59）
- %s：两位数的秒（00-59）
- %p：AM 或 PM
- %r：时间，12 小时制（hh:mm:ss AM 或 PM）
- %T 或 %X：时间，24 小时制（hh:mm:ss）
- %k：小时（0-23）
- %l：小时（1-12）
- %W：星期几的完整名称（例如：Sunday）
- %a：星期几的缩写（例如：Sun）
- %j：一年的第几天（001-366）
- %U：周数（星期天为一周的开始，00-53）
- %u：周数（星期一为一周的开始，00-53）
- %V：周数（星期天为一周的开始，但第一周至少要有 4 天在这一年中，1-53）
- %v：周数（星期一为一周的开始，但第一周至少要有 4 天在这一年中，1-53）
- %D：美国格式的日期（月/日/年）
- %F：年-月-日格式的日期（YYYY-MM-DD）

mysql> select date_format(now(), '%Y-%m-%d %H:%i:%s'),date_format(now(), '%Y/%m/%d %H-%i-%s');
+-----------------------------------------+-----------------------------------------+
| date_format(now(), '%Y-%m-%d %H:%i:%s') | date_format(now(), '%Y/%m/%d %H-%i-%s') |
+-----------------------------------------+-----------------------------------------+
| 2024-04-24 16:31:36                     | 2024/04/24 16-31-36                     |
+-----------------------------------------+-----------------------------------------+
1 row in set (0.00 sec)

DATE_ADD(date, INTERVAL expr type)函数，用于向日期值添加指定的时间间隔，INTERVAL是间隔类型的关键字，expr是表达式，要添加的时间间隔的数量或值，type表示间隔类型，如下：

expr间隔类型	描述	格式
HOUR	小时	hh
MINUTE	分	mm
SECOND	秒	ss
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
YEAR_MONTH	年和月	YY-MM
DAY_HOUR	日和小时	DD hh
DAY_MINUTE	日和分钟	DD hh:mm
DAY_SECOND	日和秒	DD hh:mm:ss
HOUR_MINUTE	小时和分	hh:mm
HOUR_SECOND	小时和秒	hh:ss
MINUTE_SECOND	分钟和秒	mm:ss

// -1 day 可以用来表示减去日给定的日期
mysql> select now(), date_add(now(), interval 30 day), date_add(now(), interval 1 month), date_add(now(), interval -1 day);
+---------------------+----------------------------------+-----------------------------------+----------------------------------+
| now()               | date_add(now(), interval 30 day) | date_add(now(), interval 1 month) | date_add(now(), interval -1 day) |
+---------------------+----------------------------------+-----------------------------------+----------------------------------+
| 2024-04-24 16:42:58 | 2024-05-24 16:42:58              | 2024-05-24 16:42:58               | 2024-04-23 16:42:58              |
+---------------------+----------------------------------+-----------------------------------+----------------------------------+
1 row in set (0.01 sec)

DATEDIFF(date1, date2)函数，用来计算两个日期之间相差的天数。date1 - date2的，所以可能出现负数的差值，如下。

mysql> select datediff('2024-04-20',now()), now(), datediff(now(), '2024-04-20');
+------------------------------+---------------------+-------------------------------+
| datediff('2024-04-20',now()) | now()               | datediff(now(), '2024-04-20') |
+------------------------------+---------------------+-------------------------------+
|                           -4 | 2024-04-24 16:45:34 |                             4 |
+------------------------------+---------------------+-------------------------------+
1 row in set (0.00 sec)

4.流程函数

这类函数可以在SQL中实现条件选择，如下：

函数	功能
IF(value, t, f)	如果value是真，返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [expr] THEN [result] … ELSE [default] END	如果expr表达式是真，返回result，否则返回default
CASE [expr] WHEN [value] THEN [result] … ELSE [default] END	如果expr等于value1，返回result，否则返回default

这里测试示例我们模拟人员表的年龄进行分类，先创建一个person表，并插入一些数据，如下：

// 主键自增
mysql> create table person (
    -> id int primary key auto_increment,
    -> age int
    -> );
Query OK, 0 rows affected (0.02 sec)

// 插入年龄数据
mysql> insert into person (age) values (10),(18),(20),(30),(50),(70),(90),(100),(null);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

IF(value, t, f)函数，这里认为年龄在60以上的属于高龄，用 “老人” 表示，而60以下的属于年轻人，用”年轻人“表示。

mysql> select id, age, if(age>60, '老人', '年轻人') as info from person;
+----+------+--------+
| id | age  | info   |
+----+------+--------+
|  1 |   10 | 年轻人 |
|  2 |   18 | 年轻人 |
|  3 |   20 | 年轻人 |
|  4 |   30 | 年轻人 |
|  5 |   50 | 年轻人 |
|  6 |   70 | 老人   |
|  7 |   90 | 老人   |
|  8 |  100 | 老人   |
|  9 | NULL | 年轻人 |
+----+------+--------+
9 rows in set (0.00 sec)

IFNULL(value1, value2)函数，这个函数一般用来替换NULL值，NULL值是不能参与数值运算的，举例替换NULL值为0，如下

// id = 9的那条数据，ifnull替换为了0
mysql> select id, age, ifnull(age, 0) from person;
+----+------+----------------+
| id | age  | ifnull(age, 0) |
+----+------+----------------+
|  1 |   10 |             10 |
|  2 |   18 |             18 |
|  3 |   20 |             20 |
|  4 |   30 |             30 |
|  5 |   50 |             50 |
|  6 |   70 |             70 |
|  7 |   90 |             90 |
|  8 |  100 |            100 |
|  9 | NULL |              0 |
+----+------+----------------+
9 rows in set (0.00 sec)

CASE WHEN [expr] THEN [result] … ELSE [default] END 函数，允许根据一个或多个条件返回不同的值，它的基本完整语法如下：

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

- condition1, condition2, ... 是要评估的条件。
- result1, result2, ... 是当对应条件为真时返回的结果。
- ELSE 子句是可选的，用于当所有条件都不满足时返回的结果。
- END 关键字标志着 CASE 语句的结束。

继续使用年龄在60以上的属于高龄，用 “老人” 表示，而60以下的属于年轻人，用”年轻人“表示的这个列子，如下：

// 注意 else 的使用，如果没有前面符合条件的，都是用 else 来表示
mysql> select id, age, case when age>60 then '老人' when age< 60 then '年轻人' else '=====' end from person;
+----+------+--------------------------------------------------------------------------+
| id | age  | case when age>60 then '老人' when age< 60 then '年轻人' else '=====' end |
+----+------+--------------------------------------------------------------------------+
|  1 |   10 | 年轻人                                                                   |
|  2 |   18 | 年轻人                                                                   |
|  3 |   20 | 年轻人                                                                   |
|  4 |   30 | 年轻人                                                                   |
|  5 |   50 | 年轻人                                                                   |
|  6 |   70 | 老人                                                                     |
|  7 |   90 | 老人                                                                     |
|  8 |  100 | 老人                                                                     |
|  9 | NULL | =====                                                                    |
+----+------+--------------------------------------------------------------------------+
9 rows in set (0.00 sec)

CASE [expr] WHEN [value] THEN [result] … ELSE [default] END 函数，这其实是case的一个简单函数，case后面跟着列名或者列的表达式，when后面跟表达式（case后面的）所有可能的值（不是值得范围，是具体等于的值），如果相等，就返回then后面的值，如下：

mysql> select id, age, (case age when 10 then '孩子' when '20' then '年轻人' when 100 then '老者' else '===' end) as info from person;
+----+------+--------+
| id | age  | info   |
+----+------+--------+
|  1 |   10 | 孩子   |
|  2 |   18 | ===    |
|  3 |   20 | 年轻人 |
|  4 |   30 | ===    |
|  5 |   50 | ===    |
|  6 |   70 | ===    |
|  7 |   90 | ===    |
|  8 |  100 | 老者   |
|  9 | NULL | ===    |
+----+------+--------+
9 rows in set (0.00 sec)

END

未完，持续更新中… …

深入浅出MySQL-04-【常用函数】

前言

1.字符串函数

2.数值函数

3.日期和时间函数

4.流程函数

END

网站公告

今日签到

热门文章

最新发布