五、MySQL函数

　　MySQL提供了众多功能强大的、方便易用的函数。使用这些函数，可以极大的提高用户对数据库的管理效率。

一、MySQL函数简介

　　函数表示对输入参数值返回一个具有特定关系的值，MySQL提供了大量丰富的函数，在进行数据库管理以及数据的查询和操作时将会经常用到各种函数。通过对数据的处理，数据库功能可以变得更加强大，更加灵活的满足不同用户的需求。MySQL中的函数包括：数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等。

二、数学函数

　　数学函数主要用来处理数值数据，主要的数学函数有：绝对值函数、三角函数（包括正弦函数、余弦函数、正切函数、余切函数等）、对数函数、随机数函数等。在有错误产生时，数学函数将会返回空值NULL。

2.1 绝对值函数和圆周率函数

　　（1）ABS(x)返回x的绝对值。

　　（2）PI()返回圆周率π的值。默认的显示小数位数是6位。

2.2 平方根函数和求余函数

　　（1）SQRT(x)返回非负数X的二次方根。

　　（2）MOD(x,y)返回x被y除后的余数，MOD()对于带有小数部分的数值也起作用，它返回除法运算后的精确余数。

2.3 获取整数的函数

　　（1）CEIL(x)和CEILING(x)意义相同，返回不小于x的最小整数值，返回值转化为一个BIGINT。

　　（2）FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT。

2.4 获取随机数的函数

　　RAND(x)返回一个随机浮点值v，范围在0到1之间（即0<=v<=1）。若已指定一个整数参数X，则它被用作种子值，用来产生重复序列。不带参数的RAND()每次产生的随机数值是不同的。当RAND(x)的参数相同时，将产生相同的随机数，不同的x产生的随机数值不同。

2.5 四舍五入函数和数值截取函数

　　（1）ROUND(x)返回最接近于参数x的整数，对X值进行四舍五入。ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位，保留的小数点左边的相应位数直接保存为0，不进行四舍五入。

　　（2）TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y值为0，则结果不带有小数点或不带有小数部分。若y为负数，则截去（归零）x小数点左起第y位开始后面所有低位的值。

2.6 符号函数

　　SIGN(x)返回参数的符号，x的值为负、零和正时结果依次为-1、0和1。

2.7 幂运算函数

　　（1）POW(x,y)或者POWER(x,y)函数返回x的y次乘方的结果值。

　　（2）EXP(x)返回e的x乘方后的值。

2.8 对数运算函数

　　LOG(x)返回x的自然对数，x相对于基数e的对数，对数定义域不能为负数，因此LOG(-3)返回结果为NULL。LOG10(x)返回x的基数为10的对数。

2.9 角度、弧度相互转换函数

　　（1）RADIANS(x)将参数x由角度转化为弧度。

　　（2）DEGREES(x)将参数x由弧度转化为角度。

2.10 正弦、余弦、正切和反正切函数

　　（1）SIN(x)返回x的正弦，其中x为弧度值。

　　（2）ASIN(x)返回x的反正弦，即正弦为x的值。若x不在-1和1的范围之内，则返回NULL。

　　（3）COS(x)返回x的余弦，其中x为弧度值。

　　（4）ACOS(x)返回x的反余弦，即余弦是x的值。若x不在-1和1的范围之内，则返回NULL。

　　（5）TAN(x)返回x的正切，其中x为给定的弧度值。

　　（6）ATAN(x)返回x的反正切，即正切为x的值。

　　（7）CAT(x)返回x的余切。

三、字符串函数

　　字符串函数主要用来处理数据库的字符串数据，MySQL中字符串函数有：计算字符串长度、字符串合并函数、字符串替换函数、查找指定字符串位置函数等。

3.1 计算字符串字符数的函数和字符串长度的函数

　　（1）CHAR_LENGTH(str)返回值为字符串str所包含的字符个数，一个多字节字符算作一个字符。

　　（2）LENGTH(str)返回值为字符串的字节长度，使用utf8（UNICODE的一种变长字符编码，又称万国码）编码字符集时，一个汉字是3个字节，一个数字或字母算一个字节。

3.2 合并字符串函数

　　（1）CONCAT(s1,s2,...)返回结果为连续参数产生的字符串，或许有一个或多个参数。如果有任何一个参数为NULL，则返回值为NULL。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。

　　（2）CONCAT_WS(x,s1,s2,...)，CONCAT_WS代表CONCAT With Separator，是CONCAT的特殊形式。第一个参数X是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其他参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。

3.3 字符串替换函数

