一、数值函数

数值函数是MySQL中一种很重要的函数，主要用于处理数值方面的运算。如果没有这些函数，用户在编写有关数值运算方面的代码时将会复杂很多。例如，如果没有ABS求绝对值函数，要取一个数的绝对值，就需要进行多次判断，直接使用该函数可以大大提高用户的工作效率。

MySQL中常用的数值函数及其功能

1.1 求绝对值函数

函数ABS(x)的返回值是数值x的绝对值。正数的绝对值是其本身，负数的绝对值是其相反数。

实例1：执行SQL语句，求5，-5和-5.5的绝对值，执行结果如下

mysql> SELECT ABS(5),ABS(-5),ABS(-5.5);
+--------------+--------------+----------------+
| ABS(5)  | ABS(-5)  | ABS(-5.5)     |
+--------------+--------------+----------------+
|      5  |     5  |      5.5     |
+--------------+--------------+----------------+
1 row in set (0.03 sec)

1.2 求余函数

函数MOD(x,y)的返回值是数值x除以数值y后的余数。与x%y的结果相同，除数和被除数任何一个为NULL，返回结果都将为NULL；除数为0将是非法运算，返回结果为NULL。

实例2：执行SQL语句，求6除以4，6除以-4，NULL除以6，6除以0，及0除以2.5的余数，执行结果如下

mysql> SELECT MOD(6,4),MOD(6,-4), MOD(NULL,6), MOD(6,0),MOD(0,2.5);
+--------------+---------------+----------------------------+----------------+----------------+
| MOD(6,4) | MOD(6,-4) | MOD(NULL,6)  | MOD(6,0)   | MOD(0,2.5) |
+--------------+---------------+----------------------------+----------------+----------------+
|        2   |         2   |      NULL    |     NULL    |         0.0        |
+--------------+---------------+----------------------------+----------------+----------------+
1 row in set (0.00 sec)

1.3 用于获取整数的函数

MySQL中用于获取整数的函数主要有CEIL(x)和FLOOR(x)，下面分别介绍。

1.3.1 CEIL(x)

函数CEIL(x)的返回值是大于数值x的最小整数值，下面通过实例介绍。

实例3：执行SQL语句，分别求大于数值2.45和-2.45的最小整数值，执行结果如下

mysql> SELECT CEIL(2.45),CEIL(-2.45);
+------------------------------+-----------------------------+
| CEIL(2.45)   | CEIL(-2.45)  |
+------------------------------+-----------------------------+
|          3   |         -2    |
+------------------------------+-----------------------------+
1 row in set (0.00 sec)

1.3.2 FLOOR(x)

函数FLOOR(x)的返回值是小于数值x的最大整数值，下面通过实例介绍。

实例4：执行SQL语句，求小于数值2.45和-2.45的最大整数值，执行结果如下

mysql> SELECT FLOOR(2.45),FLOOR(-2.45);
+------------------------------+-----------------------------+
| FLOOR(2.45)   | FLOOR(-2.45)   |
+------------------------------+-----------------------------+
|           2   |           -3   |
+------------------------------+-----------------------------+
1 row in set (0.03 sec)

1.4 获取随机数的函数

函数RAND()的返回值是0～1内的小数，并且每次的运行结果都不同，下面通过实例介绍。

实例5：执行SQL语句，使用函数RAND()获取随机数，执行结果如下

mysql> SELECT RAND(),RAND(),RAND();
+----------------------------------------------+--------------------------------------------+---------------------------------------------+
| RAND()                | RAND()              | RAND()                |
+----------------------------------------------+--------------------------------------------+---------------------------------------------+
| 0.32945327863977597    | 0.017887058684497196   | 0.10107548823680308  |
+----------------------------------------------+--------------------------------------------+---------------------------------------------+
1 row in set (0.04 sec)

1.5  四舍五入函数

函数ROUND()的作用是对数值执行四舍五入操作，当函数格式为ROUND(x)时，返回值为整数；当函数格式为ROUND(x,y)时，对数值x进行四舍五入并保留小数点后y位，下面通过实例进行介绍。

实例6：执行SQL语句，使用函数ROUND(x)和ROUND(x,y)对数值进行四舍五入操作，执行结果如下

