浅谈 MySQL 可支持的运算符和函数

Question

同 PHP 等程序设计语言一样，MySQL 也有许多 DBMS 都有自己的数据类型（即列类型），运算符、语句结构、关键字以及函数。其中广泛用于 SELECT 和 WHERE 子句中的函数，对程序员简化查询语句的构造，提高查询计算的效率，起着非常重要的作用。这些函数将一些复杂的查询计算操作用函数封装起来，由 MySQL 自己执行计算，仅将结果返回给 PHP。

这些函数，涵盖了数学运算，字符串处理，逻辑运算，日期时间运算，比较运算，语句流程控制，分组汇总，排序等许多方面的处理，下面介绍常用到的一些函数。

为简便起见，用 -> 表示执行查询后 MySQL 返回的结果。

强制运算

小括号 ()。使用它们来强制在一个表达式的计算顺序。

mysql> select 1+2*3;
 -> 7
mysql> select (1+2)*3;
 -> 9

算术运算

+ 加法

mysql> select 3+5;
 -> 8

以此类推可以有以下的计算法：

• - 减法
• * 乘法
• / 除法

被零除产生一个 NULL 结果：

mysql> select 102/(1-1);
 -> NULL

逻辑运算

所有的逻辑函数返回1（TRUE）或0（FALSE）。NULL 被认为是假值。

• NOT （!）逻辑非
• OR （||）逻辑或
• AND（&&）逻辑与

比较运算符

比较操作得出值1（TRUE）、0（FALSE）或NULL等结果。这些函数工作运用在数字和字符串上。

• = 等于
• <>不等于
• != 不等于
• <= 小于或等于
• < 小于
• >= 大于或等于
• > 大于

mysql> select 2 > 2;
 -> 0

IS NULL 是否为空
IS NOT NULL 是否不为空

mysql> select 1 IS NULL, 0 IS NULL, NULL IS NULL:
 -> 00 1

expr BETWEEN min AND max

如果 expr 对大于或等于 min 且 expr 是小于或等于 max，BETWEEN 返回 1，否则它返回 0。

mysql> select 1 BETWEEN 2 AND 3;
 -> 0
mysql> select 2 BETWEEN 2 AND '3';
 -> 1

expr IN (value,...)

如果 expr 是在IN表中的任何值，返回1，否则返回0。如果所有的值是常数，那么所有的值根据expr类型被计算和排序，然后项目的搜索是用二进制的搜索完成。这意味着如果IN值表全部由常数组成，IN 是很快的。如果 expr 是一个大小写敏感的字符串表达式，字符串比较以大小写敏感方式执行。

mysql> select 2 IN(0,3,5,'wefwf');
 -> 0
mysql> select 'wefwf' IN (0,3,5,'wefwf');
 -> 1

expr NOT IN(value,...)

与NOT (expr IN (value,...))相同。

ISNULL(expr)

如果expr是NULL，ISNULL()返回1，否则它返回0。

mysql> select ISNULL(1+1);
 -> 0
mysql> select ISNULL(1/0);
 -> 1

注意，使用=的NULL的值比较总为假！

字符串比较函数

通常，如果在字符串比较中的任何表达式是区分大小写的，比较以大小写敏感的方式执行。

expr LIKE pat tern[ESCAPE 'escape-char']

将expr与模式字符串pattern进行模式匹配。返回1（TRUE）或0（FALSE）。用LIKE，你可以在模式中使用下列2个

• % 匹配任何数目的字符，甚至零个字符
• _ 精确匹配一个字符

mysql> select 'David!' LIKE 'David_';
 -> 1
mysql> select 'David!' LIKE '%D%v%';
 -> 1

为了测试一个通配符的文字实例，用转义字符的加在字符前面。如果你不指定ESCAPE字符，假定为“\”：

• \% 匹配%字符
• \_ 匹配_字符

mysql> select 'David!' LIKE 'David\_';
 -> 0
mysql> select 'David_' LIKE 'David\_';
 -> 1

为了指定一个不同的转义字符，使用ESCAPE子句：

mysql> select 'David_' LIKE 'David|_' ESCAPE '|';
 -> 1

LIKE允许用在数字的表达式上！（这是MySQL对ANSI SQL LIKE的一个扩充。)

mysql> select 10 LIKE '1%';
 -> 1

expr NOT LIKE pattern [ESCAPE 'escape-char']

与NOT (expr LIKE pattern[ESCAPE'escape-char'])相同。

控制流函数

IF(expr1,expr2,expr3)

如果expr1是TRUE(expr1<>0且expr1<>NULL)，那么IF()返回expr2，否则它返回expr3。IF()返回一个数字或字符串值，取决于它被使用的上下文。

