MySQL中流程函数和数据库系统相关函数_mysql 与操作系统有关的函数-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_32838955/article/details/115110423

本文详细介绍了MySQL中的流程函数，如IF、IFNULL、CASE WHEN/ELSE，以及如何在SQL查询中运用它们提升查询效率。此外，还涵盖了数据库操作函数如DATABASE(), VERSION(), USER()等，以及IP地址转换函数INET_ATON()和INET_NTOA()在实际应用中的优化技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、流程函数

MySQL中流程函数常用与sql语句中实现所需条件选择，提高查询语句的执行效率，主要流程函数为以下几种：

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

示例：

mysql> select * from sal;
+--------+---------+
| userid | sal     |
+--------+---------+
|      1 | 1000.00 |
|      2 | 2000.00 |
|      3 | 3000.00 |
|      4 | 4000.00 |
|      5 | 5000.00 |
|      6 | 6000.00 |
|      1 |    NULL |
+--------+---------+
7 rows in set (0.00 sec)

mysql> select if(sal>3000,'high','low') from sal;
+---------------------------+
| if(sal>3000,'high','low') |
+---------------------------+
| low                       |
| low                       |
| low                       |
| high                      |
| high                      |
| high                      |
| low                       |
+---------------------------+
7 rows in set (0.05 sec)

mysql> select ifnull(sal,0) from sal;
+---------------+
| ifnull(sal,0) |
+---------------+
|       1000.00 |
|       2000.00 |
|       3000.00 |
|       4000.00 |
|       5000.00 |
|       6000.00 |
|          0.00 |
+---------------+
7 rows in set (0.04 sec)

mysql> select case when sal<3000 then 'low' else 'high' end from sal;
+-----------------------------------------------+
| case when sal<3000 then 'low' else 'high' end |
+-----------------------------------------------+
| low                                           |
| low                                           |
| high                                          |
| high                                          |
| high                                          |
| high                                          |
| high                                          |
+-----------------------------------------------+
7 rows in set (0.00 sec)

mysql> select userid,case when sal<3000 then 'low' else 'high' end from sal;
+--------+-----------------------------------------------+
| userid | case when sal<3000 then 'low' else 'high' end |
+--------+-----------------------------------------------+
|      1 | low                                           |
|      2 | low                                           |
|      3 | high                                          |
|      4 | high                                          |
|      5 | high                                          |
|      6 | high                                          |
|      1 | high                                          |
+--------+-----------------------------------------------+
7 rows in set (0.00 sec)

mysql> select userid,case sal when '1000' then 'low' when 2000 then 'mid' else 'high' end from sal;
+--------+----------------------------------------------------------------------+
| userid | case sal when '1000' then 'low' when 2000 then 'mid' else 'high' end |
+--------+----------------------------------------------------------------------+
|      1 | low                                                                  |
|      2 | mid                                                                  |
|      3 | high                                                                 |
|      4 | high                                                                 |
|      5 | high                                                                 |
|      6 | high                                                                 |
|      1 | high                                                                 |
+--------+----------------------------------------------------------------------+
7 rows in set (0.01 sec)

mysql>

需要注意的是null在比较大小时则是无穷大。所以以上示例中userid为1的在case的判断中都是high。

二、其他函数

1.DATABASE()：返回当前数据库名称。

mysql> use magl;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| magl       |
+------------+
1 row in set (0.00 sec)

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

mysql>

2.VERSION()：返回当前数据库版本。

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.02 sec)

mysql>

3.USER()：获取当前登录用户名。

[root@local129 ~]# mysql -h 192.168.172.129 -P 3306 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user();
+---------------+
| user()        |
+---------------+
| root@local129 |
+---------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@local129 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.7.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

本地登陆和远程登陆显示的有所区别。

4.INET_ATON(IP)：返回IP地址的网络字节序表示。给出一个作为字符串的网络地址，返回一个代表地址数值的整数。

mysql> select inet_aton('192.168.172.129');
+------------------------------+
| inet_aton('192.168.172.129') |
+------------------------------+
|                   3232279681 |
+------------------------------+
1 row in set (0.02 sec)

mysql>

还有另外对应的函数INET_NTOA(IP)，将给定一个数字网络地址，返回作为字符串的该地址的电地址表示。也就是正常看到的IP地址。

mysql> select inet_ntoa('3232279681');
+-------------------------+
| inet_ntoa('3232279681') |
+-------------------------+
| 192.168.172.129         |
+-------------------------+
1 row in set (0.00 sec)

mysql>

当前很多应用都是用字符串char(15)来存储IP地址（占16个字节），利用INET_ATON（）和INET_NTOA（）函数，来存储IP地址和INT值之间的转化，只需要4个字节，节省了存储空间，同时效率也高很多。

同时也方便比较两个IP。尤其在各种防火墙规则过滤算法中有很大作用。
例如，有一个IP列表，现在查询在IP 192.168.172.15和192.168.172.20之间的IP有哪些：

mysql> select * from ip_list;
+----------------+
| ip             |
+----------------+
| 192.168.172.15 |
| 192.168.172.17 |
| 192.168.172.19 |
| 192.168.172.20 |
| 192.168.172.21 |
| 192.168.172.2  |
| 192.168.172.3  |
| 192.168.172.6  |
+----------------+
8 rows in set (0.00 sec)

mysql> select * from ip_list where ip>='192.168.172.15' and ip<='192.168.172.20';
+----------------+
| ip             |
+----------------+
| 192.168.172.15 |
| 192.168.172.17 |
| 192.168.172.19 |
| 192.168.172.20 |
| 192.168.172.2  |
+----------------+
5 rows in set (0.00 sec)

mysql>

很显然查出的记过中有不符合要求的，因此就需要使用INET_ATON函数来进行比较：

mysql> select * from ip_list where inet_aton(ip)>=inet_aton('192.168.172.15') and inet_aton(ip)<=inet_aton('192.168.172.20');
+----------------+
| ip             |
+----------------+
| 192.168.172.15 |
| 192.168.172.17 |
| 192.168.172.19 |
| 192.168.172.20 |
+----------------+
4 rows in set (0.00 sec)

mysql>

本次主要调查的是INET_ATON(EXPR): 将IP地址格式定义为（A.B.C.D）转化后INT数值计算方式为：A2^ 24 + B2^ 16+C*2^8+D
例如：

mysql> select inet_aton('1.1.1.1');
+----------------------+
| inet_aton('1.1.1.1') |
+----------------------+
|             16843009 |
+----------------------+
1 row in set (0.00 sec)

结果：12^ 24+12^ 16+1*2^8+1=16843009

5.PASSWORD(str)：返回字符串str的加密版本，一个41位长的字符串。
此函数主要用来设置用户密码，但不能用于应用数据的加密。

mysql> select password('1234root');
+-------------------------------------------+
| password('1234root')                      |
+-------------------------------------------+
| *5F0989D527DB91B75FB710058F4FC4554E69FC5E |
+-------------------------------------------+
1 row in set, 1 warning (0.04 sec)

mysql>

常用与修改mysql.user表中用户的操作：

mysql> update mysql.user set authentication_string=password('*******') where user='*******';  #修改密码成功
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;  #立即生效
Query OK, 0 rows affected (0.00 sec)

6.MD5(str)：返回字符串str的MD5值，可用来对应用中的数据进行加密。

mysql> select md5('root123');
+----------------------------------+
| md5('root123')                   |
+----------------------------------+
| ff9830c42660c1dd1942844f8069b74a |
+----------------------------------+
1 row in set (0.03 sec)

mysql>