MySQL-函数

一、简介

1.1 内容

介绍了 MySQL 参考手册中的一些基本函数的用法。

1.2 知识点

  • 基本函数

二、控制流程函数

  • CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result …] [ELSE result] END

CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END

在上面第一条语句返回的是value=compare-value的结果。而第二条语句的返回结果是第一条语句的真正的结果。如果没有匹配的结果值，则返回结果为ELSE语句后的结果，如果没有ELSE部分，则返回值为NULL。

1
2
3
4
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
 -> WHEN 2 THEN 'two' ELSE 'more' END;
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
mysql> SELECT CASE BINARY 'B'

-> WHEN ‘a’ THEN 1 WHEN ‘b’ THEN 2 END;

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

  • IF(expr1,expr2,expr3)

如果expr1是TRUE(expr1 <> 0 and expr1 <> NULL)，则IF()的返回值为expr2; 否则返回值则为expr3。IF()的返回值是否为数字值或字符串值，具体情况视其所在语境而定。

1
2
3
mysql> SELECT IF(1>2,2,3);
mysql> SELECT IF(1<2,'yes ','no');
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');

如果expr2或expr3中只有一个表达式是NULL值，则IF()函数的结果类型 为非NULL表达式的结果类型。

expr1必须作为一个整数值进行评估，也就是说，假如你正在验证浮点值或字符串值，那么应该使用比较运算进行检验。

1
2
3
4
mysql> SELECT IF(0.1,1,0);
 -> 1
mysql> SELECT IF(0.1<>0,1,0);
 -> 1

观察并对比上述语句的返回结果，发现在上述的第一个例子中，IF(0.1)的返回值为1，原因是IF(0.1)检验为真。在第二个例子中，比较检验了原始浮点值，目的是为了了解是否其为非零值，对比的结果是0.1确实不等于0，那么第一个表达式的结果就是整数1，因此返回结果为1。

IF()（这一点在其被储存到临时表时很重要）的默认返回值类型按照以下方式计算：

假如expr2和expr3都是字符串类型，且其中任何一个字符串区分大小写，则返回结果都是区分大小写。

  • IFNULL(expr1,expr2)

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

1
2
3
mysql> SELECT IFNULL(1,0);
mysql> SELECT IFNULL(NULL,10);
mysql> SELECT IFNULL(1/0,10);

IFNULL(expr1,expr2)的默认结果值为两个表达式中数据类型更加“通用”的一个，顺序为STRING、REAL或INTEGER。假设有一个表中含有该表达式，或MySQL必须在内存储器中储存IFNULL()的返回值到一个临时表中：

1
2
CREATE TABLE tmp SELECT IFNULL(1,'test') AS test；
DESCRIBE tmp;

在这个例子中，测试列的类型为字符串类型CHAR(4)。

  • NULLIF(expr1,expr2)

如果expr1 = expr2成立，那么返回值为NULL，否则返回值为expr1。这和CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END语句的原理相同。

1
2
mysql> SELECT NULLIF(1,1);
mysql> SELECT NULLIF(1,2);

注意：如果参数不相等，则MySQL会评估expr1两次。

三、字符串函数

如果字符串函数返回结果的长度大于max_allowed_packet系统变量的最大值时，字符串值函数的返回值为NULL。

对于在字符串上操作的函数，第一个位置的编号为 1。

  • ASCII(str)

返回值为字符串str的最左字符的数值。假如str为空字符串，则返回值为 0。假如str为NULL，则返回值为NULL。ASCII()用于从0到255的 数值的字符。

1
2
3
mysql> SELECT ASCII('2');
mysql> SELECT ASCII(2);
mysql> SELECT ASCII('dx');
  • CHAR_LENGTH(str)

返回值为字符串str的长度，长度单位为字符，一个多字节字符算作一个单字符。对于一个包含五个二字节字符集,LENGTH()返回值为10,而CHAR_LENGTH()的返回值为5。

  • CONCAT(str1,str2,…)

返回结果为连接参数产生的字符串。如有任何一个参数为NULL，则返回值为 NULL。 如果所有参数均为非二进制字符串，则结果为非二进制字符串。 如果自变量中含有任意一个二进制字符串，则结果为一个二进制字符串。一个数字参数将被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型cast转换, 例如：

1
2
3
4
SELECT CONCAT(CAST(int_col AS CHAR), char_col)
mysql> SELECT CONCAT('My', 'S', 'QL');
mysql> SELECT CONCAT('My', NULL, 'QL');
mysql> SELECT CONCAT(14.3);
  • FIELD(str,str1,str2,str3,…)

返回值为str1, str2,str3,……列表中的str所在位置。在找不到str的情况下，返回值为0。如果所有FIELD()的参数均为字符串，则所有参数均按照字符串进行比较。如果所有的参数均为数字，则按照数字进行比较。否则，参数按照双精度类型进行比较。如果str为NULL值，则返回值为0，原因是NULL不能同任何值进行同等比较。

1
2
mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
  • FIND_IN_SET(str,strlist)

