MySQL 函数
MySQL 函数简介
- MySQL 提供了大量丰富的函数,在进行数据库管理以及数据的查询和操作时将会经常用到各种函数。各类函数从功能方面主要分为数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等。
数学函数
- 数学函数主要用来处理数值数据,主要的数学函数有绝对值函数、三角函数(包括正弦函数、余弦函数、正切函数、余切函数等)、对数函数、随机数函数等。在有错误产生时,数学函数将会返回空值 NULL。
绝对值函数 ABS(x)和返回圆周率的功能 PI()
-
ABS(x)
ABS(x)返回 X 的绝对值。
-
PI()
PI()返回圆周率的值。默认的显示小数位数是 6 位。
平方根函数 SQRT(x)和求余函数 MOD(x,y)
-
SQRT(x)
SQRT(x)返回非负数 x 的二次方根。当 x 为负数时,返回结果为 NULL。
-
MOD(x,y)
MOD(x,y)返回 x 被 y 除后的余数,MOD()对于带有小数部分的数值也起作用,返回除法运算后的精确余数。
获取整数的函数 CEIL(x)、CEILING(x)和 FLOOR(x)
-
CEIL(x)和 CEILING(x)
CEIL(x)和 CEILING(x)的意义相同,返回不小于 x 的最小整数值,返回值转化为一个 BIGINT。
-
FLOOR(x)
FLOOR(x)返回不大于 x 的最大整数值,返回值转化为一个 BIGINT。
获取随机数的函数 RAND()和 RAND(x)
- RAND(x)返回一个随机浮点值 v,范围在 0 到 1 之间 (0 <= v <= 1.0)。若已指定一个整数参数 x,则它被用作种子值,用来产生重复序列。
- 不带参数的 RAND() 每次产生的随机数值是不同的。而当 RAND(x) 的参数相同时,将产生相同的随机数,不同的 x 产生的随机数值不同。
函数 ROUND(x)、ROUND(x,y)和 TRUNCATE(x,y)
-
ROUND(x)
ROUND(x)返回最接近于参数 x 的整数,对 x 值进行四舍五入。
-
ROUND(x,y)
ROUND(x,y)返回最接近于参数 x 的数,其值保留到小数点后面 y 位,若 y 为负值,则将保留 x 值到小数点左边 y 位。
-
TRUNCATE(x,y)
TRUNCATE(x,y)返回被舍去至小数点后 y 位的数字 x。若 y 的值为 0,则结果不带有小数点或不带有小数部分。若 y 为负数,则截去(归零) x 小数点左起第 y 位 开始后面所有低位的值。
提示:ROUND(x,y) 函数在截取值的时候会四舍五入,而 TRUNCATE(x,y) 直接截取值,并不进行四舍五入。
符号函数 SIGN(x)
- SIGN(x)返回参数的符号,x 的值为负、零或正时返回结果依次为 -1、0 或 1。
幂运算函数 POW(x,y)、POWER(x,y)和 EXP(x)
-
POW(x,y)和 POWER(x,y)
POW(x,y)或者 POWER(x,y)函数返回 x 的 y 次乘方结果值。
-
EXP(x)
EXP(x)返回 e 的 x 次乘方后的值。
对数运算函数 LOG(x)和 LOG10(x)
-
LOG(x)
LOG(x)返回 x 的自然对数,x 相对于基数 e 的对数。对数定义域不能为负数,若 x 为负数,则返回结果 NULL。
-
LOG10(x)
LOG10(x)返回 x 相对于基数 10 的对数。
角度与弧度相互转换的函数 RADIANS(x)和 DEGREES(x)
-
RADIANS(x)
RADIANS(x)将参数 x 由角度转化为弧度。
-
DEGREES(x)
DEGREES(x)将参数 x 由弧度转化为角度。
正弦函数 SIN(x)和反正弦函数 ASIN(x)
-
SIN(x)
SIN(x)返回 x 的正弦值,其中 x 为弧度值。
-
ASIN(x)
ASIN(x)返回 x 的反正弦,即正弦为 x 的值。若 x 不在 -1 到 1 的范围之内,则返回 NULL。
余弦函数 COS(x)和反余弦函数 ACOS(x)
-
COS(x)
COS(x)返回 x 的余弦值,其中 x 为弧度值。
-
ACOS(x)
ASIN(x)返回 x 的反余弦,即余弦为 x 的值。若 x 不在 -1 到 1 的范围之内,则返回 NULL。
正切函数、反正切函数和余切函数
-
正切函数 TAN(x)
TAN(x)返回 x 的正切,其中 x 为给定的弧度值。
-
反正切函数 ATAN(x)
ATAN(x)返回 x 的反正切,即正切为 x 的值。
-
余切函数 COT(x)
COT(x)返回 x 的余切。
字符串函数
- 字符串函数主要用来处理数据库中的字符串数据,MySQL 中的字符串函数有计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。
计算字符串字符数和字符串长度的函数
-
CHAR_LENGTH(str)
CHAR_LENGTH(str)返回值为字符串 str 所包含的字符个数。一个多字节字符算作一个单字符。
-
LENGTH(str)
LENGTH(str)返回值为字符串的字节长度,使用 utf8(UNICODE 的一种变长字符编码,又称万国码)编码字符集时,一个汉字是 3 个字节,一个数字或字母算一个字节。
合并字符串函数 CONCAT(s1,s2,...)、CONCAT_WS(x,s1,s2,...)
-
CONCAT(s1,s2,...)
CONCAT(s1,s2,...)返回结果为连接参数产生的字符串,或许有一个或多个参数。任何一个参数为 NULL,则返回值为 NULL。如果所有参数均为非二进制字符串,则结果为非二进制字符串。如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。
-
CONCAT_WS(x,s1,s2,...)
在 CONCAT_WS(x,s1,s2,...)中,CONCAT_WS 代表 CONCAT With Separator,是 CONCAT() 的特殊形式;第一个参数 x 是其他参数的分隔符,分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其他参数。 如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
替换字符串的函数 INSERT(s1,x,len,s2)
- INSERT(s1,x,len,s2)返回字符串 s1,其子字符串起始于 x 位置和被字符串 s2 取代的 len 字符。如果 x 超过字符串长度,那么返回值为原始字符串。如果 len 的长度大于原字符串长度,就从位置 x 开始替换。若任何一个参数为 NULL,则返回值为 NULL。例如
INSERT('opd',1,2,'abc')
执行后返回字符串 'abcd'。
字母大小写转换函数
-
LOWER(str)和 LCASE(str)
LOWER(str)或者 LCASE(str)可以将字符串 str 中的字母字符全部转换成小写字母。
-
UPPER(str)或者 UCASE(str)
UPPER(str)或者 UCASE(str)可以将字符串 str 中的字母字符全部转换成大写字母。
获取指定长度的字符串的函数 LEFT(s,n)和 RIGHT(s,n)
-
LEFT(s,n)
LEFT(s,n)返回字符串 s 开始的最左边 n 个字符。
-
RIGHT(s,n)
RIGHT(s,n)返回字符串 s 开始的最右边 n 个字符。
填充字符串的函数 LPAD(s1,len,s2)和 RPAD(s1,len,s2)
-
LPAD(s1,len,s2)
LPAD(s1,len,s2)返回字符串 s1,其左边由字符串 s2 填补到 len 字符长度。若 s1 的长度大于 len,则返回值被缩短至 len 字符长度。
-
RPAD(s1,len,s2)
RPAD(s1,len,s2)返回字符串 s1,其右边由字符串 s2 填补到 len 字符长度。若 s1 的长度大于 len,则返回值被缩短至 len 字符长度。
删除空格的函数 LTRIM(s)、RTRIM(s)和 TRIM(s)
-
LTRIM(s)
LTRIM(s)返回字符串 s,字符串左侧空格字符被删除。
-
RTRIM(s)
RTRIM(s)返回字符串 s,字符串右侧空格字符被删除。
-
TRIM(s)
TRIM(s)删除字符串 s 两侧的空格。
删除特定字符串的函数 TRIM(s1 FROM s)
- TRIM(s1 FROM s)删除字符串 s 中两端所有的子字符串 s1。s1 为可选项,在未指定情况下,删除空格。
重复生成字符串的函数 REPEAT(s,n)
- REPEAT(s,n)返回一个由重复的字符串 s 组成的字符串,字符串 s 的数目等于 n。若 n 小于等于 0,则返回一个空字符串。若 s 或 n 为 NULL,则返回 NULL。
空格函数 SPACE(n)和替换函数 REPLACE(s,s1,s2)
-
SPACE(n)
SPACE(n)返回一个由 n 个空格组成的字符串。
-
REPLACE(s,s1,s2)
REPLACE(s,s1,s2)使用字符串 s2 替代字符串 s 中所有的字符串 s1。
比较字符串大小的函数 STRCMP(s1,s2)
- 在 STRCMP(s1,s2)中,若所有的字符串均相同,则返回 0;根据当前分类次序,若第一个参数小于第二个,则返回 -1,其他情况返回 1。
获取子串的函数 SUBSTRING(s,n,len)和 MID(s,n,len)
-
SUBSTRING(s,n,len)
SUBSTRING(s,n,len)带有 len 参数的格式,从字符串 s 返回一个长度同 len 字符相同的子字符串,起始于位置 n。也可能对 n 使用一个负值,则子字符串的位置起始于字符串结尾的 n 字符,即倒数第 n 个字符,而不是字符串的开头位置。
-
MID(s,n,len)
MID(s,n,len)与 SUBSTRING(s,n,len)的作用相同。
- 提示:如果对 len 使用的是一个小于 1 的值,则结果始终为空字符串。
匹配子串开始位置的函数
- LOCATE(str1,str)、POSITION(str1 IN str) 和 INSTR(str,str1) 三个函数作用相同,返回子字符串 str1 在字符串 str 中的开始位置。若匹配失败则返回结果 0。(提示:注意 INSTR 函数参数位置与前两个函数不同)
字符串逆序的函数 REVERSE(s)
- REVERSE(s)将字符串 s 反转,返回的字符串的顺序和 s 字符串顺序相反。
返回指定位置的字符串的函数
- 在 ELT(N,字符串 1,字符串 2,字符串 3,...,字符串 N) 中,若 N=1,则返回值为字符串 1;若 N=2,则返回值为字符串 2;以此类推。若 N 小于 1 或大于参数的数目,则返回值为 NULL。
返回指定字符串位置的函数 FIELD(s,s1,s2,...)
- FIELD(s,s1,s2,...) 返回字符串 s 在列表(s1,s2,...) 中 第一次 出现的位置,在找不到 s 的情况下,返回值为 0。如果 s 为 NULL,则返回值为 0,原因是 NULL 不能同任何值进行同等比较。
返回子串位置的函数 FIND_IN_SET(s1,s2)
- FIND_IN_SET(s1,s2) 返回字符串 s1 在字符串列表 s2 中出现的位置,字符串列表是一个由多个逗号 ’,‘ 分开的字符串组成的列表。 若 s1 不在 s2 中或 s2 为空字符串,则返回值为 0。若任意一个参数为 NULL,则返回值为 NULL。第一个参数包含一个逗号 ',' 时这个函数将无法正常运行。
选取字符串的函数 MAKE_SET(x,s1,s2...)
- MAKE_SET(x,s1,s2...) 返回由 x 的二进制数指定的相应位的字符串组成的字符串,s1 对应比特 0001,s2 对应比特 0010,以此类推。(s1,s2,...)中的 NULL 值不会被添加到结果中。例如,
MAKE_SET(0101,'a','b','c')
的执行结果为 'a,c'。
日期和时间函数
- 日期和时间函数主要用来处理日期和时间值。一般的日期函数除了使用 DATE 类型的参数外,也可以使用 DATETIME 或者 TIMESTAMP 类型的参数,但会忽略这些值的时间部分。相同的,以 TIME 类型值为参数的函数,可以接受 TIMESTAMP 类型的参数,但会忽略日期部分。许多日期函数可以同时接受数字和字符串类型的两种参数。
获取当前日期的函数和获取当前时间的函数
-
CURDATE()和 CURRENT_DATE()
- CURDATE( )和 CURRENT_DATE( )函数函数作用相同,将当前日期按照 ‘YYYY-MM-DD’ 或 YYYYMMDD 格式的值返回,具体格式根据函数是在字符串还是数字语境中而定。
- “CURDATE( )+0” 将当前日期值转换为数值型。
-
CURTIME()和 CURRENT_TIME()
- CURTIME( )和 CURRENT_TIME( )函数函数作用相同,将当前日期按照 ‘HH:MM:SS’ 或 HHMMSS 格式的值返回,具体格式根据函数是在字符串还是数字语境中而定。
- “CURTIME( )+0” 将当前日期值转换为数值型。
获取当前日期和时间的函数
- CURRENT_TIMESTAMP( )、LOCALTIME( )、NOW( )和 SYSDATE( ) 4 个函数的作用相同,均返回当前日期和时间值,格式为 ‘YYYY-MM-DD HH:MM:SS’ 或 YYYYMMDDHHMMSS,具体格式根据函数是在字符串还是数字语境中而定。
UNIX 时间戳函数
-
UNIX_TIMESTAMP(date)
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 格式的数字。
-
FROM_UNIXTIME(date)
FROM_UNIXTIME(date)函数把 UNIX 时间戳转换为普通格式的时间。输入语句如下:
FROM_UNIXTIME('1614498353')
返回 UTC 日期的函数和返回 UTC 时间的函数
-
UTC_DATE()
UTC_DATE( )函数返回当前 UTC(世界标准时间)日期值,其格式为 ‘YYYY-MM-DD’ 或 YYYYMMDD,具体格式取决于函数是用在字符串还是数字语境中。
-
UTC_TIME()
UTC_TIME( )函数返回当前 UTC(世界标准时间)时间值,其格式为 ‘HH:MM:SS’ 或 HHMMSS,具体格式取决于函数是用在字符串还是数字语境中。
获取月份的函数 MONTH(date)和 MONTHNAME(date)
-
MONTH(date)
MONTH(date)函数返回 date 对应的月份,值为 1~12。
-
MONTHNAME(date)
MONTHNAME(date)函数返回日期 date 对应月份的英文全名。
获取星期的函数 DAYNAME(d)、DAYOFWEEK(d)和 WEEKDAY(d)
-
DAYNAME(d)
DAYNAME(d) 函数返回 d 对应的工作日的英文名称,例如 Sunday、Monday 等。
-
DAYOFWEEK(d)
DAYOFWEEK(d) 函数返回 d 对应的一周中的索引(位置):1 表示周日,2 表示周一,......,7 表示周六。
-
WEEKDAY(d)
WEEKDAY(d) 返回 d 对应的工作日索引:0 表示周一,1 表示周二,......,6 表示周日。
获取星期数的函数 WEEK(d)和 WEEKOFYEAR(d)
-
WEEK(d)
WEEK(d) 计算日期 d 是一年中的第几周。WEEK(d,Mode) 的双参数形式允许指定该星期是否起始于周日或周一,以及返回值的范围是否为 0~53 或 1~53。若 Mode 参数被省略,则使用 default_week_format 系统自变量的值。MySQL 中该值默认为 0。WEEK 函数中 Mode 参数取值如下表所示。
Mode 一周的第一天 范围 Week 1 为第一周 0 周日 0~53 本年度中有一个周日 1 周一 0~53 本年度中有 3 天以上 2 周日 1~53 本年度中有一个周日 3 周一 1~53 本年度中有 3 天以上 4 周日 0~53 本年度中有 3 天以上 5 周一 0~53 本年度中有一个周一 6 周日 1~53 本年度中有 3 天以上 7 周一 1~53 本年度中有一个周一 -
WEEKOFYEAR(d)
WEEKOFYEAR(d) 计算某天位于一年中的第几周,范围是 1~53,相当于 WEEK(d,3)。
获取天数的函数 DAYOFYEAR(d)和 DAYOFMONTH(d)
-
DAYOFYEAR(d)
DAYOFYEAR(d) 函数返回 d 是一年中的第几天,范围是 1~366。
-
DAYOFMONTH(d)
DAYOFMONTH(d) 函数返回 d 是一个月中的第几天,范围是 1~31。
获取年份、季度、小时、分钟和秒数的函数
-
YEAR(date)
YEAR(date) 返回 date 对应的年份,范围是 1970~2069。
提示:‘ 0~69 ’ 转换为 ’ 2000~2069 ‘ ,’ 70~99 ‘ 转换为 ’ 1970~1999 ‘。
-
QUARTER(date)
QUARTER(date) 返回 date 对应的一年中的季度值,范围是 1~4。
-
MINUTE(time)
MINUTE(time) 返回 time 对应的分钟数,范围是 0~59。
-
SECOND(time)
SECOND(time) 返回 time 对应的秒数,范围是 0~59。
获取日期的指定值的函数 EXTRACT(type FROM date)
-
EXTRACT(type FROM date) 函数所使用的时间间隔类型说明符与 DATE_ADD( ) 或 DATE_SUB( ) 的相同,但它从日期中提取一部分,而不是执行日期运算。语法格式如下所示。
EXTRACT(YEAR_MONTH FROM '2011-07-12 01:02:03')
上述 SQL 语句为获取年和月份。执行后返回结果为 201107。
时间和秒钟转换的函数
-
TIME_TO_SEC(time)
TIME_TO_SEC(time) 返回已转化为秒的 time 参数。转换公式为:小时 x3600+ 分钟 x60+ 秒。
-
SEC_TO_TIME(seconds)
SEC_TO_TIME(seconds) 返回被转化为小时、分钟和秒数的 seconds 参数值,其格式为 ’HH:MM:SS‘ 或 HHMMSS,具体格式根据该函数是用在字符串还是数字语境中而定。
计算日期和时间的函数
-
计算日期和时间的函数有 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 是一个表达式,用来指定从起始日期添加或减去的时间间隔值,对于负值的时间的间隔,它可以以一个负号 ’-‘ 开头;type 为关键词,指定表达式被解释的方式。
-
MySQL 中计算日期和时间的格式如下表所示:
type 值 预期的 expr 格式 MICROSECOND MICROSECONDS SECOND SECONDS MINUTE MINUTES HOUR HOURS DAY DAYS WEEK WEEKS MONTH MONTHS QUARTER QUARTERS YEAR YEARS SECOND_MICROSECOND 'SECONDS.MICROSECONDS' MINUTE_MICROSECOND 'MINUTES.MICROSECONDS' MINUTE_SECOND 'MINUTES:SECONDS' HOUR_MICROSECOND 'HOURS.MICROSECONDS' HOUR_SECOND 'HOURS:MINUTES:SECONDS' HOUR_MINUTE 'HOURS:MINUTES' DAY_MICROSECOND 'DAYS.MICROSECONDS' DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS' DAY_MINUTE 'DAYS HOURS:MINUTES' DAY_HOUR 'DAYS HOURS' YEAR_MONTH 'YEARS-MONTHS' 若 date 参数是一个 DATE 值,计算只会包括 YEAR、MONTH 和 DAY 部分(没有时间部分),其结果是一个 DATE 值;否则,结果将是一个 DATETIME 值。
提示:DATE_ADD 和 DATE_SUB 在指定修改的时间段,也可以指定负值,负值代表相减,即返回以前的日期和时间。
- ADDTIME(date,expr) 函数将 expr 值添加到 date,并返回修改后的值,date 是一个日期或者日期时间表达式,而 expr 是一个时间表达式。
- SUBTIME(date,expr) 函数将 date 减去 expr 值,并返回修改后的值。date 是一个日期或者日期时间表达式,expr 是一个时间表达式。
- DATEDIFF(date1,date2) 返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日期或 date-and-time 表达式。
将日期和时间格式化的函数
-
DATE_FORMAT()
DATE_FORMAT(date,format) 根据 format 指定的格式显示 date 值。主要 format 格式如下表所示。
说明符 说明 %a 工作日的缩写名称(Sun...Sat) %b 月份的缩写名称(Jan...Dec) %c 月份,数字形式(0...12) %D 带有英语后缀的该月日期(0th,1st,2nd,3rd,...) %d 该月日期,数字形式(00...31) %e 该月日期,数字形式(0...31) %f 微秒(000000...999999) %H 以 2 位数表示 24 小时(00...23) %h,%I 以 2 位数表示 12 小时(01...12) %i 分钟,数字形式(00...59) %j 一年中的天数(001...366) %k 以 24(0...23)小时表示时间 %l 以 12(1...12)小时表示时间 %M 月份名称(January...December) %m 月份,数字形式(00...12) %p 上午(AM)或下午(PM) %r 时间,12 小时制(小时 hh:分钟 mm:秒数 ss 后加 AM 或 PM) %S,%s 以 2 位数形式表示秒(00...59) %T 时间,24 小时制(小时 hh:分钟 mm:秒数 ss) %U 周(00...53),其中周日为每周的第一天 %u 周(00...53),其中周一为每周的第一天 %V 周(01...53),其中周日为每周的第一天;和 %X 同时使用 %v 周(01...53),其中周日为每周的第一天;和 %x 同时使用 %W 工作日名称(Sunday...Saturday) %w 一周中的每日(0=周日...6=周六) %X 该周的年份,其中周日为每周的第一天;数字形式,4 位数;和 %V 同时使用 %x 该周的年份,其中周一为每周的第一天;数字形式,4 位数;和 %v 同时使用 %Y 4 位数形式表示年份 %y 2 位数形式表示年份 %% ‘%’ 文字字符 -
TIME_FORMAT()
TIME_FORMAT(time,format) 根据 format 字符串安排 time 值的格式。format 字符串可能仅会处理小时、分钟和秒的格式说明符,其他说明符产生一个 NULL 值或 0。若 time 值包含一个大于 23 的小时部分,则 %H 和 %k 小时格式说明符会产生一个 大于(0...23) 的通常范围的值。
-
GET_FORMAT()
GET_FORMAT(val_type,format_type) 返回日期时间字符串的显示格式,val_type 表示日期数据类型,包括 DATE、DATETIME 和 TIME;format_type 表示格式化显示类型,包括 EUR、INTERVAL、ISO、JIS、USA。GET_FORMAT 根据两个值的类型组合返回的字符串显示格式如下表所示。
值类型 格式化类型 显示格式字符串 DATE EUR %d.%m.%Y DATE INTERVAL %Y%m%d DATE ISO %Y-%m-%d DATE JIS %Y-%m-%d DATE USA %m.%d.%Y TIME EUR %H.%i.%s TIME INTERVAL %H%i%s TIME ISO %H:%i:%s TIME JIS %H:%i:%s TIME USA %h:%i:%s %p DATETIME EUR %Y-%m-%d %H.%i.%s DATETIME INTERVAL %Y%m%d%H%i%s DATETIME ISO %Y-%m-%d %H:%i:%s DATETIME JIS %Y-%m-%d %H:%i:%s DATETIME USA %Y-%m-%d %H.%i.%s GET_FORMAT(DATE,'USA') 返回的显示格式字符串为 %m.%d.%Y。
条件判断函数
- 条件判断函数也称为控制流程函数,根据满足的条件执行相应的流程。MySQL 中进行条件判断的函数有 IF、IFNULL 和 CASE。
IF(expr,v1,v2)函数
- 在 IF(expr,v1,v2) 中,若表达式 expr 是 true(expr<>0 and expr<>NULL),则 IF() 的返回值为 v1;否则返回值为 v2。IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定。
IFNULL(v1,v2)函数
- 在 IFNULL(v1,v2)中,假如 v1 不为 NULL,则 IFNULL() 的返回值为 v1;否则,返回值为 v2。IFNULL() 的返回值是数字值或字符串值,具体情况取决于其所在语境。
提示:若 v1 或 v2 只有一个明确是 NULL,则 IFNULL() 函数的结果类型为非 NULL 表达式的结果类型。
CASE 函数
-
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END
该函数表示,如果 expr 值等于某个 vi,则返回对应位置 THEN 后面的结果;如果与所有值都不相等,则返回 ELSE 后面的 rn。
-
CASE WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn] END
该函数表示,某个 vi 值为 true 时,返回对应位置 THEN 后面的结果,如果所有值都不为 true,则返回 ELSE 后的 rn。
提示:一个 CASE 表达式的默认返回值类型是任何返回值的相容集合类型,具体情况视其所在语境而定;用在字符串语境中,返回结果为字符串;用在数字语境中,返回结果为十进制值、实数值或整数值。
系统信息函数
- MySQL 中的系统信息有数据库的版本号、当前用户名和连接数、系统字符集、最后一个自动生成的 ID 值等。
获取 MySQL 版本号、连接数和数据库名的函数
-
VERSION()
VERSION() 返回指示 MySQL 服务器版本的字符串。这个字符串使用 utf8 字符集。
-
CONNECTION_ID()
CONNECTION_ID() 返回 MySQL 服务器当前用户连接数,每个连接都有各自唯一的 ID。
-
SHOW PROCESSLIST 和 SHOW FULL PROCESSLIST
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 语句,是判断问题语句的一个重要依据。
-
DATABASE()和 SCHEMA()
DATABASE( )和 SCHEMA( ) 函数返回使用 utf8 字符集的默认(当前)数据库名。
获取用户名的函数
- USER()、CURRENT_USER()、SYSTEM_USER() 和 SESSION_USER() 这几个函数返回当前被 MySQL 服务器验证的用户名和主机名组合。这个值符合确定当前登录用户存取权限的 MySQL 账户。一般情况下,这几个函数的返回值是相同的。
获取字符串的字符集和排序方式的函数
-
CHARSET(str)
CHARSET(str) 返回字符串 str 自变量的字符集。
-
COLLATION(str)
COLLATION(str) 返回字符串 str 的字符排列方式。
获取最后一个自动生成的 ID 值的函数
- LAST_INSERT_ID() 自动返回最后一个 INSERT 或 UPDATE 为 AUTO_INCREMENT 列设置的第一个发生的值。
加密函数
- 加密函数主要用来数据进行加密和界面处理,以保证某些重要数据不被别人获取。这些函数在保证数据库安全时非常有用。
加密函数 PASSWORD(str)
- PASSWORD(str) 从原明文密码 str 计算并返回加密后的密码字符串,当参数为 NULL 时,返回 NULL。
- MySQL 将 PASSWORD 函数加密后的密码保存到用户权限表中。
- 提示:PASSWORD() 函数在 MySQL 服务器的鉴定系统中使用;不应将它用在个人的应用程序中。PASSWORD() 加密是单向的(不可逆)。PASSWORD() 执行密码加密与 UNIX 中密码被加密的方式不同。
加密函数 MD5(str)
- MD5(str) 为字符串算出一个 MD5 128 比特校验和。该值以 32 位十六进制数字的二进制字符串形式返回,若参数为 NULL,则会返回 NULL。
加密函数 ENCODE(str,pswd_str)
- ENCODE(str,pswd_str) 使用 pswd_str 作为密钥,加密 str。使用 DECODE() 解密结果,结果是一个和 str 长度相同的二进制字符串。
解密函数 DECODE(crypt_str,pswd_str)
- DECODE(crypt_str,pswd_str) 使用 pswd_str 作为密钥,解密加密字符串 crypt_str。crypt_str 是由 ENCODE() 返回的字符串。
其他函数
- 这些函数不能笼统地分为哪一类,但是这些函数也非常有用,例如重复指定操作函数、改变字符集函数、IP 地址与数字转换函数等。
格式化函数 FORMAT(x,n)
- FORMAT(x,n) 将数字 x 格式化,并以四舍五入的方式保留小数点后 n 位,结果以字符串的形式返回。若 n 为 0,则返回结果不含小数部分。
不同进制的数字进行转换的函数
- CONV(N,from_base,to_base) 函数进行不同进制数间的转换。返回值为数值 N 的字符串表示,由 from_base 进制转化为 to_base 进制。若有任意一个参数为 NULL,则返回值为 NULL。自变量 N 被理解为一个整数,但是可以被指定为一个整数或字符串。最小基数为 2,最大基数为 36。
IP 地址与数字相互转换的函数
-
INET_ATON()
- INET_ATON(expr) 给出一个作为字符串的网络地址的点地址表示,返回一个代表该地址数值的整数。地址可以是 4bit 或 8bit 地址。
- 例如 IP 地址为 a.b.c.d,计算方法为 a *(256^3) + b *(256^2) + c *(256^1) + d *(256^0)。
-
INET_NTOA()
INET_NTOA(expr) 给定一个数字网络地址(4bit 或 8bit),返回作为字符串的该地址的点地址表示。
加锁函数和解锁函数
-
GET_LOCK(str,timeout)
GET_LOCK(str,timeout) 设法使用字符串 str 给定的名字得到一个锁,超时为 timeout 秒。若成功得到锁,则返回 1;若操作超时,则返回 0;若发生错误,则返回 NULL。假如有一个用 GET_LOCK() 得到的锁,当执行 RELEASE_LOCK() 或连接断开(正常或非正常)时,这个锁就会解除。
-
RELEASE_LOCK(str)
RELEASE_LOCK(str) 解开被 GET_LOCK() 获取的,用字符串 str 所命名的锁。若锁被解开,则返回 1;若线程尚未创建锁,则返回 0(此时锁没有被解开);若命名的锁不存在,则返回 NULL。若该锁从未被 GET_LOCK() 的调用获取,或锁已经被提前解开,则该锁不存在。
-
IS_FREE_LOCK(str)
IS_FREE_LOCK(str) 检查名为 str 的锁是否可以使用(换言之,没有被封锁)。若锁可以使用,则返回 1(没有人在用这个锁);若这个锁正在被使用,则返回 0;出现错误,则返回 NULL(诸如不正确的参数)。
-
IS_USED_LOCK(str)
IS_USED_LOCK(str) 检查名为 str 的锁是否正在被使用(换言之,被封锁)。若被封锁,则返回使用该锁的客户端的连接标识符(connection ID);否则,返回 NULL。
重复执行指定操作的函数
-
BENCHMARK(count,expr) 函数重复执行表达式(expr)count 次。它可以用于计算 MySQL 处理表达式的速度。结果值通常为 0(0 只是表示处理过程很快,并不是没有花费时间)。另一个作用是它可以在 MySQL 客户端内部报告语句执行的时间。
提示:BENCHMARK 报告的时间是客户端经过的时间,而不是在服务器端的 CPU 时间,每次执行后报告的时间并不一定是相同的。
改变字符集的函数
- CONVERT(...USING...) 带有 USING 的 CONVERT() 函数被用来在不同的字符集之间转化数据。
改变数据类型的函数
- CAST(x,AS type) 和 CONVERT(x,type) 函数将一个类型的值转换为另一个类型的值,可转换的 type 有 BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED。
欢迎来到这里!
我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。
注册 关于