mysql> SELECT ROUND(100.144),ROUND(100.568),ROUND(100.144,2),ROUND(100.568,2);
+------------------------------+-----------------------------+------------------------------+-----------------------------+
| ROUND(100.144)  | ROUND(100.568)  | ROUND(100.144,2)  | ROUND(100.568,2)  |
+------------------------------+-----------------------------+------------------------------+-----------------------------+
|            100  |            101  |           100.14   |           100.57   |
+------------------------------+-----------------------------+------------------------------+-----------------------------+
1 row in set (0.03 sec)

1.6 截取小数函数

函数TRUNCATE(x,y)的作用是对数值x进行截取，保留小数点后y位。其与ROUND()函数的区别是，ROUND()函数在截取值时会四舍五入；而TRUNCATE(x,y)函数直接截取值，并不进行四舍五入。

实例7：执行SQL语句，使用函数TRUNCATE(x,y)和ROUND(x,y)分别截取数值，执行结果如下

mysql> SELECT TRUNCATE(1.42,1),TRUNCATE(1.58,1),ROUND(1.58,1);
+------------------------------+-----------------------------+------------------------------+
| TRUNCATE(1.42,1)  | TRUNCATE(1.58,1)  | ROUND(1.58,1)  |
+------------------------------+-----------------------------+------------------------------+
|              1.4  |              1.5  |           1.6    |
+------------------------------+-----------------------------+------------------------------+
1 row in set (0.03 sec)

二、字符串函数

字符串函数是MySQL中使用最频繁的函数，主要用于处理数据库中字符串类型的数据。表10-2列出了MySQL中常用的字符串函数及其功能。

MySQL中常用的字符串函数及其功能

2.1  返回字符串长度和字符串中字符个数的函数

函数LENGTH(str)用于返回字符串的长度，一个汉字占用2个字节，一个英文字符和数字占用1个字节。

实例8：执行SQL语句，使用函数LENGTH(str)返回字符串长度，执行结果如下

mysql> SELECT LENGTH('abcdef'),LENGTH('字符长度');
+-----------------------------+------------------------------+
| LENGTH('abcdef')   | LENGTH('字符长度')  |
+-----------------------------+------------------------------+
|               6   |                8  |
+-----------------------------+------------------------------+
1 row in set (0.00 sec)

函数CHAR_LENGTH(str)用于返回字符串中的字符个数。

实例9：执行SQL语句，使用函数CHAR_LENGTH(str)返回字符串中的字符个数，执行结果如下

mysql> SELECT CHAR_LENGTH('abcdef'),CHAR_LENGTH('字符个数');
+---------------------------------------------+---------------------------------------------+
| CHAR_LENGTH('abcdef')   | CHAR_LENGTH('字符个数')  |
+---------------------------------------------+---------------------------------------------+
|                     6     |                      4     |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.00 sec)

2.2  合并字符串的函数

函数CONCAT(str1,str2…strn)可以将多个字符串拼接成为一个字符串，但如果参数中有一个NULL值，则返回结果都将为NULL。

实例10：执行SQL语句，使用函数CONCAT(str1,str2…strn)拼接字符串，执行结果如下

mysql> SELECT CONCAT('abcd','efg'),CONCAT('abcd',NULL,'efg');
+------------------------------+---------------------------------------------+
| CONCAT('abcd','efg')  | CONCAT('abcd',NULL,'efg')  |
+------------------------------+---------------------------------------------+
| abcdefg             | NULL                      |
+------------------------------+---------------------------------------------+
1 row in set (0.02 sec)

函数CONCAT_WS(x,str1,str2…strn)是函数CONCAT(str1,str2…strn)的特殊形式，作用是以第一个参数为分隔符，连接后面的多个字符串。

实例11：执行SQL语句，使用函数CONCAT_WS(x,str1,str2…strn)拼接字符串，执行结果如下

mysql> SELECT CONCAT_WS('_','ab','cd','ef'),CONCAT_WS('_','gh',NULL,'ij');
+---------------------------------------------+----------------------------------------------+
| CONCAT_WS('_','ab','cd','ef')   | CONCAT_WS('_','gh',NULL,'ij')   |
+---------------------------------------------+----------------------------------------------+
| ab_cd_ef                   | gh_ij                        |
+---------------------------------------------+----------------------------------------------+
1 row in set (0.00 sec)