假如字符串str在由N子字符串组成的字符串列表strlist中，则返回值的范围在1到N之间。一个字符串列表就是一个由一些被‘,’符号分开的子字符串组成的字符串。如果第一个参数是一个常数字符串，而第二个是SET类型的数据，则FIND_IN_SET()函数将被使用比特计算优化。如果str不在strlist或strlist为空字符串，则返回值为0。如果任意一个参数为NULL，则返回值为NULL。 该函数在第一个参数就包含逗号(‘,’)时将无法正常运行。

1
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
  • INSERT(str,pos,len,newstr)

返回字符串str中起始于pos位置被字符串newstr替换长度为len 后的字符串。如果pos不在字符串长度范围内，则返回值为原始字符串。 假如len的长度大于剩下的字符串的长度，则从位置pos开始替换。若任何一个参数为null，则返回值为NULL。

1
2
3
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
  • INSTR(str,substr)

返回字符串str中子字符串substr第一次出现的位置。

1
2
mysql> SELECT INSTR('foobarbar', 'bar');
mysql> SELECT INSTR('xbar', 'foobar');
  • LEFT(str,len)

返回从字符串str左边数前len个字符。

1
mysql> SELECT LEFT('foobarbar', 5);
  • LENGTH(str)

返回值为字符串str的长度，单位为字节。对于一个包含5个2字节字符的字符串，LENGTH()的返回值为10,而CHAR_LENGTH()的返回值则为5。

1
mysql> SELECT LENGTH('text');
  • LOCATE(substr,str)

LOCATE(substr,str,pos)在没有参数pos时，返回为字符串str中子字符串substr的第一次出现的位置。反之，返回字符串str中以起始位置为pos开始的子字符串substr的第一次出现的位置。如若substr不在str中，则返回值为0。

1
2
3
mysql> SELECT LOCATE('bar', 'foobarbar');
mysql> SELECT LOCATE('xbar', 'foobar');
mysql> SELECT LOCATE('bar', 'foobarbar',5);
  • LOWER(str)

返回字符串str根据最新的字符集(默认为cp1252 Latin1)映射表转换为小写字母的字符 。

1
mysql> SELECT LOWER('QUADRATICALLY');
  • LTRIM(str)

返回删除左侧空格后的字符串str。

1
mysql> SELECT LTRIM(' barbar');
  • REPLACE(str,from_str,to_str)

返回所有被字符串to_str替代成字符串from_str后的str。

1
mysql> SELECT REPLACE('www.shiyanlou.com', 'w', 'Ww');
  • REVERSE(str)

返回和字符正常顺序相反的str。

1
mysql> SELECT REVERSE('abc');
  • RIGHT(str,len)

返回str中从最右开始数len个字符。

1
mysql> SELECT RIGHT('foobarbar', 4);
  • SUBSTRING_INDEX(str,delim,count)

若count为正值，则返回str中第count个定界符delim(从左边开始)左边的一切内容。若count为负值，则返回定界符（从右边开始）右边的一切内容。

1
2
mysql> SELECT SUBSTRING_INDEX('www.shiyanlou.com', '.', 2);
mysql> SELECT SUBSTRING_INDEX('www.shiyanlou.com', '.', -2);
  • TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)

返回字符串str，其中所有remstr前缀或后缀都已被删除。若分类符BOTH、LEADING或TRAILING中没有一个被指定，则假设为BOTH。remstr为可选项，在未指定情况下，删除空格。

1
2
3
4
mysql> SELECT TRIM(' bar ');
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');

四、数值函数

  • ABS(X)

返回X的绝对值。

1
2
mysql> SELECT ABS(2);
mysql> SELECT ABS(-32);

该函数支持使用BIGINT值。

  • ACOS(X)

返回X的反余弦, 即余弦是X的值。若X不在-1到1的范围之内，则返回NULL。

1
2
3
mysql> SELECT ACOS(1);
mysql> SELECT ACOS(1.0001);
mysql> SELECT ACOS(0);
  • ATAN(Y,X) , ATAN2(Y,X)

返回两个变量X及Y的反正切。

1
2
mysql> SELECT ATAN(-2,2);
mysql> SELECT ATAN2(PI(),0);
  • CEILING(X),CEIL(X)

返回不小于X的最小整数值。

1
2
mysql> SELECT CEILING(1.23);
mysql> SELECT CEIL(-1.23);
  • RAND()

RAND(N)返回一个范围在0到1之间（即范围为 0 ≤ v ≤1.0）的随机浮点值v。若已指定一个整数参数N，则该参数将被用作种子值，用来产生重复序列。

1
2
3
4
mysql> SELECT RAND();
mysql> SELECT RAND(20);
mysql> SELECT RAND(20);
mysql> SELECT RAND();

若要在i ≤ R ≤ j这个范围得到一个随机整数R，需要用到表达式FLOOR(i + RAND() * (j – i + 1))。例如， 若要在7到 12 的范围（包括7和12）内得到一个随机整数, 可使用以下语句：

1
SELECT FLOOR(7 + (RAND() * 6));

在ORDER BY语句中，不能使用一个带有RAND()值的列，原因是 ORDER BY会计算列中的重复数值。但是也可按照如下的随机顺序检索数据行：

