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

前言

环境：

• Window11
• MySQL-8.0.35

1.字符串函数

最常用的一种函数。

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

• 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)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7
8

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

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

• 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)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

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

mysql> select ltrim('   |abc|   '), rtrim('   |abc|   ');
+----------------------+----------------------+
| ltrim('   |abc|   ') | rtrim('   |abc|   ') |
+----------------------+----------------------+
| |abc|                |    |abc|             |
+----------------------+----------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

2.数值函数

处理数值方面的运算。

• 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)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

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

mysql> select rand(), rand(), rand();
+--------------------+--------------------+--------------------+
| rand()             | rand()             | rand()             |
+--------------------+--------------------+--------------------+
| 0.5018647653068088 | 0.8444691345509786 | 0.7167514075681115 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

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)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

3.日期和时间函数

常用的日期函数如下：

• 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)
1
2
3
4
5
6
7

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

mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1713946688 |
+-----------------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

mysql> select week(now()), year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
|          16 |        2024 |
+-------------+-------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

• 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)
1
2
3
4
5
6
7

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

// -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)
1
2
3
4
5
6
7
8

• 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)
1
2
3
4
5
6
7

4.流程函数

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

这里测试示例我们模拟人员表的年龄进行分类，先创建一个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
1
2
3
4
5
6
7
8
9
10
11

• 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

CASE   
    WHEN condition1 THEN result1  
    WHEN condition2 THEN result2  
    ...  
    ELSE resultN  
END
1
2
3
4
5
6

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

继续使用 年龄在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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• 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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

5.JSON函数

END, 持续更新中…

未完，持续更新中… …