提示：由执行结果可以看出，函数CONCAT_WS(x,str1,str2…strn)会忽略分隔符后的NULL值，但如果分隔符为NULL，则返回结果为NULL。

2.3  替换字符串的函数

函数INSERT(str,x,y,instr)的作用是将字符串str从第x位置开始，y个字符长的子串替换为字符串instr。

实例12：执行SQL语句，使用函数INSERT(str,x,y,instr)把字符串“beijinglvyoushichang”从第13个字符开始后面的8个字符替换为字符串“gonglue”，执行结果如下

mysql> SELECT INSERT('beijinglvyoushichang',13,8,'gonglue');
+----------------------------------------------------------------------------+
| INSERT('beijinglvyoushichang',13,8,'gonglue')  |
+----------------------------------------------------------------------------+
| beijinglvyougonglue                          |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

函数REPLACE(str,a,b)也可以替换字符串，作用是使用字符串b替换字符串str中的子串a。

实例13：执行SQL语句，使用函数REPLACE(str,a,b)把字符串“abcabc”中的子串“abc”替换为字符串“you”，执行结果如下

mysql> SELECT REPLACE('abcabc','abc','you');
+---------------------------------------------+
| REPLACE('abcabc','abc','you')  |
+---------------------------------------------+
| youyou                       |
+---------------------------------------------+
1 row in set (0.00 sec)

2.4  字母大小写转换函数

函数LOWER(str)用于将字符串str中的字母全部转换为小写字母，函数UPPER(str)用于将字符串str中的字母全部转换为大写字母。

实例14：执行SQL语句，分别使用函数LOWER(str)和UPPER(str)把字符串“aBcD”转换为小写字母和大写字母，执行结果如下

mysql> SELECT LOWER('aBcD'),UPPER('aBcD');
+------------------------------+-----------------------------+
| LOWER('aBcD')  | UPPER('aBcD')  |
+------------------------------+-----------------------------+
| abcd            | ABCD           |
+------------------------------+-----------------------------+
1 row in set (0.07 sec)

2.5  获取指定长度字符串的函数

函数LEFT(str,x)用于获取字符串str中最左边的x个字符，函数RIGHT(str,x)用于获取字符串str中最右边的x个字符。

实例15：执行SQL语句，分别使用函数LEFT(str,x)和RIGHT(str,x)获取字符串“beijinglvyougonglue”左边和右边的7个字符，执行结果如下

mysql> SELECT LEFT('beijinglvyougonglue',7),RIGHT('beijinglvyougonglue',7);
+---------------------------------------------+---------------------------------------------+
| LEFT('beijinglvyougonglue',7)  | RIGHT('beijinglvyougonglue',7)  |
+---------------------------------------------+---------------------------------------------+
| beijing                        | gonglue                       |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.02 sec)

函数SUBSTRING(str,x,y)用于获取字符串str中从x位置开始，后面y个字符长度的子串。该函数常用于在给定字符串中提取子串。

实例16：执行SQL语句，使用函数SUBSTRING(str,x,y)获取字符串“beijinglvyougonglue”从第8个字符开始，后面的5个字符，执行结果如下

mysql> SELECT SUBSTRING('beijinglvyougonglue',8,5);
+-------------------------------------------------------------+
| SUBSTRING('beijinglvyougonglue',8,5)  |
+-------------------------------------------------------------+
| lvyou                               |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

2.6  填充字符串的函数

函数LPAD(str1,n,str2)的作用是使用字符串str2对字符串str1最左边进行填充，直到字符串str1总长度达到n个字符长度。如果str1的字符长度大于或等于n，则不填充。

实例17：执行SQL语句，使用字符串km对字符串“abcdefg”最左边进行填充，直到字符串达到5个或10个字符长度。执行结果如下

mysql> SELECT LPAD('abcdefg',5,'km'),LPAD('abcdefg',10,'km');
+---------------------------------------------+---------------------------------------------+
| LPAD('abcdefg',5,'km')  | LPAD('abcdefg',10,'km')  |
+---------------------------------------------+---------------------------------------------+
| abcde                | kmkabcdefg       |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.00 sec)

函数RPAD(str1,n,str2)的作用是使用字符串str2对字符串str1最右边进行填充，直到字符串str1总长度达到n个字符长度。

实例18：执行SQL语句，使用字符串km对字符串“abcdefg”最右边进行填充，直到字符串达到5个或10个字符长度。执行结果如下