mysql> select IF(1>2,2,3);
 -> 3
mysql> select IF(1<2,'yes','no');
 ->'yes'
mysql> selectIF(strcmp('test','test1'),'yes','no');
 ->'no'

expr1作为整数值被计算，它意味着如果你正在测试浮点或字符串值，应该使用一个比较操作来做。

mysql> select IF(0.1,1,0);
 -> 0
mysql> select IF(0.1<>0,1,0);
 -> 1

在上面的第一种情况中，IF(0.1)返回0，因为0.1被变换到整数值, 导致测试IF(0)。这可能不是你期望的。在第二种情况中，比较测试原来的浮点值看它是否是非零，比较的结果被用作一个整数。

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

第一个版本返回result，其中value=compare-value。第二个版本中如果第一个条件为真，返回result。如果没有匹配的result值，那么结果在ELSE后的result被返回。如果没有ELSE部分，那么NULL被返回。

mysql> SELECT CASE 1 WHEN 1 THEN "one"WHEN 2 THEN "two" ELSE "more" END;
 ->"one"
mysql> SELECT CASE WHEN 1>0 THEN"true" ELSE "false" END;
 ->"true"
mysql> SELECT CASE BINARY "B" when"a" then 1 when "b" then 2 END;
 ->NULL

数学函数

所有的数学函数在一个出错的情况下返回NULL。

• ABS(X)：返回X的绝对值。
• SIGN(X)：返回参数的符号，为-1、0或1，取决于X是否是负数、零或正数。
• MOD(N,M)
• %：模 (类似C中的%操作符)。返回N被M除的余数。

mysql> select MOD(29,9);
 -> 2

FLOOR(X)

对X向下取整。

mysql> select FLOOR(1.23);
 -> 1
mysql> select FLOOR(-1.23);
 ->-2

CEILING(X)

对X向上取整。

mysql> select CEILING(1.23);
 -> 2
mysql> select CEILING(-1.23);
 ->-1

ROUND(X)

返回参数X的四舍五入的一个整数。

mysql> select ROUND(-1.23);
 ->-1
mysql> select ROUND(-1.58);
 ->-2
mysql> select ROUND(1.58);
 -> 2

ROUND(X,D)

返回参数X的四舍五入的有D位小数的一个数字。如果D为0，结果将没有小数点或小数部分。

mysql> select ROUND(1.298, 1);
 ->1.3
mysql> select ROUND(1.298, 0);
 -> 1

RAND(N)

返回在范围0到1.0内的随机浮点值。如果一个整数参数N被指定，它被用作种子值。

mysql> select RAND();
 ->0.5925
mysql> select RAND(20);
 ->0.1811
mysql> select RAND(20);
 ->0.1811
mysql> select RAND();
 ->0.2079
mysql> select RAND();
 ->0.7888

你不能在一个ORDER BY子句用RAND()值使用列，因为ORDER BY将重复计算列多次。然而在MySQL3.23中，你可以做： SELECT * FROMtable_name ORDER BY RAND()，这是有利于得到一个来自SELECT * FROM table1,table2 WHERE a=b ANDc的集合的随机样本。注意在一个WHERE子句里的一个RAND()将在每次WHERE被执行时重新评估。

LEAST(X,Y,...)

有2和2个以上的参数，返回最小(最小值)的参数。参数使用下列规则进行比较：

• 如果返回值被使用在一个INT上下文，或所有的参数都是整数值，他们作为整数比较。
• 如果返回值被使用在一个FLOAT上下文，或所有的参数是实数值，他们作为实数比较。
• 如果任何参数是一个大小敏感的字符串，参数作为大小写敏感的字符串被比较。

在其他的情况下，参数作为大小写无关的字符串被比较。

mysql> select LEAST(2,0);
 -> 0
mysql> select LEAST(34.0,3.0,5.0,767.0);
 ->3.0
mysql> selectLEAST("B","A","C");
 ->"A"

在MySQL 3.22.5以前的版本，你可以使用MIN()而不是LEAST。

GREATEST(X,Y,...)

返回最大(最大值)的参数。参数使用与LEAST一样的规则进行比较。

mysql> select GREATEST(2,0);
 -> 2
mysql> select GREATEST(34.0,3.0,5.0,767.0);
 ->767.0
mysql> selectGREATEST("B","A","C");
 ->"C"

在MySQL在 3.22.5以前的版本, 你能使用MAX()而不是GREATEST.

字符串函数

对于针对字符串位置的操作，第一个位置被标记为1。

MySQL根据上下文自动变换数字为字符串，并且反过来也如此：

mysql> SELECT 1+"1";
 -> 2

CONCAT(str1,str2,...)

返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。

