MySQL笔记-07 常用函数

MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。函数的执行速度非常快，可以提高MySQL的处理速度，简化用户的操作。本章将详细介绍MySQL函数的相关知识

MySQL函数是MySQL数据库提供的内置函数。这些内置函数可以帮助用户更加方便地处理表中的数据。主要包括：

MySQL的内置函数不但可以在SELECT查询语句中应用，同样也可以在INSERT、UPDATE和DELECT等语句中应用。例如，在INSERT语句中，应用日期时间函数获取系统的当前时间，并且将其添加到数据表中。MySQL内置函数可以对表中数据进行相应的处理，以便得到用户希望得到的数据。

1 数学函数

数学函数是MySQL中常用的一类函数，主要用于处理数字，包括整型和浮点数等。

1.1 ABS

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

mysql> 
1
2
3
4
5
6
7
8
9

1.2 CEIL和CEILIN

mysql> select ceil(1.5),ceil(1.9),ceil(1.4),ceil(0),ceil(null);
+-----------+-----------+-----------+---------+------------+
| ceil(1.5) | ceil(1.9) | ceil(1.4) | ceil(0) | ceil(null) |
+-----------+-----------+-----------+---------+------------+
|         2 |         2 |         2 |       0 |       NULL |
+-----------+-----------+-----------+---------+------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

1.3 FLOOR

mysql> select floor(1.5),floor(1.9),floor(1.4),floor(0),ceil(null);
+------------+------------+------------+----------+------------+
| floor(1.5) | floor(1.9) | floor(1.4) | floor(0) | ceil(null) |
+------------+------------+------------+----------+------------+
|          1 |          1 |          1 |        0 |       NULL |
+------------+------------+------------+----------+------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

1.4 MOD

mysql> select mod(9,2),mod(null,2),mod(2,null),mod(0,9),mod(9,0);
+----------+-------------+-------------+----------+----------+
| mod(9,2) | mod(null,2) | mod(2,null) | mod(0,9) | mod(9,0) |
+----------+-------------+-------------+----------+----------+
|        1 |        NULL |        NULL |        0 |     NULL |
+----------+-------------+-------------+----------+----------+
1 row in set, 1 warning (0.01 sec)

mysql> 
1
2
3
4
5
6
7
8
9

1.5 ROUND

mysql> select round(1.45),round(1.45,1),round(1.45,3),round(1.45,null);
+-------------+---------------+---------------+------------------+
| round(1.45) | round(1.45,1) | round(1.45,3) | round(1.45,null) |
+-------------+---------------+---------------+------------------+
|           1 |           1.5 |         1.450 |             NULL |
+-------------+---------------+---------------+------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

1.6 TRUNCATE

当y值是小数时，会进行四舍五入。例如y为1.6时，默认为2。

mysql> select truncate(1.45,0),truncate(1.45,1),truncate(1.45,1.6),truncate(1.45,-1.6),truncate(1.45,null);
+------------------+------------------+--------------------+---------------------+---------------------+
| truncate(1.45,0) | truncate(1.45,1) | truncate(1.45,1.6) | truncate(1.45,-1.6) | truncate(1.45,null) |
+------------------+------------------+--------------------+---------------------+---------------------+
|                1 |              1.4 |               1.45 |                   0 |                NULL |
+------------------+------------------+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

2 字符串函数

字符串函数是MySQL中最常用的一类函数，主要用于处理表中的字符串

2.1 CONCAT

mysql> select concat('ab','cd'),concat('ab',null),concat(1,'ab'),concat(1,2),concat(2,bin(10));
+-------------------+-------------------+----------------+-------------+-------------------+
| concat('ab','cd') | concat('ab',null) | concat(1,'ab') | concat(1,2) | concat(2,bin(10)) |
+-------------------+-------------------+----------------+-------------+-------------------+
| abcd              | NULL              | 1ab            | 12          | 21010             |
+-------------------+-------------------+----------------+-------------+-------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

2.2 CONCAT_WS

该函数可以实现指定分隔符并拼接字符串。

mysql> select concat_ws('^','a','b','c');
+----------------------------+
| concat_ws('^','a','b','c') |
+----------------------------+
| a^b^c                      |
+----------------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

2.3 INSERT

字符串的位置从1开始。

mysql> select insert('12345678',2,3,'abc');
+------------------------------+
| insert('12345678',2,3,'abc') |
+------------------------------+
| 1abc5678                     |
+------------------------------+
1 row in set (0.00 sec)

mysql> select insert('12345678',2,3,'abcdef');
+---------------------------------+
| insert('12345678',2,3,'abcdef') |
+---------------------------------+
| 1abcdef5678                     |
+---------------------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.4 LOWER和UPPER