mysql> SELECT RPAD('abcdefg',5,'km'),RPAD('abcdefg',10,'km');
+---------------------------------------------+---------------------------------------------+
| RPAD('abcdefg',5,'km')  | RPAD('abcdefg',10,'km')  |
+---------------------------------------------+---------------------------------------------+
| abcde                | abcdefgkmk       |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.02 sec)

2.7  删除字符串中空格的函数

函数LTRIM(str)用于删除字符串左侧的空格字符，函数RTRIM(str)用于删除字符串右侧的空格字符。

实例19：执行SQL语句，验证函数LTRIM(str)和RTRIM(str)的应用。执行结果如下

mysql> SELECT CONCAT('ab',' cd ','ef') AS str1,
    -> CONCAT('ab',LTRIM(' cd '),'ef') AS str2,
    -> CONCAT('ab',' cd ','ef') AS str3,
    -> CONCAT('ab',RTRIM(' cd '),'ef') AS str4;
+--------------+--------------+--------------+-------------+
| str1      | str2     | str3     | str4    |
+--------------+--------------+--------------+-------------+
| ab cd ef  | abcd ef  | ab cd ef  | ab cdef  |
+--------------+--------------+--------------+-------------+
1 row in set (0.03 sec)

提示：上述语句中，字符串“cd”两侧各有一个空格。在语句CONCAT('ab',LTRIM(' cd '),'ef') AS str2中，函数LTRIM(' cd ')将cd左侧的空格删除了，所以语句执行结果为abcd ef；在语句CONCAT('ab',RTRIM(' cd '),'ef') AS str4中，函数RTRIM(' cd ')将cd右侧的空格删除了，所以语句执行结果为ab cdef。

函数TRIM(str)用于删除字符串开头和结尾的空格，另外，它还可以删除字符串两侧的指定字符。

实例20：执行SQL语句，验证函数TRIM(str)的应用。执行结果如下

mysql> SELECT CONCAT('ab',' cd ','ef') AS str1,
    -> CONCAT('ab',TRIM(' cd '),'ef') AS str2,
    -> TRIM('a' from 'aabacaa') AS str3;
+--------------+--------------+--------------+
| str1     | str2     | str3   |
+--------------+--------------+--------------+
| ab cd ef  | abcdef  | bac   |
+--------------+--------------+--------------+
1 row in set (0.00 sec)

可以看到，在语句CONCAT('ab',TRIM(' cd '),'ef') AS str2中，函数TRIM(' cd ')将cd两侧的空格全部删除了，所以合并字符串的结果为abcdef；在语句TRIM('a' from 'aabacaa') AS str3中，函数TRIM('a' from 'aabacaa')将字符串aabacaa两侧的指定字符a全部删除了。

2.8  重复生成字符串的函数

函数REPEAT(str,n)返回字符串str重复n次的结果。

实例21：执行SQL语句，验证函数REPEAT(str,n)的应用。执行结果如下

mysql> SELECT REPEAT('abc ',3);
+------------------------------+
| REPEAT('abc ',3)   |
+------------------------------+
| abc abc abc        |
+------------------------------+
1 row in set (0.01 sec)

提示：此处c后面有一个空格。

2.9  获取字符串中子串开始位置的函数

函数LOCATE(str1,str)返回子串str1在字符串str中的开始位置，返回值的最小值为1，如果字符串str中不包含字符串str1，则返回0。

实例22：执行SQL语句，验证函数LOCATE(str1,str)的应用。执行结果如下

mysql> SELECT LOCATE('abc','ababcabd'),LOCATE('efg','ababcabd');
+---------------------------------------------+---------------------------------------------+
| LOCATE('abc','ababcabd')   | LOCATE('efg','ababcabd')  |
+---------------------------------------------+---------------------------------------------+
|                    3   |                    0  |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.04 sec)

2.10  反转字符串的函数

函数REVERSE(str)返回将字符串str中字符倒序排列后的结果。

实例23：执行SQL语句，验证函数REVERSE(str)的应用。执行结果如下

mysql> SELECT REVERSE('abcdefg');
+------------------------------+
| REVERSE('abcdefg') |
+------------------------------+
| gfedcba             |
+------------------------------+
1 row in set (0.00 sec)