MySQL 的常用函数(六)

其中常用的函数有字 符串函数、日期函数和数值函数。

字符串函数 

字符串函数是常用的一种函数了，如果大家编写过程序的话，不妨回过头去看看自己使用 过的函数，可能会惊讶地发现字符串处理的相关函数占已使用过的函数很大一部分。MySQL 中字符串函数也是丰富的一类函数，表 5-1 中列出了这些函数以供参考。 

函数	功能
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个字符长度的字串

 

CANCAT(S1,S2,…Sn)函数

把传入的参数连接成为一个字符串。 下面的例子把“aaa”、“bbb”、“ccc”3 个字符串连接成了一个字符串“aaabbbccc”。另外， 任何字符串与 NULL 进行连接的结果都将是 NULL。 mysql 

mysql>  select concat('aaa','bbb','ccc') ,concat('aaa',null);
+---------------------------+--------------------+
| concat('aaa','bbb','ccc') | concat('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc                 | NULL               |
+---------------------------+--------------------+
1 row in set (0.00 sec)

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.12 sec)

 LOWER(str)和 UPPER(str)函数

把字符串转换成小写或大写。 在字符串比较中，通常要将比较的字符串全部转换为大写或者小写，如下例所示： 
 

mysql> select LOWER('BEIJING2008'), UPPER('beijing2008');
+----------------------+----------------------+
| LOWER('BEIJING2008') | UPPER('beijing2008') |
+----------------------+----------------------+
| beijing2008          | BEIJING2008          |
+----------------------+----------------------+
1 row in set (0.14 sec)

LEFT(str,x)和 RIGHT(str,x)函数：

分别返回字符串左边的x个字符和右边的x个字符。 如果第二个参数是 NULL，那么将不返回任何字符串。 下例中显示了对字符串“beijing2008”应用函数后的结果。 

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)

LTRIM(str)和 RTRIM(str)函数

去掉字符串 str 左侧和右侧空格。 下例中显示了字符串“beijing”加空格进行过滤后的结果。 

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

REPEAT(str,x)函数：

返回 str 重复 x 次的结果。 

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

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.10 sec)

TRIM(str)函数

去掉目标字符串的开头和结尾的空格。 下例中对字符串“$ beijing2008 $   ”进行了前后空格的过滤。 

mysql>  select trim(' $ beijing2008 $   ');
+-----------------------------+
| trim(' $ beijing2008 $   ') |
+-----------------------------+
| $ beijing2008 $             |
+-----------------------------+
1 row in set (0.31 sec)

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                      |
+------------------------------+------------------------------+
1 row in set (0.00 sec)

 

数值函数

MySQL 中另外一类很重要的函数就是数值函数，这些函数能处理很多数值方面的运算。可 以想象，如果没有这些函数的支持，用户在编写有关数值运算方面的代码时将会困难重重， 举个例子，如果没有 ABS 函数的话，如果要取一个数值的绝对值，就需要进行好多次判断 才能返回这个值，而数字函数能够大大高用户的工作效率。

函数	功能
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.29 sec)

CEIL(x)函数

返回大于 x的大整数

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

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 (1.52 sec)

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)

RAND()函数

返回 0 到 1 内的随机值。 每次执行结果都不一样，如下例所示： 

mysql> select RAND(),RAND();
+---------------------+--------------------+
| RAND()              | RAND()             |
+---------------------+--------------------+
| 0.12236927088570713 | 0.2684669525068877 |
+---------------------+--------------------+
1 row in set (0.00 sec)

利用此函数可以取任意指定范围内的随机数，比如需要产生 0～100 内的任意随机整数，可 以操作如下： 

mysql> select ceil(100*rand()),ceil(100*rand());
+------------------+------------------+
| ceil(100*rand()) | ceil(100*rand()) |
+------------------+------------------+
|               98 |                8 |
+------------------+------------------+
1 row in set (0.01 sec)

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 |
+------------+--------------+------------+
1 row in set (0.00 sec)

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)

日期和时间函数 

有时我们可能会遇到这样的需求：当前时间是多少、下个月的今天是星期几、统计截止到当 前日期前 3 天的收入总和等。这些需求就需要日期和时间函数来实现。

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

CURDATE()函数

返回当前日期，只包含年月日。 

mysql>  select CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2019-03-23 |
+------------+
1 row in set (1.61 sec)

CURTIME()函数

返回当前时间，只包含时分秒。 

mysql>  select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 15:56:18  |
+-----------+
1 row in set (0.00 sec)

NOW()函数

返回当前的日期和时间，年月日时分秒全都包含。 

mysql>  select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2019-03-23 15:56:48 |
+---------------------+
1 row in set (0.00 sec)

 UNIX_TIMESTAMP(date)函数

返回日期 date 的 UNIX 时间戳。 

mysql>  select UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
|            1553327845 |
+-----------------------+
1 row in set (0.00 sec)

FROM_UNIXTIME （ unixtime ） 函数

返回 UNIXTIME 时间戳的日期值， 和 UNIX_TIMESTAMP(date)互为逆操作。 

mysql> select FROM_UNIXTIME(1553327845);
+---------------------------+
| FROM_UNIXTIME(1553327845) |
+---------------------------+
| 2019-03-23 15:57:25       |
+---------------------------+
1 row in set (0.11 sec)

WEEK(DATE)和 YEAR(DATE)函数

前者返回所给的日期是一年中的第几周，后者返回所 给的日期是哪一年。 

mysql> select WEEK(now()),YEAR(now());
+-------------+-------------+
| WEEK(now()) | YEAR(now()) |
+-------------+-------------+
|          11 |        2019 |
+-------------+-------------+
1 row in set (0.00 sec)

HOUR(time)和 MINUTE(time)函数

前者返回所给时间的小时，后者返回所给时间的分钟