mysql> select CONCAT('My', 'S', 'QL');
 ->'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
 ->NULL
mysql> select CONCAT(14.3);
 ->'14.3'
mysql> SELECT CONCAT(2,' test');
 ->'2 test'

LEFT(str,len)

返回字符串str的最左面len个字符。

mysql> select LEFT('foobarbar', 5);
 ->'fooba'

RIGHT(str,len)

返回字符串str的最右面len个字符。

mysql> select RIGHT('foobarbar', 4);
 ->'rbar'

SUBSTRING(str,pos,len)
MID(str,pos,len)

从字符串str返回一个len个字符的子串，从位置pos开始。

mysql> select SUBSTRING('123456789123456',5,6);
 ->'567891'

LTRIM(str)

返回删除了其前置空格的字符串str。

mysql> select LTRIM(' barbar');
 -> 'barbar'

RTRIM(str)

返回删除了其尾部空格的字符串str。

mysql> select RTRIM('barbar ');
 ->'barbar'

TRIM(str)

返回去处了首尾空格的字符串str。

mysql> select TRIM(' bar ');
 ->'bar'

SPACE(N)

返回由N个空格字符组成的一个字符串。

mysql> select SPACE(6);
 ->' '

REPEAT(str,count)

返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。

mysql> select REPEAT('MySQL', 3);
 ->'MySQLMySQLMySQL'

LCASE(str)
LOWER(str)

返回字符串str，根据当前字符集映射(缺省是ISO-8859-1Latin1)把所有的字符改变成小写。

mysql> select LCASE('ABCDEFG');
 ->'abcdefg'

UCASE(str)
UPPER(str)

返回字符串str，根据当前字符集映射(缺省是ISO-8859-1Latin1)把所有的字符改变成大写。

mysql> select UCASE(' abcdefg ');
 -> 'ABCDEFG '

LOAD_FILE(file_name)

读入文件并且作为一个字符串返回文件内容。文件必须在服务器上，你必须指定到文件的完整路径名，而且你必须有file权限。文件必须所有内容都是可读的并且小于max_allowed_packet。如果文件不存在或由于上面原因之一不能被读出，函数返回NULL。

mysql> UPDATE table_name SETblob_column=LOAD_FILE("/tmp/picture") WHERE id=1;

日期和时间函数

NOW()
SYSDATE()
CURRENT_TIMESTAMP
以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回当前的日期和时间，取决于函数是在一个字符串还是在数字的上下文被使用。

mysql> select NOW();
 ->'1997-12-15 23:50:26'
mysql> select NOW() + 0;
 ->19971215235026

CURDATE()
CURRENT_DATE
以'YYYY-MM-DD'或YYYYMMDD格式返回今天日期值，取决于函数是在一个字符串还是数字上下文被使用。

mysql> select CURDATE();
 -> '1997-12-15'
mysql> select CURDATE() + 0;
 ->19971215

CURTIME()
CURRENT_TIME
以'HH:MM:SS'或HHMMSS格式返回当前时间值，取决于函数是在一个字符串还是在数字的上下文被使用。

mysql> select CURTIME();
 ->'23:50:26'
mysql> select CURTIME() + 0;
 ->235026
 ->10

分组计算函数

这些函数，常常是与GROUP BY子句一起使用的函数，作用是对聚合在组内的行，进行计算。
如果在不包含GROUP BY子句的一个语句中使用聚合函数，它等价于聚合所有行。

COUNT(expr)

返回由一个SELECT语句检索出来的行的非NULL值的数目。

mysql> select student.student_name,COUNT(*)
 fromstudent,course
 where student.student_id=course.student_id
 GROUP BY student_name;

COUNT(*)在它返回的检索出来的行数目上有些不同，不管他们是否包含NULL值。如果SELECT从一个表检索，或没有检索出其他列并且没有WHERE子句，COUNT(*)被优化以便快速地返回。例如：

mysql> select COUNT(*) from student;
COUNT(DISTINCT expr,[expr...])

返回一个无重复值的数目。

mysql> selectCOUNT(DISTINCT results) from student;

在MySQL中，你可以通过给出一个表达式列表以得到不同的表达式组合的数目。

AVG(expr)

返回expr的平均值。

mysql> select student_name, AVG(test_score)
 fromstudent
 GROUP BY student_name;

MIN(expr)
MAX(expr)

返回expr的最小或最大值。MIN()和MAX()可以有一个字符串参数；在这种的情况下，他们返回最小或最大的字符串值。

mysql> select student_name, MIN(test_score),MAX(test_score)
 fromstudent
 GROUP BY student_name;

SUM(expr)

返回expr的和。注意，如果返回的集合没有行，它返回NULL！