　　INSERT(s1,X,len,s2)返回字符串s1，其子字符串起始于x位置和被字符串s2取代的len字符。如果x超过字符串长度，则返回值为原始字符串。假如len的长度大于其他字符串的长度，则从位置x开始替换。若任何一个参数为NULL，则返回值为NULL。

3.4 字母大小写转换函数

　　（1）LOWER(str)或者LCASE(str)可以将字符串str中的字母字符全部转换成小写字母。

　　（2）UPPER(str)或者UCASE(str)函数将字符串str中的字母字符全部转换成大写字母。

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

　　（1）LEFT(s,n)返回字符串s开始的最左边n个字符。

　　（2）RIGHT(s,n)返回字符串中最右边n个字符。

3.6 填充字符串的函数

　　（1）LPAD(s1,len,s2)返回字符串s1，其左边由字符串s2填补到len字符长度。如果s1的长度大于len，则返回值被缩短至len字符长度。

　　（2）RPAD(s1,len,s2)返回字符串s1，其右边由字符串s2填补到len字符长度。如果s1的长度大于len，则返回值被缩短至len字符长度。

3.7 删除空格的函数

　　（1）LTRIM(s)返回字符串s，字符串左侧空格字符被删除。

　　（2）RTRIM(s)返回字符串s，字符串右侧空格字符被删除。

　　（3）TRIM(s)删除字符串s两侧的空格。

3.8 删除指定字符串的函数

　　TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1。s1位可选项，在未指定的情况下，删除空格。

3.9 重复生成字符串的函数

　　REPEAT(s,n)返回一个由重复的字符串s组成的字符串，字符串s的数目等于n。若n<=0，则返回一个空字符串。若s或n为NULL，则但会NULL

3.10 空格函数和替换函数

　　（1）SPACE(n)返回一个由n个空格组成的字符串。

　　（2）REPLACE(s,s1,s2)使用字符串s2替换s中所有的字符串s1。

3.11 比较字符串大小的函数

　　STRCMP(s1,s2)若所有的字符串均相同，则返回0；若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1。

3.12 获取子串的函数

　　（1）SUBSTRING(s,n,len)带有len参数的格式，从字符串s返回一个同len字符相同的子字符串，起始于位置n。也可能对n使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置。

　　（2）MID(s,n,len)的作用于SUBSTRING(s,n,len)相同。

3.13 匹配子串开始位置的函数

　　LOCATE(str1,str)、POSITION(str1,str)、INSTR(str1,str)3个函数的作用相同，返回子字符串str1在字符串str中的开始位置。

3.14 字符串逆序的函数

　　REVERSE(s)将字符串s反转，返回的字符串顺序与s字符串顺序相反。

3.15 返回指定位置的字符串的函数

　　ELT(n,s1,s2,s3,s3,......,sn)若n=1，则返回值为s1，若n=2，则返回值为s2，以此类推。若n小于1或大于参数的数目，则返回值为NULL。

3.16 返回指定字符串位置的函数

　　FIELD(s,s1,s2,s3,......,sn)返回字符串s在列表s1,s2,s3,......,sn中第一次出现的位置，在找不到s的情况下返回0。若s为NULL，也返回0，原因是NULL不能与任何值进行同等比较。

3.17 返回字串位置的函数

　　FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号‘,’分开的字符串组成的列表。如果s1不在s2或s2为空字符串，则返回值为0。如果任意一个参数为NULL，则返回值为NULL。这个函数在第一个参数包含逗号‘,’时无法正常运行。

3.18 选取字符串的函数

　　MAKE_SET(x,s1,s2,......)返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1，s2对应比特10，以此类推。s1，s2中的NULL值不会被添加到结果中。

四、日期和时间函数

　　日期和时间函数主要用来处理日期和时间值，一般的日期函数除了使用DATE类型的参数外，也可以使用DATETIME和TIMESTMAP类型的参数，但会忽略这些值的时间部分。相同的，以TIME类型值为参数的函数，可以接受TIMESTMAP类型的参数，但会忽略日期部分，许多日期函数可以同时接受数和字符串类型的两种参数。

4.1 获取当前日期的函数和获取当前时间的函数

　　（1）CURDATE()和CURRENT_DATE()函数作用相同，将当前日期按‘YYYY-MM-DD’或YYYYMMDD格式的值返回，具体格式根据函数在字符串或是数字语境中而定。“CURDATE()+0”将当前日期值转换为数值型。

　　（2）CURTIME()和CURRENT_TIME()函数作用相同，将当前时间以‘HH:MM:SS’或HHMMSS格式返回，具体格式根据函数在字符串或是数字语境中而定。“CURTIME()+0”将当前时间值转换为数值型。

4.2 获取当前日期和时间的函数

