MySQL 函数（五）

MySQL 函数

MySQL 函数简介

• MySQL 提供了大量丰富的函数，在进行数据库管理以及数据的查询和操作时将会经常用到各种函数。各类函数从功能方面主要分为数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等。

数学函数

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

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

1. ABS(x)

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

2. PI()

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

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

1. SQRT(x)

   SQRT(x)返回非负数 x 的二次方根。当 x 为负数时，返回结果为 NULL。

2. MOD(x,y)

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

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

1. CEIL(x)和 CEILING(x)

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

2. 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)

1. ROUND(x)

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

2. ROUND(x,y)

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

3. 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)

1. POW(x,y)和 POWER(x,y)

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

2. EXP(x)

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

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

1. LOG(x)

   LOG(x)返回 x 的自然对数，x 相对于基数 e 的对数。对数定义域不能为负数，若 x 为负数，则返回结果 NULL。

2. LOG10(x)

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

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

1. RADIANS(x)

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

2. DEGREES(x)

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

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

1. SIN(x)

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

2. ASIN(x)

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

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

1. COS(x)

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

2. ACOS(x)

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

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

1. 正切函数 TAN(x)

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

2. 反正切函数 ATAN(x)

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

3. 余切函数 COT(x)

   COT(x)返回 x 的余切。

字符串函数

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

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

1. CHAR_LENGTH(str)

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

2. LENGTH(str)

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

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

1. CONCAT(s1,s2,...)

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

2. 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'。

字母大小写转换函数

1. LOWER(str)和 LCASE(str)

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

2. UPPER(str)或者 UCASE(str)

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

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

1. LEFT(s,n)

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

2. RIGHT(s,n)

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

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

1. LPAD(s1,len,s2)

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

2. RPAD(s1,len,s2)

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

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

1. LTRIM(s)

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

2. RTRIM(s)

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

3. 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)

1. SPACE(n)

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

2. 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)

1. SUBSTRING(s,n,len)

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

2. 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 类型的参数，但会忽略日期部分。许多日期函数可以同时接受数字和字符串类型的两种参数。

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

1. CURDATE()和 CURRENT_DATE()

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

2. 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 时间戳函数

1. 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 格式的数字。

2. FROM_UNIXTIME(date)

   FROM_UNIXTIME(date)函数把 UNIX 时间戳转换为普通格式的时间。输入语句如下：

   FROM_UNIXTIME('1614498353')
   

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

1. UTC_DATE()

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

2. UTC_TIME()

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

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

1. MONTH(date)

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

2. MONTHNAME(date)

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

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

1. DAYNAME(d)

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

2. DAYOFWEEK(d)

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

3. WEEKDAY(d)

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

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

1. 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	本年度中有一个周一

2. WEEKOFYEAR(d)

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

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

1. DAYOFYEAR(d)

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

2. DAYOFMONTH(d)

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

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

1. YEAR(date)

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

   提示：‘ 0~69 ’ 转换为 ’ 2000~2069 ‘ ，’ 70~99 ‘ 转换为 ’ 1970~1999 ‘。

2. QUARTER(date)

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

3. MINUTE(time)

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

4. 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。

时间和秒钟转换的函数

1. TIME_TO_SEC(time)

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

2. 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 表达式。

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

1. 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 位数形式表示年份
   %%	‘%’ 文字字符

2. TIME_FORMAT()

   TIME_FORMAT(time,format) 根据 format 字符串安排 time 值的格式。format 字符串可能仅会处理小时、分钟和秒的格式说明符，其他说明符产生一个 NULL 值或 0。若 time 值包含一个大于 23 的小时部分，则 %H 和 %k 小时格式说明符会产生一个 大于（0...23） 的通常范围的值。

3. 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 函数

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

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

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

   该函数表示，某个 vi 值为 true 时，返回对应位置 THEN 后面的结果，如果所有值都不为 true，则返回 ELSE 后的 rn。

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

系统信息函数

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

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

1. VERSION()

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

2. CONNECTION_ID()

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

3. 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 语句，是判断问题语句的一个重要依据。

4. DATABASE()和 SCHEMA()

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

获取用户名的函数

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

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

1. CHARSET(str)

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

2. 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 地址与数字相互转换的函数

1. INET_ATON()

   • INET_ATON(expr) 给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。地址可以是 4bit 或 8bit 地址。
   • 例如 IP 地址为 a.b.c.d，计算方法为 a *(256^3) + b *(256^2) + c *(256^1) + d *(256^0)。

2. INET_NTOA()

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

加锁函数和解锁函数

1. GET_LOCK(str,timeout)

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

2. RELEASE_LOCK(str)

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

3. IS_FREE_LOCK(str)

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

4. 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。