mysql> select LOWER('aBcD'),UPPER('aBcD'),LCASE('aBcD'),UCASE('aBcD');
+---------------+---------------+---------------+---------------+
| LOWER('aBcD') | UPPER('aBcD') | LCASE('aBcD') | UCASE('aBcD') |
+---------------+---------------+---------------+---------------+
| abcd          | ABCD          | abcd          | ABCD          |
+---------------+---------------+---------------+---------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

2.5 LEFT和RIGHT

mysql> select LEFT('123456',3),RIGHT('123456',3),LEFT('123456',null),RIGHT('123456',null),LEFT('123456',0),RIGHT('123456',0);
+------------------+-------------------+---------------------+----------------------+------------------+-------------------+
| LEFT('123456',3) | RIGHT('123456',3) | LEFT('123456',null) | RIGHT('123456',null) | LEFT('123456',0) | RIGHT('123456',0) |
+------------------+-------------------+---------------------+----------------------+------------------+-------------------+
| 123              | 456               | NULL                | NULL                 |                  |                   |
+------------------+-------------------+---------------------+----------------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

2.6 LPAD和RPAD

mysql> select lpad('123',5,'a'),rpad('123',5,'b'),lpad('123',0,'a'),lpad('123',5,null),lpad('123',4,'aaa');
+-------------------+-------------------+-------------------+--------------------+---------------------+
| lpad('123',5,'a') | rpad('123',5,'b') | lpad('123',0,'a') | lpad('123',5,null) | lpad('123',4,'aaa') |
+-------------------+-------------------+-------------------+--------------------+---------------------+
| aa123             | 123bb             |                   | NULL               | a123                |
+-------------------+-------------------+-------------------+--------------------+---------------------+
1 row in set (0.01 sec)

mysql> 
1
2
3
4
5
6
7
8
9

2.7 LTRIM和RTRIM

mysql> select ltrim('  abcd'),rtrim('abcd  '),trim(' abcd '),trim('a' from 'aabcdaa');
+-----------------+-----------------+----------------+--------------------------+
| ltrim('  abcd') | rtrim('abcd  ') | trim(' abcd ') | trim('a' from 'aabcdaa') |
+-----------------+-----------------+----------------+--------------------------+
| abcd            | abcd            | abcd           | bcd                      |
+-----------------+-----------------+----------------+--------------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

2.8 REPEAT

mysql> select repeat('abc',3),repeat('abc',0),repeat('abc',null);
+-----------------+-----------------+--------------------+
| repeat('abc',3) | repeat('abc',0) | repeat('abc',null) |
+-----------------+-----------------+--------------------+
| abcabcabc       |                 | NULL               |
+-----------------+-----------------+--------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

2.9 REPLACE

mysql> select replace('abcd','a','b'),replace('abcd','a',null),replace('abcd',null,'a');
+-------------------------+--------------------------+--------------------------+
| replace('abcd','a','b') | replace('abcd','a',null) | replace('abcd',null,'a') |
+-------------------------+--------------------------+--------------------------+
| bbcd                    | NULL                     | NULL                     |
+-------------------------+--------------------------+--------------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

2.10 STRCMP

STRCMP(s1,s2)，比较字符串s1和s2的ASCII码值大小。如果s1比s2小，则返回-1；如果s1和s2相等返回0；如果s1比s2大，则返回1。

mysql> select strcmp('a','b'),strcmp('c','b'),strcmp('c','abcd'),strcmp(null,'z'),strcmp('z',null);
+-----------------+-----------------+--------------------+------------------+------------------+
| strcmp('a','b') | strcmp('c','b') | strcmp('c','abcd') | strcmp(null,'z') | strcmp('z',null) |
+-----------------+-----------------+--------------------+------------------+------------------+
|              -1 |               1 |                  1 |             NULL |             NULL |
+-----------------+-----------------+--------------------+------------------+------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

2.11 SUBSTRING

字符串起始位置从1开始。

mysql> select substring('123456',2,3),substring('123456',0,3),substring('123456',null,3),substring('123456',2,0);
+-------------------------+-------------------------+----------------------------+-------------------------+
| substring('123456',2,3) | substring('123456',0,3) | substring('123456',null,3) | substring('123456',2,0) |
+-------------------------+-------------------------+----------------------------+-------------------------+
| 234                     |                         | NULL                       |                         |
+-------------------------+-------------------------+----------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9

3 日期和时间函数

日期和时间函数是MySQL中另一最常用的函数，主要用于对表中的日期和时间数据的处理。

3.1 CURDATE

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2022-07-08 |
+------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

3.2 CURTIME

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:41:28  |
+-----------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

3.3 NOW

mysql> select now(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP();
+---------------------+---------------------+---------------------+---------------------+---------------------+
| now()               | CURRENT_TIMESTAMP() | LOCALTIME()         | SYSDATE()           | LOCALTIMESTAMP()    |
+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2022-07-08 15:43:20 | 2022-07-08 15:43:20 | 2022-07-08 15:43:20 | 2022-07-08 15:43:20 | 2022-07-08 15:43:20 |
+---------------------+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.01 sec)