　　CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE()这4个函数的作用相同，均返回当前日期和时间值，格式为‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS，具体格式根据函数在字符串或数字语境中而定。

4.3 UNIX时间戳函数

　　（1）UNIX_TIMESTAMP(date)若无参数调用，则返回一个Unix时间戳（‘1970-01-01 00:00:00’ GMT之后的秒数）作为无符号整数。其中GMT（Greenwich mean time）为格林尼治标准时间。若用date来调用UNIX_TIMESTAMP()，它会将参数值以‘1970-01-01 00:00:00’ GMT之后的秒数的形式返回。date可以是一个DATE字符串、DATETIME字符串、TIMESTAMP或一个当地时间的YYMMDD或YYYYMMDD格式的数字。

　　（2）FROM_UNIXTIME(date)函数把UNIX时间戳转换为普通格式的时间，与UNIX_TIMESTAMP(date)函数互为反函数。

4.4 返回UTC日期的函数和返回UTC时间的函数

　　（1）UTC_DATE()函数返回当前UTC（世界标准时间）日期值，其格式为‘YYYY-MM-DD’或YYYYMMDD，具体格式取决于函数是否用在字符串或数字语境中。UTC_DATE()函数返回值为当前时区的日期值。

　　（2）UTC_TIME()函数返回当前UTC时间值，其格式为‘HH:MM:SS’或HHMMSS，具体格式取决于函数是否用在字符串或数字语境中。UTC_TIME()返回当前时区的时间值。

4.5 获取月份的函数

　　（1）MONTH(date)函数返回date对应的月份，范围值从1~12。

　　（2）MONTHNAME(date)函数返回date对应的英文全名。

4.6 获取星期的函数

　　（1）DAYNAME(d)函数返回d对应的工作日的英文名称，例如Sunday、Monday等。

　　（2）DAYOFWEEK(d)函数返回d对应的一周中的索引。1表示周日，2表示周一，……，7表示周六。

　　（3）WEEKDAY(d)返回d对应的工作日索引。0表示周一，1表示周二，……，6表示周日。

4.7 获取星期数的函数

　　（1）WEEK(d,Mode)计算日期d是一年中的第几周。WEEK()的双参数形式允许指定该星期是否起始于周日或周一，以及返回值的范围是否为从0~53或从1~53。若Mode参数被省略，则使用default_week_format系统自变量的值，MySQL中该值默认为0，指定一周的第一天为周日。

　　（2）WEEKOFYEAR(d)计算某天位于一年中的第几周，范围是从1~53。相当于WEEK(d,3)。

4.8 获取天数的函数

　　（1）DAYOFYEAR(d)函数返回d是一年中的第几天，范围是从1~366。

　　（2）DAYOFMONTH(d)函数返回d是一个月中第几天，范围是从1~31。

4.9 获取年份、季度、小时、分钟和秒钟的函数

　　（1）YEAR(date)返回date对应的年份，范围是1970~2069。

　　（2）QUARTER(date)返回date对应的一年中的季度值，范围是从1~4。

　　（3）MINUTE(time)返回time对应的分钟数，范围是从0~59。

　　（4）SECOND(time)返回time对应的秒数，范围是从0~59。

4.10 获取日期的指定值的函数

　　EXTRACT(type FROM date)函数所使用的时间间隔类型说明符同DATE_ADD()或DATE_SUB()的相同，但它从日期中提取一部分，而不是执行日期运算。

4.11 时间和秒数转换的函数

　　（1）TIME_TO_SEC(time)返回已转化为秒的time参数。转换公式为：小时*3600+分钟*60+秒。

　　（2）SEC_TO_TIME(seconds)返回转化为小时、分钟和秒数的seconds参数值，其格式为‘HH:MM:SS’或HHMMSS，具体格式根据该函数是否用在字符串或数字语境中而定。

4.12 计算日期和时间的函数

 

4.13 将日期和时间格式化的函数

 

五、条件判断函数

　　条件判断函数也称控制流程函数，根据满足的条件的不同，执行相应的流程。MySQL中进行条件判断的函数有IF、IFNULL和CASE。

5.1 IF(expr,v1,v2)函数

　　IF(expr,v1,v2)，如果表达式expr是TRUE，则IF()的返回值为v1；否则返回值为v2。IF()的返回值为数字值或字符串值，具体情况视其所在语境而定。

5.2 IFNULL(v1,v2)

　　假如v1不为NULL，则IFNULL()的返回值为v1；否则其返回值为v2。IFNULL()的返回值是数字或是字符串，具体情况取决于它所在的语境。

5.3 CASE函数

　　CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END

