mysql8.0 MySQL函数_mysql8.0 函数

PART1. MySQL函数介绍

函数表示对输入参数值返回一个具有特定关系的值，MySQL提供了大量丰富的函数，在进行数据库管理以及数据的查询和操作时将会经常用到各种函数。各类函数从功能方面主要分为数学函数，字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等其他函数。

PART2. 数学函数

绝对值函数、三角函数（正弦函数、余弦函数、正切函数、余切函数等）、对数函数、随机数函数等。

1. 绝对值函数ABS(x)和返回圆周率的函数PI()

ABS(X)返回X的绝对值。

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

 2. 平方根函数SQRT(x)和求余函数MOD(x,y)

SQRT(x)返回非负数x的二次方根。

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

 3. 获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)

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

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

4. 获取随机数的函数RAND()和RAND(x)

RAND(x) 返回一个随即浮点值v，范围在0到1之间(0 <= v <= 1.0)。若已指定一个整数参数x，则它被当作种子值，用来产生重复序列。

可以看到，不带参数的RAND()每次产生的随机数值是不同的。 

可以看到，当RAND(x)的参数相同时，将产生相同的随机数，不同的x产生的随机数值不同。

 5. 函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)

ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入。

 可以看到，四舍五入处理之后，只保留了各个值的整数部分。

ROUND(x,y) 返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。

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

注意： ROUND(x,y) 函数在截取值的时候四舍五入，而TRUNCATE(x,y)直接截取值，并不进行四舍五入。

6. 符号函数SIGN(x)

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

7. 幂运算函数POW(x,y)、POWER(x,y)和EXP(x)

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

 

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

8. 对数运算函数LOG(x) 和 LOG10(x) 

 LOG(x) 返回x的自然对数，x相对于基数e的对数。

对数定义域不能为负数，因此LOG(-3)返回结果为NULL。

LOG10(x) 返回x的基数为10的对数。

 

9. 角度与弧度相互转换的函数RADIANS(x)和DEGREES(x)

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

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

 

10 正弦函数SIN(x)和反正弦函数ASIN(x)

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

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

 

由结果可以看到，函数ASIN和SIN互为反函数；ASIN(3)中的参数3超出了正弦值的范围，因此返回NULL。

11. 余弦函数COS(x)和反余弦函数ACOS(x)

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

 ACOS(x) 返回x的反余弦，即余弦是x的值。若x不在-1~1的范围之间，则返回NULL。

由结果可以看到，函数COS和ACOS互为反函数。

12. 正切函数、反正切函数和余切函数

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

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

COT(x) 返回x的余切。 

由结果可以看到，函数COT和TAN互为倒函数。 

PART3. 字符串函数

1. 计算字符串字符数的函数和字符串长度的函数

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

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

2. 合并字符串函数CONCAT(s1,s2,……)、CONCAT_WS(x,s1,s2,……)

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

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

 3. 替换字符串的函数INSERT(s1,x,len,s2)

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

 4. 字母大小写转换函数

LOWER(str)或者LCASE(str)可以将字符串str中的字母字符全部变成小写字母。

UPPER(str)或者UCASE(str)可以将字符串str中的字母字符全部转换成大写字母。

5. 获取指定长度的 字符串的函数LEFT(s,n)和RIGHT(s,n)

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

RIGHT(s,n)返回字符串str最右边的n个字符。

6. 填充字符串的函数LPAD(s1,len,s2)和RPAD(s1,len,s2)

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

RPAD(s1,len,s2)返回字符串s1，其右边被字符串s2填补至len字符长度。假设字符串s1的长度大于len，则返回值被缩短到len字符长度。

7. 删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)

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

8. 删除指定字符串的函数TRIM(s1 FROM s)

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

9. 重复生成字符串的函数REPEAT(s,n)

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

10. 空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)

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

REPLACE(s,s1,s2)使用字符串s2代替字符串s中所有的字符串s1.

11. 比较字符串大小的函数STRCMP(s1,s2)

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

12. 获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)

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

 

MID(s,n,len)与SUBSTRING(s,n,len)的作用相同。

13. 匹配子串开始位置的函数

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

14. 字符串逆序的函数REVERSE(s)

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

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