mysql> 
1
2
3
4
5
6
7
8
9

3.4 UNIX_TIMESTAMP

mysql> select unix_timestamp(now()),unix_timestamp('2022/01/01 11:11:11'),unix_timestamp('2022-01-01 11:11:11');
+-----------------------+---------------------------------------+---------------------------------------+
| unix_timestamp(now()) | unix_timestamp('2022/01/01 11:11:11') | unix_timestamp('2022-01-01 11:11:11') |
+-----------------------+---------------------------------------+---------------------------------------+
|            1657266313 |                            1641006671 |                            1641006671 |
+-----------------------+---------------------------------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

3.5 FROM_UNIXTIME

mysql> select from_unixtime(1641006671);
+---------------------------+
| from_unixtime(1641006671) |
+---------------------------+
| 2022-01-01 11:11:11       |
+---------------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

3.6 DATE_FROMAT

DATE_FROMAT(data,fmt)按照字符串fmt格式化日期date值，其中fmt的日期格式可以为：

mysql> select date_format(now(),'%Y-%m-%d'),date_format('2022-01-01','%Y/%m/%d %H');
+-------------------------------+-----------------------------------------+
| date_format(now(),'%Y-%m-%d') | date_format('2022-01-01','%Y/%m/%d %H') |
+-------------------------------+-----------------------------------------+
| 2022-07-08                    | 2022/01/01 00                           |
+-------------------------------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

3.7 DATE_ADD

DATE_ADD(date,INTERVAL expr type)函数，返回与所给日期date相差INTERVAL时间段的日期。其中：

• INTERVAL是间隔类型关键字；
• expr是一个表达式，这个表达式对应后面的类型；
• type是间隔类型；

MySQL提供了13种间隔类型：

mysql> select now() current,date_add(now(),INTERVAL 31 day) after31days,date_add(now(),INTERVAL '-1_-2' year_month);
+---------------------+---------------------+---------------------------------------------+
| current             | after31days         | date_add(now(),INTERVAL '-1_-2' year_month) |
+---------------------+---------------------+---------------------------------------------+
| 2022-07-08 16:38:50 | 2022-08-08 16:38:50 | 2021-05-08 16:38:50                         |
+---------------------+---------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9

3.8 DATEDIFF

DATEDIFF(date1,date2)函数，用来计算两个日期之间相差的天数。

mysql> select datediff('2021-01-01',now()),datediff('2022-01-02','2021-07-06');
+------------------------------+-------------------------------------+
| datediff('2021-01-01',now()) | datediff('2022-01-02','2021-07-06') |
+------------------------------+-------------------------------------+
|                         -553 |                                 180 |
+------------------------------+-------------------------------------+
1 row in set (0.01 sec)

mysql> 
1
2
3
4
5
6
7
8
9

4 条件判断函数

条件函数用来在SQL语句中进行条件判断。根据不同的条件，执行不同的SQL语句。

5 系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。

6 加密函数

加密函数是MySQL中用来对数据进行加密的函数。因为数据库中有些很敏感的信息不希望被其他人看到，所以就可以通过加密的方式来使这些数据变成看似乱码的数据。

• PASSWORD(str)函数经常用来给密码加密。MySQL用户需要设置密码，用户不能将未加密的密码直接存储到MySQL的user表中。因为登录MySQL数据库时，数据库系统会将输入的密码先通过PASSWORD(str)函数加密，然后与数据库中的密码进行比较，匹配成功后才可以登录。

7 其他函数

MySQL中除了上述内置函数以外，还包含很多函数。例如，数字格式化函数FORMAT(x，n)，IP地址与数字的转换函数INET_ATON(ip)，还有加锁函数GET_LOCT(name，time)、解锁函数RELEASE_LOCK(name)等。

7.1 INET_ATON和INET_NTOA

INET_ATON和INET_NTOA函数主要的用途是将字符串的IP地址转换为数字表示的网络字节序，这样可以更方便地进行IP或者网段的比较。

mysql> select * from tip;
+--------------+
| ip           |
+--------------+
| 192.168.1.1  |
| 192.168.1.3  |
| 192.168.1.6  |
| 192.168.1.10 |
| 192.168.1.20 |
| 192.168.1.30 |
+--------------+
6 rows in set (0.00 sec)

mysql> select * from tip where ip>='192.168.1.3' and ip <='192.168.1.20';
Empty set (0.00 sec)

mysql> select * from tip where inet_aton(ip)>=inet_aton('192.168.1.3') and inet_aton(ip) <=inet_aton('192.168.1.20');
+--------------+
| ip           |
+--------------+
| 192.168.1.3  |
| 192.168.1.6  |
| 192.168.1.10 |
| 192.168.1.20 |
+--------------+
4 rows in set (0.00 sec)

mysql> 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28