　　该函数表示，如果expr值等于某个vn，则返回对应位置THEN后面的结果。如果与所有值都不相等，则返回ELSE后面的rn。

六、系统信息函数

　　MySQL中的系统信息有，数据库的版本号、当前用户名和连接数、系统字符集、最后一个自动生成的ID值等。

6.1 获取MySQL版本号、连接数和数据库名的函数

　　（1）VERSION()返回指示MySQL服务器版本的字符串。这个字符串使用utf8字符集。

　　（2）CONNECTION_ID()返回MySQL服务器当前连接的次数，每个连接都有各自唯一的ID。

6.2 获取用户名的函数

　　USER()、CURRENT_USER()、SYSTEM_USER()和SESSION_USER()这几个函数返回当前被MySQL服务器验证的用户名和主机名组合。这个值符合确定当前登录用户存取权限的MySQL账户。一般情况下，这几个函数的返回值是相同的。

6.3 获取字符串的字符集和排序方式的函数

　　（1）CHARSET(str)返回字符串str自变量的字符集。

　　（2）COLLATION(str)返回字符串str的字符排序方式。

6.4 获取最后一个自动生成的ID值的函数

　　LAST_INSERT_ID()自动返回最后一个INSERT或UPDATE为AUTO_INCREMENT列设置的第一个发生的值。

七、加/解密函数

　　加密函数主要用来对数据进行加密和界面处理，以保证某些重要的数据不被别人获取。这些函数在保证数据库安全时非常有用。

7.1 加密函数PASSWORD(str)

　　PASSWORD(str)从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。

7.2 加密函数MD5(str)

　　MD5(str)为字符串算出一个MD5 128比特校验和。该值以32位十六进制数字的二进制字符串形式返回，若参数为NULL，则会返回NULL。

7.3 加密函数ENCODE(str,pswd_str)

　　ENCODE(str,pswd_str)使用pswd_str作为密码，加密str。使用DECODE()解密结果，是一个和str长度相同的二进制字符串。

 7.4 解密函数DECODE(crypt_str,pswd_str)

　　DECODE(crypt_str,pswd_str)使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE()返回的字符串。

八、其他函数

　　下面要介绍的函数不能笼统的分为哪一类，但是这些函数也非常有用，例如重复指定操作函数、改变字符集函数、IP地址与数字转换函数等。

8.1 格式化函数FORMAT(x,n)

　　FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0，则函数返回结果不含小数部分。

8.2 不同进制的数字进行转换的函数

　　CONV(N,from_base,to_base)函数进行不同进制数间的转换。返回值为数值N的字符串表示，有from_base进制转化为to_base进制。如有任意一个参数为NULL，则返回值为NULL。自变量N被理解为一个整数，但是可以被指定为一个整数或字符串。最小基数为2，而最大技术则为36。

8.3 IP地址与数字相互转换的函数

　　INET_ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。地址可以是4或8bit地址。

8.4 加锁函数和解锁函数

　　（1）GET_LOCK(str,timeout)设法使用字符串str给定的名字得到一个锁，持续时间timeout秒。若成功得到锁，则返回1；若操作超时，则返回0；若发生错误，则返回NULL。假如有一个用GET_LOCK()得到的锁，当执行RELEASE_LOCK()或断开连接（正常或非正常）时，这个锁就会解除。

　　（2）RELEASE_LOCK(str)解开被GET_LOCK()获取的，用字符串str所命名的锁。若锁被解开，则返回1；若该线程尚未创建锁，则返回0（此时锁没有被解开）；若命名的锁不存在，则返回NULL。若该锁从未被GET_LOCK()的调用获取，或锁已经被提前解开，则该锁不存在。

　　（3）IS_USED_LOCK(str)检查名为str的锁是否可以使用（换言之，被封锁）。若被封锁，则返回使用该锁的客户端的连接标识符（connection ID）；否则，返回NULL。

8.5 重复执行指定操作的函数

　　BENCHMARK(count,expr)函数重复count次执行表达式expr。它可以用于计算MySQL处理表达式的速度。结果值通常为0（0只是表示处理过程很快，并不是没有花费时间）。另一个作用是它可以在MySQL客户端内部报告语句执行的时间。

8.6 改变字符集的函数

　　CONVERT(...USING...)带有USING的CONVERT()函数被用来在不同的字符集之间转化数据。默认为utf8字符集。

8.7 改变数据类型的函数

　　CAST(x,AS type)和CONVERT(x,type)函数将一个类型的值转换为另一个类型的值，可转换的type有：BINARY、CHAR(n)、DATE、TIME、DATETIME、SIGNED、UNSIGNED。