mysql> select HOUR(CURTIME()),MINUTE(CURTIME());
+-----------------+-------------------+
| HOUR(CURTIME()) | MINUTE(CURTIME()) |
+-----------------+-------------------+
|              15 |                59 |
+-----------------+-------------------+
1 row in set (0.00 sec)

 MONTHNAME(date)函数

返回 date 的英文月份名称。 

mysql> select MONTHNAME(now());
+------------------+
| MONTHNAME(now()) |
+------------------+
| March            |
+------------------+
1 row in set (0.00 sec)

 

                                                                           MySQL 中的日期时间格式 

格式符	格式说明
%S,%s	两位数字形式的秒（00,01,...,59）
%i	两位数字形式的分（00,01,...,59）
%H	两位数字形式的小时，24 小时（00,01,...,23）
%h,%I	两位数字形式的小时，12 小时（01,02,...,12）
%k	数字形式的小时，24 小时（0,1,...,23）
%l	数字形式的小时，12 小时（1,2,...,12）
%T	24 小时的时间形式（hh:mm:ss）
%r	12 小时的时间形式（hh:mm:ssAM或 hh:mm:ssPM）
%p	AM或 PM
%W	一周中每一天的名称（Sunday,Monday,...,Saturday）
%a	一周中每一天名称的缩写（Sun,Mon,...,Sat）
%d	两位数字表示月中的天数（00,01,...,31）
%e	数字形式表示月中的天数（1,2，...,31）
%D	英文后缀表示月中的天数（1st,2nd,3rd,...）
%w	以数字形式表示周中的天数（0=Sunday,1=Monday,...,6=Saturday）
%j	以 3 位数字表示年中的天数（001,002,...,366）
%U	周（0,1,52），其中 Sunday 为周中的第一天
%u	周（0,1,52），其中 Monday 为周中的第一天
%M	月名（January,February,...,December）
%b	缩写的月名（January,February,...,December）
%m	两位数字表示的月份（01,02,...,12）
%c	数字表示的月份（1,2,...,12）
%Y	4 位数字表示的年份
%y	两位数字表示的年份
%%	直接值“%”

下面的例子将当前时间显示为“月，日，年”格式： 

mysql> select DATE_FORMAT(now(),'%M,%D,%Y');
+-------------------------------+
| DATE_FORMAT(now(),'%M,%D,%Y') |
+-------------------------------+
| March,23rd,2019               |
+-------------------------------+
1 row in set (0.00 sec)

流程函数 

流程函数也是很常用的一类函数，用户可以使用这类函数在一个 SQL 语句中实现条件选择， 这样做能够高语句的效率。

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

下面的例子中模拟了对职员薪水进行分类，这里首先创建并初始化一个职员薪水表： 

mysql> create table salary (userid int,salary decimal(9,2));
Query OK, 0 rows affected (2.14 sec)

mysql> desc salary;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| userid | int(11)      | YES  |     | NULL    |       |
| salary | decimal(9,2) | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

模拟数据，插入假的数据：

mysql>  insert into salary values(1,1000),(2,2000), (3,3000),(4,4000),(5,5000), (1,null);
Query OK, 6 rows affected (0.41 sec)
Records: 6  Duplicates: 0  Warnings: 0

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)

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                         |
| low                          |
+------------------------------+
6 rows in set (0.00 sec)

 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)

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)

 其他常用函数 

MySQL 供的函数很丰富，除了前面介绍的字符串函数、数字函数、日期函数、流程函数 以外还有很多其他函数，在此不再一一列举，有兴趣的读者可以参考 MySQL 官方手册。表 5-7 列举了一些其他常用的函数。 

函数	功能
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登录用户名
INET_ATON(IP)	返回 IP地址的数字表示
INET_NTOA(num)	返回数字代表的IP 地址
PASSWORD(str)	返回字符串 str 的加密版本
MD5()	返回字符串 str 的 MD5 值

DATABASE()函数

返回当前数据库名。 

mysql> select DATABASE();
+------------+
| DATABASE() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

VERSION()函数

返回当前数据库版本。 

mysql>  select VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.00 sec)

USER()函数

返回当前登录用户名

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

INET_ATON(IP)函数

返回 IP 地址的网络字节序表示。 

mysql>  select INET_ATON('192.168.1.1');
+--------------------------+
| INET_ATON('192.168.1.1') |
+--------------------------+
|               3232235777 |
+--------------------------+
1 row in set (1.53 sec)

INET_NTOA(num)函数

返回网络字节序代表的 IP 地址。 

mysql> select INET_NTOA(3232235777);
+-----------------------+
| INET_NTOA(3232235777) |
+-----------------------+
| 192.168.1.1           |
+-----------------------+
1 row in set (0.00 sec)

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) 

在这里，如果要想实现上面的功能，就可用函数 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) 

结果完全符合我们的要求。 

PASSWORD(str)函数

返回字符串 str 的加密版本，一个 41 位长的字符串。 此函数只用来设置系统用户的密码，但是不能用来对应用的数据加密。如果应用方面有加密 的需求，可以使用MD5 等加密函数来实现。 

mysql> select PASSWORD('123456');
+-------------------------------------------+
| PASSWORD('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (1.61 sec)

MD5(str)函数

返回字符串 str 的MD5 值，常用来对应用中的数据进行加密。 下例中显示了字符串“123456”的 MD5 值

mysql> select MD5('123456');
+----------------------------------+
| MD5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (1.57 sec)

小结

MySQL 有很多内建函数，这些 内建函数实现了很多应用需要的功能并且拥有很好的性能，如果用户在工作中需要实现某种 功能，好先查一下 MySQL 官方文档或者帮助，看是否已经有相应的函数实现了我们需要 的功能，可以大大高工作效率.