ELT(N，字符串1，字符串2，字符串3，…，字符串N)：若N = 1，则返回值为字符串1；若N=2，则返回值为字符串2；以此类推；若N小于1或大于参数的数目，则返回值为NULL。

16. 返回指定字符串位置的函数FIELD(s,s1,s2,…,sn)

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

17. 返回字符串位置的函数FIND_IN_SET(s1,s2)

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

 

 18. 选取字符串的函数MAKE_SET(x,s1,s2,…,sn)

MAKE_SET(x,s1,s2,…,sn)函数按x的二进制数从s1,s2,……,sn中选取字符串。例如5的二进制时0101，这个二进制从右往左的第一位和第三位是1，所以选取s1和s3。s1，s2，…，sn中的NULL值不会被添加到结果中。

PART4. 日期和时间函数

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

CURDATE()和CURRENT_DATE()函数的作用相同，将当前日期按照‘YYYY-MM-DD’或者YYYYMMDD格式的值返回，具体格式根据函数在字符串或是数字语境中而定。

CURTIME()和CURRENT_TIME()函数的作用相同，将当前时间以‘HH：MM：SS'或HHMMSS的格式返回，具体格式根据函数在字符串或是数字语境中而定。

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

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

3. UNIX时间戳函数

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

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

 4. 返回UTC日期的函数和返回UTC时间的函数

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

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

5. 获取月份的函数MONTH(date)和MONTHNAME(date)

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

MONTHNAME(date)函数返回日期date对应月份的英文全名。

6. 获取星期的函数DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d) 

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

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

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

7. 获取星期数的函数WEEK(d)和WEEKOFYEAR(d)

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

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

 8. 获取天数的函数DAYOFYEAR(d)和DAYOFMONTH(d)

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

DAYOFMONTH(d)函数返回d是一年中的第几天，范围是1~31。

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

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

注意：‘00~69’转换为‘2000~2069’，‘70~99’转换为‘1970~1999’ 。

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

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

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

10. 获取日期的指定值的函数EXTRACT(type FROM date)

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

11. 时间和秒钟转换的函数

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

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

 12. 计算日期和时间的函数

计算日期和时间的函数有DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、ADDTIME()、SUBTIME()和DATE_DIFF()。

在DATE_ADD(date,INTERVAL expr type) 和DATE_SUB(date,INTERVAL expr type)中，date是一个DATETIME或DATE值，用来指定起始时间。expr是一个表达式，用来指定从起始日期添加或减去的时间间隔值。对于负值的时间间隔，expr可以以一个负号’-‘开头，type为关键词，指示了表达式被解释的方式。

 DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type) 两个函数的作用相同，执行日期的加运算。

DATE_SUB(date,INTERVAL expr type) 或者 SUBDATE(date,INTERVAL expr type) 两个函数的作用相同，执行日期的减运算。

 ADDTIME(date,expr)函数将expr值添加到date，并返回修改后的值，date是一个日期或者日期时间表达式，而expr是一个时间表达式。

 SUBTIME(date,expr)函数将date减去expr值，并返回修改后的值。其中，date是一个日期或者日期时间表达式，而expr是一个时间表达式。

 DATEDIFF(date1,date2)返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或日期时间表达式。

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

DATE_FORMAT(date,format)根据format指定的格式显示date值。

 

TIME_FORMAT(time,format)根据表达式format的要求显示时间time。表达式format指定了显示的格式。因为TIME_FORMAT(time,format)只处理时间，所以format只使用时间格式。

 GET_FORMAT(val_type,format_type)返回日期时间字符串的显示格式，val_type表示日期数据类型，包括DATE、DATETIME和TIME；format_type表示格式化显示类型。

PART5. 条件判断函数

1. IF(expr,v1,v2)函数

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

2. IFNULL(v1,v2)函数

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

3. CASE函数

CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] …[ELSE rn+1] END: 如果expr值等于某个vn，则返回对应位置THEN后面的结果；如果与所有值都不相等，则返回ELSE后面的rn+1。

CASE WHEN v1 THEN r1[WHEN v2 THEN r2]…ELSE rn+1] END: 某个vn值为TRUE时，返回对应位置THEN后面的结果；如果所有值都不为TRUE，则返回ELSE后的rn+1。

提示：一个CASE表达式的默认返回值类型时任何返回值的相容集合类型，但具体情况视其所在语境而定。如果用在字符串语境中，则返回结果为字符串。如果用在数字语境中，则返回结果为十进制值、实数值或整数值。