1
mysql> SELECT * FROM tbl_name ORDER BY RAND();

ORDER BY RAND()同LIMIT的结合可以有效的从一组列中选择随机样本：

1
2
mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d
 -> ORDER BY RAND() LIMIT 1000;

注意：在WHERE语句中，WHERE每执行一次，RAND()就会被再执行一次。

RAND()的作用不是作为一个精确的随机发生器，而是一种用来发生在同样的MySQL版本的平台之间的可移动ad hoc随机数的快速方式。

  • ROUND(X),ROUND(X,D)

返回与参数X最接近的整数。在有两个参数的情况下，返回保留到小数点后D位的X，而第D位的保留方式为四舍五入。若要返回保留X值小数点左边的D位，可将D设为负值。

1
2
3
4
5
6
mysql> SELECT ROUND(-1.23);
mysql> SELECT ROUND(-1.58);
mysql> SELECT ROUND(1.58);
mysql> SELECT ROUND(1.298, 1);
mysql> SELECT ROUND(1.298, 0);
mysql> SELECT ROUND(23.298, -1);

返回值的类型同第一个参数类型相同(假设它是一个整数、双精度数或小数)。这意味着对于一个整数参数,结果也是一个整数(无小数部分)。

ROUND()在以下情况下依赖于第一个参数的类型：

  • 对于准确值数字，ROUND()使用“四舍五入” 或“舍入成最接近的数” 的规则：对于一个分数部分为.5或大于 .5的值，正数则上舍入到邻近的整数值，负数则下舍入临近的整数值。(换言之,其舍入的方向是数轴上远离零的方向）。对于一个分数部分小于.5的值，正数则下舍入下一个整数值，负数则下舍入邻近的整数值，而正数则上舍入邻近的整数值。
    • 对于近似值数字，其结果根据 C 库而定。在很多系统中，这意味着ROUND()的使用遵循“舍入成最接近的偶数”的规则： 一个带有任何小数部分的值会被舍入成最接近的偶数。 以下举例说明舍入法对于精确值和近似值的不同之处：
1
· mysql> SELECT ROUND(2.5), ROUND(25E-1);

五、日期和时间函数

  • ADDDATE(date,INTERVAL expr type),ADDDATE(expr,days)

当被第二个参数INTERVAL被设置后，ADDDATE()就是等价于DATE_ADD()。

1
2
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);

若days参数只是整数值，则 MySQL 5.1将其作为天数值添加至expr。

1
mysql> SELECT ADDDATE('1998-01-02', 31);
  • ADDTIME(expr,expr2) ADDTIME()将expr2添加至expr然后再返回结果。expr是一个时间或日期表达式，而expr2是一个时间表达式。
1
2
3
· mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999',
·  -> '1 1:1:1.000002');
· mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
  • CURDATE()`

将当前日期按照’YYYY-MM-DD’或YYYYMMDD格式返回，具体格式根据函数用在字符串或是数字语境中而定。

1
2
mysql> SELECT CURDATE();
mysql> SELECT CURDATE() + 0;
  • CURRENT_DATE,CURRENT_DATE()

CURRENT_DATE等价于CURRENT_DATE()，CURTIME()将当前时间以’HH:MM:SS’或HHMMSS的格式返回， 具体格式根据函数用在字符串或是数字语境中而定。

1
2
mysql> SELECT CURTIME();
mysql> SELECT CURTIME() + 0;
  • DATEDIFF(expr,expr2)

DATEDIFF()返回起始时间expr和结束时间expr2之间的天数。Expr和expr2为日期或date-and-time 表达式。计算中只用到这些值的日期部分。

1
2
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
  • DATE_ADD(date,INTERVAL expr type),DATE_SUB(date,INTERVAL expr type)
  • HOUR(time)

返回time对应的小时数。对于日时值的返回值范围是从0 到23。

1
mysql> SELECT HOUR('10:05:03');

然而，TIME 值的范围实际上非常大, 所以HOUR可以返回大于23的值。如：

1
mysql> SELECT HOUR('272:59:59');
  • LAST_DAY(date)

获取一个日期或日期时间值，返回该月最后一天对应的值。若参数无效，则返回NULL。

1
2
3
4
mysql> SELECT LAST_DAY('2003-02-05');
mysql> SELECT LAST_DAY('2004-02-05');
mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
mysql> SELECT LAST_DAY('2003-03-32');
  • MAKEDATE(year,dayofyear)

给出年份值和一年中的天数，最后返回一个日期。dayofyear必须大于0，否则结果为NULL。

1
2
3
mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);
mysql> SELECT MAKEDATE(2001,0);
  • MAKETIME(hour,minute,second)

返回由hour、 minute和second参数计算得出的时间值。

1
mysql> SELECT MAKETIME(12,15,30);

NOW()

返回当前日期和时间值，其格式为’YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS ， 具体格式取决于该函数所用处于的字符串或数字类型语境中。

1
2
mysql> SELECT NOW();
mysql> SELECT NOW() + 0;

在一个存储程序或触发器内, NOW()返回一个常数时间，该常数指示了该程序或触发语句开始执行的时间。这同SYSDATE()的运行有所不同。