第5章 常用函数
喜欢学习的同学收藏+关注，一起成长！
经常编写程序的朋友一定体会得到函数的重要性，丰富的函数往往能使用户的工作事半功倍。函数能帮助用户做很多事情，比如说字符串的处理、数值的运算、日期的运算等，在这方面MySQL提供了多种内建函数帮助开发人员编写简单快捷的SQL语句，其中常用的函数有字符串函数、日期函数和数值函数。
在MySQL数据库中，函数可以用在SELECT语句及其子句（例如where、order by、having等）中，也可以用在UPDATE、DELETE语句及其子句中。本章将配合一些实例对这些常用函数进行详细的介绍。
5.1 字符串函数
字符串函数是最常用的一种函数了，如果大家编写过程序的话，不妨回过头去看看自己使用过的函数，可能会惊讶地发现字符串处理的相关函数占已使用过的函数很大一部分。MySQL中字符串函数也是最丰富的一类函数，表5-1中列出了这些函数以供参考。
下面通过具体的实例来逐个地研究每个函数的用法，需要注意的是这里的例子仅仅在于说明各个函数的使用方法，所以函数都是单个出现的，但是在一个具体的应用中通常可能需要综合几个甚至几类函数才能实现相应的应用。
l CANCAT(S1,S2,…Sn)函数：把传入的参数连接成为一个字符串。
下面的例子把“aaa”、“bbb”、“ccc”3个字符串连接成了一个字符串“aaabbbccc”。另外，任何字符串与NULL进行连接的结果都将是NULL。
mysql> select concat('aaa','bbb','ccc') ,concat('aaa',null);
+---------------------------+--------------------+
| concat('aaa','bbb','ccc') | concat('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc | NULL |
+---------------------------+--------------------+
1 row in set (0.05 sec)
l INSERT(str ,x,y,instr)函数：将字符串str从第x位置开始，y个字符长的子串替换为字符串instr。
下面的例子把字符串“beijing2008you”中的从第12个字符开始以后的3个字符替换成“me”。
mysql> select INSERT('beijing2008you',12,3, 'me') ;
+-------------------------------------+
| INSERT('beijing2008you',12,3, 'me') |
+-------------------------------------+
| beijing2008me |
+-------------------------------------+
1 row in set (0.00 sec)
l LOWER(str)和UPPER(str)函数：把字符串转换成小写或大写。
在字符串比较中，通常要将比较的字符串全部转换为大写或者小写，如下例所示：
mysql> select LOWER('BEIJING2008'), UPPER('beijing2008');
+----------------------+----------------------+
| LOWER('BEIJING2008') | UPPER('beijing2008') |
+----------------------+----------------------+
| beijing2008 | BEIJING2008 |
+----------------------+----------------------+
1 row in set (0.00 sec)
l LEFT(str,x)和 RIGHT(str,x)函数：分别返回字符串最左边的x个字符和最右边的x个字符。如果第二个参数是NULL，那么将不返回任何字符串。
下例中显示了对字符串“beijing2008”应用函数后的结果。
mysql> SELECT LEFT('beijing2008',7),LEFT('beijing',null),RIGHT('beijing2008',4);
+-----------------------+----------------------+------------------------+
| LEFT('beijing2008',7) | LEFT('beijing',null) | RIGHT('beijing2008',4) |
+-----------------------+----------------------+------------------------+
| beijing | | 2008 |
+-----------------------+----------------------+------------------------+
1 row in set (0.00 sec)
l LPAD(str,n ,pad)和 RPAD(str,n ,pad)函数：用字符串pad对str最左边和最右边进行填充,直到长度为n个字符长度。
下例中显示了对字符串“2008”和“beijing”分别填充后的结果。
mysql> select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');
+---------------------------+---------------------------+
| lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |
+---------------------------+---------------------------+
| beijingbeijingbe2008 | beijing2008200820082 |
+---------------------------+---------------------------+
1 row in set (0.00 sec)
l LTRIM(str)和 RTRIM(str)函数：去掉字符串str左侧和右侧空格。
下例中显示了字符串“beijing”加空格进行过滤后的结果。
mysql> select ltrim(' |beijing'),rtrim('beijing| ');
+---------------------+------------------------+
| ltrim(' |beijing') | rtrim('beijing| ') |
+---------------------+------------------------+
| |beijing | beijing| |
+---------------------+------------------------+
1 row in set (0.00 sec)
l REPEAT(str,x)函数：返回str重复x次的结果。
下例中对字符串“mysql”重复显示了3次。
mysql> select repeat('mysql ',3);
+--------------------+
| repeat('mysql ',3) |
+--------------------+
| mysql mysql mysql |
+--------------------+
1 row in set (0.00 sec)
l REPLACE(str,a,b)函数：用字符串b替换字符串str中所有出现的字符串a。
下例中用字符串“2008”代替了字符串“beijing_2010”中的“_2010”。
mysql> select replace('beijing_2010','_2010','2008');
+----------------------------------------+
| replace('beijing_2010','_2010','2008') |
+----------------------------------------+
| beijing2008 |
+----------------------------------------+
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('b','b'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
| -1 | 0 | 1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)
l TRIM(str)函数：去掉目标字符串的开头和结尾的空格。
下例中对字符串“$ beijing2008 $ ”进行了前后空格的过滤。
mysql> select trim(' $ beijing2008 $ ');
+-----------------------------+
| trim(' $ beijing2008 $ ') |
+-----------------------------+
| $ beijing2008 $ |
+-----------------------------+
1 row in set (0.00 sec)
l SUBSTRING(str,x,y)函数：返回从字符串str中的第x位置起y个字符长度的字串。
此函数经常用来对给定字符串进行字串的提取，如下例所示。
mysql> select substring('beijing2008',8,4),substring('beijing2008',1,7);
+------------------------------+------------------------------+
| substring('beijing2008',8,4) | substring('beijing2008',1,7) |
+------------------------------+------------------------------+
| 2008 | beijing |
+------------------------------+------------------------------+
5.2 数值函数
MySQL中另外一类很重要的函数就是数值函数，这些函数能处理很多数值方面的运算。可以想象，如果没有这些函数的支持，用户在编写有关数值运算方面的代码时将会困难重重，举个例子，如果没有ABS函数的话，如果要取一个数值的绝对值，就需要进行好多次判断才能返回这个值，而数字函数能够大大提高用户的工作效率。表5-2中列出了在MySQL中会经常使用的数值函数。
下面将结合实例对这些函数进行介绍。
l 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.09 sec)
l CEIL(x)函数：返回大于x的最大整数。
下例中显示了对0.8和－0.8分别CEIL后的结果。
mysql> select CEIL(-0.8),CEIL(0.8);
+------------+-----------+
| CEIL(-0.8) | CEIL(0.8) |
+------------+-----------+
| 0 | 1 |
+------------+-----------+
1 row in set (0.03 sec)
l FLOOR(x)函数：返回小于x的最大整数，和CEIL的用法刚好相反。
下例中显示了对0.8和－0.8分别FLOOR后的结果。
mysql> select FLOOR(-0.8), FLOOR(0.8);
+-------------+------------+
| FLOOR(-0.8) | FLOOR(0.8) |
+-------------+------------+
| -1 | 0 |
+-------------+------------+
1 row in set (0.00 sec)
l MOD(x，y)函数：返回x/y的模。
和x%y的结果相同，模数和被模数任何一个为NULL结果都为NULL。如下例所示：
mysql> select MOD(15,10),MOD(1,11),MOD(NULL,10);
+------------+-----------+--------------+
| MOD(15,10) | MOD(1,11) | MOD(NULL,10) |
+------------+-----------+--------------+
| 5 | 1 | NULL |
+------------+-----------+--------------+
1 row in set (0.00 sec)
l RAND()函数：返回0到1内的随机值。
每次执行结果都不一样，如下例所示：
mysql> select RAND(),RAND();
+------------------+------------------+
| RAND() | RAND() |
+------------------+------------------+
| 0.12090325459922 | 0.83369727882901 |
+------------------+------------------+
1 row in set (0.00 sec)
利用此函数可以取任意指定范围内的随机数，比如需要产生0～100内的任意随机整数，可以操作如下：
mysql> select ceil(100*rand()),ceil(100*rand());
+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+
| 91 | 15 |
+------------------+------------------+
1 row in set (0.00 sec)
l ROUND(x,y)函数：返回参数x的四舍五入的有y位小数的值。
如果是整数，将会保留y位数量的0；如果不写y，则默认y为0，即将x四舍五入后取整。适合于将所有数字保留同样小数位的情况。如下例所示。
mysql> select ROUND(1.1),ROUND(1.1,2),ROUND(1,2);
+------------+--------------+------------+
| ROUND(1.1) | ROUND(1.1,2) | ROUND(1,2) |
+------------+--------------+------------+
| 1 | 1.10 | 1.00 |
+------------+--------------+------------+
1 row in set (0.00 sec)
l TRUNCATE(x,y)函数：返回数字x截断为y位小数的结果。
注意TRUNCATE和ROUND的区别在于TRUNCATE仅仅是截断，而不进行四舍五入。下例中描述了二者的区别：
mysql> select ROUND(1.235,2),TRUNCATE(1.235,2);
+----------------+-------------------+
| ROUND(1.235,2) | TRUNCATE(1.235,2) |
+----------------+-------------------+
| 1.24 | 1.23 |
+----------------+-------------------+
1 row in set (0.00 sec)
5.3 日期和时间函数
有时我们可能会遇到这样的需求：当前时间是多少、下个月的今天是星期几、统计截止到当前日期前3天的收入总和等。这些需求就需要日期和时间函数来实现，表5-3列出了MySQL中支持的一些常用日期和时间函数。
下面结合一些实例来逐个讲解每个函数的使用方法。
l CURDATE()函数：返回当前日期，只包含年月日。
mysql> select CURDATE();
+------------+
| CURDATE() |
+------------+
| 2007-07-11 |
+------------+
1 row in set (0.03 sec)
l CURTIME()函数：返回当前时间，只包含时分秒。
mysql> select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 14:13:46 |
+-----------+
1 row in set (0.00 sec)
l NOW()函数：返回当前的日期和时间，年月日时分秒全都包含。
mysql> select NOW();
+---------------------+
| NOW() |
+---------------------+
| 2007-07-11 14:14:06 |
+---------------------+
1 row in set (0.00 sec)
l UNIX_TIMESTAMP(date)函数：返回日期date的UNIX时间戳。
mysql> select UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
| 1184134516 |
+-----------------------+
1 row in set (0.02 sec)
l FROM_UNIXTIME（unixtime）函数：返回UNIXTIME时间戳的日期值，和UNIX_TIMESTAMP(date)互为逆操作。
mysql> select FROM_UNIXTIME(1184134516) ;
+---------------------------+
| FROM_UNIXTIME(1184134516) |
+---------------------------+
| 2007-07-11 14:15:16 |
+---------------------------+
1 row in set (0.00 sec)
l WEEK(DATE)和YEAR(DATE)函数：前者返回所给的日期是一年中的第几周，后者返回所给的日期是哪一年。
mysql> select WEEK(now()),YEAR(now());
+-------------+-------------+
| WEEK(now()) | YEAR(now()) |
+-------------+-------------+
| 27 | 2007 |
+-------------+-------------+
1 row in set (0.02 sec)
l HOUR(time)和MINUTE(time)函数：前者返回所给时间的小时，后者返回所给时间的分钟。
mysql> select HOUR(CURTIME()),MINUTE(CURTIME());
+-----------------+-------------------+
| HOUR(CURTIME()) | MINUTE(CURTIME()) |
+-----------------+-------------------+
| 14 | 18 |
+-----------------+-------------------+
1 row in set (0.00 sec)
l MONTHNAME(date)函数：返回date的英文月份名称。
mysql> select MONTHNAME(now());
+------------------+
| MONTHNAME(now()) |
+------------------+
| July |
+------------------+
1 row in set (0.00 sec)
l DATE_FORMAT(date,fmt)函数：按字符串fmt格式化日期date值，此函数能够按指定的格式显示日期，可以用到的格式符如表5-4所示。
下面的例子将当前时间显示为“月，日，年”格式：
mysql> select DATE_FORMAT(now(),'%M,%D,%Y');
+-------------------------------+
| DATE_FORMAT(now(),'%M,%D,%Y') |
+-------------------------------+
| July,11th,2007 |
+-------------------------------+
1 row in set (0.00 sec)
l DATE_ADD(date,INTERVAL expr type)函数：返回与所给日期date相差INTERVAL时间段的日期。
其中INTERVAL是间隔类型关键字，expr是一个表达式，这个表达式对应后面的类型，type是间隔类型，MySQL提供了13种间隔类型，如表5-5所示。
表5-5 MySQL中的日期间隔类型
来看一个具体的例子，在这个例子中第1列返回了当前日期时间，第2列返回距离当前日期31天后的日期时间，第3列返回距离当前日期一年两个月后的日期时间。
mysql> select now() current,date_add(now(),INTERVAL 31 day) after31days,
date_add(now(),INTERVAL '1_2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2007-09-03 11:30:48 | 2007-10-04 11:30:48 | 2008-11-03 11:30:48 |
+---------------------+---------------------+------------------------+
1 row in set (0.01 sec)
同样也可以用负数让它返回之前的某个日期时间，如下第1列返回了当前日期时间，第2列返回距离当前日期31天前的日期时间，第3列返回距离当前日期一年两个月前的日期时间。
mysql> select now() current,date_add(now(),INTERVAL -31 day) after31days,date_a
dd(now(),INTERVAL '-1_-2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2007-09-03 11:36:35 | 2007-08-03 11:36:35 | 2006-07-03 11:36:35 |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)
l DATEDIFF（date1，date2）函数：用来计算两个日期之间相差的天数。
下面的例子计算出当前距离2008年8月8日的奥运会开幕式还有多少天：
mysql> select DATEDIFF('2008-08-08',now());
+------------------------------+
| DATEDIFF('2008-08-08',now()) |
+------------------------------+
| 328 |
+------------------------------+
1 row in set (0.01 sec)
5.4 流程函数
流程函数也是很常用的一类函数，用户可以使用这类函数在一个SQL语句中实现条件选择，这样做能够提高语句的效率。表5-6列出了MySQL中跟条件选择有关的流程函数，下面将通过具体的实例来讲解每个函数的用法。
表5-6 MySQL中的流程函数
下面的例子中模拟了对职员薪水进行分类，这里首先创建并初始化一个职员薪水表：
mysql> create table salary (userid int,salary decimal(9,2));
Query OK, 0 rows affected (0.06 sec)
插入一些测试数据：
mysql> insert into salary values(1,1000),(2,2000), (3,3000),(4,4000),(5,5000), (1,null);
Query OK, 6 rows affected (0.00 sec)
mysql> select * from salary;
+--------+---------+
| userid | salary |
+--------+---------+
| 1 | 1000.00 |
| 2 | 2000.00 |
| 3 | 3000.00 |
| 4 | 4000.00 |
| 5 | 5000.00 |
| 1 | NULL |
+--------+---------+
6 rows in set (0.00 sec)
接下来，通过这个表来介绍各个函数的应用。
l IF(value,t,f)函数：我们认为月薪在2000元以上的职员属于高薪，用“high”表示；而2000元以下的职员属于低薪，用“low”表示。
mysql> select if(salary>2000,'high','low') from salary;
+------------------------------+
| if(salary>2000,'high','low') |
+------------------------------+
| low |
| low |
| high |
| high |
| high |
+------------------------------+
5 rows in set (0.01 sec)
l IFNULL(value1,value2)函数：这个函数一般用来替换NULL值的，我们知道NULL值是不能参与数值运算的，下面这个语句就是把NULL值用0来替换。
mysql> select ifnull(salary,0) from salary;
+------------------+
| ifnull(salary,0) |
+------------------+
| 1000.00 |
| 2000.00 |
| 3000.00 |
| 4000.00 |
| 5000.00 |
| 0.00 |
+------------------+
6 rows in set (0.00 sec)
l CASE WHEN [value1] THEN[result1]…ELSE[default]END函数：我们也可以用case when…then函数实现上面例子中高薪低薪的问题。
mysql> select case when salary<=2000 then 'low' else 'high' end from salary;
+---------------------------------------------------+
| case when salary<=2000 then 'low' else 'high' end |
+---------------------------------------------------+
| low |
| low |
| high |
| high |
| high |
| high |
+---------------------------------------------------+
6 rows in set (0.00 sec)
CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END函数：这里还可以分多种情况把职员的薪水分多个档次，比如下面的例子分成高、中、低3种情况。同样还可以分成更多种情况，这里就不再举例了，有兴趣的读者可以自己测试一下。
mysql> select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
+-----------------------------------------------------------------------+
| case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end |
+-----------------------------------------------------------------------+
| low |
| mid |
| high |
| high |
| high |
| high |
+-----------------------------------------------------------------------+
6 rows in set (0.00 sec)
5.5 其他常用函数
MySQL提供的函数很丰富，除了前面介绍的字符串函数、数字函数、日期函数、流程函数以外还有很多其他函数，在此不再一一列举，有兴趣的读者可以参考MySQL官方手册。表5-7列举了一些其他常用的函数。
下面结合实例简单介绍一下这些函数的用法。
l DATABASE()函数：返回当前数据库名。
mysql> select DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
l VERSION()函数：返回当前数据库版本。
mysql> select VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.0.18-nt |
+-----------+
1 row in set (0.00 sec)
l USER()函数：返回当前登录用户名。
mysql> select USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.03 sec)
l INET_ATON(IP)函数：返回IP地址的网络字节序表示。
mysql> select INET_ATON('192.168.1.1');
+--------------------------+
| INET_ATON('192.168.1.1') |
+--------------------------+
| 3232235777 |
+--------------------------+
1 row in set (0.00 sec)
l INET_NTOA(num)函数：返回网络字节序代表的IP地址。
mysql> select INET_NTOA(3232235777);
+-----------------------+
| INET_NTOA(3232235777) |
+-----------------------+
| 192.168.1.1 |
+-----------------------+
1 row in set (0.00 sec)
INET_ATON(IP)和INET_NTOA(num)函数主要的用途是将字符串的IP地址转换为数字表示的网络字节序，这样可以更方便地进行IP或者网段的比较。比如在下面的表t中，要想知道在“192.168.1.3”和“192.168.1.20”之间一共有多少IP地址：
mysql> select * from t;
+--------------+
| 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 t where ip>='192.168.1.3' and ip<='192.168.1.20';
Empty set (0.01 sec)
结果没有如我们所愿，竟然是个空集。其实原因就在于字符串的比较是一个字符一个字符的比较，当对应字符相同时候，就比较下一个，直到遇到能区分出大小的字符，才停止比较，后面的字符也将忽略。显然，在此例中，“192.168.1.3”其实比“192.168.1.20”要“大”，因为“3”比“2”大，而不能用我们日常的思维3<20，所以“ip>='192.168.1.3' and ip<='192.168.1.20'”必然是个空集。
在这里，如果要想实现上面的功能，就可用函数INET_ATON来实现，将IP转换为字节序后再比较，如下所示：
mysql> select * from t 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)
结果完全符合我们的要求。
l PASSWORD(str)函数：返回字符串str的加密版本，一个41位长的字符串。
此函数只用来设置系统用户的密码，但是不能用来对应用的数据加密。如果应用方面有加密的需求，可以使用MD5等加密函数来实现。
下例中显示了字符串“123456”的PASSWORD加密后的值：
mysql> select PASSWORD('123456');
+-------------------------------------------+
| PASSWORD('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set (0.08 sec)
l MD5(str)函数：返回字符串str的MD5值，常用来对应用中的数据进行加密。
下例中显示了字符串“123456”的MD5值：
mysql> select MD5('123456');
+----------------------------------+
| MD5('123456') |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.06 sec)
5.6 小结
本章主要对MySQL常用的各类常用函数通过实例做了介绍。MySQL有很多内建函数，这些内建函数实现了很多应用需要的功能并且拥有很好的性能，如果用户在工作中需要实现某种功能，最好先查一下MySQL官方文档或者帮助，看是否已经有相应的函数实现了我们需要的功能，可以大大提高工作效率。由于篇幅所限，本章并没有介绍所有的函数，读者可以去进一步查询相关文档。
阿利著作知识分享