PART6. 系统信息函数

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

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

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

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

processlist 命令的输出结果显示了有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态、帮助识别出有问题的查询语句等。

如果是root账号，能看到所有用户的当前连接。如果是其他普通账号，则只能看到自己占用的连接。show processlist 只能列出前100条，如果想全部列出可使用show full processlist 命令。

各个列的含义和用途：

（1）Id列，用户登录MySQL时，系统分配的时“connection id” 。

（2）User列，显示当前用户。如果不是root，这个命令就只显示用户权限范围内的SQL语句。

（3）Host列，显示这个语句是从哪个IP的哪个端口上发出的，可以用来追踪出现问题语句的用户。

（4）db列，显示这个进程目前连接的是哪个数据库。

（5）Command列，显示当前连接执行的命令，一般取值为休眠（Sleep）、查询（Query）、连接（Connect）。

（6）Time列，显示这个状态持续的时间，单位是秒。

（7）State列，显示使用当前连接的SQL语句的状态，很重要的列。后续会有所有状态的描述，State只是语句执行中的某一个状态。一个SQL语句，以查询为例，可能需要经历Copying to tmp table、Sorting result、Sending data等状态才能完成。

（8）Info列，显示这个SQL语句，是判断问题语句的一个重要依据。

使用另一个命令行登录MySQL 

 DATABASE()和SCHEMA() 函数返回使用utf8字符集的默认（当前）数据库名。

2. 获取用户名的函数

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

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

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

COLLATION(str)返回字符串str的字符排列方式。

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

LAST_INSERT_ID()函数返回最后生成的AUTO_INCREMENT值。

（1） 一次插入一条记录

 （2）一次同时插入多条记录

当使用一条INSERT语句插入多行时，LAST_INSERT_ID()只返回插入的第一行数据时产生的值，在这里为第3条记录。之所以这样，是因为这使依靠其他服务器复制同样的INSERT语句变得简单。

注意：LAST_INSERT_ID是与数据表无关的，如果向表a插入数据后再向表b插入数据，那么LAST_INSERT_ID返回表b中的Id值。

PART7. MySQL8.0 的新特性1——加密函数

加密函数主要用来对数据进行加密和界面处理。

1. 加密函数MD5(str)

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

2. 加密函数SHA(str)

SHA(str)从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。SHA加密算法比MD5更加安全。

3. 加密函数SHA2(str, hash_length) 

 SHA2(str, hash_length) 使用hash_length作为长度，加密str。hash_length支持的值224、256、384、512和0。其中，0等同于256。

 PART8. 其他函数

1. 格式化函数FORMAT(x,n)

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

2. 不同进制的数字进行转换的函数

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

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

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

INET_NTOA(expr)给定一个数字网络地址（4或8bit），返回作为字符串的该地址的点地址表示。

INET_NTOA和INET_ATON互为反函数。

4. 加锁函数和解锁函数

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

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

IS_FREE_LOCK(str)检查名为str的锁是否可以使用（换言之，没有被封锁）。若锁可以使用，则返回1（没有人在用这个锁）；若这个锁正在被使用，则返回0；出现错误，则返回NULL（诸如不正确的参数）。

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

GET_LOCK('lock',10)返回结果为1，说明成功得到了一个名称为‘lock1’的锁，持续时间为10秒。IS_USED_LOCK('lock1‘)返回结果为当前连接ID，表示名称为‘lock1’的锁正在被使用IS_FREE_LOCK('lock1') 返回结果为0，说明名称为’lock1‘的锁正在被使用。RELEASE_LOCK('lock1')返回值为1，说明解锁成功。

5. 重复执行指定操作的函数

BENCHMAKE(count,expr)函数重复count次执行表达式expr。它可以用于计算MySQL处理表达式的速度。

6. 改变字符集的函数

CONVER(…USING…)：带有USING的CONVERT()函数被用来在不同的字符集之间转化数据。

7. 改变数据类型的函数

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

PART9. MySQL8.0的新特性2——窗口函数

 这里创建了名称为w1的窗口函数，规定对brcount字段进行排序，然后在SELECT子句中对窗口函数w1执行rank()方法，将结果输出为rank字段。

 

-- end