MySQL Reference Manual for version 4.1.0-alpha.
--------------------------------------------------------------------------------
6.3 用于 SELECT 和 WHERE 子句的函数
一个 SQL 语句中的 select_expression 或 where_definition 可由任何使用了下面所描述函数的表达式组成。
包含 NULL 的表达式总是得出一个 NULL 值结果,除非表达式中的操作和函数在文档中有另外的说明。
注意:在一个函数名和跟随它的括号之间必须不存在空格。这有助于 MySQL 语法分析程序区分函数调用和对恰巧与函数同名表或列的引用。然而,参数左右两边的空格却是允许的。
你可以强制 MySQL 接受函数名后存在空格的形式,这需要通过以 --ansi 选项启动 mysqld,或在 mysql_connect() 中使用 CLIENT_IGNORE_SPACE,但是,在这种情况下,所有的函数名均将成为保留字。查看章节 1.8.2 以 ANSI 模式运行 MySQL.
为了简洁,从 mysql 程序输出的例子以缩写的形式显示。因此:
mysql> SELECT MOD(29,9);
1 rows in set (0.00 sec)
+-----------+
| mod(29,9) |
+-----------+
| 2 |
+-----------+
将被显示为这样:
mysql> SELECT MOD(29,9);
-> 2
6.3.1 无类型的特殊运算符和函数
6.3.1.1 圆括号
( ... )
括号,使用它来强制一个表达式的计算顺序。
mysql> SELECT 1+2*3;
-> 7
mysql> SELECT (1+2)*3;
-> 9
6.3.1.2 比较运算符
比较运算符的结果是 1 (TRUE)、0 (FALSE) 或 NULL。这些函数可工作于数字和字符串上。根据需要,字符串将会自动地被转换到数字,以及数字被转换成字符串(比如在 Perl 中)。
MySQL 使用下列规则进行比较:
如果一个或两个参数是 NULL,比较的结果是 NULL,除了 <=> 运算符。
如果在一个比较操作中两个参数均是字符串,他们将作为字符串被比较。
如果两个参数均是整数,他们作为整数被比较。
十六进制值如果不与一个数字进行比较,那么它将当作一个二进制字符串。
如果参数之一是一个 TIMESTAMP 或 DATETIME 列,而另一参数是一个常数,在比较执行之前,这个常数被转换为一个时间戳。这样做是为了对 ODBC 更友好。
在所有其它情况下,参数作为浮点(real)数字被比较。
缺省地,字符串使用当前字符集以忽略字母大小写的方式进行比较(缺省的字符集为 ISO-8859-1 Latin1,它对英语处理得很出色)。
下面的例子演示了对于比较操作字符串到数字的转换:
mysql> SELECT 1 > '6x';
-> 0
mysql> SELECT 7 > '6x';
-> 1
mysql> SELECT 0 > 'x6';
-> 0
mysql> SELECT 0 = 'x6';
-> 1
=
等于:
mysql> SELECT 1 = 0;
-> 0
mysql> SELECT '0' = 0;
-> 1
mysql> SELECT '0.0' = 0;
-> 1
mysql> SELECT '0.01' = 0;
-> 0
mysql> SELECT '.01' = 0.01;
-> 1
<>
!=
不等于:
mysql> SELECT '.01' <> '0.01';
-> 1
mysql> SELECT .01 <> '0.01';
-> 0
mysql> SELECT 'zapp' <> 'zappp';
-> 1
<=
小于或等于:
mysql> SELECT 0.1 <= 2;
-> 1
<
小于:
mysql> SELECT 2 < 2;
-> 0
>=
大于或等于:
mysql> SELECT 2 >= 2;
-> 1
>
大于:
mysql> SELECT 2 > 2;
-> 0
<=>
NULL 值安全等于:
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1 1 0
IS NULL
IS NOT NULL
测试一个值是或不是 NULL:
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
-> 0 0 1
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
-> 1 1 0
为了能够与其它程序更好的工作,在使用 IS NULL 是 MySQL 支持下列额外选择性:
通过它,你可以找到最后插入的记录行:
SELECT * FROM tbl_name WHERE auto_col IS NULL
这个操作可以通过设置 SQL_AUTO_IS_NULL=0 来禁止。查看章节 5.5.6 SET 句法.
对于 NOT NULL 的 DATE 和 DATETIME 列,可以通过使用下列语句找到特殊的值 0000-00-00:
SELECT * FROM tbl_name WHERE date_column IS NULL
这需要通过某些 ODBC 应用程序才能工作(因为 ODBC 不支持一个 0000-00-00 日期)
expr BETWEEN min AND max
如果 expr 大于或等于 min ,并且 expr 小于或等于 max,BETWEEN 返回 1,否则返回 0。它等价于表达式 (min <= expr AND expr <= max) ,只要所有的参数均是相同的类型。 否则类型会依照上面的规则发生转换,但是应用于所有三个参数。注意,在 MySQL 4.0.5 之前,参数被转换到 expr 的类型。
mysql> SELECT 1 BETWEEN 2 AND 3;
-> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
-> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
-> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
-> 0
expr NOT BETWEEN min AND max
等同于 NOT (expr BETWEEN min AND max)。
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
从 MySQL 4.1 开始(符合 SQL-99 标准),如果左手边的表达式是 NULL,或者在列表中没有发现相匹配的值并且列表中的一个表达式是 NULL,IN 均返回 NULL。
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 值使用 = 进行比较总是为 false !
COALESCE(list)
返回列表中第一个非 NULL 的元素:
mysql> SELECT COALESCE(NULL,1);
-> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
-> NULL
INTERVAL(N,N1,N2,N3,...)
Returns 如果 N < N1 返回 0,如果 N < N2 返回 1,等等。所有的参数均被当作整数。为了函数能正确地工作,它要求 N1 < N2 < N3 < ... < Nn。这是因为它使用的是一个二进制的搜索(非常地快):
mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
-> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
-> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
-> 0
如果以任何一个标准运算符(=, <>..., 但除了 LIKE)对一个忽略大小写的字符串进行比较,尾部的空白空间(空格、TAB 和换行)均被忽略。
mysql> SELECT "a" ="A \n";
-> 1
6.3.1.3 逻辑运算符
在 SQL 中,所有的逻辑运算符返回的值均为 TRUE、FALSE 或 NULL (未知)。在 MySQL 中,它们由 1 (TRUE)、0 (FALSE) 和 NULL 实现。这些大部分在不同的 SQL 数据库间是相同的,然而某些可能会以一个非零值返回 TRUE。
NOT
!
逻辑非。如果操作数为 0,返回 1;如果操作数为非零,返回 0;如果操作数为 NOT NULL,返回 NULL。
mysql> SELECT NOT 10;
-> 0
mysql> SELECT NOT 0;
-> 1
mysql> SELECT NOT NULL;
-> NULL
mysql> SELECT ! (1+1);
-> 0
mysql> SELECT ! 1+1;
-> 1
最后一个例子返回 1,因为表达式是与 (!1)+1 一样被计算的。
AND
&&
逻辑与。如果所有的操作数都是非零或非 NULL 的,返回 1;如果有一个或多个操作数为 0 ,则返回 0,只要操作数中有 NULL 返回值就为 NULL。
mysql> SELECT 1 && 1;
-> 1
mysql> SELECT 1 && 0;
-> 0
mysql> SELECT 1 && NULL;
-> NULL
mysql> SELECT 0 && NULL;
-> 0
mysql> SELECT NULL && 0;
-> 0
请注意,在 MySQL 4.0.5 以前版本中,当遇到一个 NULL 时就停止计算,而不是继续进程检查可能存在的0。这就意味着,在这些版本中,SELECT (NULL AND 0) 返回 NULL,而不是 0。在 4.0.5 中,代码已被重新设计了,已便于在任何仍然使用优化情况下,返回值总是能如 ANSI 所规定的那样。
OR
||
逻辑或。如果任何一个操作数是非零的,返回值为 1,如果任一操作数为 NULL,返回值为 NULL,否则返回 0。
mysql> SELECT 1 || 1;
-> 1
mysql> SELECT 1 || 0;
-> 1
mysql> SELECT 0 || 0;
-> 0
mysql> SELECT 0 || NULL;
-> NULL
mysql> SELECT 1 || NULL;
-> 1
XOR
逻辑异或。如果任一操作数为 NULL,返回值为 NULL。对于非 NULL 的操作数,如果有奇数个非零的操作数,结果返回为 1,否则返回 0。
mysql> SELECT 1 XOR 1;
-> 0
mysql> SELECT 1 XOR 0;
-> 1
mysql> SELECT 1 XOR NULL;
-> NULL
mysql> SELECT 1 XOR 1 XOR 1;
-> 1
a XOR b 算术相等于 (a AND (NOT b)) OR ((NOT a) and b)。 XOR 在 MySQL 4.0.2 中被添加。
6.3.1.4 控制流函数
IFNULL(expr1,expr2)
如果 expr1 为非 NULL 的,IFNULL() 返回 expr1,否则返回 expr2。IFNULL() 返回一个数字或字符串值,这取决于它被使用的语境:
mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'
在 MySQL 4.0.6 和更新版本中,IFNULL(expr1,expr2) 的默认返回值以 STRING、REAL 或 INTEGER 顺序更加'general'了两个表达式。当你基于一个表达式创建一个表或在一个临时表中 MySQL 不得不存储一个从 IFNULL() 返回的值时,这个与较早 MySQL 版本的不同将更加值得注意。
CREATE TABLE foo SELECT IFNULL(1,"test") as test;
在 MySQL 4.0.6 中,列 'test' 的类型为 CHAR(4),然而在较早的版本中,你得到的却是 BIGINT。
NULLIF(expr1,expr2)
如果 expr1 = expr2 为真,返回 NULL,否则返回 expr1。它等同于 CASE WHEN x = y THEN NULL ELSE x END:
mysql> SELECT NULLIF(1,1);
-> NULL
mysql> SELECT NULLIF(1,2);
-> 1
注意,如果参数不相等,在 MySQL 中,expr1 被求值两次。
IF(expr1,expr2,expr3)
如果 expr1 为真(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> SELECT IF(STRCMP('test','test1'),'no','yes');
-> 'no'
如果 expr2 或 expr3 明确地为 NULL,那么函数 IF() 的返回值类型为非 NULL 列的类型。(这在选择在 MySQL 4.0.3 中新加入)。 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) 的测试结果。这可能不是你所期望的。在第二种情况下,比较测试原浮点数是否为一个非零值。比较的结果被作为整数使用。 缺省的 IF() 返回值类型 (当结果存储在临时表中时,这是非常重要的) 在 MySQL 3.23 中按下列方式确定: 表达式 返回值
表达式(expr2)或表达式(expr3)返回值为字符串 字符串
表达式(expr2)或表达式(expr3)返回值为浮点型值 浮点型
表达式(expr2)或表达式(expr3)返回值为整型 整型
如果表达式(expr2)和表达式(expr3)均是字符串,同时两个字符串均是忽略字母大小写的,那么返回值也是忽略字母大小写的(从 MySQL 3.23.51 开始)。
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 时返回 result。第二个形式当第一个为真值的 condition 出现时,返回该条件的结果。如果没有匹配的结果值,那么 ELSE 后的结果将被返回。如果没有 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
返回值的类型 (INTEGER、DOUBLE 或 STRING) 与第一个返回值(第一个 THEN 后的表达式)的类型相同。
6.3.2 字符串函数
如果返回结果的长度超过服务器参数 max_allowed_packet 的大小,字符串值函数将返回 NULL。查看章节 5.5.2 调节服务器参数。
对于操作字符串位置的函数,第一个位置被标记为 1。
ASCII(str)
返回字符串 str 最左边的那个字符的 ASCII 码值。如果 str 是一个空字符串,那么返回值为 0。如果 str 是一个 NULL,返回值也是 NULL:
mysql> SELECT ASCII('2');
-> 50
mysql> SELECT ASCII(2);
-> 50
mysql> SELECT ASCII('dx');
-> 100
也可参看 ORD() 函数。
ORD(str)
如果字符串 str 的最左边的字符是一个多字节的字符,根据多字节字符的成分字符的 ASCII 码值通过下面的公式计算返回那个的编码:((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]。如果最左边的字符不是一个多字节字符,返回值与 ASCII() 函数相同:
mysql> SELECT ORD('2');
-> 50
CONV(N,from_base,to_base)
在不同的数字基数之间转换数字。将数字 N 从 from_base 转换到 to_base,并以字符串表示形式返回。如果任何一个参数为 NULL,那么返回值也为 NULL。参数 N 被解释为是一个整数,但是也可以被指定为一个整数或一个字符串。最小基为 2,最大基为 36。如果 to_base 是一个负值,N 将被看作为是一个有符号数字。否则,N 被视为是无符号的。CONV 以 64 位精度工作:
mysql> SELECT CONV("a",16,2);
-> '1010'
mysql> SELECT CONV("6E",18,8);
-> '172'
mysql> SELECT CONV(-17,10,-18);
-> '-H'
mysql> SELECT CONV(10+"10"+'10'+0xa,10,10);
-> '40'
BIN(N)
返回 N 的字符串表示的二进制值形式,在这里,N 长长的(BIGINT)数字。这个函数等价于 CONV(N,10,2)。如果 N 是一个 NULL,返回值也是 NULL:
mysql> SELECT BIN(12);
-> '1100'
OCT(N)
返回 N 的字符串表示的八进制值形式,在这里,N 是一个长长的数字。这个函数等价于 CONV(N,10,8)。如果 N 是一个 NULL,返回值也是 NULL :
mysql> SELECT OCT(12);
-> '14'
HEX(N_or_S)
如果 N_OR_S 是一个数字,返回 N 的字符串表示的十六进制值形式,这里 N 是一个长长的(BIGINT)数字。这个函数等价于 CONV(N,10,16)。 如果 N_OR_S 是一个字符串,N_OR_S 中的每个字符均被转换为 2 位十六进制数字,并以十六进制的字符串形式返回。这是 0xff 形式的字符串反转操作。
mysql> SELECT HEX(255);
-> 'FF'
mysql> SELECT HEX("abc");
-> 616263
mysql> SELECT 0x616263;
-> "abc"
CHAR(N,...)
CHAR() 以整数类型解释参数,返回这个整数所代表的 ASCII 码值给出的字符组成的字符串。NULL 值将被忽略:
mysql> SELECT CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
-> 'MMM'
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'
CONCAT_WS(separator, str1, str2,...)
CONCAT_WS() 支持 CONCAT 加上一个分隔符,它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间:
mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
-> 'First name,Last Name'
LENGTH(str)
OCTET_LENGTH(str)
CHAR_LENGTH(str)
CHARACTER_LENGTH(str)
返回字符串 str 的长度:
mysql> SELECT LENGTH('text');
-> 4
mysql> SELECT OCTET_LENGTH('text');
-> 4
注意,CHAR_LENGTH() 和 CHARACTER_LENGTH() 对于多字节字符只计数一次。
BIT_LENGTH(str)
返回字符串 str 的比特长度:
mysql> SELECT BIT_LENGTH('text');
-> 32
LOCATE(substr,str)
POSITION(substr IN str)
返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在,返回值为 0:
mysql> SELECT LOCATE('bar', 'foobarbar');
-> 4
mysql> SELECT LOCATE('xbar', 'foobar');
-> 0
这个函数是多字节安全的。在 MySQL 3.23 中,这个函数是字母大小写敏感的,当在 MySQL 4.0 中时,如有任一参数是一个二进制字符串,它才是字母大小写敏感的。
LOCATE(substr,str,pos)
返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0 :
mysql> SELECT LOCATE('bar', 'foobarbar',5);
-> 7
这个函数是多字节安全的。在 MySQL 3.23 中,这个函数是字母大小写敏感的,当在 MySQL 4.0 中时,如有任一参数是一个二进制字符串,它才是字母大小写敏感的。
INSTR(str,substr)
返回子串 substr 在字符串 str 中第一次出现的位置。这与有两个参数形式的 LOCATE() 相似,只是参数的位置被颠倒了:
mysql> SELECT INSTR('foobarbar', 'bar');
-> 4
mysql> SELECT INSTR('xbar', 'foobar');
-> 0
这个函数是多字节安全的。在 MySQL 3.23 中,这个函数是字母大小写敏感的,当在 MySQL 4.0 中时,如有任一参数是一个二进制字符串,它才是字母大小写敏感的。
LPAD(str,len,padstr)
用字符串 padstr 对 str 进行左边填补直至它的长度达到 len 个字符长度,然后返回 str。如果 str 的长度长于 len',那么它将被截除到 len 个字符。
mysql> SELECT LPAD('hi',4,'??');
-> '??hi'
RPAD(str,len,padstr)
用字符串 padstr 对 str 进行右边填补直至它的长度达到 len 个字符长度,然后返回 str。如果 str 的长度长于 len',那么它将被截除到 len 个字符。
mysql> SELECT RPAD('hi',5,'?');
-> 'hi???'
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)
SUBSTRING(str FROM pos FOR len)
MID(str,pos,len)
从字符串 str 的 pos 位置起返回 len 个字符的子串。使用 FROM 的变体形式是 ANSI SQL92 的句法:
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
这个函数是多字节安全的。
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
从字符串 str 的 pos 位置起返回子串:
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
这个函数是多字节安全的。
SUBSTRING_INDEX(str,delim,count)
返回字符串 str 中在第 count 个出现的分隔符 delim 之前的子串。如果 count 是一个正数,返回从最后的(从左边开始计数)分隔符到左边所有字符。如果 count 是负数,返回从最后的(从右边开始计数)分隔符到右边所有字符:
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
这个函数是多字节安全的。
LTRIM(str)
返回移除了领头的空格字符后的 str:
mysql> SELECT LTRIM(' barbar');
-> 'barbar'
RTRIM(str)
返回移除了尾部的空格字符后的 str:
mysql> SELECT RTRIM('barbar ');
-> 'barbar'
这个函数是多字节安全的。
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
移除字符串 str 中所有的 remstr 前缀或后缀,然后将其返回。如果没有任何 BOTH、LEADING 或 TRAILING 修饰符被给出,BOTH 被假定。如果 remstr 没有被指定,空格将被移除:
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
这个函数是多字节安全的。
SOUNDEX(str)
返回 str 的近音字。两个发音差不多的字符串应该有同样的近音字。一个标准的近音字为 4 个字符长,但是函数 SOUNDEX() 却返回一个任意长的字符串。你可以在结果上使用 SUBSTRING() 标准的近音字。提供的字符中所有的非数字字母的字符均被忽略。所有在 A-Z 范围之外的国际 alpha 字符被视为元音:
mysql> SELECT SOUNDEX('Hello');
-> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
-> 'Q36324'
SPACE(N)
返回有 N 空格字符组成的字符串:
mysql> SELECT SPACE(6);
-> ' '
REPLACE(str,from_str,to_str)
在字符串 str 中所有出现的字符串 from_str 均被 to_str替换,然后返回这个字符串:
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
这个函数是多字节安全的。
REPEAT(str,count)
返回一个由重复了 count 次的字符串 str 组成的字符串。如果 count <= 0,返回一个空字符串。如果 str 或 count 是 NULL,返回值也为 NULL:
mysql> SELECT REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'
REVERSE(str)
以颠倒的字符顺序返回字符串 str:
mysql> SELECT REVERSE('abc');
-> 'cba'
这个函数是多字节安全的。
INSERT(str,pos,len,newstr)
在字符串 str 中,将从 pos 位置开始,len 个字符长的子串替换为字符串 newstr ,然后将结果返回:
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
这个函数是多字节安全的。
ELT(N,str1,str2,str3,...)
如果 N = 1,返回 str1,如果N = 2,返回 str2,等等。如果 N 小于 1 或大于参数的数量,返回 NULL。ELT() FIELD() 反运算:
mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
-> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
-> 'foo'
FIELD(str,str1,str2,str3,...)
返回 str 在列表 str1, str2, str3, ... 中的索引。如果 str 没有发现,返回 0。FIELD() 是 ELT() 的反运算:
mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 2
mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 0
FIND_IN_SET(str,strlist)
Returns a value 如果字符串 str 在由 N 个子串组成的列表 strlist 中,返回一个 1 到 N 的值。一个字符串列表是由通过字符 “,” 分隔的多个子串组成。如果第一个参数是一个常数字符串,并且第二个参数是一个 SET 列类型,FIND_IN_SET() 函数将被优化为使用位运算!如果 str 在不 strlist 中或者如果 strlist 是一个空串,返回值为 0。如果任何一个参数为 NULL,返回值也是 NULL。如果第一个参数包含一个 “,”,这个函数将完全不能工作:
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
MAKE_SET(bits,str1,str2,...)
返回一个集合 (包含由字符 “,” 分隔的多个子串组成的一个字符串),它由在 bits 集合中有相应的比特位的字符串组成。str1 对应于比特位 0,str2 对应比特位 1,等等。在 str1, str2, ... 中的 NULL 串不允许被添加到结果中:
mysql> SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
-> ''
EXPORT_SET(bits,on,off,[separator,[number_of_bits]])
返回一个字符串,对于在 'bits' 中的每个比特字位,你将得到一个 'on' 字符,而如果比特字位是一个清零比特位(reset bit)你将得到一个 'off' 字符串。每个字符串均被“分隔符”(缺省为“,”)分隔,并且只有 'number_of_bits'(缺省为 64) 个 'bits' 被使用:
mysql> SELECT EXPORT_SET(5,'Y','N',',',4)
-> Y,N,Y,N
示例(译者注):
mysql> select EXPORT_SET(1,'1','0','',5);
-> 10000
# 最左边第一位为 2 的 0 次幂
mysql> select EXPORT_SET(2,'1','0','',5);
-> 01000
# 最左边第一位为 2 的 0 次幂,第二位为 2 的 1 次幂
mysql> select EXPORT_SET(4,'1','0','',5);
-> 00100
# 最左边第一位为 2 的 0 次幂,第二位为 2 的 1 次幂,第三位为 2 的 2 次幂
mysql> select EXPORT_SET(15,'1','0','',5);
-> 11110
# 最左边第一位为 2 的 1 次幂,第四位为 2 的 3 次幂
mysql> select EXPORT_SET(16,'1','0','',5);
-> 00001
# 最左边第一位为 2 的 1 次幂,第五位为 2 的 4 次幂
# 以上结果在 MySQL 4.0.12 中测试通过
示例结束(译者注)
LCASE(str)
LOWER(str)
依照当前字符集设置映射(缺省为 ISO-8859-1 Latin1),将字符串 str 中的所有字符改变为小写,然后返回该值:
mysql> SELECT LCASE('QUADRATICALLY');
-> 'quadratically'
这个函数是多字节安全的。
UCASE(str)
UPPER(str)
依照当前字符集设置映射(缺省为 ISO-8859-1 Latin1),将字符串 str 中的所有字符改变为大写,然后返回该值:
mysql> SELECT UCASE('Hej');
-> 'HEJ'
这个函数是多字节安全的。
LOAD_FILE(file_name)
读入文件,并将文件内容作为一个字符串返回。这个文件必须在服务器上,必须指定文件完整的路径名,并且你必须有 FILE 权限。文件必须完全可读,并且小于 max_allowed_packet。 如果该文件不存在,或因为上面的任一原因而不能被读出,函数返回 NULL:
mysql> UPDATE tbl_name
SET blob_column=LOAD_FILE("/tmp/picture")
WHERE id=1;
如果你没有使用 MySQL 3.23,你不得不在你的应用程序中读取文件,以文件的信息创建一个 INSERT 语句,来更新数据库。如果你使用 MySQL++ 库,下面有一个示例,详细请查看 http://www.mysql.com/documentation/mysql++/mysql++-examples.html。
QUOTE(str)
引用一个字符串,并返回一个结果,该结果可作为一个适当转义过的数据值在一个 SQL 语句中使用。字符串被单引号包围着返回,并且在该字符串中每个单引号(“'”)、反斜线符号(“\”)、ASCII NUL 和 Control-Z 出现的地方,在该字符之前均被加上了一个反斜线。如果参数是 NULL,那么结果值是一个没有单引号包围的单词 “NULL”。 QUOTE 函数在 MySQL 4.0.3 中被加入。
mysql> SELECT QUOTE("Don't");
-> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
-> NULL
6.3.2.1 字符串比较函数
在必要的时候,MySQL 会自动地将数字转换到字符串,反之亦然:
mysql> SELECT 1+"1";
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test'
如果你希望明确地将一个数字转换为字符串,将它参数传递到 CONCAT() 中。
如果将一个二进制字符串作为参数传递给一个字符串函数,结果返回也是一个二进制字符串。一个数字被转换到字符串,该字符串被视为是一个二进制字符串。这仅仅会影响结果。
通常,只要字符串比较中的任何一个表达式是区分大小写的,比较就会以字母大小写敏感方式执行。
expr LIKE pat [ESCAPE 'escape-char']
使用 SQL 的简单的正规表达式进行比较的模式匹配。返回 1 (TRUE) 或 0 (FALSE)。可以在模式中使用下面所示的两个通配符字符与 LIKE 配合: 字符 含义
% 匹配任意多个字符,甚至是零个字符
_ 严格地匹配一个字符
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
下面两个语句表明,字符串比较是忽略大小写的,除非任一操作数是一个二进制字符串:
mysql> SELECT 'abc' LIKE 'ABC';
-> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
-> 0
LIKE 允许用在一个数字表达式上。(这是 MySQL 对 ANSI SQL LIKE 的扩展。)
mysql> SELECT 10 LIKE '1%';
-> 1
注意:因为 MySQL 在字符串中使用的是 C 的转义句法(例如 “\n”),所以在 LIKE 字符串中使用的任何一个 “\” 必须被双写。例如,为了查找 “\n”,必须以 “\\n” 形式指定它。为了查找 “\”,必须指定它为 “\\\\” (反斜线被语法分析器剥离一次,另一次在模式匹配时完成,留下一条单独的反斜线被匹配)。
expr NOT LIKE pat [ESCAPE 'escape-char']
相同于 NOT (expr LIKE pat [ESCAPE 'escape-char'])。
expr SOUNDS LIKE expr
等同于 SOUNDEX(expr)=SOUNDEX(expr) (适用于 MySQL 4.1 或更新的版本)。
expr REGEXP pat
expr RLIKE pat
依照模式 pat 对字符串表达式 expr 执行一个模式比较。模式可以是一个扩展的正则表达式。查看章节 G MySQL 正则表达式。Returns 如果表达式 expr 匹配 pat,返回 1,否则返回 0。RLIKE 是 REGEXP 的同义词,它提供了与 mSQL 的兼容。注意:MySQL 在字符串中使用的是 C 的转义句法(例如 “\n”),所以在 REGEXP 字符串中使用的任何一个 “\” 必须被双写。在 MySQL 3.23.4 中,REGEXP 对于正常的(不是二进制)字符串是忽略大小写的:
mysql> SELECT 'Monty!' REGEXP 'm%y%%';
-> 0
mysql> SELECT 'Monty!' REGEXP '.*';
-> 1
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
-> 1
mysql> SELECT "a" REGEXP "A", "a" REGEXP BINARY "A";
-> 1 0
mysql> SELECT "a" REGEXP "^[a-d]";
-> 1
当决定一个字符的类型时,REGEXP 和 RLIKE 使用当前使用的字符集(缺省为 ISO-8859-1 Latin1)。
expr NOT REGEXP pat
expr NOT RLIKE pat
等同于 NOT (expr REGEXP pat)。
STRCMP(expr1,expr2)
如果字符串是相同,STRCMP() 返回 0,如果第一个参数根据当前排序次序小于第二个参数,返回 -1,否则返回 1:
mysql> SELECT STRCMP('text', 'text2');
-> -1
mysql> SELECT STRCMP('text2', 'text');
-> 1
mysql> SELECT STRCMP('text', 'text');
-> 0
MATCH (col1,col2,...) AGAINST (expr)
MATCH (col1,col2,...) AGAINST (expr IN BOOLEAN MODE)
MATCH ... AGAINST() 用于全文搜索,返回在列 (col1,col2,...) 和查询 expr 之间文本的相关相似的尺度。相关性是一个正的浮点型数字。零相关性意味着不相似。MATCH ... AGAINST() 可用于 MySQL 3.23.23 或更新的版本中。IN BOOLEAN MODE 扩展在 MySQL 4.0.1 中被新加入。详细描述和使用范例,请查看 6.8 MySQL 全文搜索。
6.3.2.2 字母大小写敏感性
BINARY
BINARY 操作符将跟在它后面的字符串强制作为一个二进制字符串。这可以很容易地强制一个列的比较以字母大小写敏感方式进行,即使该列没有定义为 BINARY 或 BLOB?
mysql> SELECT "a" = "A";
-> 1
mysql> SELECT BINARY "a" = "A";
-> 0
BINARY string 是 CAST(string AS BINARY) 的缩写。查看章节 6.3.5 Cast 函数。BINARY 在 MySQL 3.23.0 中被加入。 注意,当将一个索引列强制为 BINARY 时,在某些语境中,MySQL 将不能有效地使用索引。
如果希望对一个 blob 进行忽略字母大小的比较时,你通常可以在比较前将它转换到大写:
SELECT 'A' LIKE UPPER(blob_col) FROM table_name;
我们计划不久推出在不同字符集间的转化,以使字符串比较有更好的伸缩性。
6.3.3 数字函数
6.3.3.1 算术运算
常用的算术操作符均是可用的。注意,如果两个参数均是整型,`-', `+' 和 `*' 以 BIGINT (64 位)精度运算并返回结果!如果一个参数是一个无符号的整数,另一个参数也是一个整数,结果也将是一个无符号整数。查看章节 6.3.5 Cast 函数。
+
加法:
mysql> SELECT 3+5;
-> 8
-
减法:
mysql> SELECT 3-5;
-> -2
*
乘法:
mysql> SELECT 3*5;
-> 15
mysql> SELECT 18014398509481984*18014398509481984.0;
-> 324518553658426726783156020576256.0
mysql> SELECT 18014398509481984*18014398509481984;
-> 0
最后一个表达式的结果是错误的,这是因为乘法结果超过了 64 位 BIGINT 计算范围。
/
除法:
mysql> SELECT 3/5;
-> 0.60
被 0 除将返回一个 NULL 结果:
mysql> SELECT 102/(1-1);
-> NULL
只有当在一个结果被转换到一个整数的语境中执行时,除法将会以 BIGINT 进行算术计算。
6.3.3.2 数学函数
所有的数学函数在发生错误的情况下,均返回 NULL。
-
一元减。 改变参数的符号:
mysql> SELECT - 2;
-> -2
注意,如果这个操作符被用于一个 BIGINT,返回值也是一个 BIGINT!这就意味着,应该避免在一个可能有值 -2^63 的整数上使用 - 操作符!
ABS(X)
返回 X 的绝对值:
mysql> SELECT ABS(2);
-> 2
mysql> SELECT ABS(-32);
-> 32
这个函数可安全地使用于 BIGINT 值。
SIGN(X)
以 -1、0 或 1 方式返回参数的符号,它取决于参数 X 是负数、0 或正数。
mysql> SELECT SIGN(-32);
-> -1
mysql> SELECT SIGN(0);
-> 0
mysql> SELECT SIGN(234);
-> 1
MOD(N,M)
%
取模 (就如 C 中的 % 操作符)。返回 N 被 M 除后的余数:
mysql> SELECT MOD(234, 10);
-> 4
mysql> SELECT 253 % 7;
-> 1
mysql> SELECT MOD(29,9);
-> 2
mysql> SELECT 29 MOD 9;
-> 2
这个函数可安全地使用于 BIGINT 值。最后一个示例可在 MySQL 4.1 中工作。
FLOOR(X)
返回不大于 X 的最大整数值:
mysql> SELECT FLOOR(1.23);
-> 1
mysql> SELECT FLOOR(-1.23);
-> -2
注意,返回值被转换为一个 BIGINT!
CEILING(X)
返回不小于 X 的最小整数:
mysql> SELECT CEILING(1.23);
-> 2
mysql> SELECT CEILING(-1.23);
-> -1
注意,返回值被转换为一个 BIGINT!
ROUND(X)
ROUND(X,D)
将参数 X 四舍五入到最近的整数,然后返回。两个参数的形式是将一个数字四舍五入到 D 个小数后返回。
mysql> SELECT ROUND(-1.23);
-> -1
mysql> SELECT ROUND(-1.58);
-> -2
mysql> SELECT ROUND(1.58);
-> 2
mysql> SELECT ROUND(1.298, 1);
-> 1.3
mysql> SELECT ROUND(1.298, 0);
-> 1
注意,当参数在两个整数之间时, ROUND() 的行为取决于 C 库的实现。某些取整到最近的偶数,总是向下取,总是向上取,也可能总是接近于零。如果你需要某种取整类型,应该使用一个明确定义的函数比如 TRUNCATE() 或 FLOOR() 代替。
DIV
整除。类似于 FLOOR(),但是它可安全地用于 BIGINT 值。
mysql> SELECT 5 DIV 2
-> 2
DIV 在 MySQL 4.1.0 中新加入。
EXP(X)
返回值 e (自然对数的底) 的 X 次方:
mysql> SELECT EXP(2);
-> 7.389056
mysql> SELECT EXP(-2);
-> 0.135335
LN(X)
返回 X 的自然对数:
mysql> SELECT LN(2);
-> 0.693147
mysql> SELECT LN(-2);
-> NULL
这个函数在 MySQL 4.0.3 被新加入。在 MySQL 中,它是 LOG(X) 的同义词。
LOG(X)
LOG(B,X)
如果以一个参数调用,它返回 X 的自然对数:
mysql> SELECT LOG(2);
-> 0.693147
mysql> SELECT LOG(-2);
-> NULL
如果以两个参数调用,这个函数返回 X 任意底 B 的对数:
mysql> SELECT LOG(2,65536);
-> 16.000000
mysql> SELECT LOG(1,100);
-> NULL
任意底选项在 MySQL 4.0.3 中被加入。LOG(B,X) 等价于 LOG(X)/LOG(B)。
LOG2(X)
返回 X 的以 2 为底的对数:
mysql> SELECT LOG2(65536);
-> 16.000000
mysql> SELECT LOG2(-100);
-> NULL
LOG2() 通常可以用于计数出一个数字需要多少个比特位用于存储它。这个函数在 MySQL 4.0.3 中被添加。在更早的版本中,可以使用 LOG(X)/LOG(2) 来代替它。
LOG10(X)
返回 X 以 10 为底的对数:
mysql> SELECT LOG10(2);
-> 0.301030
mysql> SELECT LOG10(100);
-> 2.000000
mysql> SELECT LOG10(-100);
-> NULL
POW(X,Y)
POWER(X,Y)
返回 X 的 Y 幂:
mysql> SELECT POW(2,2);
-> 4.000000
mysql> SELECT POW(2,-2);
-> 0.250000
SQRT(X)
返回 X 的非否平方根:
mysql> SELECT SQRT(4);
-> 2.000000
mysql> SELECT SQRT(20);
-> 4.472136
PI()
返回 PI 值(圆周率)。缺少显示 5 位小数,但是在 MySQL 内部,为 PI 使用全部的双精度。
mysql> SELECT PI();
-> 3.141593
mysql> SELECT PI()+0.000000000000000000;
-> 3.141592653589793116
COS(X)
返回 X 的余弦,在这里,X 以弧度给出:
mysql> SELECT COS(PI());
-> -1.000000
SIN(X)
返回 X 的正弦,在这里,X 以弧度给出:
mysql> SELECT SIN(PI());
-> 0.000000
TAN(X)
返回 X 的正切,在这里,X 以弧度给出:
mysql> SELECT TAN(PI()+1);
-> 1.557408
ACOS(X)
返回 X 的反余弦,更确切地说,返回余弦值为 X 的值。如果 X 不在 -1 到 1 之间的范围内,返回 NULL:
mysql> SELECT ACOS(1);
-> 0.000000
mysql> SELECT ACOS(1.0001);
-> NULL
mysql> SELECT ACOS(0);
-> 1.570796
ASIN(X)
返回 X 的反正弦,更确切地说,返回正弦值为 X 的值。如果 X 不在 -1 到 1 之间的范围内,返回 NULL:
mysql> SELECT ASIN(0.2);
-> 0.201358
mysql> SELECT ASIN('foo');
-> 0.000000
ATAN(X)
返回 X 的反正切, 更确切地说,返回正切值为 X 的值:
mysql> SELECT ATAN(2);
-> 1.107149
mysql> SELECT ATAN(-2);
-> -1.107149
ATAN(Y,X)
ATAN2(Y,X)
返回两个变量 X 和 Y 的反正切。它类似于计算 Y / X 的反正切,除了两个参数的符号用于决定结果的象限:
mysql> SELECT ATAN(-2,2);
-> -0.785398
mysql> SELECT ATAN2(PI(),0);
-> 1.570796
COT(X)
返回 X 的余切:
mysql> SELECT COT(12);
-> -1.57267341
mysql> SELECT COT(0);
-> NULL
RAND()
RAND(N)
返回一个范围在 0 到 1.0 之间的随机浮点值。如果一个整数参数 N 被指定,它被当做种子值使用(用于产生一个可重复的数值):
mysql> SELECT RAND();
-> 0.9233482386203
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND();
-> 0.63553050033332
mysql> SELECT RAND();
-> 0.70100469486881
在一个 ORDER BY 子句中,不可以使用 RAND() 值使用一个列,因为 ORDER BY 将多次重复计算列。从 MySQL 3.23 开始,你可以使用:SELECT * FROM table_name ORDER BY RAND(),这有利于得到一个来自 SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000 的集合中的随机样本。 注意,在一个 WHERE 子句中的 RAND() 将在每次 WHERE 执行时被重新计算。 RAND() 并不是预期完美的随机数发生器,但是可以代替做为产生特别的随机数一个快速的方法,这样便于在两个不同平台下的同一 MySQL 版本间移动。
LEAST(X,Y,...)
有两个或更多个参数,返回最小(最小值)的参数。参数使用下列规则进行比较:
如果返回值用于一个 INTEGER 语境,或所有的参数是整数值,它们作为整数比较。
如果返回值用于一个 REAL 语境,或所有的参数均是实数值,它们作为实数被比较。
如果任何一个参数是字母大小写敏感的,参数作为大小写敏感的字符串进行比较。
在其它情况下,参数做为忽略大小写的字符中进行比较:
mysql> SELECT LEAST(2,0);
-> 0
mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
-> 3.0
mysql> SELECT LEAST("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> SELECT GREATEST("B","A","C");
-> "C"
在早于 MySQL 3.22.5 的版本中,可以使用 MAX() 代替 GREATEST。
DEGREES(X)
将参数 X 从弧度转换为角度,然后返回:
mysql> SELECT DEGREES(PI());
-> 180.000000
RADIANS(X)
将参数 X 从角度转换为弧度,然后返回:
mysql> SELECT RADIANS(90);
-> 1.570796
TRUNCATE(X,D)
将数值 X 截到 D 个小数,然后返回。如果 D 为 0,结果将不包含小数点和小数部分:
mysql> SELECT TRUNCATE(1.223,1);
-> 1.2
mysql> SELECT TRUNCATE(1.999,1);
-> 1.9
mysql> SELECT TRUNCATE(1.999,0);
-> 1
mysql> SELECT TRUNCATE(-1.999,1);
-> -1.9
从 MySQL 3.23.51 开始,所有数字被四舍五入到零。 如果 D 是负数,那么数字的整个部分被对准零位输出:
mysql> SELECT TRUNCATE(122,-2);
-> 100
注意, 十进值小数在计算机中通常不以精确数字存储,而是双精度型的值,你可能会被下列结果所愚弄:
mysql> SELECT TRUNCATE(10.28*100,0);
-> 1027
上面结果的发生是因为 10.28 实际上是以某些像 10.2799999999999999 的形式被存储的。
6.3.4 日期和时间函数
对于每个类型的值范围以及日期和时间值有效指定格式,请查看章节 6.2.2 Date 和 Time 类型。
这里是一个使用日期函数的例子。下面的查询选择所有 date_col 值在最后 30 天内的记录。
mysql> SELECT something FROM tbl_name
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
DAYOFWEEK(date)
返回 date 的星期索引(1 = Sunday, 2 = Monday, ... 7 = Saturday)。索引值符合 ODBC 的标准。
mysql> SELECT DAYOFWEEK('1998-02-03');
-> 3
WEEKDAY(date)
返回 date 的星期索引(0 = Monday, 1 = Tuesday, ... 6 = Sunday):
mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
-> 1
mysql> SELECT WEEKDAY('1997-11-05');
-> 2
DAYOFMONTH(date)
返回 date 是一月中的第几天,范围为 1 到 31:
mysql> SELECT DAYOFMONTH('1998-02-03');
-> 3
DAYOFYEAR(date)
返回 date 是一年中的第几天,范围为 1 到 366:
mysql> SELECT DAYOFYEAR('1998-02-03');
-> 34
MONTH(date)
返回 date 中的月份,范围为 1 到 12:
mysql> SELECT MONTH('1998-02-03');
-> 2
DAYNAME(date)
返回 date 的星期名:
mysql> SELECT DAYNAME("1998-02-05");
-> 'Thursday'
MONTHNAME(date)
返回 date 的月份名:
mysql> SELECT MONTHNAME("1998-02-05");
-> 'February'
QUARTER(date)
返回 date 在一年中的季度,范围为 1 到 4:
mysql> SELECT QUARTER('98-04-01');
-> 2
WEEK(date)
WEEK(date,first)
对于星期日是一周中的第一天的场合,如果函数只有一个参数调用,返回 date 为一年的第几周,返回值范围为 0 到 53 (是的,可能有第 53 周的开始)。两个参数形式的 WEEK() 允许你指定一周是否以星期日或星期一开始,以及返回值为 0-53 还是 1-52。 这里的一个表显示第二个参数是如何工作的: 值 含义
0 一周以星期日开始,返回值范围为 0-53
1 一周以星期一开始,返回值范围为 0-53
2 一周以星期日开始,返回值范围为 1-53
3 一周以星期一开始,返回值范围为 1-53 (ISO 8601)
mysql> SELECT WEEK('1998-02-20');
-> 7
mysql> SELECT WEEK('1998-02-20',0);
-> 7
mysql> SELECT WEEK('1998-02-20',1);
-> 8
mysql> SELECT WEEK('1998-12-31',1);
-> 53
注意,在版本 4.0 中,WEEK(#,0) 被更改为匹配 USA 历法。 注意,如果一周是上一年的最后一周,当你没有使用 2 或 3 做为可选参数时,MySQL 将返回 0:
mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
-> 2000, 0
mysql> SELECT WEEK('2000-01-01',2);
-> 52
你可能会争辩说,当给定的日期值实际上是 1999 年的第 52 周的一部分时,MySQL 对 WEEK() 函数应该返回 52。我们决定返回 0 ,是因为我们希望该函数返回“在指定年份中是第几周”。当与其它的提取日期值中的月日值的函数结合使用时,这使得 WEEK() 函数的用法可靠。 如果你更希望能得到恰当的年-周值,那么你应该使用参数 2 或 3 做为可选参数,或者使用函数 YEARWEEK() :
mysql> SELECT YEARWEEK('2000-01-01');
-> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
-> 52
YEAR(date)
返回 date 的年份,范围为 1000 到 9999:
mysql> SELECT YEAR('98-02-03');
-> 1998
YEARWEEK(date)
YEARWEEK(date,first)
返回一个日期值是的哪一年的哪一周。第二个参数的形式与作用完全与 WEEK() 的第二个参数一致。注意,对于给定的日期参数是一年的第一周或最后一周的,返回的年份值可能与日期参数给出的年份不一致:
mysql> SELECT YEARWEEK('1987-01-01');
-> 198653
注意,对于可选参数 0 或 1,周值的返回值不同于 WEEK() 函数所返回值(0), WEEK() 根据给定的年语境返回周值。
HOUR(time)
返回 time 的小时值,范围为 0 到 23:
mysql> SELECT HOUR('10:05:03');
-> 10
MINUTE(time)
返回 time 的分钟值,范围为 0 到 59:
mysql> SELECT MINUTE('98-02-03 10:05:03');
-> 5
SECOND(time)
返回 time 的秒值,范围为 0 到 59:
mysql> SELECT SECOND('10:05:03');
-> 3
PERIOD_ADD(P,N)
增加 N 个月到时期 P(格式为 YYMM 或 YYYYMM)中。以 YYYYMM 格式返回值。 注意,期间参数 P 不是 一个日期值:
mysql> SELECT PERIOD_ADD(9801,2);
-> 199803
PERIOD_DIFF(P1,P2)
返回时期 P1 和 P2 之间的月数。P1 和 P2 应该以 YYMM 或 YYYYMM 指定。 注意,时期参数 P1 和 P2 不是 日期值:
mysql> SELECT PERIOD_DIFF(9802,199703);
-> 11
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)
这些函数执行日期的算术运算。ADDDATE() 和 SUBDATE() 分别是 DATE_ADD() 和 DATE_SUB() 的同义词。 在 MySQL 3.23 中,如果表达式的右边是一个日期值或一个日期时间型字段,你可以使用 + 和 - 代替 DATE_ADD() 和 DATE_SUB()(示例如下)。 参数 date 是一个 DATETIME 或 DATE 值,指定一个日期的开始。expr 是一个表达式,指定从开始日期上增加还是减去间隔值。expr 是一个字符串;它可以以一个 “-” 领头表示一个负的间隔值。type 是一个关键词,它标志着表达式以何格式被解释。 下表显示 type 和 expr 参数是如何关联的: type 值 expr 期望的格式
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
MONTH MONTHS
YEAR YEARS
MINUTE_SECOND "MINUTES:SECONDS"
HOUR_MINUTE "HOURS:MINUTES"
DAY_HOUR "DAYS HOURS"
YEAR_MONTH "YEARS-MONTHS"
HOUR_SECOND "HOURS:MINUTES:SECONDS"
DAY_MINUTE "DAYS HOURS:MINUTES"
DAY_SECOND "DAYS HOURS:MINUTES:SECONDS"
在 expr 的格式中,MySQL 允许任何字符作为定界符。表中所显示的是建议的定界字符。如果 date 参数是一个 DATE 值,并且计算的间隔仅仅有 YEAR、MONTH 和 DAY 部分(没有时间部分),那么返回值也是一个 DATE 值。否则返回值是一个 DATETIME 值:
mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
-> 1998-01-01 00:00:00
mysql> SELECT INTERVAL 1 DAY + "1997-12-31";
-> 1998-01-01
mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;
-> 1997-12-31 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
-> INTERVAL 1 SECOND);
-> 1998-01-01 00:00:00
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
-> INTERVAL 1 DAY);
-> 1998-01-01 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
-> INTERVAL "1:1" MINUTE_SECOND);
-> 1998-01-01 00:01:00
mysql> SELECT DATE_SUB("1998-01-01 00:00:00",
-> INTERVAL "1 1:1:1" DAY_SECOND);
-> 1997-12-30 22:58:59
mysql> SELECT DATE_ADD("1998-01-01 00:00:00",
-> INTERVAL "-1 10" DAY_HOUR);
-> 1997-12-30 14:00:00
mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
-> 1997-12-02
如果你指定了一个太短的间隔值(没有包括 type 关键词所期望的所有间隔部分),MySQL 假设你遗漏了间隔值的最左边部分。例如,如果指定一个 type 为 DAY_SECOND,那么 expr 值被期望包含天、小时、分钟和秒部分。如果你象 "1:10" 样指定一个值,MySQL 假设天和小时部分被遗漏了,指定的值代表分钟和秒。换句话说,"1:10" DAY_SECOND 被解释为等价于 "1:10" MINUTE_SECOND。这类似于 MySQL 解释 TIME 值为经过的时间而不是一天的时刻。 注意,如果依着包含一个时间部分的间隔增加或减少一个日期值,该日期值将被自动地转换到一个日期时间值:
mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 DAY);
-> 1999-01-02
mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 HOUR);
-> 1999-01-01 01:00:00
如果你使用了确定不正确的日期,返回结果将是 NULL。如果你增加 MONTH、YEAR_MONTH 或 YEAR,并且结果日期的天比新月份的最大天数还大,那么它将被调整到新月份的最大天数:
mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
-> 1998-02-28
注意,上面的例子中,单词 INTERVAL 和关键词 type 是不区分字母大小写的。
EXTRACT(type FROM date)
EXTRACT() 函数使用与 DATE_ADD() 或 DATE_SUB() 一致的间隔类型,但是它用于指定从日期中提取的部分,而不是进行日期算术运算。
mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
-> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
-> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
-> 20102
TO_DAYS(date)
给出一个日期 date,返回一个天数(从 0 年开始的天数):
mysql> SELECT TO_DAYS(950501);
-> 728779
mysql> SELECT TO_DAYS('1997-10-07');
-> 729669
TO_DAYS() 无意于使用先于格里高里历法(即现行的阳历)(1582)出现的值,因为它不考虑当历法改变时所遗失的天数。
FROM_DAYS(N)
给出一个天数 N,返回一个 DATE 值:
mysql> SELECT FROM_DAYS(729669);
-> '1997-10-07'
FROM_DAYS() 无意于使用先于格里高里历法(1582)出现的值,因为它不考虑当历法改变时所遗失的天数。
DATE_FORMAT(date,format)
依照 format 字符串格式化 date 值。下面的修饰符可被用于 format 字符串中: 修饰符 含义
%M 月的名字 (January..December)
%W 星期的名字 (Sunday..Saturday)
%D 有英文后缀的某月的第几天 (0th, 1st, 2nd, 3rd, etc.)
%Y 年份,数字的,4 位
%y 年份,数字的,2 位
%X 周值的年份,星期日是一个星期的第一天,数字的,4 位,与 '%V' 一同使用
%x 周值的年份,星期一是一个星期的第一天,数字的,4 位,与 '%v' 一同使用
%a 缩写的星期名 (Sun..Sat)
%d 月份中的天数,数字的 (00..31)
%e 月份中的天数,数字的 (0..31)
%m 月,数字的 (00..12)
%c 月,数字的 (0..12)
%b 缩写的月份名 (Jan..Dec)
%j 一年中的天数 (001..366)
%H 小时 (00..23)
%k 小时 (0..23)
%h 小时 (01..12)
%I 小时 (01..12)
%l 小时 (1..12)
%i 分钟,数字的 (00..59)
%r 时间,12 小时 (hh:mm:ss [AP]M)
%T 时间,24 小时 (hh:mm:ss)
%S 秒 (00..59)
%s 秒 (00..59)
%p AM 或 PM
%w 一周中的天数 (0=Sunday..6=Saturday)
%U 星期 (00..53),星期日是一个星期的第一天
%u 星期 (00..53),星期一是一个星期的第一天
%V 星期 (01..53),星期日是一个星期的第一天。与 '%X' 一起使用
%v 星期 (01..53),星期一是一个星期的第一天。与 '%x' 一起使用
%% 一个字母 “%”
所有其它的字符不经过解释,直接复制到结果中:
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
在 MySQL 3.23 中,在格式修饰符前需要字符 `%'。在更早的 MySQL 版本中,`%' 是可选的。 月份与天修饰符的范围从零开始的原因是,在 MySQL 3.23 中,它允许存储不完善的日期值(例如 '2004-00-00')。
TIME_FORMAT(time,format)
它的使用方法与上面的 DATE_FORMAT() 函数相似,但是 format 字符串只包含处理小时、分和秒的那些格式修饰符。使用其它的修饰符会产生一个 NULL 值或 0。
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
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
注意,函数 NOW() 在每个查询中只计算一次,也就是在查询开始执行时。这就是说,如果在一个单独的查询中多次引用了 NOW(),它只会给出值都是一个相同的时间。
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
如果调用时没有参数,以无符号的整数形式返回一个 Unix 时间戳(从 '1970-01-01 00:00:00' GMT 开始的秒数)。如果以一个参数 date 调用 UNIX_TIMESTAMP(),它将返回该参数值从 '1970-01-01 00:00:00' GMT 开始经过的秒数值。date 可以是一个 DATE 字符串,一个 DATETIME 字符串,一个 TIMESTAMP,或者以一个 YYMMDD 或 YYYYMMDD 显示的本地时间:
mysql> SELECT UNIX_TIMESTAMP();
-> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
当 UNIX_TIMESTAMP 被用于一个 TIMESTAMP 列时,函数直接返回一个内部的时间戳值,而不进行一个隐含地 “string-to-unix-timestamp” 转换。如果你传递一个超出范围的日期参数给 UNIX_TIMESTAMP() ,它将返回 0,但是请注意,MySQL 对其仅仅进行基本的检验(年范围 1970-2037,月份 01-12,日期 01-31)。 如果你希望减去 UNIX_TIMESTAMP() 列,你应该需要将结果强制转换为一有符号整数。查看章节 6.3.5 Cast 函数。
FROM_UNIXTIME(unix_timestamp [,format])
以 'YYYY-MM-DD HH:MM:SS' 或 YYYYMMDDHHMMSS 格式返回一个 unix_timestamp 参数值,返回值的形式取决于该函数使用于字符串还是数字语境。 如果 format 给出,返回值依 format 字符串被格式。format 可以包含与 DATE_FORMAT() 函数同样的修饰符。
mysql> SELECT FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
'%Y %D %M %h:%i:%s %x');
-> '1997 23rd December 03:43:30 1997'
SEC_TO_TIME(seconds)
以 'HH:MM:SS' 或 HHMMSS 格式返回参数 seconds 被转换到时分秒后的值,返回值的形式取决于该函数使用于字符串还是数字语境:
mysql> SELECT SEC_TO_TIME(2378);
-> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
-> 3938
TIME_TO_SEC(time)
将参数 time 转换为秒数后返回:
mysql> SELECT TIME_TO_SEC('22:23:00');
-> 80580
mysql> SELECT TIME_TO_SEC('00:39:38');
-> 2378
6.3.5 Cast 函数
CAST 函数的句法如下:
CAST(expression AS type)
或
CONVERT(expression,type)
type 可以是下面的任一个:
BINARY
CHAR (4.0.6 中新加入)
DATE
DATETIME
SIGNED {INTEGER}
TIME
UNSIGNED {INTEGER}
CAST() 是 ANSI SQL99 的句法,CONVERT() 是 ODBC 的句法。
CAST 函数主要用于以特殊的 CREATE ... SELECT 形式建立一个列时:
CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);
CAST(string AS BINARY 与 BINARY string 是相同的。 CAST(expr AS CHAR 表示一个使用当前默认字符集的字符串。
为了将一个字符串转换成一个数字值,通常不需要做任何事情;只要将字符串值当做一个数字即可:
mysql> SELECT 1+'1';
-> 2
如果在一个字符串语境中使用一个数字,该数字会被自动地转换为一个 BINARY 字符串。
mysql> SELECT CONCAT("hello you ",2);
-> "hello you 2"
MySQL 支持两方是有符号的和无符号的 64 位值的算术运算。如果你使用一个数字操作符(比如 +),并且其中的一个操作数是 unsigned integer,那么结果将是无符号的。为了不考虑这些问题,你可以使用 SIGNED 和 UNSIGNED CAST 操作符来,它会分别地强制运算到一个有符号的或一个无符号的 64 位整数。
mysql> SELECT CAST(1-2 AS UNSIGNED)
-> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
-> -1
注意,如果任一个操作数是一个浮点值(在这种语境下,DECIMAL() 被当作是一个浮点数值),结果也将是一个浮点数值,并且结果不受上面的规则影响。
mysql> SELECT CAST(1 AS UNSIGNED) -2.0
-> -1.0
如果在一个算术运算中使用一个字符串,它将被转换为一个浮点数字。
函数 CAST() 和 CONVERT() 在 MySQL 4.0.2 中被加入。
为了完全支持 BIGINT,在 MySQL 4.0 中对无符号值的处理发生了改变。如果希望你的代码在 MySQL 4.0 和 3.23 中均能够正常运行(在这种情况下,你或许不能够使用 CAST 函数),当进行两个无符号整数列的减法时,你可以使用下面的技巧得到一个有符号的结果:
SELECT (unsigned_column_1+0.0)-(unsigned_column_2+0.0);
这个做法是在进行减法之前,先将无符号列转换为一个浮点数。
如果你将旧的 MySQL 应用程序移植到 MySQL 4.0 时,在 UNSIGNED 列上出现了问题,你可以在启动 mysqld 时使用 --sql-mode=NO_UNSIGNED_SUBTRACTION 选项。注意,只要你使用了这个选项,你将不能直接地使用 UNSIGNED BIGINT 列类型。
6.3.6 其它函数
6.3.6.1 位函数
MySQL 使用 BIGINT (64 位) 算法进行位运算,因而这些操作符有一个 64 位的最大范围。
|
位或
mysql> SELECT 29 | 15;
-> 31
返回值是一个 64 位的无符号整数。
&
位与
mysql> SELECT 29 & 15;
-> 13
返回值是一个 64 位的无符号整数。
^
位异或
mysql> SELECT 1 ^ 1;
-> 0
mysql> SELECT 1 ^ 0;
-> 1
mysql> SELECT 11 ^ 3;
-> 8
返回值是一个 64 位的无符号整数。 XOR 在 MySQL 4.0.2 中被加入。
<<
左移一个长长的数字(BIGINT):
mysql> SELECT 1 << 2;
-> 4
返回值是一个 64 位的无符号整数。
>>
右移一个长长的数字(BIGINT):
mysql> SELECT 4 >> 2;
-> 1
返回值是一个 64 位的无符号整数。
~
置反所有位:
mysql> SELECT 5 & ~1;
-> 4
返回值是一个 64 位的无符号整数。
BIT_COUNT(N)
返回在参数 N 中嵌入的比特位数量:
mysql> SELECT BIT_COUNT(29);
-> 4
--------------------------------------------------------------------------------
6.3 用于 SELECT 和 WHERE 子句的函数
一个 SQL 语句中的 select_expression 或 where_definition 可由任何使用了下面所描述函数的表达式组成。
包含 NULL 的表达式总是得出一个 NULL 值结果,除非表达式中的操作和函数在文档中有另外的说明。
注意:在一个函数名和跟随它的括号之间必须不存在空格。这有助于 MySQL 语法分析程序区分函数调用和对恰巧与函数同名表或列的引用。然而,参数左右两边的空格却是允许的。
你可以强制 MySQL 接受函数名后存在空格的形式,这需要通过以 --ansi 选项启动 mysqld,或在 mysql_connect() 中使用 CLIENT_IGNORE_SPACE,但是,在这种情况下,所有的函数名均将成为保留字。查看章节 1.8.2 以 ANSI 模式运行 MySQL.
为了简洁,从 mysql 程序输出的例子以缩写的形式显示。因此:
mysql> SELECT MOD(29,9);
1 rows in set (0.00 sec)
+-----------+
| mod(29,9) |
+-----------+
| 2 |
+-----------+
将被显示为这样:
mysql> SELECT MOD(29,9);
-> 2
6.3.1 无类型的特殊运算符和函数
6.3.1.1 圆括号
( ... )
括号,使用它来强制一个表达式的计算顺序。
mysql> SELECT 1+2*3;
-> 7
mysql> SELECT (1+2)*3;
-> 9
6.3.1.2 比较运算符
比较运算符的结果是 1 (TRUE)、0 (FALSE) 或 NULL。这些函数可工作于数字和字符串上。根据需要,字符串将会自动地被转换到数字,以及数字被转换成字符串(比如在 Perl 中)。
MySQL 使用下列规则进行比较:
如果一个或两个参数是 NULL,比较的结果是 NULL,除了 <=> 运算符。
如果在一个比较操作中两个参数均是字符串,他们将作为字符串被比较。
如果两个参数均是整数,他们作为整数被比较。
十六进制值如果不与一个数字进行比较,那么它将当作一个二进制字符串。
如果参数之一是一个 TIMESTAMP 或 DATETIME 列,而另一参数是一个常数,在比较执行之前,这个常数被转换为一个时间戳。这样做是为了对 ODBC 更友好。
在所有其它情况下,参数作为浮点(real)数字被比较。
缺省地,字符串使用当前字符集以忽略字母大小写的方式进行比较(缺省的字符集为 ISO-8859-1 Latin1,它对英语处理得很出色)。
下面的例子演示了对于比较操作字符串到数字的转换:
mysql> SELECT 1 > '6x';
-> 0
mysql> SELECT 7 > '6x';
-> 1
mysql> SELECT 0 > 'x6';
-> 0
mysql> SELECT 0 = 'x6';
-> 1
=
等于:
mysql> SELECT 1 = 0;
-> 0
mysql> SELECT '0' = 0;
-> 1
mysql> SELECT '0.0' = 0;
-> 1
mysql> SELECT '0.01' = 0;
-> 0
mysql> SELECT '.01' = 0.01;
-> 1
<>
!=
不等于:
mysql> SELECT '.01' <> '0.01';
-> 1
mysql> SELECT .01 <> '0.01';
-> 0
mysql> SELECT 'zapp' <> 'zappp';
-> 1
<=
小于或等于:
mysql> SELECT 0.1 <= 2;
-> 1
<
小于:
mysql> SELECT 2 < 2;
-> 0
>=
大于或等于:
mysql> SELECT 2 >= 2;
-> 1
>
大于:
mysql> SELECT 2 > 2;
-> 0
<=>
NULL 值安全等于:
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1 1 0
IS NULL
IS NOT NULL
测试一个值是或不是 NULL:
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
-> 0 0 1
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
-> 1 1 0
为了能够与其它程序更好的工作,在使用 IS NULL 是 MySQL 支持下列额外选择性:
通过它,你可以找到最后插入的记录行:
SELECT * FROM tbl_name WHERE auto_col IS NULL
这个操作可以通过设置 SQL_AUTO_IS_NULL=0 来禁止。查看章节 5.5.6 SET 句法.
对于 NOT NULL 的 DATE 和 DATETIME 列,可以通过使用下列语句找到特殊的值 0000-00-00:
SELECT * FROM tbl_name WHERE date_column IS NULL
这需要通过某些 ODBC 应用程序才能工作(因为 ODBC 不支持一个 0000-00-00 日期)
expr BETWEEN min AND max
如果 expr 大于或等于 min ,并且 expr 小于或等于 max,BETWEEN 返回 1,否则返回 0。它等价于表达式 (min <= expr AND expr <= max) ,只要所有的参数均是相同的类型。 否则类型会依照上面的规则发生转换,但是应用于所有三个参数。注意,在 MySQL 4.0.5 之前,参数被转换到 expr 的类型。
mysql> SELECT 1 BETWEEN 2 AND 3;
-> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
-> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
-> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
-> 0
expr NOT BETWEEN min AND max
等同于 NOT (expr BETWEEN min AND max)。
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
从 MySQL 4.1 开始(符合 SQL-99 标准),如果左手边的表达式是 NULL,或者在列表中没有发现相匹配的值并且列表中的一个表达式是 NULL,IN 均返回 NULL。
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 值使用 = 进行比较总是为 false !
COALESCE(list)
返回列表中第一个非 NULL 的元素:
mysql> SELECT COALESCE(NULL,1);
-> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
-> NULL
INTERVAL(N,N1,N2,N3,...)
Returns 如果 N < N1 返回 0,如果 N < N2 返回 1,等等。所有的参数均被当作整数。为了函数能正确地工作,它要求 N1 < N2 < N3 < ... < Nn。这是因为它使用的是一个二进制的搜索(非常地快):
mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
-> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
-> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
-> 0
如果以任何一个标准运算符(=, <>..., 但除了 LIKE)对一个忽略大小写的字符串进行比较,尾部的空白空间(空格、TAB 和换行)均被忽略。
mysql> SELECT "a" ="A \n";
-> 1
6.3.1.3 逻辑运算符
在 SQL 中,所有的逻辑运算符返回的值均为 TRUE、FALSE 或 NULL (未知)。在 MySQL 中,它们由 1 (TRUE)、0 (FALSE) 和 NULL 实现。这些大部分在不同的 SQL 数据库间是相同的,然而某些可能会以一个非零值返回 TRUE。
NOT
!
逻辑非。如果操作数为 0,返回 1;如果操作数为非零,返回 0;如果操作数为 NOT NULL,返回 NULL。
mysql> SELECT NOT 10;
-> 0
mysql> SELECT NOT 0;
-> 1
mysql> SELECT NOT NULL;
-> NULL
mysql> SELECT ! (1+1);
-> 0
mysql> SELECT ! 1+1;
-> 1
最后一个例子返回 1,因为表达式是与 (!1)+1 一样被计算的。
AND
&&
逻辑与。如果所有的操作数都是非零或非 NULL 的,返回 1;如果有一个或多个操作数为 0 ,则返回 0,只要操作数中有 NULL 返回值就为 NULL。
mysql> SELECT 1 && 1;
-> 1
mysql> SELECT 1 && 0;
-> 0
mysql> SELECT 1 && NULL;
-> NULL
mysql> SELECT 0 && NULL;
-> 0
mysql> SELECT NULL && 0;
-> 0
请注意,在 MySQL 4.0.5 以前版本中,当遇到一个 NULL 时就停止计算,而不是继续进程检查可能存在的0。这就意味着,在这些版本中,SELECT (NULL AND 0) 返回 NULL,而不是 0。在 4.0.5 中,代码已被重新设计了,已便于在任何仍然使用优化情况下,返回值总是能如 ANSI 所规定的那样。
OR
||
逻辑或。如果任何一个操作数是非零的,返回值为 1,如果任一操作数为 NULL,返回值为 NULL,否则返回 0。
mysql> SELECT 1 || 1;
-> 1
mysql> SELECT 1 || 0;
-> 1
mysql> SELECT 0 || 0;
-> 0
mysql> SELECT 0 || NULL;
-> NULL
mysql> SELECT 1 || NULL;
-> 1
XOR
逻辑异或。如果任一操作数为 NULL,返回值为 NULL。对于非 NULL 的操作数,如果有奇数个非零的操作数,结果返回为 1,否则返回 0。
mysql> SELECT 1 XOR 1;
-> 0
mysql> SELECT 1 XOR 0;
-> 1
mysql> SELECT 1 XOR NULL;
-> NULL
mysql> SELECT 1 XOR 1 XOR 1;
-> 1
a XOR b 算术相等于 (a AND (NOT b)) OR ((NOT a) and b)。 XOR 在 MySQL 4.0.2 中被添加。
6.3.1.4 控制流函数
IFNULL(expr1,expr2)
如果 expr1 为非 NULL 的,IFNULL() 返回 expr1,否则返回 expr2。IFNULL() 返回一个数字或字符串值,这取决于它被使用的语境:
mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'
在 MySQL 4.0.6 和更新版本中,IFNULL(expr1,expr2) 的默认返回值以 STRING、REAL 或 INTEGER 顺序更加'general'了两个表达式。当你基于一个表达式创建一个表或在一个临时表中 MySQL 不得不存储一个从 IFNULL() 返回的值时,这个与较早 MySQL 版本的不同将更加值得注意。
CREATE TABLE foo SELECT IFNULL(1,"test") as test;
在 MySQL 4.0.6 中,列 'test' 的类型为 CHAR(4),然而在较早的版本中,你得到的却是 BIGINT。
NULLIF(expr1,expr2)
如果 expr1 = expr2 为真,返回 NULL,否则返回 expr1。它等同于 CASE WHEN x = y THEN NULL ELSE x END:
mysql> SELECT NULLIF(1,1);
-> NULL
mysql> SELECT NULLIF(1,2);
-> 1
注意,如果参数不相等,在 MySQL 中,expr1 被求值两次。
IF(expr1,expr2,expr3)
如果 expr1 为真(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> SELECT IF(STRCMP('test','test1'),'no','yes');
-> 'no'
如果 expr2 或 expr3 明确地为 NULL,那么函数 IF() 的返回值类型为非 NULL 列的类型。(这在选择在 MySQL 4.0.3 中新加入)。 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) 的测试结果。这可能不是你所期望的。在第二种情况下,比较测试原浮点数是否为一个非零值。比较的结果被作为整数使用。 缺省的 IF() 返回值类型 (当结果存储在临时表中时,这是非常重要的) 在 MySQL 3.23 中按下列方式确定: 表达式 返回值
表达式(expr2)或表达式(expr3)返回值为字符串 字符串
表达式(expr2)或表达式(expr3)返回值为浮点型值 浮点型
表达式(expr2)或表达式(expr3)返回值为整型 整型
如果表达式(expr2)和表达式(expr3)均是字符串,同时两个字符串均是忽略字母大小写的,那么返回值也是忽略字母大小写的(从 MySQL 3.23.51 开始)。
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 时返回 result。第二个形式当第一个为真值的 condition 出现时,返回该条件的结果。如果没有匹配的结果值,那么 ELSE 后的结果将被返回。如果没有 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
返回值的类型 (INTEGER、DOUBLE 或 STRING) 与第一个返回值(第一个 THEN 后的表达式)的类型相同。
6.3.2 字符串函数
如果返回结果的长度超过服务器参数 max_allowed_packet 的大小,字符串值函数将返回 NULL。查看章节 5.5.2 调节服务器参数。
对于操作字符串位置的函数,第一个位置被标记为 1。
ASCII(str)
返回字符串 str 最左边的那个字符的 ASCII 码值。如果 str 是一个空字符串,那么返回值为 0。如果 str 是一个 NULL,返回值也是 NULL:
mysql> SELECT ASCII('2');
-> 50
mysql> SELECT ASCII(2);
-> 50
mysql> SELECT ASCII('dx');
-> 100
也可参看 ORD() 函数。
ORD(str)
如果字符串 str 的最左边的字符是一个多字节的字符,根据多字节字符的成分字符的 ASCII 码值通过下面的公式计算返回那个的编码:((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]。如果最左边的字符不是一个多字节字符,返回值与 ASCII() 函数相同:
mysql> SELECT ORD('2');
-> 50
CONV(N,from_base,to_base)
在不同的数字基数之间转换数字。将数字 N 从 from_base 转换到 to_base,并以字符串表示形式返回。如果任何一个参数为 NULL,那么返回值也为 NULL。参数 N 被解释为是一个整数,但是也可以被指定为一个整数或一个字符串。最小基为 2,最大基为 36。如果 to_base 是一个负值,N 将被看作为是一个有符号数字。否则,N 被视为是无符号的。CONV 以 64 位精度工作:
mysql> SELECT CONV("a",16,2);
-> '1010'
mysql> SELECT CONV("6E",18,8);
-> '172'
mysql> SELECT CONV(-17,10,-18);
-> '-H'
mysql> SELECT CONV(10+"10"+'10'+0xa,10,10);
-> '40'
BIN(N)
返回 N 的字符串表示的二进制值形式,在这里,N 长长的(BIGINT)数字。这个函数等价于 CONV(N,10,2)。如果 N 是一个 NULL,返回值也是 NULL:
mysql> SELECT BIN(12);
-> '1100'
OCT(N)
返回 N 的字符串表示的八进制值形式,在这里,N 是一个长长的数字。这个函数等价于 CONV(N,10,8)。如果 N 是一个 NULL,返回值也是 NULL :
mysql> SELECT OCT(12);
-> '14'
HEX(N_or_S)
如果 N_OR_S 是一个数字,返回 N 的字符串表示的十六进制值形式,这里 N 是一个长长的(BIGINT)数字。这个函数等价于 CONV(N,10,16)。 如果 N_OR_S 是一个字符串,N_OR_S 中的每个字符均被转换为 2 位十六进制数字,并以十六进制的字符串形式返回。这是 0xff 形式的字符串反转操作。
mysql> SELECT HEX(255);
-> 'FF'
mysql> SELECT HEX("abc");
-> 616263
mysql> SELECT 0x616263;
-> "abc"
CHAR(N,...)
CHAR() 以整数类型解释参数,返回这个整数所代表的 ASCII 码值给出的字符组成的字符串。NULL 值将被忽略:
mysql> SELECT CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
-> 'MMM'
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'
CONCAT_WS(separator, str1, str2,...)
CONCAT_WS() 支持 CONCAT 加上一个分隔符,它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间:
mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
-> 'First name,Last Name'
LENGTH(str)
OCTET_LENGTH(str)
CHAR_LENGTH(str)
CHARACTER_LENGTH(str)
返回字符串 str 的长度:
mysql> SELECT LENGTH('text');
-> 4
mysql> SELECT OCTET_LENGTH('text');
-> 4
注意,CHAR_LENGTH() 和 CHARACTER_LENGTH() 对于多字节字符只计数一次。
BIT_LENGTH(str)
返回字符串 str 的比特长度:
mysql> SELECT BIT_LENGTH('text');
-> 32
LOCATE(substr,str)
POSITION(substr IN str)
返回子串 substr 在字符串 str 中第一次出现的位置。如果子串 substr 在 str 中不存在,返回值为 0:
mysql> SELECT LOCATE('bar', 'foobarbar');
-> 4
mysql> SELECT LOCATE('xbar', 'foobar');
-> 0
这个函数是多字节安全的。在 MySQL 3.23 中,这个函数是字母大小写敏感的,当在 MySQL 4.0 中时,如有任一参数是一个二进制字符串,它才是字母大小写敏感的。
LOCATE(substr,str,pos)
返回子串 substr 在字符串 str 中的第 pos 位置后第一次出现的位置。如果 substr 不在 str 中返回 0 :
mysql> SELECT LOCATE('bar', 'foobarbar',5);
-> 7
这个函数是多字节安全的。在 MySQL 3.23 中,这个函数是字母大小写敏感的,当在 MySQL 4.0 中时,如有任一参数是一个二进制字符串,它才是字母大小写敏感的。
INSTR(str,substr)
返回子串 substr 在字符串 str 中第一次出现的位置。这与有两个参数形式的 LOCATE() 相似,只是参数的位置被颠倒了:
mysql> SELECT INSTR('foobarbar', 'bar');
-> 4
mysql> SELECT INSTR('xbar', 'foobar');
-> 0
这个函数是多字节安全的。在 MySQL 3.23 中,这个函数是字母大小写敏感的,当在 MySQL 4.0 中时,如有任一参数是一个二进制字符串,它才是字母大小写敏感的。
LPAD(str,len,padstr)
用字符串 padstr 对 str 进行左边填补直至它的长度达到 len 个字符长度,然后返回 str。如果 str 的长度长于 len',那么它将被截除到 len 个字符。
mysql> SELECT LPAD('hi',4,'??');
-> '??hi'
RPAD(str,len,padstr)
用字符串 padstr 对 str 进行右边填补直至它的长度达到 len 个字符长度,然后返回 str。如果 str 的长度长于 len',那么它将被截除到 len 个字符。
mysql> SELECT RPAD('hi',5,'?');
-> 'hi???'
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)
SUBSTRING(str FROM pos FOR len)
MID(str,pos,len)
从字符串 str 的 pos 位置起返回 len 个字符的子串。使用 FROM 的变体形式是 ANSI SQL92 的句法:
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
这个函数是多字节安全的。
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
从字符串 str 的 pos 位置起返回子串:
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
这个函数是多字节安全的。
SUBSTRING_INDEX(str,delim,count)
返回字符串 str 中在第 count 个出现的分隔符 delim 之前的子串。如果 count 是一个正数,返回从最后的(从左边开始计数)分隔符到左边所有字符。如果 count 是负数,返回从最后的(从右边开始计数)分隔符到右边所有字符:
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
这个函数是多字节安全的。
LTRIM(str)
返回移除了领头的空格字符后的 str:
mysql> SELECT LTRIM(' barbar');
-> 'barbar'
RTRIM(str)
返回移除了尾部的空格字符后的 str:
mysql> SELECT RTRIM('barbar ');
-> 'barbar'
这个函数是多字节安全的。
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
移除字符串 str 中所有的 remstr 前缀或后缀,然后将其返回。如果没有任何 BOTH、LEADING 或 TRAILING 修饰符被给出,BOTH 被假定。如果 remstr 没有被指定,空格将被移除:
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
这个函数是多字节安全的。
SOUNDEX(str)
返回 str 的近音字。两个发音差不多的字符串应该有同样的近音字。一个标准的近音字为 4 个字符长,但是函数 SOUNDEX() 却返回一个任意长的字符串。你可以在结果上使用 SUBSTRING() 标准的近音字。提供的字符中所有的非数字字母的字符均被忽略。所有在 A-Z 范围之外的国际 alpha 字符被视为元音:
mysql> SELECT SOUNDEX('Hello');
-> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
-> 'Q36324'
SPACE(N)
返回有 N 空格字符组成的字符串:
mysql> SELECT SPACE(6);
-> ' '
REPLACE(str,from_str,to_str)
在字符串 str 中所有出现的字符串 from_str 均被 to_str替换,然后返回这个字符串:
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
这个函数是多字节安全的。
REPEAT(str,count)
返回一个由重复了 count 次的字符串 str 组成的字符串。如果 count <= 0,返回一个空字符串。如果 str 或 count 是 NULL,返回值也为 NULL:
mysql> SELECT REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'
REVERSE(str)
以颠倒的字符顺序返回字符串 str:
mysql> SELECT REVERSE('abc');
-> 'cba'
这个函数是多字节安全的。
INSERT(str,pos,len,newstr)
在字符串 str 中,将从 pos 位置开始,len 个字符长的子串替换为字符串 newstr ,然后将结果返回:
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
这个函数是多字节安全的。
ELT(N,str1,str2,str3,...)
如果 N = 1,返回 str1,如果N = 2,返回 str2,等等。如果 N 小于 1 或大于参数的数量,返回 NULL。ELT() FIELD() 反运算:
mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
-> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
-> 'foo'
FIELD(str,str1,str2,str3,...)
返回 str 在列表 str1, str2, str3, ... 中的索引。如果 str 没有发现,返回 0。FIELD() 是 ELT() 的反运算:
mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 2
mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 0
FIND_IN_SET(str,strlist)
Returns a value 如果字符串 str 在由 N 个子串组成的列表 strlist 中,返回一个 1 到 N 的值。一个字符串列表是由通过字符 “,” 分隔的多个子串组成。如果第一个参数是一个常数字符串,并且第二个参数是一个 SET 列类型,FIND_IN_SET() 函数将被优化为使用位运算!如果 str 在不 strlist 中或者如果 strlist 是一个空串,返回值为 0。如果任何一个参数为 NULL,返回值也是 NULL。如果第一个参数包含一个 “,”,这个函数将完全不能工作:
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
MAKE_SET(bits,str1,str2,...)
返回一个集合 (包含由字符 “,” 分隔的多个子串组成的一个字符串),它由在 bits 集合中有相应的比特位的字符串组成。str1 对应于比特位 0,str2 对应比特位 1,等等。在 str1, str2, ... 中的 NULL 串不允许被添加到结果中:
mysql> SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
-> ''
EXPORT_SET(bits,on,off,[separator,[number_of_bits]])
返回一个字符串,对于在 'bits' 中的每个比特字位,你将得到一个 'on' 字符,而如果比特字位是一个清零比特位(reset bit)你将得到一个 'off' 字符串。每个字符串均被“分隔符”(缺省为“,”)分隔,并且只有 'number_of_bits'(缺省为 64) 个 'bits' 被使用:
mysql> SELECT EXPORT_SET(5,'Y','N',',',4)
-> Y,N,Y,N
示例(译者注):
mysql> select EXPORT_SET(1,'1','0','',5);
-> 10000
# 最左边第一位为 2 的 0 次幂
mysql> select EXPORT_SET(2,'1','0','',5);
-> 01000
# 最左边第一位为 2 的 0 次幂,第二位为 2 的 1 次幂
mysql> select EXPORT_SET(4,'1','0','',5);
-> 00100
# 最左边第一位为 2 的 0 次幂,第二位为 2 的 1 次幂,第三位为 2 的 2 次幂
mysql> select EXPORT_SET(15,'1','0','',5);
-> 11110
# 最左边第一位为 2 的 1 次幂,第四位为 2 的 3 次幂
mysql> select EXPORT_SET(16,'1','0','',5);
-> 00001
# 最左边第一位为 2 的 1 次幂,第五位为 2 的 4 次幂
# 以上结果在 MySQL 4.0.12 中测试通过
示例结束(译者注)
LCASE(str)
LOWER(str)
依照当前字符集设置映射(缺省为 ISO-8859-1 Latin1),将字符串 str 中的所有字符改变为小写,然后返回该值:
mysql> SELECT LCASE('QUADRATICALLY');
-> 'quadratically'
这个函数是多字节安全的。
UCASE(str)
UPPER(str)
依照当前字符集设置映射(缺省为 ISO-8859-1 Latin1),将字符串 str 中的所有字符改变为大写,然后返回该值:
mysql> SELECT UCASE('Hej');
-> 'HEJ'
这个函数是多字节安全的。
LOAD_FILE(file_name)
读入文件,并将文件内容作为一个字符串返回。这个文件必须在服务器上,必须指定文件完整的路径名,并且你必须有 FILE 权限。文件必须完全可读,并且小于 max_allowed_packet。 如果该文件不存在,或因为上面的任一原因而不能被读出,函数返回 NULL:
mysql> UPDATE tbl_name
SET blob_column=LOAD_FILE("/tmp/picture")
WHERE id=1;
如果你没有使用 MySQL 3.23,你不得不在你的应用程序中读取文件,以文件的信息创建一个 INSERT 语句,来更新数据库。如果你使用 MySQL++ 库,下面有一个示例,详细请查看 http://www.mysql.com/documentation/mysql++/mysql++-examples.html。
QUOTE(str)
引用一个字符串,并返回一个结果,该结果可作为一个适当转义过的数据值在一个 SQL 语句中使用。字符串被单引号包围着返回,并且在该字符串中每个单引号(“'”)、反斜线符号(“\”)、ASCII NUL 和 Control-Z 出现的地方,在该字符之前均被加上了一个反斜线。如果参数是 NULL,那么结果值是一个没有单引号包围的单词 “NULL”。 QUOTE 函数在 MySQL 4.0.3 中被加入。
mysql> SELECT QUOTE("Don't");
-> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
-> NULL
6.3.2.1 字符串比较函数
在必要的时候,MySQL 会自动地将数字转换到字符串,反之亦然:
mysql> SELECT 1+"1";
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test'
如果你希望明确地将一个数字转换为字符串,将它参数传递到 CONCAT() 中。
如果将一个二进制字符串作为参数传递给一个字符串函数,结果返回也是一个二进制字符串。一个数字被转换到字符串,该字符串被视为是一个二进制字符串。这仅仅会影响结果。
通常,只要字符串比较中的任何一个表达式是区分大小写的,比较就会以字母大小写敏感方式执行。
expr LIKE pat [ESCAPE 'escape-char']
使用 SQL 的简单的正规表达式进行比较的模式匹配。返回 1 (TRUE) 或 0 (FALSE)。可以在模式中使用下面所示的两个通配符字符与 LIKE 配合: 字符 含义
% 匹配任意多个字符,甚至是零个字符
_ 严格地匹配一个字符
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
下面两个语句表明,字符串比较是忽略大小写的,除非任一操作数是一个二进制字符串:
mysql> SELECT 'abc' LIKE 'ABC';
-> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
-> 0
LIKE 允许用在一个数字表达式上。(这是 MySQL 对 ANSI SQL LIKE 的扩展。)
mysql> SELECT 10 LIKE '1%';
-> 1
注意:因为 MySQL 在字符串中使用的是 C 的转义句法(例如 “\n”),所以在 LIKE 字符串中使用的任何一个 “\” 必须被双写。例如,为了查找 “\n”,必须以 “\\n” 形式指定它。为了查找 “\”,必须指定它为 “\\\\” (反斜线被语法分析器剥离一次,另一次在模式匹配时完成,留下一条单独的反斜线被匹配)。
expr NOT LIKE pat [ESCAPE 'escape-char']
相同于 NOT (expr LIKE pat [ESCAPE 'escape-char'])。
expr SOUNDS LIKE expr
等同于 SOUNDEX(expr)=SOUNDEX(expr) (适用于 MySQL 4.1 或更新的版本)。
expr REGEXP pat
expr RLIKE pat
依照模式 pat 对字符串表达式 expr 执行一个模式比较。模式可以是一个扩展的正则表达式。查看章节 G MySQL 正则表达式。Returns 如果表达式 expr 匹配 pat,返回 1,否则返回 0。RLIKE 是 REGEXP 的同义词,它提供了与 mSQL 的兼容。注意:MySQL 在字符串中使用的是 C 的转义句法(例如 “\n”),所以在 REGEXP 字符串中使用的任何一个 “\” 必须被双写。在 MySQL 3.23.4 中,REGEXP 对于正常的(不是二进制)字符串是忽略大小写的:
mysql> SELECT 'Monty!' REGEXP 'm%y%%';
-> 0
mysql> SELECT 'Monty!' REGEXP '.*';
-> 1
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
-> 1
mysql> SELECT "a" REGEXP "A", "a" REGEXP BINARY "A";
-> 1 0
mysql> SELECT "a" REGEXP "^[a-d]";
-> 1
当决定一个字符的类型时,REGEXP 和 RLIKE 使用当前使用的字符集(缺省为 ISO-8859-1 Latin1)。
expr NOT REGEXP pat
expr NOT RLIKE pat
等同于 NOT (expr REGEXP pat)。
STRCMP(expr1,expr2)
如果字符串是相同,STRCMP() 返回 0,如果第一个参数根据当前排序次序小于第二个参数,返回 -1,否则返回 1:
mysql> SELECT STRCMP('text', 'text2');
-> -1
mysql> SELECT STRCMP('text2', 'text');
-> 1
mysql> SELECT STRCMP('text', 'text');
-> 0
MATCH (col1,col2,...) AGAINST (expr)
MATCH (col1,col2,...) AGAINST (expr IN BOOLEAN MODE)
MATCH ... AGAINST() 用于全文搜索,返回在列 (col1,col2,...) 和查询 expr 之间文本的相关相似的尺度。相关性是一个正的浮点型数字。零相关性意味着不相似。MATCH ... AGAINST() 可用于 MySQL 3.23.23 或更新的版本中。IN BOOLEAN MODE 扩展在 MySQL 4.0.1 中被新加入。详细描述和使用范例,请查看 6.8 MySQL 全文搜索。
6.3.2.2 字母大小写敏感性
BINARY
BINARY 操作符将跟在它后面的字符串强制作为一个二进制字符串。这可以很容易地强制一个列的比较以字母大小写敏感方式进行,即使该列没有定义为 BINARY 或 BLOB?
mysql> SELECT "a" = "A";
-> 1
mysql> SELECT BINARY "a" = "A";
-> 0
BINARY string 是 CAST(string AS BINARY) 的缩写。查看章节 6.3.5 Cast 函数。BINARY 在 MySQL 3.23.0 中被加入。 注意,当将一个索引列强制为 BINARY 时,在某些语境中,MySQL 将不能有效地使用索引。
如果希望对一个 blob 进行忽略字母大小的比较时,你通常可以在比较前将它转换到大写:
SELECT 'A' LIKE UPPER(blob_col) FROM table_name;
我们计划不久推出在不同字符集间的转化,以使字符串比较有更好的伸缩性。
6.3.3 数字函数
6.3.3.1 算术运算
常用的算术操作符均是可用的。注意,如果两个参数均是整型,`-', `+' 和 `*' 以 BIGINT (64 位)精度运算并返回结果!如果一个参数是一个无符号的整数,另一个参数也是一个整数,结果也将是一个无符号整数。查看章节 6.3.5 Cast 函数。
+
加法:
mysql> SELECT 3+5;
-> 8
-
减法:
mysql> SELECT 3-5;
-> -2
*
乘法:
mysql> SELECT 3*5;
-> 15
mysql> SELECT 18014398509481984*18014398509481984.0;
-> 324518553658426726783156020576256.0
mysql> SELECT 18014398509481984*18014398509481984;
-> 0
最后一个表达式的结果是错误的,这是因为乘法结果超过了 64 位 BIGINT 计算范围。
/
除法:
mysql> SELECT 3/5;
-> 0.60
被 0 除将返回一个 NULL 结果:
mysql> SELECT 102/(1-1);
-> NULL
只有当在一个结果被转换到一个整数的语境中执行时,除法将会以 BIGINT 进行算术计算。
6.3.3.2 数学函数
所有的数学函数在发生错误的情况下,均返回 NULL。
-
一元减。 改变参数的符号:
mysql> SELECT - 2;
-> -2
注意,如果这个操作符被用于一个 BIGINT,返回值也是一个 BIGINT!这就意味着,应该避免在一个可能有值 -2^63 的整数上使用 - 操作符!
ABS(X)
返回 X 的绝对值:
mysql> SELECT ABS(2);
-> 2
mysql> SELECT ABS(-32);
-> 32
这个函数可安全地使用于 BIGINT 值。
SIGN(X)
以 -1、0 或 1 方式返回参数的符号,它取决于参数 X 是负数、0 或正数。
mysql> SELECT SIGN(-32);
-> -1
mysql> SELECT SIGN(0);
-> 0
mysql> SELECT SIGN(234);
-> 1
MOD(N,M)
%
取模 (就如 C 中的 % 操作符)。返回 N 被 M 除后的余数:
mysql> SELECT MOD(234, 10);
-> 4
mysql> SELECT 253 % 7;
-> 1
mysql> SELECT MOD(29,9);
-> 2
mysql> SELECT 29 MOD 9;
-> 2
这个函数可安全地使用于 BIGINT 值。最后一个示例可在 MySQL 4.1 中工作。
FLOOR(X)
返回不大于 X 的最大整数值:
mysql> SELECT FLOOR(1.23);
-> 1
mysql> SELECT FLOOR(-1.23);
-> -2
注意,返回值被转换为一个 BIGINT!
CEILING(X)
返回不小于 X 的最小整数:
mysql> SELECT CEILING(1.23);
-> 2
mysql> SELECT CEILING(-1.23);
-> -1
注意,返回值被转换为一个 BIGINT!
ROUND(X)
ROUND(X,D)
将参数 X 四舍五入到最近的整数,然后返回。两个参数的形式是将一个数字四舍五入到 D 个小数后返回。
mysql> SELECT ROUND(-1.23);
-> -1
mysql> SELECT ROUND(-1.58);
-> -2
mysql> SELECT ROUND(1.58);
-> 2
mysql> SELECT ROUND(1.298, 1);
-> 1.3
mysql> SELECT ROUND(1.298, 0);
-> 1
注意,当参数在两个整数之间时, ROUND() 的行为取决于 C 库的实现。某些取整到最近的偶数,总是向下取,总是向上取,也可能总是接近于零。如果你需要某种取整类型,应该使用一个明确定义的函数比如 TRUNCATE() 或 FLOOR() 代替。
DIV
整除。类似于 FLOOR(),但是它可安全地用于 BIGINT 值。
mysql> SELECT 5 DIV 2
-> 2
DIV 在 MySQL 4.1.0 中新加入。
EXP(X)
返回值 e (自然对数的底) 的 X 次方:
mysql> SELECT EXP(2);
-> 7.389056
mysql> SELECT EXP(-2);
-> 0.135335
LN(X)
返回 X 的自然对数:
mysql> SELECT LN(2);
-> 0.693147
mysql> SELECT LN(-2);
-> NULL
这个函数在 MySQL 4.0.3 被新加入。在 MySQL 中,它是 LOG(X) 的同义词。
LOG(X)
LOG(B,X)
如果以一个参数调用,它返回 X 的自然对数:
mysql> SELECT LOG(2);
-> 0.693147
mysql> SELECT LOG(-2);
-> NULL
如果以两个参数调用,这个函数返回 X 任意底 B 的对数:
mysql> SELECT LOG(2,65536);
-> 16.000000
mysql> SELECT LOG(1,100);
-> NULL
任意底选项在 MySQL 4.0.3 中被加入。LOG(B,X) 等价于 LOG(X)/LOG(B)。
LOG2(X)
返回 X 的以 2 为底的对数:
mysql> SELECT LOG2(65536);
-> 16.000000
mysql> SELECT LOG2(-100);
-> NULL
LOG2() 通常可以用于计数出一个数字需要多少个比特位用于存储它。这个函数在 MySQL 4.0.3 中被添加。在更早的版本中,可以使用 LOG(X)/LOG(2) 来代替它。
LOG10(X)
返回 X 以 10 为底的对数:
mysql> SELECT LOG10(2);
-> 0.301030
mysql> SELECT LOG10(100);
-> 2.000000
mysql> SELECT LOG10(-100);
-> NULL
POW(X,Y)
POWER(X,Y)
返回 X 的 Y 幂:
mysql> SELECT POW(2,2);
-> 4.000000
mysql> SELECT POW(2,-2);
-> 0.250000
SQRT(X)
返回 X 的非否平方根:
mysql> SELECT SQRT(4);
-> 2.000000
mysql> SELECT SQRT(20);
-> 4.472136
PI()
返回 PI 值(圆周率)。缺少显示 5 位小数,但是在 MySQL 内部,为 PI 使用全部的双精度。
mysql> SELECT PI();
-> 3.141593
mysql> SELECT PI()+0.000000000000000000;
-> 3.141592653589793116
COS(X)
返回 X 的余弦,在这里,X 以弧度给出:
mysql> SELECT COS(PI());
-> -1.000000
SIN(X)
返回 X 的正弦,在这里,X 以弧度给出:
mysql> SELECT SIN(PI());
-> 0.000000
TAN(X)
返回 X 的正切,在这里,X 以弧度给出:
mysql> SELECT TAN(PI()+1);
-> 1.557408
ACOS(X)
返回 X 的反余弦,更确切地说,返回余弦值为 X 的值。如果 X 不在 -1 到 1 之间的范围内,返回 NULL:
mysql> SELECT ACOS(1);
-> 0.000000
mysql> SELECT ACOS(1.0001);
-> NULL
mysql> SELECT ACOS(0);
-> 1.570796
ASIN(X)
返回 X 的反正弦,更确切地说,返回正弦值为 X 的值。如果 X 不在 -1 到 1 之间的范围内,返回 NULL:
mysql> SELECT ASIN(0.2);
-> 0.201358
mysql> SELECT ASIN('foo');
-> 0.000000
ATAN(X)
返回 X 的反正切, 更确切地说,返回正切值为 X 的值:
mysql> SELECT ATAN(2);
-> 1.107149
mysql> SELECT ATAN(-2);
-> -1.107149
ATAN(Y,X)
ATAN2(Y,X)
返回两个变量 X 和 Y 的反正切。它类似于计算 Y / X 的反正切,除了两个参数的符号用于决定结果的象限:
mysql> SELECT ATAN(-2,2);
-> -0.785398
mysql> SELECT ATAN2(PI(),0);
-> 1.570796
COT(X)
返回 X 的余切:
mysql> SELECT COT(12);
-> -1.57267341
mysql> SELECT COT(0);
-> NULL
RAND()
RAND(N)
返回一个范围在 0 到 1.0 之间的随机浮点值。如果一个整数参数 N 被指定,它被当做种子值使用(用于产生一个可重复的数值):
mysql> SELECT RAND();
-> 0.9233482386203
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND();
-> 0.63553050033332
mysql> SELECT RAND();
-> 0.70100469486881
在一个 ORDER BY 子句中,不可以使用 RAND() 值使用一个列,因为 ORDER BY 将多次重复计算列。从 MySQL 3.23 开始,你可以使用:SELECT * FROM table_name ORDER BY RAND(),这有利于得到一个来自 SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000 的集合中的随机样本。 注意,在一个 WHERE 子句中的 RAND() 将在每次 WHERE 执行时被重新计算。 RAND() 并不是预期完美的随机数发生器,但是可以代替做为产生特别的随机数一个快速的方法,这样便于在两个不同平台下的同一 MySQL 版本间移动。
LEAST(X,Y,...)
有两个或更多个参数,返回最小(最小值)的参数。参数使用下列规则进行比较:
如果返回值用于一个 INTEGER 语境,或所有的参数是整数值,它们作为整数比较。
如果返回值用于一个 REAL 语境,或所有的参数均是实数值,它们作为实数被比较。
如果任何一个参数是字母大小写敏感的,参数作为大小写敏感的字符串进行比较。
在其它情况下,参数做为忽略大小写的字符中进行比较:
mysql> SELECT LEAST(2,0);
-> 0
mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
-> 3.0
mysql> SELECT LEAST("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> SELECT GREATEST("B","A","C");
-> "C"
在早于 MySQL 3.22.5 的版本中,可以使用 MAX() 代替 GREATEST。
DEGREES(X)
将参数 X 从弧度转换为角度,然后返回:
mysql> SELECT DEGREES(PI());
-> 180.000000
RADIANS(X)
将参数 X 从角度转换为弧度,然后返回:
mysql> SELECT RADIANS(90);
-> 1.570796
TRUNCATE(X,D)
将数值 X 截到 D 个小数,然后返回。如果 D 为 0,结果将不包含小数点和小数部分:
mysql> SELECT TRUNCATE(1.223,1);
-> 1.2
mysql> SELECT TRUNCATE(1.999,1);
-> 1.9
mysql> SELECT TRUNCATE(1.999,0);
-> 1
mysql> SELECT TRUNCATE(-1.999,1);
-> -1.9
从 MySQL 3.23.51 开始,所有数字被四舍五入到零。 如果 D 是负数,那么数字的整个部分被对准零位输出:
mysql> SELECT TRUNCATE(122,-2);
-> 100
注意, 十进值小数在计算机中通常不以精确数字存储,而是双精度型的值,你可能会被下列结果所愚弄:
mysql> SELECT TRUNCATE(10.28*100,0);
-> 1027
上面结果的发生是因为 10.28 实际上是以某些像 10.2799999999999999 的形式被存储的。
6.3.4 日期和时间函数
对于每个类型的值范围以及日期和时间值有效指定格式,请查看章节 6.2.2 Date 和 Time 类型。
这里是一个使用日期函数的例子。下面的查询选择所有 date_col 值在最后 30 天内的记录。
mysql> SELECT something FROM tbl_name
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
DAYOFWEEK(date)
返回 date 的星期索引(1 = Sunday, 2 = Monday, ... 7 = Saturday)。索引值符合 ODBC 的标准。
mysql> SELECT DAYOFWEEK('1998-02-03');
-> 3
WEEKDAY(date)
返回 date 的星期索引(0 = Monday, 1 = Tuesday, ... 6 = Sunday):
mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
-> 1
mysql> SELECT WEEKDAY('1997-11-05');
-> 2
DAYOFMONTH(date)
返回 date 是一月中的第几天,范围为 1 到 31:
mysql> SELECT DAYOFMONTH('1998-02-03');
-> 3
DAYOFYEAR(date)
返回 date 是一年中的第几天,范围为 1 到 366:
mysql> SELECT DAYOFYEAR('1998-02-03');
-> 34
MONTH(date)
返回 date 中的月份,范围为 1 到 12:
mysql> SELECT MONTH('1998-02-03');
-> 2
DAYNAME(date)
返回 date 的星期名:
mysql> SELECT DAYNAME("1998-02-05");
-> 'Thursday'
MONTHNAME(date)
返回 date 的月份名:
mysql> SELECT MONTHNAME("1998-02-05");
-> 'February'
QUARTER(date)
返回 date 在一年中的季度,范围为 1 到 4:
mysql> SELECT QUARTER('98-04-01');
-> 2
WEEK(date)
WEEK(date,first)
对于星期日是一周中的第一天的场合,如果函数只有一个参数调用,返回 date 为一年的第几周,返回值范围为 0 到 53 (是的,可能有第 53 周的开始)。两个参数形式的 WEEK() 允许你指定一周是否以星期日或星期一开始,以及返回值为 0-53 还是 1-52。 这里的一个表显示第二个参数是如何工作的: 值 含义
0 一周以星期日开始,返回值范围为 0-53
1 一周以星期一开始,返回值范围为 0-53
2 一周以星期日开始,返回值范围为 1-53
3 一周以星期一开始,返回值范围为 1-53 (ISO 8601)
mysql> SELECT WEEK('1998-02-20');
-> 7
mysql> SELECT WEEK('1998-02-20',0);
-> 7
mysql> SELECT WEEK('1998-02-20',1);
-> 8
mysql> SELECT WEEK('1998-12-31',1);
-> 53
注意,在版本 4.0 中,WEEK(#,0) 被更改为匹配 USA 历法。 注意,如果一周是上一年的最后一周,当你没有使用 2 或 3 做为可选参数时,MySQL 将返回 0:
mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
-> 2000, 0
mysql> SELECT WEEK('2000-01-01',2);
-> 52
你可能会争辩说,当给定的日期值实际上是 1999 年的第 52 周的一部分时,MySQL 对 WEEK() 函数应该返回 52。我们决定返回 0 ,是因为我们希望该函数返回“在指定年份中是第几周”。当与其它的提取日期值中的月日值的函数结合使用时,这使得 WEEK() 函数的用法可靠。 如果你更希望能得到恰当的年-周值,那么你应该使用参数 2 或 3 做为可选参数,或者使用函数 YEARWEEK() :
mysql> SELECT YEARWEEK('2000-01-01');
-> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
-> 52
YEAR(date)
返回 date 的年份,范围为 1000 到 9999:
mysql> SELECT YEAR('98-02-03');
-> 1998
YEARWEEK(date)
YEARWEEK(date,first)
返回一个日期值是的哪一年的哪一周。第二个参数的形式与作用完全与 WEEK() 的第二个参数一致。注意,对于给定的日期参数是一年的第一周或最后一周的,返回的年份值可能与日期参数给出的年份不一致:
mysql> SELECT YEARWEEK('1987-01-01');
-> 198653
注意,对于可选参数 0 或 1,周值的返回值不同于 WEEK() 函数所返回值(0), WEEK() 根据给定的年语境返回周值。
HOUR(time)
返回 time 的小时值,范围为 0 到 23:
mysql> SELECT HOUR('10:05:03');
-> 10
MINUTE(time)
返回 time 的分钟值,范围为 0 到 59:
mysql> SELECT MINUTE('98-02-03 10:05:03');
-> 5
SECOND(time)
返回 time 的秒值,范围为 0 到 59:
mysql> SELECT SECOND('10:05:03');
-> 3
PERIOD_ADD(P,N)
增加 N 个月到时期 P(格式为 YYMM 或 YYYYMM)中。以 YYYYMM 格式返回值。 注意,期间参数 P 不是 一个日期值:
mysql> SELECT PERIOD_ADD(9801,2);
-> 199803
PERIOD_DIFF(P1,P2)
返回时期 P1 和 P2 之间的月数。P1 和 P2 应该以 YYMM 或 YYYYMM 指定。 注意,时期参数 P1 和 P2 不是 日期值:
mysql> SELECT PERIOD_DIFF(9802,199703);
-> 11
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)
这些函数执行日期的算术运算。ADDDATE() 和 SUBDATE() 分别是 DATE_ADD() 和 DATE_SUB() 的同义词。 在 MySQL 3.23 中,如果表达式的右边是一个日期值或一个日期时间型字段,你可以使用 + 和 - 代替 DATE_ADD() 和 DATE_SUB()(示例如下)。 参数 date 是一个 DATETIME 或 DATE 值,指定一个日期的开始。expr 是一个表达式,指定从开始日期上增加还是减去间隔值。expr 是一个字符串;它可以以一个 “-” 领头表示一个负的间隔值。type 是一个关键词,它标志着表达式以何格式被解释。 下表显示 type 和 expr 参数是如何关联的: type 值 expr 期望的格式
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
MONTH MONTHS
YEAR YEARS
MINUTE_SECOND "MINUTES:SECONDS"
HOUR_MINUTE "HOURS:MINUTES"
DAY_HOUR "DAYS HOURS"
YEAR_MONTH "YEARS-MONTHS"
HOUR_SECOND "HOURS:MINUTES:SECONDS"
DAY_MINUTE "DAYS HOURS:MINUTES"
DAY_SECOND "DAYS HOURS:MINUTES:SECONDS"
在 expr 的格式中,MySQL 允许任何字符作为定界符。表中所显示的是建议的定界字符。如果 date 参数是一个 DATE 值,并且计算的间隔仅仅有 YEAR、MONTH 和 DAY 部分(没有时间部分),那么返回值也是一个 DATE 值。否则返回值是一个 DATETIME 值:
mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
-> 1998-01-01 00:00:00
mysql> SELECT INTERVAL 1 DAY + "1997-12-31";
-> 1998-01-01
mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;
-> 1997-12-31 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
-> INTERVAL 1 SECOND);
-> 1998-01-01 00:00:00
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
-> INTERVAL 1 DAY);
-> 1998-01-01 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
-> INTERVAL "1:1" MINUTE_SECOND);
-> 1998-01-01 00:01:00
mysql> SELECT DATE_SUB("1998-01-01 00:00:00",
-> INTERVAL "1 1:1:1" DAY_SECOND);
-> 1997-12-30 22:58:59
mysql> SELECT DATE_ADD("1998-01-01 00:00:00",
-> INTERVAL "-1 10" DAY_HOUR);
-> 1997-12-30 14:00:00
mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
-> 1997-12-02
如果你指定了一个太短的间隔值(没有包括 type 关键词所期望的所有间隔部分),MySQL 假设你遗漏了间隔值的最左边部分。例如,如果指定一个 type 为 DAY_SECOND,那么 expr 值被期望包含天、小时、分钟和秒部分。如果你象 "1:10" 样指定一个值,MySQL 假设天和小时部分被遗漏了,指定的值代表分钟和秒。换句话说,"1:10" DAY_SECOND 被解释为等价于 "1:10" MINUTE_SECOND。这类似于 MySQL 解释 TIME 值为经过的时间而不是一天的时刻。 注意,如果依着包含一个时间部分的间隔增加或减少一个日期值,该日期值将被自动地转换到一个日期时间值:
mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 DAY);
-> 1999-01-02
mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 HOUR);
-> 1999-01-01 01:00:00
如果你使用了确定不正确的日期,返回结果将是 NULL。如果你增加 MONTH、YEAR_MONTH 或 YEAR,并且结果日期的天比新月份的最大天数还大,那么它将被调整到新月份的最大天数:
mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
-> 1998-02-28
注意,上面的例子中,单词 INTERVAL 和关键词 type 是不区分字母大小写的。
EXTRACT(type FROM date)
EXTRACT() 函数使用与 DATE_ADD() 或 DATE_SUB() 一致的间隔类型,但是它用于指定从日期中提取的部分,而不是进行日期算术运算。
mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
-> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
-> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
-> 20102
TO_DAYS(date)
给出一个日期 date,返回一个天数(从 0 年开始的天数):
mysql> SELECT TO_DAYS(950501);
-> 728779
mysql> SELECT TO_DAYS('1997-10-07');
-> 729669
TO_DAYS() 无意于使用先于格里高里历法(即现行的阳历)(1582)出现的值,因为它不考虑当历法改变时所遗失的天数。
FROM_DAYS(N)
给出一个天数 N,返回一个 DATE 值:
mysql> SELECT FROM_DAYS(729669);
-> '1997-10-07'
FROM_DAYS() 无意于使用先于格里高里历法(1582)出现的值,因为它不考虑当历法改变时所遗失的天数。
DATE_FORMAT(date,format)
依照 format 字符串格式化 date 值。下面的修饰符可被用于 format 字符串中: 修饰符 含义
%M 月的名字 (January..December)
%W 星期的名字 (Sunday..Saturday)
%D 有英文后缀的某月的第几天 (0th, 1st, 2nd, 3rd, etc.)
%Y 年份,数字的,4 位
%y 年份,数字的,2 位
%X 周值的年份,星期日是一个星期的第一天,数字的,4 位,与 '%V' 一同使用
%x 周值的年份,星期一是一个星期的第一天,数字的,4 位,与 '%v' 一同使用
%a 缩写的星期名 (Sun..Sat)
%d 月份中的天数,数字的 (00..31)
%e 月份中的天数,数字的 (0..31)
%m 月,数字的 (00..12)
%c 月,数字的 (0..12)
%b 缩写的月份名 (Jan..Dec)
%j 一年中的天数 (001..366)
%H 小时 (00..23)
%k 小时 (0..23)
%h 小时 (01..12)
%I 小时 (01..12)
%l 小时 (1..12)
%i 分钟,数字的 (00..59)
%r 时间,12 小时 (hh:mm:ss [AP]M)
%T 时间,24 小时 (hh:mm:ss)
%S 秒 (00..59)
%s 秒 (00..59)
%p AM 或 PM
%w 一周中的天数 (0=Sunday..6=Saturday)
%U 星期 (00..53),星期日是一个星期的第一天
%u 星期 (00..53),星期一是一个星期的第一天
%V 星期 (01..53),星期日是一个星期的第一天。与 '%X' 一起使用
%v 星期 (01..53),星期一是一个星期的第一天。与 '%x' 一起使用
%% 一个字母 “%”
所有其它的字符不经过解释,直接复制到结果中:
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
在 MySQL 3.23 中,在格式修饰符前需要字符 `%'。在更早的 MySQL 版本中,`%' 是可选的。 月份与天修饰符的范围从零开始的原因是,在 MySQL 3.23 中,它允许存储不完善的日期值(例如 '2004-00-00')。
TIME_FORMAT(time,format)
它的使用方法与上面的 DATE_FORMAT() 函数相似,但是 format 字符串只包含处理小时、分和秒的那些格式修饰符。使用其它的修饰符会产生一个 NULL 值或 0。
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
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
注意,函数 NOW() 在每个查询中只计算一次,也就是在查询开始执行时。这就是说,如果在一个单独的查询中多次引用了 NOW(),它只会给出值都是一个相同的时间。
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
如果调用时没有参数,以无符号的整数形式返回一个 Unix 时间戳(从 '1970-01-01 00:00:00' GMT 开始的秒数)。如果以一个参数 date 调用 UNIX_TIMESTAMP(),它将返回该参数值从 '1970-01-01 00:00:00' GMT 开始经过的秒数值。date 可以是一个 DATE 字符串,一个 DATETIME 字符串,一个 TIMESTAMP,或者以一个 YYMMDD 或 YYYYMMDD 显示的本地时间:
mysql> SELECT UNIX_TIMESTAMP();
-> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
当 UNIX_TIMESTAMP 被用于一个 TIMESTAMP 列时,函数直接返回一个内部的时间戳值,而不进行一个隐含地 “string-to-unix-timestamp” 转换。如果你传递一个超出范围的日期参数给 UNIX_TIMESTAMP() ,它将返回 0,但是请注意,MySQL 对其仅仅进行基本的检验(年范围 1970-2037,月份 01-12,日期 01-31)。 如果你希望减去 UNIX_TIMESTAMP() 列,你应该需要将结果强制转换为一有符号整数。查看章节 6.3.5 Cast 函数。
FROM_UNIXTIME(unix_timestamp [,format])
以 'YYYY-MM-DD HH:MM:SS' 或 YYYYMMDDHHMMSS 格式返回一个 unix_timestamp 参数值,返回值的形式取决于该函数使用于字符串还是数字语境。 如果 format 给出,返回值依 format 字符串被格式。format 可以包含与 DATE_FORMAT() 函数同样的修饰符。
mysql> SELECT FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
-> 19971004222300
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
'%Y %D %M %h:%i:%s %x');
-> '1997 23rd December 03:43:30 1997'
SEC_TO_TIME(seconds)
以 'HH:MM:SS' 或 HHMMSS 格式返回参数 seconds 被转换到时分秒后的值,返回值的形式取决于该函数使用于字符串还是数字语境:
mysql> SELECT SEC_TO_TIME(2378);
-> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
-> 3938
TIME_TO_SEC(time)
将参数 time 转换为秒数后返回:
mysql> SELECT TIME_TO_SEC('22:23:00');
-> 80580
mysql> SELECT TIME_TO_SEC('00:39:38');
-> 2378
6.3.5 Cast 函数
CAST 函数的句法如下:
CAST(expression AS type)
或
CONVERT(expression,type)
type 可以是下面的任一个:
BINARY
CHAR (4.0.6 中新加入)
DATE
DATETIME
SIGNED {INTEGER}
TIME
UNSIGNED {INTEGER}
CAST() 是 ANSI SQL99 的句法,CONVERT() 是 ODBC 的句法。
CAST 函数主要用于以特殊的 CREATE ... SELECT 形式建立一个列时:
CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);
CAST(string AS BINARY 与 BINARY string 是相同的。 CAST(expr AS CHAR 表示一个使用当前默认字符集的字符串。
为了将一个字符串转换成一个数字值,通常不需要做任何事情;只要将字符串值当做一个数字即可:
mysql> SELECT 1+'1';
-> 2
如果在一个字符串语境中使用一个数字,该数字会被自动地转换为一个 BINARY 字符串。
mysql> SELECT CONCAT("hello you ",2);
-> "hello you 2"
MySQL 支持两方是有符号的和无符号的 64 位值的算术运算。如果你使用一个数字操作符(比如 +),并且其中的一个操作数是 unsigned integer,那么结果将是无符号的。为了不考虑这些问题,你可以使用 SIGNED 和 UNSIGNED CAST 操作符来,它会分别地强制运算到一个有符号的或一个无符号的 64 位整数。
mysql> SELECT CAST(1-2 AS UNSIGNED)
-> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
-> -1
注意,如果任一个操作数是一个浮点值(在这种语境下,DECIMAL() 被当作是一个浮点数值),结果也将是一个浮点数值,并且结果不受上面的规则影响。
mysql> SELECT CAST(1 AS UNSIGNED) -2.0
-> -1.0
如果在一个算术运算中使用一个字符串,它将被转换为一个浮点数字。
函数 CAST() 和 CONVERT() 在 MySQL 4.0.2 中被加入。
为了完全支持 BIGINT,在 MySQL 4.0 中对无符号值的处理发生了改变。如果希望你的代码在 MySQL 4.0 和 3.23 中均能够正常运行(在这种情况下,你或许不能够使用 CAST 函数),当进行两个无符号整数列的减法时,你可以使用下面的技巧得到一个有符号的结果:
SELECT (unsigned_column_1+0.0)-(unsigned_column_2+0.0);
这个做法是在进行减法之前,先将无符号列转换为一个浮点数。
如果你将旧的 MySQL 应用程序移植到 MySQL 4.0 时,在 UNSIGNED 列上出现了问题,你可以在启动 mysqld 时使用 --sql-mode=NO_UNSIGNED_SUBTRACTION 选项。注意,只要你使用了这个选项,你将不能直接地使用 UNSIGNED BIGINT 列类型。
6.3.6 其它函数
6.3.6.1 位函数
MySQL 使用 BIGINT (64 位) 算法进行位运算,因而这些操作符有一个 64 位的最大范围。
|
位或
mysql> SELECT 29 | 15;
-> 31
返回值是一个 64 位的无符号整数。
&
位与
mysql> SELECT 29 & 15;
-> 13
返回值是一个 64 位的无符号整数。
^
位异或
mysql> SELECT 1 ^ 1;
-> 0
mysql> SELECT 1 ^ 0;
-> 1
mysql> SELECT 11 ^ 3;
-> 8
返回值是一个 64 位的无符号整数。 XOR 在 MySQL 4.0.2 中被加入。
<<
左移一个长长的数字(BIGINT):
mysql> SELECT 1 << 2;
-> 4
返回值是一个 64 位的无符号整数。
>>
右移一个长长的数字(BIGINT):
mysql> SELECT 4 >> 2;
-> 1
返回值是一个 64 位的无符号整数。
~
置反所有位:
mysql> SELECT 5 & ~1;
-> 4
返回值是一个 64 位的无符号整数。
BIT_COUNT(N)
返回在参数 N 中嵌入的比特位数量:
mysql> SELECT BIT_COUNT(29);
-> 4