12. 函数和操作符 · MySQL 5.1参考手册

### 第12章：函数和操作符 ** 目录** [ 12.1. 操作符](#)[ 12.1.1. 操作符优先级](#)[ 12.1.2. 圆括号](#)[ 12.1.3. 比较函数和操作符](#)[ 12.1.4. 逻辑操作符](#)[ 12.2. 控制流程函数](#)[ 12.3. 字符串函数](#)[ 12.3.1. 字符串比较函数](#)[ 12.4. 数值函数](#)[ 12.4.1. 算术操作符](#)[ 12.4.2. 数学函数](#)[ 12.5. 日期和时间函数](#)[ 12.6. MySQL使用什么日历？](#)[ 12.7. 全文搜索功能](#)[ 12.7.1. 布尔全文搜索](#)[ 12.7.2. 全文搜索带查询扩展](#)[ 12.7.3. 全文停止字](#)[ 12.7.4. 全文限定条件](#)[ 12.7.5. 微调MySQL全文搜索](#)[ 12.8. Cast函数和操作符](#)[ 12.9. 其他函数](#)[ 12.9.1. 位函数](#)[ 12.9.2. 加密函数](#)[ 12.9.3. 信息函数](#)[ 12.9.4. 其他函数](#)[ 12.10. 与GROUP BY子句同时使用的函数和修改程序``](#)[12.10.1. GROUP BY（聚合）函数](#)[12.10.2. GROUP BY修改程序](#)[12.10.3. 具有隐含字段的GROUP BY](#) 在SQL 语句中，表达式可用于一些诸如SELECT语句的ORDER BY 或HAVING子句、SELECT、 DELETE或 UPDATE语句的WHERE 子句或 SET语句之类的地方。使用文本值、column值、NULL值、函数、操作符来书写表达式。本章叙述了可用于书写MySQL表达式的函数和操作符。除非在文档编制中对一个函数或操作符另有指定的情况外，一个包含NULL 的表达式通常产生一个NULL 值。 **注释**：在默认状态下, 在函数和紧随其后的括号之间不得存在空格。这能帮助 MySQL 分析程序区分一些同函数名相同的函数调用以及表或列。不过，函数自变量周围允许有空格出现。可以通过选择--sql-mode=IGNORE_SPACE来打开MySQL服务器的方法使服务器接受函数名后的空格。个人客户端程序可通过选择mysql_real_connect()的CLIENT_IGNORE_SPACE 实现这一状态。在以上两种情况中，所有的函数名都成为保留字。请参见[5.3.2节，“SQL服务器模式”](# "5.3.2. The Server SQL Mode"). 为节省时间，本章中对大多数例子使用简写形式展示了 **mysql**程序的输出结果。对于以下格式的举例展示： mysql> **SELECT MOD(29,9);** +-----------+ | mod(29,9) | +-----------+ | 2 | +-----------+ 1 rows in set (0.00 秒) 使用如下格式进行代替： mysql> **SELECT MOD(29,9);** -> 2 ### 12.1. 操作符 [ 12.1.1. 操作符优先级](#)[ 12.1.2. 圆括号](#)[ 12.1.3. 比较函数和操作符](#)[ 12.1.4. 逻辑操作符](#) ### 12.1.1. 操作符优先级以下列表显示了操作符优先级的由低到高的顺序。排列在同一行的操作符具有相同的优先级。 := ||, OR, XOR &&, AND NOT BETWEEN, CASE, WHEN, THEN, ELSE =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN | & <<, >> -, + *, /, DIV, %, MOD ^ - (一元减号), ~ (一元比特反转) ! BINARY, COLLATE **注释**：假如 HIGH_NOT_PRECEDENCE SQL 模式被激活，则 NOT 的优先级同 the ! 操作符相同。请参见[5.3.2节，“SQL服务器模式”](# "5.3.2. The Server SQL Mode")。 ### 12.1.2. 圆括号 - ( ... ) 使用括弧来规定表达式的运算顺序，例如： ~~~ mysql> SELECT 1+2*3; -> 7 mysql> SELECT (1+2)*3; -> 9 ~~~ ### 12.1.3. 比较函数和操作符比较运算产生的结果为1(TRUE)、0 (FALSE)或 NULL。这些运算可用于数字和字符串。根据需要，字符串可自动转换为数字，而数字也可自动转换为字符串。本章中的一些函数 (如LEAST()和GREATEST()) 的所得值不包括 1 (TRUE)、 0 (FALSE)和 NULL。然而，其所得值乃是基于按照下述规则运行的比较运算： MySQL按照以下规则进行数值比较： - 若有一个或两个参数为 NULL，除非NULL-safe <=> 等算符，则比较运算的结果为NULL。 - 若同一个比较运算中的两个参数都是字符串，则按照字符串进行比较。 - 若两个参数均为整数，则按照整数进行比较。 - 十六进制值在不需要作为数字进行比较时，则按照二进制字符串进行处理。 - 假如参数中的一个为 TIMESTAMP 或 DATETIME 列，而其它参数均为常数，则在进行比较前将常数转为 timestamp。这样做的目的是为了使ODBC的进行更加顺利。注意，这不适合IN()中的参数!为了更加可靠，在进行对比时通常使用完整的 datetime/date/time字符串。 - 在其它情况下，参数作为浮点数进行比较。在默认状态下，字符串比较不区分大小写，并使用现有字符集(默认为cp1252 Latin1，同时对英语也适合)。为了进行比较，可使用CAST()函数将某个值转为另外一种类型。使用CONVERT()将字符串值转为不同的字符集。请参见[12.8节，“Cast函数和操作符”](# "12.8. Cast Functions and Operators")。以下例子说明了比较运算中将字符串转为数字的过程： mysql> ** SELECT 1 > '6x';** -> 0 mysql> ** SELECT 7 > '6x';** -> 1 mysql> ** SELECT 0 > 'x6';** -> 0 mysql> ** SELECT 0 = 'x6';** -> 1 注意，在将一个字符串列同一个数字进行比较时， MySQL 不能使用列中的索引进行快速查找。假如*str_col*是一个编入索引的字符串列，则在以下语句中，索引不能执行查找功能： SELECT * FROM *tbl_name* WHERE *str_col*=1; 其原因是许多不同的字符串都可被转换为数值 1: '1'、 ' 1'、 '1a'、 …… - = 等于： 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 - <=> NULL-safe equal.这个操作符和=操作符执行相同的比较操作，不过在两个操作码均为NULL时，其所得值为1而不为NULL，而当一个操作码为NULL时，其所得值为0而不为NULL。 mysql> ** SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;** -> 1, 1, 0 mysql> ** SELECT 1 = 1, NULL = NULL, 1 = NULL;** -> 1, NULL, NULL - <> != 不等于： 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 - IS * boolean_value* IS NOT *boolean_value* 根据一个布尔值来检验一个值，在这里，布尔值可以是TRUE、FALSE或UNKNOWN。 mysql> ** SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;** -> 1, 1, 1 mysql> ** SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;** -> 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 为了能够顺利的使用ODBC 程序工作，在使用IS NULL时，MySQL支持一下额外特性： - 在一个值产生后，立即运行一个以下格式的语句，从而找到最新AUTO_INCREMENT 值的所在行： o SELECT * FROM *tbl_name* WHERE *auto_col* IS NULL 当设置SQL_AUTO_IS_NULL=0时，这项操作无法运行。请参见[13.5.3节，“SET语法”](# "13.5.3. SET Syntax")。 - 对于命名为NOT NULL 的DATE 和DATETIME列，可通过使用如下的语句找到特定日期 '0000-00-00'： o 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*)。其它类型的转换根据本章开篇所述规律进行，且适用于3种参数中任意一种。 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*)。 · COALESCE(*value*,...) 返回值为列表当中的第一个非 NULL值，在没有非NULL 值得情况下返回值为 NULL 。 mysql> ** SELECT COALESCE(NULL,1);** -> 1 mysql> ** SELECT COALESCE(NULL,NULL,NULL);** -> NULL · GREATEST(*value1*,*value2*,...) 当有2或多个参数时，返回值为最大(最大值的)参数。比较参数所依据的规律同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' 在没有自变量为NULL的情况下，GREATEST()的返回值为NULL。 · *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 IN 列表中所列值的个数仅受限于 max_allowed_packet 值。为了同SQL 标准相一致，在左侧表达式为NULL的情况下，或是表中找不到匹配项或是表中一个表达式为NULL 的情况下，IN的返回值均为NULL。 IN() 语构也可用书写某些类型的子查询。请参见[13.2.8.3节，“使用ANY、IN和SOME进行子查询”](# "13.2.8.3. Subqueries with ANY, IN, and SOME")。 · *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 值对比通常是错误的。 ISNULL() 函数同 IS NULL比较操作符具有一些相同的特性。请参见有关IS NULL 的说明。 · INTERVAL(*N*,*N1*,*N2*,*N3*,...) 假如*N* < *N1**，*则返回值为0；假如*N* < *N2*等等，则返回值为1；假如*N*为NULL，则返回值为 -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 · LEAST(*value1*,*value2*,...) 在有两个或多个参数的情况下，返回值为最小 (最小值)参数。用一下规则将自变量进行对比： - 假如返回值被用在一个 INTEGER 语境中，或是所有参数均为整数值，则将其作为整数值进行比较。 - 假如返回值被用在一个 REAL语境中，或所有参数均为实值，则将其作为实值进行比较。 - 假如任意一个参数是一个区分大小写的字符串，则将参数按照区分大小写的字符串进行比较。 - 在其它情况下，将参数作为区分大小写的字符串进行比较。假如任意一个自变量为NULL，则 LEAST()的返回值为NULL 。 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> ** SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);** -> -9223372036854775808 发生这种情况的原因是MySQL在整数语境中读取9223372036854775808.0。整数表示法不利于保存数值，因此它包括一个带符号整数。 ### 12.1.4. 逻辑操作符在SQL中，所有逻辑操作符的求值所得结果均为 TRUE、FALSE或 NULL (UNKNOWN)。在 MySQL中，它们体现为 1 (TRUE)、 0 (FALSE)和 NULL。其大多数都与不同的数据库SQL通用，然而一些服务器对TRUE的返回值可能是任意一个非零值。 - NOT ! 逻辑 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 && 逻辑AND。当所有操作数均为非零值、并且不为NULL时，计算所得结果为 1 ，当一个或多个操作数为0 时，所得结果为 0 ，其余情况返回值为 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 - OR || 逻辑 OR。当两个操作数均为非 NULL值时，如有任意一个操作数为非零值，则结果为1，否则结果为0。当有一个操作数为NULL时，如另一个操作数为非零值，则结果为1，否则结果为 NULL 。假如两个操作数均为 NULL，则所得结果为 NULL。 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 逻辑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)和 b)。同样见 [ 12.1.1节，“操作符优先级”](# "12.1.1. Operator Precedence")。 ### 12.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。 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 一个CASE表达式的默认返回值类型是任何返回值的相容集合类型，但具体情况视其所在语境而定。如果用在字符串语境中，则返回结果味字符串。如果用在数字语境中，则返回结果为十进制值、实值或整数值。 - IF(*expr1*,*expr2*,*expr3*) 如果 * expr1*是TRUE (*expr1* <> 0 and *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表达式的结果类型。 *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() (这一点在其被储存到临时表时很重要 ) 的默认返回值类型按照以下方式计算： <table border="1" cellpadding="0" id="table1"><tr><td> 表达式</td> <td> 返回值</td> </tr><tr><td> expr2 或expr3 返回值为一个字符串。</td> <td> 字符串</td> </tr><tr><td> expr2 或expr3 返回值为一个浮点值。</td> <td> 浮点</td> </tr><tr><td> expr2 或 expr3 返回值为一个整数。 </td> <td> 整数</td> </tr></table> 假如*expr2*和*expr3* 都是字符串，且其中任何一个字符串区分大小写，则返回结果是区分大小写。 - 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' IFNULL(*expr1*,*expr2*)的默认结果值为两个表达式中更加“通用”的一个，顺序为STRING、 REAL或 INTEGER。假设一个基于表达式的表的情况，或MySQL必须在内存储器中储存一个临时表中IFNULL()的返回值： CREATE TABLE tmp SELECT IFNULL(1,'test') AS test；在这个例子中，测试列的类型为 CHAR(4)。 - NULLIF(*expr1*,*expr2*) 如果*expr1* = *expr2* 成立，那么返回值为NULL，否则返回值为 *expr1*。这和CASE WHEN *expr1* = *expr2* THEN NULL ELSE *expr1* END相同。 mysql> **SELECT NULLIF(1,1);** -> NULL mysql> **SELECT NULLIF(1,2);** -> 1 注意，如果参数不相等，则 MySQL 两次求得的值为 expr1 。 ### 12.3. 字符串函数 [ 12.3.1. 字符串比较函数](#) 假如结果的长度大于 max_allowed_packet 系统变量的最大值时，字符串值函数的返回值为NULL。请参见[7.5.2节，“调节服务器参数”](# "7.5.2. Tuning Server Parameters")。对于在字符串位置操作的函数，第一个位置的编号为 1。 - ASCII(*str*) 返回值为字符串*str*的最左字符的数值。假如*str*为空字符串，则返回值为 0 。假如*str*为NULL，则返回值为 NULL。 ASCII()用于带有从 0到255的数值的字符。 mysql> **SELECT ASCII('2');** -> 50 mysql> **SELECT ASCII(2);** -> 50 mysql> **SELECT ASCII('dx');** -> 100 见 ORD()函数。 - BIN(*N*) 返回值为*N*的二进制值的字符串表示，其中 *N*为一个longlong (BIGINT) 数字。这等同于 CONV(*N*,10,2)。假如*N*为NULL，则返回值为 NULL。 mysql> **SELECT BIN(12);** -> '1100' - BIT_LENGTH(*str*) 返回值为二进制的字符串*str *长度。 mysql> **SELECT BIT_LENGTH('text');** -> 32 - CHAR(*N*,... [USING *charset*]) CHAR()将每个参数*N*理解为一个整数，其返回值为一个包含这些整数的代码值所给出的字符的字符串。NULL值被省略。 mysql> **SELECT CHAR(77,121,83,81,'76');** -> 'MySQL' mysql> **SELECT CHAR(77,77.3,'77.3');** -> 'MMM' 大于 255的CHAR()参数被转换为多结果字符。例如，CHAR(256) 相当于 CHAR(1,0), 而CHAR(256*256) 则相当于 CHAR(1,0,0)： mysql> **SELECT HEX(CHAR(1,0)), HEX(CHAR(256));** +----------------+----------------+ | HEX(CHAR(1,0)) | HEX(CHAR(256)) | +----------------+----------------+ | 0100 | 0100 | +----------------+----------------+ mysql> **SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));** +------------------+--------------------+ | HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) | +------------------+--------------------+ | 010000 | 010000 | +------------------+--------------------+ CHAR()的返回值为一个二进制字符串。可选择使用USING语句产生一个给出的字符集中的字符串： mysql> **SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));** ~~~ mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8)); ~~~ ~~~ +---------------------+--------------------------------+ ~~~ ~~~ | CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) | ~~~ ~~~ +---------------------+--------------------------------+ ~~~ ~~~ | binary | utf8 | ~~~ ~~~ +---------------------+--------------------------------+ ~~~ 如果 USING已经产生，而结果字符串不符合给出的字符集，则会发出警告。同样，如果严格的SQL模式被激活，则CHAR()的结果会成为 NULL。 - CHAR_LENGTH(*str*) 返回值为字符串*str* 的长度，长度的单位为字符。一个多字节字符算作一个单字符。*对于一个*包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。 - CHARACTER_LENGTH(*str*) CHARACTER_LENGTH()是CHAR_LENGTH()的同义词。 - COMPRESS(*string_to_compress*) 压缩一个字符串。这个函数要求 MySQL已经用一个诸如zlib的压缩库压缩过。否则，返回值始终是NULL。UNCOMPRESS() 可将压缩过的字符串进行解压缩。 mysql> **SELECT LENGTH(COMPRESS(REPEAT('a',1000)));** -> 21 mysql> **SELECT LENGTH(COMPRESS(''));** -> 0 mysql> **SELECT LENGTH(COMPRESS('a'));** -> 13 mysql> **SELECT LENGTH(COMPRESS(REPEAT('a',16)));** -> 15 压缩后的字符串的内容按照以下方式存储： - 空字符串按照空字符串存储。 - 非空字符串未压缩字符串的四字节长度进行存储(首先为低字节),后面是压缩字符串。如果字符串以空格结尾，就会在后加一个"."号,以防止当结果值是存储在CHAR或VARCHAR类型的字段列时,出现自动把结尾空格去掉的现象。(不推荐使用 CHAR 或VARCHAR 来存储压缩字符串。最好使用一个 BLOB 列代替）。 - CONCAT(*str1*,*str2*,...) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。或许有一个或多个参数。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如： SELECT CONCAT(CAST(int_col AS CHAR), char_col) 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 With Separator ，是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' CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。 - CONV(*N*,*from_base*,*to_base*) 不同数基间转换数字。返回值为数字的*N*字符串表示，由*from_base*基转化为 *to_base *基。如有任意一个参数为NULL，则返回值为 NULL。自变量 *N* 被理解为一个整数，但是可以被指定为一个整数或字符串。最小基数为 2 ，而最大基数则为 36。 If *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' - 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' - EXPORT_SET(*bits*,*on*,*off*[,*separator*[,*number_of_bits*]]) 返回值为一个字符串，其中对于*bits*值中的每个位组，可以得到一个 *on* 字符串，而对于每个清零比特位，可以得到一个*off*字符串。*bits* 中的比特值按照从右到左的顺序接受检验 (由低位比特到高位比特)。字符串被分隔字符串分开(默认为逗号‘,’)，按照从左到右的顺序被添加到结果中。*number_of_bits*会给出被检验的二进制位数 (默认为 64)。 mysql> **SELECT EXPORT_SET(5,'Y','N',',',4);** -> 'Y,N,Y,N' mysql> **SELECT EXPORT_SET(6,'1','0',',',10);** -> '0,1,1,0,0,0,0,0,0,0' - FIELD(*str*,*str1*,*str2*,*str3*,...) 返回值为*str1*, *str2*, *str3*,……列表中的*str*指数。在找不到*str* 的情况下，返回值为 0 。如果所有对于FIELD() 的参数均为字符串，则所有参数均按照字符串进行比较。如果所有的参数均为数字，则按照数字进行比较。否则，参数按照双倍进行比较。如果*str*为NULL，则返回值为0 ，原因是NULL不能同任何值进行同等比较。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*) 假如字符串*str*在由*N* 子链组成的字符串列表*strlist* 中，则返回值的范围在 1 到 *N*之间。一个字符串列表就是一个由一些被‘,’符号分开的自链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则 FIND_IN_SET() 函数被优化，使用比特计算。如果*str*不在*strlist*或*strlist* 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。 mysql> **SELECT FIND_IN_SET('b','a,b,c,d');** -> 2 - FORMAT(*X*,*D*) 将number *X*设置为格式 '#,###,###.##', 以四舍五入的方式保留到小数点后*D*位, 而返回结果为一个字符串。详见[12.9.4节，“其他函数”](# "12.9.4. Miscellaneous Functions"). - HEX(*N_or_S*) 如果*N_OR_S*是一个数字，则返回一个十六进制值 *N 的*字符串表示，在这里， *N*是一个longlong (BIGINT)数。这相当于 CONV(N,10,16)。如果*N_OR_S*是一个字符串，则返回值为一个*N_OR_S*的十六进制字符串表示，其中每个*N_OR_S* 里的每个字符被转化为两个十六进制数字。 mysql> **SELECT HEX(255);** -> 'FF' mysql> **SELECT 0x616263;** -> 'abc' mysql> **SELECT HEX('abc');** -> 616263 - INSERT(*str*,*pos*,*len*,*newstr*) 返回字符串 *str*, 其子字符串起始于 *pos*位置和长期被字符串 *newstr*取代的*len*字符。如果*pos*超过字符串长度，则返回值为原始字符串。假如*len*的长度大于其它字符串的长度，则从位置*pos*开始替换。若任何一个参数为null，则返回值为NULL。 mysql> **SELECT INSERT('Quadratic', 3, 4, 'What');** -> 'QuWhattic' mysql> **SELECT INSERT('Quadratic', -1, 4, 'What');** -> 'Quadratic' mysql> **SELECT INSERT('Quadratic', 3, 100, 'What');** -> 'QuWhat' 这个函数支持多字节字元。 - INSTR(*str*,*substr*) 返回字符串 *str*中子字符串的第一个出现位置。这和LOCATE()的双参数形式相同，除非参数的顺序被颠倒。 mysql> **SELECT INSTR('foobarbar', 'bar');** -> 4 mysql> **SELECT INSTR('xbar', 'foobar');** -> 0 这个函数支持多字节字元，并且只有当至少有一个参数是二进制字符串时区分大小写。 - LCASE(*str*) LCASE() 是 LOWER()的同义词。 - LEFT(*str*,*len*) 返回从字符串*str*开始的*len*最左字符。 mysql> **SELECT LEFT('foobarbar', 5);** -> 'fooba' - LENGTH(*str*) 返回值为字符串*str*的长度，单位为字节。一个多字节字符算作多字节。这意味着对于一个包含5个2字节字符的字符串， LENGTH() 的返回值为 10, 而 CHAR_LENGTH()的返回值则为5。 mysql> **SELECT LENGTH('text');** -> 4 - LOAD_FILE(*file_name*) 读取文件并将这一文件按照字符串的格式返回。文件的位置必须在服务器上,你必须为文件制定路径全名，而且你还必须拥有FILE 特许权。文件必须可读取，文件容量必须小于 max_allowed_packet字节。若文件不存在，或因不满足上述条件而不能被读取，则函数返回值为 NULL。 mysql> **UPDATE *tbl_name*** SET *blob_column*=LOAD_FILE('/tmp/picture') WHERE id=1; - LOCATE(*substr*,*str*) , LOCATE(*substr*,*str*,*pos*) 第一个语法返回字符串 *str*中子字符串*substr*的第一个出现位置。第二个语法返回字符串 *str*中子字符串*substr*的第一个出现位置, 起始位置在*pos*。如若*substr*不在*str*中，则返回值为0。 mysql> **SELECT LOCATE('bar', 'foobarbar');** -> 4 mysql> **SELECT LOCATE('xbar', 'foobar');** -> 0 mysql> **SELECT LOCATE('bar', 'foobarbar',5);** -> 7 这个函数支持多字节字元，并且只有当至少有一个参数是二进制字符串时区分大小写。 - LOWER(*str*) 返回字符串 *str* 以及所有根据最新的字符集映射表变为小写字母的字符 (默认为 cp1252 Latin1)。 mysql> **SELECT LOWER('QUADRATICALLY');** -> 'quadratically' 这个函数支持多字节字元。 - LPAD(*str*,*len*,*padstr*) 返回字符串 *str*, 其左边由字符串*padstr*填补到*len* 字符长度。假如*str*的长度大于*len*, 则返回值被缩短至 *len* 字符。 mysql> **SELECT LPAD('hi',4,'??');** -> '??hi' mysql> **SELECT LPAD('hi',1,'??');** -> 'h' - LTRIM(*str*) 返回字符串 *str*，其引导空格字符被删除。 mysql> **SELECT LTRIM(' barbar');** -> 'barbar' 这个函数支持多字节字元。 - 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(1 | 4,'hello','nice',NULL,'world');** -> 'hello' mysql> **SELECT MAKE_SET(0,'a','b','c');** -> '' - MID(*str*,*pos*,*len*) MID(*str*,*pos*,*len*) 是 SUBSTRING(*str*,*pos*,*len*)的同义词。 - OCT(*N*) 返回一个 *N*的八进制值的字符串表示，其中 *N*是一个longlong (BIGINT)数。这等同于CONV(N,10,8)。若*N*为 NULL ，则返回值为NULL。 mysql> **SELECT OCT(12);** -> '14' - OCTET_LENGTH(*str*) OCTET_LENGTH() 是 LENGTH()的同义词。 - ORD(*str*) 若字符串*str*的最左字符是一个多字节字符，则返回该字符的代码，代码的计算通过使用以下公式计算其组成字节的数值而得出: ~~~ (1st byte code) ~~~ ~~~ + (2nd byte code × 256) ~~~ ~~~ + (3rd byte code × 2562) ... ~~~ 假如最左字符不是一个多字节字符，那么 ORD()和函数ASCII()返回相同的值。 mysql> **SELECT ORD('2');** -> 50 - POSITION(*substr* IN *str*) POSITION(*substr* IN *str*)是 LOCATE(*substr*,*str*)同义词。 - QUOTE(*str*) 引证一个字符串，由此产生一个在SQL语句中可用作完全转义数据值的结果。返回的字符串由单引号标注，每例都带有单引号 (‘'’)、反斜线符号 (‘\’)、 ASCII NUL以及前面有反斜线符号的Control-Z 。如果自变量的值为NULL, 则返回不带单引号的单词 “NULL”。 mysql> **SELECT QUOTE('Don\'t!');** -> 'Don\'t!' mysql> **SELECT QUOTE(NULL);** -> NULL - REPEAT(*str*,*count*) 返回一个由重复的字符串*str* 组成的字符串，字符串*str*的数目等于*count*。若 *count* <= 0,则返回一个空字符串。若*str*或 *count*为 NULL，则返回 NULL 。 mysql> **SELECT REPEAT('MySQL', 3);** -> 'MySQLMySQLMySQL' - REPLACE(*str*,*from_str*,*to_str*) 返回字符串*str*以及所有被字符串*to_str*替代的字符串*from_str*。 mysql> **SELECT REPLACE('www.mysql.com', 'w', 'Ww');** -> 'WwWwWw.mysql.com' 这个函数支持多字节字元。 - REVERSE(*str*) 返回字符串 *str*，顺序和字符顺序相反。 mysql> **SELECT REVERSE('abc');** -> 'cba' 这个函数支持多字节字元。 - RIGHT(*str*,*len*) 从字符串*str*开始，返回最右*len* 字符。 mysql> **SELECT RIGHT('foobarbar', 4);** -> 'rbar' 这个函数支持多字节字元。 - RPAD(*str*,*len*,*padstr*) 返回字符串*str*, 其右边被字符串 *padstr*填补至*len*字符长度。假如字符串*str*的长度大于 *len*,则返回值被缩短到与 *len*字符相同长度。 mysql> **SELECT RPAD('hi',5,'?');** -> 'hi???' mysql> **SELECT RPAD('hi',1,'?');** -> 'h' 这个函数支持多字节字元。 - RTRIM(*str*) 返回字符串 *str*，结尾空格字符被删去。 mysql> **SELECT RTRIM('barbar ');** -> 'barbar' 这个函数支持多字节字元。 - SOUNDEX(*str*) 从*str*返回一个soundex字符串。两个具有几乎同样探测的字符串应该具有同样的 soundex 字符串。一个标准的soundex 字符串的长度为4个字符，然而SOUNDEX() 函数会返回一个人以长度的字符串。可使用结果中的SUBSTRING() 来得到一个标准 soundex 字符串。在*str*中*，*会忽略所有未按照字母顺序排列的字符。所有不在A-Z范围之内的国际字母符号被视为元音字母。 mysql> **SELECT SOUNDEX('Hello');** -> 'H400' mysql> **SELECT SOUNDEX('Quadratically');** -> 'Q36324' **注意：**这个函数执行原始的Soundex算法，而非更加流行的加强版本(如D. Knuth所述)。其区别在于原始版本首先会删去元音，其次是重复，而加强版则首先删去重复，而后删去元音。 - *expr1* SOUNDS LIKE *expr2* 这相当于SOUNDEX(*expr1*) = SOUNDEX(*expr2*)。 - SPACE(*N*) 返回一个由*N*间隔符号组成的字符串。 mysql> **SELECT SPACE(6);** -> ' ' - SUBSTRING(*str*,*pos*) , SUBSTRING(*str* FROM *pos*) SUBSTRING(*str*,*pos*,*len*) , SUBSTRING(*str* FROM *pos* FOR *len*) 不带有*len*参数的格式从字符串*str*返回一个子字符串，起始于位置 *pos*。带有*len*参数的格式从字符串*str*返回一个长度同*len*字符相同的子字符串，起始于位置 *pos*。使用 FROM的格式为标准 SQL 语法。也可能对*pos*使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的*pos*字符，而不是字符串的开头位置。在以下格式的函数中可以对*pos*使用一个负值。 mysql> **SELECT SUBSTRING('Quadratically',5);** -> 'ratically' mysql> **SELECT SUBSTRING('foobarbar' FROM 4);** -> 'barbar' mysql> **SELECT SUBSTRING('Quadratically',5,6);** -> 'ratica' mysql> **SELECT SUBSTRING('Sakila', -3);** -> 'ila' mysql> **SELECT SUBSTRING('Sakila', -5, 3);** -> 'aki' mysql> **SELECT SUBSTRING('Sakila' FROM -4 FOR 2);** -> 'ki' 这个函数支持多字节字元。注意，如果对*len*使用的是一个小于1的值，则结果始终为空字符串。 SUBSTR()是 SUBSTRING()的同义词。 - SUBSTRING_INDEX(*str*,*delim*,*count*) 在定界符 *delim*以及*count*出现前，从字符串*str*返回自字符串。若*count*为正值,则返回最终定界符(从左边开始)左边的一切内容。若*count*为负值，则返回定界符（从右边开始）右边的一切内容。 mysql> **SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);** -> 'www.mysql' mysql> **SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);** -> 'mysql.com' 这个函数支持多字节字元。 - TRIM([{BOTH | LEADING | TRAILING} [*remstr*] FROM] *str*) TRIM(*remstr* FROM] *str*) 返回字符串 *str*，其中所有*remstr*前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或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' 这个函数支持多字节字元。 - UCASE(*str*) UCASE()是UPPER()的同义词。 - UNCOMPRESS(*string_to_uncompress*) 对经COMPRESS()函数压缩后的字符串进行解压缩。若参数为压缩值，则结果为 NULL。这个函数要求 MySQL 已被诸如zlib 之类的压缩库编译过。否则, 返回值将始终是 NULL。 mysql> **SELECT UNCOMPRESS(COMPRESS('any string'));** -> 'any string' mysql> **SELECT UNCOMPRESS('any string');** -> NULL - UNCOMPRESSED_LENGTH(*compressed_string*) 返回压缩字符串压缩前的长度。 mysql> **SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));** -> 30 - UNHEX(*str*) 执行从HEX(*str*)的反向操作。就是说，它将参数中的每一对十六进制数字理解为一个数字，并将其转化为该数字代表的字符。结果字符以二进制字符串的形式返回。 mysql> **SELECT UNHEX('4D7953514C');** -> 'MySQL' mysql> **SELECT 0x4D7953514C;** -> 'MySQL' mysql> **SELECT UNHEX(HEX('string'));** -> 'string' mysql> **SELECT HEX(UNHEX('1267'));** -> '1267' - UPPER(*str*) 返回字符串*str，* 以及根据最新字符集映射转化为大写字母的字符 (默认为cp1252 Latin1). mysql> **SELECT UPPER('Hej');** -> 'HEJ' 该函数支持多字节字元。 ### 12.3.1. 字符串比较函数根据，MySQL 会自动将数字转化为字符串，反之亦然。 mysql> **SELECT 1+'1';** -> 2 mysql> **SELECT CONCAT(2,' test');** -> '2 test' 若想要将数字明确地转化为字符串，可使用 CAST()或 CONCAT()函数： mysql> **SELECT 38.8, CAST(38.8 AS CHAR);** -> 38.8, '38.8' mysql> **SELECT 38.8, CONCAT(38.8);** -> 38.8, '38.8' CAST() 比较可取。. 若已经对一个字符串函数给定一个二进制字符串作为参数，则所得到的结果字符串也是一个二进制字符串。一个转化为字符串的数字被作为二进制字符串对待。这仅会对比较结果产生影响。一般而言, 若字符串比较中任意一个表达式是区分大小写的，则执行比较时也区分大小写。 - *expr* LIKE *pat* [ESCAPE '*escape-char*'] 模式匹配，使用SQL简单正规表达式比较。返回1 (TRUE) 或 0 (FALSE)。若 *expr*或 *pat*中任何一个为 NULL,则结果为 NULL。模式不需要为文字字符串。例如，可以被指定为一个字符串表达式或表列。在模式中可以同LIKE一起使用以下两种通配符： <table border="1" cellpadding="0" id="table2"><tr><td> 字符</td> <td> 说明</td> </tr><tr><td> %</td> <td> 匹配任何数目的字符，甚至包括零字符 </td> </tr><tr><td> _</td> <td> 只能匹配一种字符 </td> </tr></table> mysql> **SELECT 'David!' LIKE 'David_';** -> 1 mysql> **SELECT 'David!' LIKE '%D%v%';** -> 1 若要对通配符的文字实例进行检验, 可将转义字符放在该字符前面。如果没有指定 ESCAPE字符, 则假设为‘\’。 <table border="1" cellpadding="0" id="table3"><tr><td> 字符串</td> <td width="103"> 说明</td> </tr><tr><td> \%</td> <td colspan="2"> 匹配一个 ‘%’字符</td> </tr><tr><td> \_</td> <td colspan="2"> 匹配一个 ‘_’ 字符</td> </tr></table> mysql> **SELECT 'David!' LIKE 'David\_';** -> 0 mysql> **SELECT 'David_' LIKE 'David\_';** -> 1 要指定一个不同的转义字符,可使用ESCAPE语句： mysql> **SELECT 'David_' LIKE 'David|_' ESCAPE '|';** -> 1 转义序列可以为空，也可以是一个字符的长度。从 MySQL 5.1.2开始, 如若 NO_BACKSLASH_ESCAPES SQL模式被激活, 则该序列不能为空。以下两个语句举例说明了字符串比较不区分大小写，除非其中一个操作数为二进制字符串： mysql> **SELECT 'abc' LIKE 'ABC';** -> 1 mysql> **SELECT 'abc' LIKE BINARY 'ABC';** -> 0 在MySQL中, LIKE 允许出现在数字表达式中。 (这是标准SQL LIKE 的延伸）。 mysql> **SELECT 10 LIKE '1%';** -> 1 **注释**：由于 MySQL在字符串中使用 C转义语法(例如, 用‘\n’代表一个换行字符)，在LIKE字符串中，必须将用到的‘\’双写。例如，若要查找 ‘\n’, 必须将其写成 ‘\\n’。而若要查找 ‘\’, 则必须将其写成 it as ‘\\\\’;原因是反斜线符号会被语法分析程序剥离一次，在进行模式匹配时，又会被剥离一次，最后会剩下一个反斜线符号接受匹配。 - *expr* NOT LIKE *pat* [ESCAPE '*escape-char*'] 这相当于 NOT (*expr* LIKE *pat* [ESCAPE '*escape-char*'])。 - *expr* NOT REGEXP *pat**expr* NOT RLIKE *pat* 这相当于NOT (*expr* REGEXP *pat*)。 - *expr* REGEXP *pat**expr* RLIKE *pat* 执行字符串表达式 *expr* 和模式*pat* 的模式匹配。该模式可以被延伸为正规表达式。正规表达式的语法在[附录G：](#)[*MySQL正则表达式*](# "Appendix G. MySQL Regular Expressions")中有详细讨论。若*expr*匹配 *pat，*则返回 1; 否则返回0。若 *expr*或 *pat* 任意一个为 NULL, 则结果为 NULL。 RLIKE 是REGEXP的同义词, 作用是为mSQL 提供兼容性。模式不需要为文字字符串。例如,可以被指定为一个字符串表达式或表列。 **注释**：由于在字符串中， MySQL使用 C 转义语法 (例如, 用‘\n’来代表换行字符 ),在REGEXP字符串中必须将用到的‘\’ 双写。 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 使用当前字符集 (默认为cp1252 Latin1 )。 **警告：**这些操作符不支持多字节字元。 - STRCMP(*expr1*,*expr2*) 若所有的字符串均相同，则返回STRCMP()，若根据当前分类次序，第一个参数小于第二个，则返回 -1，其它情况返回 1 。 mysql> **SELECT STRCMP('text', 'text2');** -> -1 mysql> **SELECT STRCMP('text2', 'text');** -> 1 mysql> **SELECT STRCMP('text', 'text');** -> 0 在执行比较时，STRCMP() 使用当前字符集。这使得默认的比较区分大小写，当操作数中的一个或两个都是二进制字符串时除外。 ### 12.4. 数值函数 [ 12.4.1. 算术操作符](#)[ 12.4.2. 数学函数](#) ### 12.4.1. 算术操作符可使用常见的算术操作符。注意就 -、 +和 *而言, 若两个参数均为正数，则其计算结果的精确度为 BIGINT (64比特)，若其中一个参数为无符号整数，而其它参数也是整数, 则结果为无符号整数。请参见[12.8节，“Cast函数和操作符”](# "12.8. Cast Functions and Operators")。 - + 加号: mysql> **SELECT 3+5;** -> 8 - - 减号: mysql> **SELECT 3-5;** -> -2 - - 一元减号。更换参数符号。 mysql> **SELECT - 2;** -> -2 **注意**：若该操作符同一个BIGINT同时使用，则返回值也是一个BIGINT。这意味着你应当尽量避免对可能产生–263的整数使用 –。 - * 乘号: mysql> **SELECT 3*5;** -> 15 mysql> **SELECT 18014398509481984*18014398509481984.0;** -> 324518553658426726783156020576256.0 mysql> **SELECT 18014398509481984*18014398509481984;** -> 0 最后一个表达式的结果是不正确的。原因是整数相乘的结果超过了BIGINT 计算的 64比特范围。 (见[11.2节，“数值类型”](# "11.2. Numeric Types").) - / 除号: mysql> **SELECT 3/5;** -> 0.60 被零除的结果为 NULL： mysql> **SELECT 102/(1-1);** -> NULL 只有当执行的语境中，其结果要被转化为一个整数时，除法才会和 BIGINT 算法一起使用。 - DIV 整数除法。类似于 FLOOR()，然而使用BIGINT 算法也是可靠的。 mysql> **SELECT 5 DIV 2;** -> 2 ### 12.4.2. 数学函数若发生错误，所有数学函数会返回 NULL 。 - ABS(*X*) 返回*X*的绝对值。 mysql> **SELECT ABS(2);** -> 2 mysql> **SELECT ABS(-32);** -> 32 该函数支持使用BIGINT值。 - ACOS(*X*) 返回*X*反余弦, 即, 余弦是*X*的值。若*X*不在-1到 1的范围之内，则返回 NULL 。 mysql> **SELECT ACOS(1);** -> 0 mysql> **SELECT ACOS(1.0001);** -> NULL mysql> **SELECT ACOS(0);** -> 1.5707963267949 - ASIN(*X*) 返回*X*的反正弦，即，正弦为*X*的值。若*X* 若*X*不在-1到 1的范围之内，则返回 NULL 。 ~~~ mysql> SELECT ASIN(0.2); ~~~ ~~~ -> 0.20135792079033 ~~~ ~~~ mysql> SELECT ASIN('foo'); ~~~ ~~~ ~~~ ~~~ +-------------+ ~~~ ~~~ | ASIN('foo') | ~~~ ~~~ +-------------+ ~~~ ~~~ | 0 | ~~~ ~~~ +-------------+ ~~~ ~~~ 1 row in set, 1 warning (0.00 sec) ~~~ ~~~ ~~~ ~~~ mysql> SHOW WARNINGS; ~~~ ~~~ +---------+------+-----------------------------------------+ ~~~ ~~~ | Level | Code | Message | ~~~ ~~~ +---------+------+-----------------------------------------+ ~~~ ~~~ | Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' | ~~~ ~~~ +---------+------+-----------------------------------------+ ~~~ - ATAN(*X*) 返回*X*的反正切，即，正切为*X*的值。 mysql> **SELECT ATAN(2);** -> 1.1071487177941 mysql> **SELECT ATAN(-2);** -> -1.1071487177941 - ATAN(*Y*,*X*) , ATAN2(*Y*,*X*) 返回两个变量*X*及*Y*的反正切。它类似于 *Y*或 *X*的反正切计算, 除非两个参数的符号均用于确定结果所在象限。 mysql> **SELECT ATAN(-2,2);** -> -0.78539816339745 mysql> **SELECT ATAN2(PI(),0);** -> 1.5707963267949 - CEILING(*X*) CEIL(*X*) 返回不小于*X*的最小整数值。 mysql> **SELECT CEILING(1.23);** -> 2 mysql> **SELECT CEIL(-1.23);** -> -1 这两个函数的意义相同。注意返回值会被转化为一个BIGINT。 - COS(*X*) 返回*X*的余弦，其中*X*在弧度上已知。 mysql> **SELECT COS(PI());** -> -1 - COT(*X*) 返回*X*的余切。 mysql> **SELECT COT(12);** -> -1.5726734063977 mysql> **SELECT COT(0);** -> NULL - CRC32(*expr*) 计算循环冗余码校验值并返回一个 32比特无符号值。若参数为NULL ，则结果为 NULL。该参数应为一个字符串，而且在不是字符串的情况下会被作为字符串处理（若有可能）。 mysql> **SELECT CRC32('MySQL');** -> 3259397556 mysql> **SELECT CRC32('mysql');** -> 2501908538 - DEGREES(*X*) 返回参数 *X*, 该参数由弧度被转化为度。 mysql> **SELECT DEGREES(PI());** -> 180 mysql> **SELECT DEGREES(PI() / 2);** -> 90 - EXP(*X*) 返回e的*X*乘方后的值(自然对数的底)。 mysql> **SELECT EXP(2);** -> 7.3890560989307 mysql> **SELECT EXP(-2);** -> 0.13533528323661 mysql> **SELECT EXP(0);** -> 1 - FLOOR(*X*) 返回不大于*X*的最大整数值。 mysql> **SELECT FLOOR(1.23);** -> 1 mysql> **SELECT FLOOR(-1.23);** -> -2 注意，返回值会被转化为一个 BIGINT。 - FORMAT(*X*,*D*) 将数字*X*的格式写成'#,###,###.##'格式, 即保留小数点后 *D*位，而第D位的保留方式为四舍五入，然后将结果以字符串的形式返回。详见[12.9.4节，“其他函数”](# "12.9.4. Miscellaneous Functions")。 - LN(*X*) 返回*X*的自然对数,即,* X*相对于基数e 的对数。 mysql> **SELECT LN(2);** -> 0.69314718055995 mysql> **SELECT LN(-2);** -> NULL 这个函数同LOG(*X*)具有相同意义。 - LOG(*X*) LOG(*B*,*X*) 若用一个参数调用，这个函数就会返回*X*的自然对数。 mysql> **SELECT LOG(2);** -> 0.69314718055995 mysql> **SELECT LOG(-2);** -> NULL 若用两个参数进行调用，这个函数会返回*X*对于任意基数*B* 的对数。 mysql> **SELECT LOG(2,65536);** -> 16 mysql> **SELECT LOG(10,100);** -> 2 LOG(*B*,*X*) 就相当于 LOG(*X*) / LOG(*B*)。 - LOG2(*X*) 返回*X*的基数为2的对数。 mysql> **SELECT LOG2(65536);** -> 16 mysql> **SELECT LOG2(-100);** -> NULL 对于查出存储一个数字需要多少个比特，LOG2()非常有效。这个函数相当于表达式 LOG(*X*) / LOG(2)。 - LOG10(*X*) 返回*X*的基数为10的对数。 mysql> **SELECT LOG10(2);** -> 0.30102999566398 mysql> **SELECT LOG10(100);** -> 2 mysql> **SELECT LOG10(-100);** -> NULL LOG10(*X*)相当于LOG(10,*X*)。 - MOD(*N*,*M*) , *N* % *M**N* MOD *M* 模操作。返回*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 值。 MOD() 对于带有小数部分的数值也起作用，它返回除法运算后的精确余数： mysql> **SELECT MOD(34.5,3);** -> 1.5 - PI() 返回 ϖ (pi)的值。默认的显示小数位数是7位,然而 MySQL内部会使用完全双精度值。 mysql> **SELECT PI();** -> 3.141593 mysql> **SELECT PI()+0.000000000000000000;** -> 3.141592653589793116 - POW(*X*,*Y*) , POWER(*X*,*Y*) 返回*X*的*Y*乘方的结果值。 mysql> **SELECT POW(2,2);** -> 4 mysql> **SELECT POW(2,-2);** -> 0.25 - RADIANS(*X*) 返回由度转化为弧度的参数 *X*, (注意 ϖ弧度等于180度）。 mysql> **SELECT RADIANS(90);** -> 1.5707963267949 - RAND() RAND(*N*) 返回一个随机浮点值 *v* ，范围在 0 到1 之间 (即, 其范围为 0 ≤ *v*≤ 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 mysql> **SELECT RAND(20);** -> 0.15888261251047 若要在*i*≤ *R*≤ *j*这个范围得到一个随机整数*R*，需要用到表达式 FLOOR(*i* + RAND() * (*j*– *i* + 1))。例如，若要在7 到 12 的范围（包括7和12）内得到一个随机整数, 可使用以下语句： SELECT FLOOR(7 + (RAND() * 6)); 在ORDER BY语句中，不能使用一个带有RAND()值的列，原因是 ORDER BY 会计算列的多重时间。然而，可按照如下的随机顺序检索数据行： mysql> **SELECT * FROM *tbl_name* ORDER BY RAND();** ORDER BY RAND()同 LIMIT 的结合从一组列中选择随机样本很有用： 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*, 其值接近于最近似的整数。在有两个参数的情况下，返回 *X*，其值保留到小数点后*D*位，而第*D*位的保留方式为四舍五入。若要接保留*X*值小数点左边的*D*位，可将 *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 mysql> **SELECT ROUND(23.298, -1);** -> 20 返回值的类型同第一个自变量相同(假设它是一个整数、双精度数或小数)。这意味着对于一个整数参数,结果也是一个整数(无小数部分)。当第一个参数是十进制常数时，对于准确值参数，ROUND() 使用精密数学题库： - 对于准确值数字, ROUND() 使用“四舍五入” 或“舍入成最接近的数” 的规则:对于一个分数部分为 .5或大于 .5的值，正数则上舍入到邻近的整数值，负数则下舍入临近的整数值。(换言之, 其舍入的方向是数轴上远离零的方向）。对于一个分数部分小于.5 的值，正数则下舍入下一个整数值，负数则下舍入邻近的整数值，而正数则上舍入邻近的整数值。 - 对于近似值数字，其结果根据C 库而定。在很多系统中，这意味着 ROUND()的使用遵循“舍入成最接近的偶数”的规则：一个带有任何小数部分的值会被舍入成最接近的偶数整数。以下举例说明舍入法对于精确值和近似值的不同之处： mysql> **SELECT ROUND(2.5), ROUND(25E-1);** +------------+--------------+ | ROUND(2.5) | ROUND(25E-1) | +------------+--------------+ | 3 | 2 | +------------+--------------+ 详见[第24章：](#)[*精度数学*](# "Chapter 24. Precision Math")。 - SIGN(*X*) 返回参数作为-1、 0或1的符号，该符号取决于*X*的值为负、零或正。 mysql> **SELECT SIGN(-32);** -> -1 mysql> **SELECT SIGN(0);** -> 0 mysql> **SELECT SIGN(234);** -> 1 - SIN(*X*) 返回*X*正弦，其中 *X* 在弧度中被给定。 mysql> **SELECT SIN(PI());** -> 1.2246063538224e-16 mysql> **SELECT ROUND(SIN(PI()));** -> 0 - SQRT(*X*) 返回非负数*X*的二次方根。 mysql> **SELECT SQRT(4);** -> 2 mysql> **SELECT SQRT(20);** -> 4.4721359549996 mysql> **SELECT SQRT(-16);** -> NULL - TAN(*X*) 返回*X*的正切，其中*X* 在弧度中被给定。 mysql> **SELECT TAN(PI());** -> -1.2246063538224e-16 mysql> **SELECT TAN(PI()+1);** -> 1.5574077246549 - TRUNCATE(*X*,*D*) 返回被舍去至小数点后*D*位的数字*X*。若*D*的值为 0, 则结果不带有小数点或不带有小数部分。可以将D设为负数,若要截去(归零) X小数点左起第D位开始后面所有低位的值.* * 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> **SELECT TRUNCATE(122,-2);** -> 100 mysql> **SELECT TRUNCATE(10.28*100,0);** -> 1028 所有数字的舍入方向都接近于零。 ### 12.5. 日期和时间函数本章论述了一些可用于操作时间值的函数。关于每个时间和日期类型具有的值域及指定值的有效格式，请参见[11.3节，“日期和时间类型”](# "11.3. Date and Time Types")。下面的例子使用了时间函数。以下询问选择了最近的 30天内所有带有date_col 值的记录： mysql> **SELECT *something* FROM *tbl_name*** -> **WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= *date_col*;** 注意，这个询问也能选择将来的日期记录。用于日期值的函数通常会接受时间日期值而忽略时间部分。而用于时间值的函数通常接受时间日期值而忽略日期部分。返回各自当前日期或时间的函数在每次询问执行开始时计算一次。这意味着在一个单一询问中，对诸如NOW() 的函数多次访问总是会得到同样的结果(未达到我们的目的，单一询问也包括对存储程序或触发器和被该程序/触发器调用的所有子程序的调用 )。这项原则也适用于 CURDATE()、 CURTIME()、 UTC_DATE()、 UTC_TIME()、UTC_TIMESTAMP()，以及所有和它们意义相同的函数。 CURRENT_TIMESTAMP()、 CURRENT_TIME()、 CURRENT_DATE()以及FROM_UNIXTIME()函数返回连接当前时区内的值，这个值可用作time_zone系统变量的值。此外， UNIX_TIMESTAMP() 假设其参数为一个当前时区的时间日期值。请参见[5.10.8节，“MySQL服务器时区支持”](# "5.10.8. MySQL Server Time Zone Support")。以下函数的论述中返回值的范围会请求完全日期。若一个日期为“零” 值，或者是一个诸如'2001-11-00'之类的不完全日期，提取部分日期值的函数可能会返回 0。例如， DAYOFMONTH('2001-11-00') 会返回0。 - ADDDATE(*date*,INTERVAL *expr**type*) ADDDATE(*expr*,*days*) 当被第二个参数的INTERVAL格式激活后， ADDDATE()就是DATE_ADD()的同义词。相关函数SUBDATE() 则是DATE_SUB()的同义词。对于INTERVAL参数上的信息，请参见关于DATE_ADD()的论述。 mysql> **SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);** -> '1998-02-02' mysql> **SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);** -> '1998-02-02' 若 *days*参数只是整数值，则 MySQL 5.1将其作为天数值添加至 *expr*。 mysql> **SELECT ADDDATE('1998-01-02', 31);** -> '1998-02-02' - ADDTIME(*expr*,*expr2*) ADDTIME()将 *expr2*添加至*expr*然后返回结果。 *expr* 是一个时间或时间日期表达式，而*expr2*是一个时间表达式。 mysql> **SELECT ADDTIME('1997-12-31 23:59:59.999999',** -> **'1 1:1:1.000002');** -> '1998-01-02 01:01:01.000001' mysql> **SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');** -> '03:00:01.999997' - CONVERT_TZ(*dt*,*from_tz*,*to_tz*) CONVERT_TZ() 将时间日期值*dt*从*from_tz* 给出的时区转到*to_tz*给出的时区，然后返回结果值。关于可能指定的时区的详细论述，请参见[5.10.8节，“MySQL服务器时区支持”](# "5.10.8. MySQL Server Time Zone Support")。若自变量无效，则这个函数会返回 NULL。在从若*from_tz*到UTC的转化过程中，该值超出 TIMESTAMP 类型的被支持范围，那么转化不会发生。关于 TIMESTAMP 范围的论述，请参见[11.1.2节，“日期和时间类型概述”](# "11.1.2. Overview of Date and Time Types")。 mysql> **SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');** -> '2004-01-01 13:00:00' mysql> **SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');** -> '2004-01-01 22:00:00' **注释**：若要使用诸如 'MET'或 'Europe/Moscow'之类的指定时间区，首先要设置正确的时区表。详细说明见[5.10.8节，“MySQL服务器时区支持”](# "5.10.8. MySQL Server Time Zone Support")。 - CURDATE() 将当前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回，具体格式根据函数用在字符串或是数字语境中而定。 mysql> **SELECT CURDATE();** -> '1997-12-15' mysql> **SELECT CURDATE() + 0;** -> 19971215 - CURRENT_DATE CURRENT_DATE() CURRENT_DATE和CURRENT_DATE()是的同义词. - CURTIME() 将当前时间以'HH:MM:SS'或 HHMMSS 的格式返回，具体格式根据函数用在字符串或是数字语境中而定。 mysql> **SELECT CURTIME();** -> '23:50:26' mysql> **SELECT CURTIME() + 0;** -> 235026 - CURRENT_TIME, CURRENT_TIME() CURRENT_TIME 和CURRENT_TIME() 是CURTIME()的同义词。 - CURRENT_TIMESTAMP, CURRENT_TIMESTAMP() CURRENT_TIMESTAMP和 CURRENT_TIMESTAMP()是NOW()的同义词。 - DATE(*expr*) 提取日期或时间日期表达式*expr*中的日期部分。 mysql> **SELECT DATE('2003-12-31 01:02:03');** -> '2003-12-31' - DATEDIFF(*expr*,*expr2*) DATEDIFF() 返回起始时间 *expr*和结束时间*expr2*之间的天数。*Expr*和*expr2*为日期或 date-and-time 表达式。计算中只用到这些值的日期部分。 mysql> **SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');** -> 1 mysql> **SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');** -> -31 - DATE_ADD(*date*,INTERVAL *expr**type*) DATE_SUB(*date*,INTERVAL *expr**type*) 这些函数执行日期运算。 *date* 是一个 DATETIME 或DATE值，用来指定起始时间。 *expr* 是一个表达式，用来指定从起始日期添加或减去的时间间隔值。 *Expr*是一个字符串;对于负值的时间间隔，它可以以一个 ‘-’开头。 *type*为关键词，它指示了表达式被解释的方式。关键词INTERVA及 *type*分类符均不区分大小写。以下表显示了*type*和*expr*参数的关系： <table border="1" cellpadding="0" id="table4"><tr><td> type 值</td> <td> 预期的 expr 格式</td> </tr><tr><td> MICROSECOND</td> <td> MICROSECONDS</td> </tr><tr><td> SECOND</td> <td> SECONDS</td> </tr><tr><td> MINUTE</td> <td> MINUTES</td> </tr><tr><td> HOUR</td> <td> HOURS</td> </tr><tr><td> DAY</td> <td> DAYS</td> </tr><tr><td> WEEK</td> <td> WEEKS</td> </tr><tr><td> MONTH</td> <td> MONTHS</td> </tr><tr><td> QUARTER</td> <td> QUARTERS</td> </tr><tr><td> YEAR</td> <td> YEARS</td> </tr><tr><td> SECOND_MICROSECOND</td> <td> 'SECONDS.MICROSECONDS'</td> </tr><tr><td> MINUTE_MICROSECOND</td> <td> 'MINUTES.MICROSECONDS'</td> </tr><tr><td> MINUTE_SECOND</td> <td> 'MINUTES:SECONDS'</td> </tr><tr><td> HOUR_MICROSECOND</td> <td> 'HOURS.MICROSECONDS'</td> </tr><tr><td> HOUR_SECOND</td> <td> 'HOURS:MINUTES:SECONDS'</td> </tr><tr><td> HOUR_MINUTE</td> <td> 'HOURS:MINUTES'</td> </tr><tr><td> DAY_MICROSECOND</td> <td> 'DAYS.MICROSECONDS'</td> </tr><tr><td> DAY_SECOND</td> <td> 'DAYS HOURS:MINUTES:SECONDS'</td> </tr><tr><td> DAY_MINUTE</td> <td> 'DAYS HOURS:MINUTES'</td> </tr><tr><td> DAY_HOUR</td> <td> 'DAYS HOURS'</td> </tr><tr><td> YEAR_MONTH</td> <td> 'YEARS-MONTHS'</td> </tr></table> MySQL 允许任何*expr*格式中的标点分隔符。表中所显示的是建议的分隔符。若 *date*参数是一个 DATE 值，而你的计算只会包括 YEAR、MONTH和DAY部分(即, 没有时间部分), 其结果是一个DATE 值。否则，结果将是一个 DATETIME值。若位于另一端的表达式是一个日期或日期时间值，则INTERVAL *expr**type*只允许在 + 操作符的两端。对于 –操作符， INTERVAL *expr**type*只允许在其右端，原因是从一个时间间隔中提取一个日期或日期时间值是毫无意义的。 (见下面的例子）。 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' mysql> **SELECT DATE_ADD('1992-12-31 23:59:59.000002',** -> **INTERVAL '1.999999' SECOND_MICROSECOND);** -> '1993-01-01 00:00:01.000001' 若你指定了一个过于短的时间间隔值 (不包括*type*关键词所预期的所有时间间隔部分), MySQL 假定你已经省去了时间间隔值的最左部分。例如，你指定了一种类型的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' - DATE_FORMAT(*date*,*format*) 根据*format*字符串安排*date* 值的格式。以下说明符可用在 *format*字符串中： <table border="1" cellpadding="0" width="531"><tr><td width="59"> 说明符</td> <td> 说明</td> </tr><tr><td width="59"> %a</td> <td> 工作日的缩写名称 (Sun..Sat)</td> </tr><tr><td width="59"> %b</td> <td> 月份的缩写名称 (Jan..Dec)</td> </tr><tr><td width="59"> %c</td> <td> 月份，数字形式(0..12)</td> </tr><tr><td width="59"> %D</td> <td> 带有英语后缀的该月日期 (0th, 1st, 2nd, 3rd, ...)</td> </tr><tr><td width="59"> %d</td> <td> 该月日期, 数字形式 (00..31)</td> </tr><tr><td width="59"> %e</td> <td> 该月日期, 数字形式(0..31)</td> </tr><tr><td width="59"> %f</td> <td> 微秒 (000000..999999)</td> </tr><tr><td width="59"> %H</td> <td> 小时(00..23)</td> </tr><tr><td width="59"> %h</td> <td> 小时(01..12)</td> </tr><tr><td width="59"> %I</td> <td> 小时 (01..12)</td> </tr><tr><td width="59"> %i</td> <td> 分钟,数字形式 (00..59)</td> </tr><tr><td width="59"> %j</td> <td> 一年中的天数 (001..366)</td> </tr><tr><td width="59"> %k</td> <td> 小时 (0..23)</td> </tr><tr><td width="59"> %l</td> <td> 小时 (1..12)</td> </tr><tr><td width="59"> %M</td> <td> 月份名称 (January..December)</td> </tr><tr><td width="59"> %m</td> <td> 月份, 数字形式 (00..12)</td> </tr><tr><td width="59"> %p</td> <td> 上午（AM）或下午（ PM）</td> </tr><tr><td width="59"> %r</td> <td> 时间 , 12小时制 (小时hh:分钟mm:秒数ss 后加 AM或PM)</td> </tr><tr><td width="59"> %S</td> <td> 秒 (00..59)</td> </tr><tr><td width="59"> %s</td> <td> 秒 (00..59)</td> </tr><tr><td width="59"> %T</td> <td> 时间 , 24小时制 (小时hh:分钟mm:秒数ss)</td> </tr><tr><td width="59"> %U</td> <td> 周 (00..53), 其中周日为每周的第一天 </td> </tr><tr><td width="59"> %u</td> <td> 周 (00..53), 其中周一为每周的第一天 </td> </tr><tr><td width="59"> %V</td> <td> 周 (01..53), 其中周日为每周的第一天 ; 和 %X同时使用</td> </tr><tr><td width="59"> %v</td> <td> 周 (01..53), 其中周一为每周的第一天 ; 和 %x同时使用</td> </tr><tr><td width="59"> %W</td> <td> 工作日名称 (周日..周六)</td> </tr><tr><td width="59"> %w</td> <td> 一周中的每日 (0=周日..6=周六)</td> </tr><tr><td width="59"> %X</td> <td> 该周的年份，其中周日为每周的第一天, 数字形式,4位数;和%V同时使用</td> </tr><tr><td width="59"> %x</td> <td> 该周的年份，其中周一为每周的第一天, 数字形式,4位数;和%v同时使用 </td> </tr><tr><td width="59"> %Y</td> <td> 年份, 数字形式,4位数</td> </tr><tr><td width="59"> %y</td> <td> 年份, 数字形式 (2位数)</td> </tr><tr><td width="59"> %%</td> <td> ‘%’文字字符</td> </tr></table> 所有其它字符都被复制到结果中，无需作出解释。注意， ‘%’字符要求在格式指定符之前。月份和日期说明符的范围从零开始，原因是 MySQL允许存储诸如 '2004-00-00'的不完全日期. ~~~ 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' ~~~ - DAY(*date*) DAY() 和DAYOFMONTH()的意义相同。 - DAYNAME(*date*) 返回*date*对应的工作日名称。 mysql> **SELECT DAYNAME('1998-02-05');** -> '周四' - DAYOFMONTH(*date*) 返回*date*对应的该月日期，范围是从 1到31。 mysql> **SELECT DAYOFMONTH('1998-02-03');** -> 3 - DAYOFWEEK(*date*) 返回*date* (1 = 周日, 2 = 周一, ..., 7 = 周六)对应的工作日索引。这些索引值符合 ODBC标准。 mysql> **SELECT DAYOFWEEK('1998-02-03');** -> 3 - DAYOFYEAR(*date*) 返回*date*对应的一年中的天数，范围是从 1到366。 mysql> **SELECT DAYOFYEAR('1998-02-03');** -> 34 - 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 mysql> **SELECT EXTRACT(MICROSECOND** -> **FROM '2003-01-02 10:30:00.00123');** -> 123 - FROM_DAYS(*N*) 给定一个天数 *N*, 返回一个DATE值。 mysql> **SELECT FROM_DAYS(729669);** -> '1997-10-07' 使用 FROM_DAYS()处理古老日期时，务必谨慎。他不用于处理阳历出现前的日期(1582)。请参见[12.6节，“MySQL使用什么日历？”](# "12.6. What Calendar Is Used By MySQL?")。 - FROM_UNIXTIME(*unix_timestamp*) , 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');** -> '2003 6th August 06:22:58 2003' - GET_FORMAT(DATE|TIME|DATETIME, 'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL') 返回一个格式字符串。这个函数在同DATE_FORMAT() 及STR_TO_DATE()函数结合时很有用。第一个参数的3个可能值和第二个参数的5个可能值产生 15 个可能格式字符串 (对于使用的说明符，请参见DATE_FORMAT()函数说明表 )。 <table border="1" cellpadding="0" id="table6"><tr><td> 函数调用</td> <td> 结果</td> </tr><tr><td> GET_FORMAT(DATE,'USA')</td> <td> '%m.%d.%Y'</td> </tr><tr><td> GET_FORMAT(DATE,'JIS')</td> <td> '%Y-%m-%d'</td> </tr><tr><td> GET_FORMAT(DATE,'ISO')</td> <td> '%Y-%m-%d'</td> </tr><tr><td> GET_FORMAT(DATE,'EUR')</td> <td> '%d.%m.%Y'</td> </tr><tr><td> GET_FORMAT(DATE,'INTERNAL')</td> <td> '%Y%m%d'</td> </tr><tr><td> GET_FORMAT(DATETIME,'USA')</td> <td> '%Y-%m-%d-%H.%i.%s'</td> </tr><tr><td> GET_FORMAT(DATETIME,'JIS')</td> <td> '%Y-%m-%d %H:%i:%s'</td> </tr><tr><td> GET_FORMAT(DATETIME,'ISO')</td> <td> '%Y-%m-%d %H:%i:%s'</td> </tr><tr><td> GET_FORMAT(DATETIME,'EUR')</td> <td> '%Y-%m-%d-%H.%i.%s'</td> </tr><tr><td> GET_FORMAT(DATETIME,'INTERNAL')</td> <td> '%Y%m%d%H%i%s'</td> </tr><tr><td> GET_FORMAT(TIME,'USA')</td> <td> '%h:%i:%s %p'</td> </tr><tr><td> GET_FORMAT(TIME,'JIS')</td> <td> '%H:%i:%s'</td> </tr><tr><td> GET_FORMAT(TIME,'ISO')</td> <td> '%H:%i:%s'</td> </tr><tr><td> GET_FORMAT(TIME,'EUR')</td> <td> '%H.%i.%S'</td> </tr><tr><td> GET_FORMAT(TIME,'INTERNAL')</td> <td> '%H%i%s'</td> </tr></table> ISO 格式为ISO 9075, 而非ISO 8601. 也可以使用TIMESTAMP, 这时GET_FORMAT()的返回值和DATETIME相同。 mysql> **SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));** -> '03.10.2003' mysql> **SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));** -> '2003-10-31' 或见[13.5.3节，“SET语法”](# "13.5.3. SET Syntax")。 - HOUR(*time*) 返回*time*对应的小时数。对于日时值的返回值范围是从 0 到 23 。 mysql> **SELECT HOUR('10:05:03');** -> 10 然而, TIME 值的范围实际上非常大, 所以HOUR可以返回大于23的值。 mysql> **SELECT HOUR('272:59:59');** -> 272 - LAST_DAY(*date*) 获取一个日期或日期时间值，返回该月最后一天对应的值。若参数无效，则返回NULL。 mysql> **SELECT LAST_DAY('2003-02-05');** -> '2003-02-28' mysql> **SELECT LAST_DAY('2004-02-05');** -> '2004-02-29' mysql> **SELECT LAST_DAY('2004-01-01 01:01:01');** -> '2004-01-31' mysql> **SELECT LAST_DAY('2003-03-32');** -> NULL - LOCALTIME, LOCALTIME() LOCALTIME 及 LOCALTIME()和NOW()具有相同意义。 - LOCALTIMESTAMP, LOCALTIMESTAMP() LOCALTIMESTAMP和LOCALTIMESTAMP()和NOW()具有相同意义。 - MAKEDATE(*year*,*dayofyear*) 给出年份值和一年中的天数值，返回一个日期。*dayofyear*必须大于 0 ，否则结果为 NULL。 mysql> **SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);** -> '2001-01-31', '2001-02-01' mysql> **SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);** -> '2001-12-31', '2004-12-30' mysql> **SELECT MAKEDATE(2001,0);** -> NULL - MAKETIME(*hour*,*minute*,*second*) 返回由*hour*、 *minute*和*second*参数计算得出的时间值。 mysql> **SELECT MAKETIME(12,15,30);** -> '12:15:30' - MICROSECOND(*expr*) 从时间或日期时间表达式*expr*返回微秒值，其数字范围从 0到 999999。 mysql> **SELECT MICROSECOND('12:00:00.123456');** -> 123456 mysql> **SELECT MICROSECOND('1997-12-31 23:59:59.000010');** -> 10 - MINUTE(*time*) 返回* time*对应的分钟数,范围是从 0 到 59。 mysql> **SELECT MINUTE('98-02-03 10:05:03');** -> 5 - MONTH(*date*) 返回*date*对应的月份，范围时从 1 到 12。 mysql> **SELECT MONTH('1998-02-03');** -> 2 - MONTHNAME(*date*) 返回*date*对应月份的全名。 mysql> **SELECT MONTHNAME('1998-02-05');** -> 'February ' - NOW() 返回当前日期和时间值，其格式为 'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS ，具体格式取决于该函数是否用在字符串中或数字语境中。 mysql> **SELECT NOW();** -> '1997-12-15 23:50:26' mysql> **SELECT NOW() + 0;** -> 19971215235026 在一个存储程序或触发器内, NOW() 返回一个常数时间，该常数指示了该程序或触发语句开始执行的时间。这同SYSDATE()的运行有所不同。 - 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 - QUARTER(*date*) 返回*date*对应的一年中的季度值，范围是从 1到 4。 mysql> **SELECT QUARTER('98-04-01');** -> 2 - SECOND(*time*) 返回*time*对应的秒数, 范围是从 0到59。 mysql> **SELECT SECOND('10:05:03');** -> 3 - SEC_TO_TIME(*seconds*) 返回被转化为小时、分钟和秒数的*seconds*参数值, 其格式为 'HH:MM:SS' 或HHMMSS，具体格式根据该函数是否用在字符串或数字语境中而定。 mysql> **SELECT SEC_TO_TIME(2378);** -> '00:39:38' mysql> **SELECT SEC_TO_TIME(2378) + 0;** -> 3938 - STR_TO_DATE(*str*,*format*) 这是DATE_FORMAT() 函数的倒转。它获取一个字符串 *str*和一个格式字符串*format*。若格式字符串包含日期和时间部分，则 STR_TO_DATE()返回一个 DATETIME 值，若该字符串只包含日期部分或时间部分，则返回一个 DATE 或TIME值。 * str*所包含的日期、时间或日期时间值应该在*format*指示的格式中被给定。对于可用在*format*中的说明符，请参见DATE_FORMAT() 函数说明表。所有其它的字符被逐字获取，因此不会被解释。若 *str*包含一个非法日期、时间或日期时间值，则 STR_TO_DATE()返回NULL。同时，一个非法值会引起警告。对日期值部分的范围检查在[11.3.1节，“DATETIME、DATE和TIMESTAMP类型”](# "11.3.1. The DATETIME, DATE, and TIMESTAMP Types")有详细说明。其意义是,例如, 只要具体日期部分的范围时从 1到 31之间，则允许一个日期中的具体日期部分大于一个月中天数值。并且，允许“零”日期或带有0值部分的日期。 mysql> ** SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');** -> '0000-00-00' mysql> ** SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');** -> '2004-04-31' - SUBDATE(*date*,INTERVAL *expr**type*) SUBDATE(*expr*,*days*) 当被第二个参数的 INTERVAL型式调用时, SUBDATE()和DATE_SUB()的意义相同。对于有关INTERVAL参数的信息，见有关 DATE_ADD()的讨论。 mysql> ** SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);** -> '1997-12-02' mysql> ** SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);** -> '1997-12-02' 第二个形式允许对*days*使用整数值。在这些情况下，它被算作由日期或日期时间表达式 *expr*中提取的天数。 mysql> ** SELECT SUBDATE('1998-01-02 12:00:00', 31);** -> '1997-12-02 12:00:00' **注意**不能使用格式 "%X%V" 来将一个 year-week 字符串转化为一个日期，原因是当一个星期跨越一个月份界限时，一个年和星期的组合不能标示一个唯一的年和月份。若要将year-week转化为一个日期，则也应指定具体工作日： mysql> ** select str_to_date('200442 Monday', '%X%V %W');** -> 2004-10-18 - SUBTIME(*expr*,*expr2*) SUBTIME()从*expr*中提取*expr2* ，然后返回结果。*expr* 是一个时间或日期时间表达式，而*xpr2* 是一个时间表达式。 mysql> ** SELECT SUBTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');** -> '1997-12-30 22:58:58.999997' mysql> ** SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');** -> '-00:59:59.999999' - SYSDATE() 返回当前日期和时间值，格式为'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS，具体格式根据函数是否用在字符串或数字语境而定。在一个存储程序或触发器中, SYSDATE()返回其执行的时间, 而非存储成都或触发语句开始执行的时间。这个NOW()的运作有所不同。 - TIME(*expr*) 提取一个时间或日期时间表达式的时间部分，并将其以字符串形式返回。 mysql> ** SELECT TIME('2003-12-31 01:02:03');** -> '01:02:03' mysql> ** SELECT TIME('2003-12-31 01:02:03.000123');** -> '01:02:03.000123' - TIMEDIFF(*expr*,*expr2*) TIMEDIFF() 返回起始时间 *expr*和结束时间*expr2*之间的时间。 *expr* 和*expr2*为时间或 date-and-time 表达式,两个的类型必须一样。 mysql> ** SELECT TIMEDIFF('2000:01:01 00:00:00',** -> **'2000:01:01 00:00:00.000001');** -> '-00:00:00.000001' mysql> ** SELECT TIMEDIFF('1997-12-31 23:59:59.000001',** -> **'1997-12-30 01:01:01.000002');** -> '46:58:57.999999' - TIMESTAMP(*expr*) , TIMESTAMP(*expr*,*expr2*) 对于一个单参数,该函数将日期或日期时间表达式 *expr*作为日期时间值返回.对于两个参数, 它将时间表达式 *expr2*添加到日期或日期时间表达式 *expr*中，将theresult作为日期时间值返回。 mysql> ** SELECT TIMESTAMP('2003-12-31');** -> '2003-12-31 00:00:00' mysql> ** SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');** -> '2004-01-01 00:00:00' - TIMESTAMPADD(*interval*,*int_expr*,*datetime_expr*) 将整型表达式*int_expr*添加到日期或日期时间表达式 *datetime_expr*中。 *int_expr* 的单位被时间间隔参数给定，该参数必须是以下值的其中一个： FRAC_SECOND、SECOND、 MINUTE、 HOUR、 DAY、 WEEK、 MONTH、 QUARTER或 YEAR。可使用所显示的关键词指定*Interval*值，或使用SQL_TSI_前缀。例如, DAY或SQL_TSI_DAY 都是正确的。 mysql> ** SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');** -> '2003-01-02 00:01:00' mysql> ** SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');** -> '2003-01-09' - TIMESTAMPDIFF(*interval*,*datetime_expr1*,*datetime_expr2*) 返回日期或日期时间表达式*datetime_expr1*和*datetime_expr2*the 之间的整数差。其结果的单位由*interval*参数给出。*interval*的法定值同TIMESTAMPADD()函数说明中所列出的相同。 mysql> ** SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');** -> 3 mysql> ** SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');** -> -1 - TIME_FORMAT(*time*,*format*) 其使用和 DATE_FORMAT()函数相同, 然而*format*字符串可能仅会包含处理小时、分钟和秒的格式说明符。其它说明符产生一个NULL值或0。若*time* value包含一个大于23的小时部分，则 %H 和 %k 小时格式说明符会产生一个大于0..23的通常范围的值。另一个小时格式说明符产生小时值模数12。 mysql> ** SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');** -> '100 100 04 04 4' - TIME_TO_SEC(*time*) 返回已转化为秒的*time*参数。 mysql> ** SELECT TIME_TO_SEC('22:23:00');** -> 80580 mysql> ** SELECT TIME_TO_SEC('00:39:38');** -> 2378 - TO_DAYS(*date*) 给定一个日期*date*, 返回一个天数 (从年份0开始的天数 )。 mysql> ** SELECT TO_DAYS(950501);** -> 728779 mysql> ** SELECT TO_DAYS('1997-10-07');** -> 729669 TO_DAYS() 不用于阳历出现(1582)前的值，原因是当日历改变时，遗失的日期不会被考虑在内。请参见[12.6节，“MySQL使用什么日历？”](# "12.6. What Calendar Is Used By MySQL?")。请记住， MySQL使用[11.3节，“日期和时间类型”](# "11.3. Date and Time Types")中的规则将日期中的二位数年份值转化为四位。例如， '1997-10-07'和 '97-10-07' 被视为同样的日期: mysql> ** SELECT TO_DAYS('1997-10-07'), TO_DAYS('97-10-07');** -> 729669, 729669 对于1582 年之前的日期(或许在其它地区为下一年 ), 该函数的结果实不可靠的。详见[12.6节，“MySQL使用什么日历？”](# "12.6. What Calendar Is Used By MySQL?") 。 - UNIX_TIMESTAMP(), UNIX_TIMESTAMP(*date*) 若无参数调用，则返回一个Unix timestamp ('1970-01-01 00:00:00' GMT 之后的秒数) 作为无符号整数。若用*date*来调用UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。*date*可以是一个DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD 或YYYMMDD格式的数字。 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,但请注意只有基本范围检查会被履行 (年份从1970 到 2037，月份从01到12,日期从 01 到31)。假如你想要减去 UNIX_TIMESTAMP() 列, 你或许希望删去带符号整数的结果。请参见[12.8节，“Cast函数和操作符”](# "12.8. Cast Functions and Operators")。 - UTC_DATE, UTC_DATE() 返回当前 UTC日期值，其格式为 'YYYY-MM-DD' 或 YYYYMMDD，具体格式取决于函数是否用在字符串或数字语境中。 mysql> ** SELECT UTC_DATE(), UTC_DATE() + 0;** -> '2003-08-14', 20030814 - UTC_TIME, UTC_TIME() 返回当前 UTC 值，其格式为 'HH:MM:SS' 或HHMMSS，具体格式根据该函数是否用在字符串或数字语境而定。 mysql> ** SELECT UTC_TIME(), UTC_TIME() + 0;** -> '18:07:53', 180753 - UTC_TIMESTAMP, UTC_TIMESTAMP() 返回当前UTC日期及时间值，格式为 'YYYY-MM-DD HH:MM:SS' 或YYYYMMDDHHMMSS，具体格式根据该函数是否用在字符串或数字语境而定。 mysql> ** SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;** -> '2003-08-14 18:08:04', 20030814180804 - WEEK(*date*[,*mode*]) 该函数返回*date*对应的星期数。WEEK() 的双参数形式允许你指定该星期是否起始于周日或周一，以及返回值的范围是否为从0 到53 或从1 到53。若 *mode*参数被省略，则使用default_week_format系统自变量的值。请参见[5.3.3节，“服务器系统变量”](# "5.3.3. Server System Variables")。以下表说明了*mode*参数的工作过程：d <table border="1" cellpadding="0" id="table7"><tr><td> </td> <td> 第一天 </td> <td> </td> <td> </td> </tr><tr><td> Mode</td> <td> 工作日</td> <td> 范围</td> <td> Week 1 为第一周 ...</td> </tr><tr><td> 0</td> <td> 周日 </td> <td> 0-53</td> <td> 本年度中有一个周日 </td> </tr><tr><td> 1</td> <td> 周一</td> <td> 0-53</td> <td> 本年度中有3天以上 </td> </tr><tr><td> 2</td> <td> 周日</td> <td> 1-53</td> <td> 本年度中有一个周日</td> </tr><tr><td> 3</td> <td> 周一</td> <td> 1-53</td> <td> 本年度中有3天以上 </td> </tr><tr><td> 4</td> <td> 周日</td> <td> 0-53</td> <td> 本年度中有3天以上</td> </tr><tr><td> 5</td> <td> 周一</td> <td> 0-53</td> <td> 本年度中有一个周一 </td> </tr><tr><td> 6</td> <td> 周日</td> <td> 1-53</td> <td> 本年度中有3天以上</td> </tr><tr><td> 7</td> <td> 周一</td> <td> 1-53</td> <td> 本年度中有一个周一</td> </tr></table> 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 注意，假如有一个日期位于前一年的最后一周，若你不使用2、3、6或7作为*mode*参数选择，则MySQL返回 0： mysql> ** SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);** -> 2000, 0 有人或许会提出意见，认为 MySQL 对于WEEK() 函数应该返回 52 ，原因是给定的日期实际上发生在1999年的第52周。我们决定返回0作为代替的原因是我们希望该函数能返回“给定年份的星期数”。这使得WEEK() 函数在同其它从日期中抽取日期部分的函数结合时的使用更加可靠。假如你更希望所计算的关于年份的结果包括给定日期所在周的第一天，则应使用 0、2、5或 7 作为*mode*参数选择。 mysql> ** SELECT WEEK('2000-01-01',2);** -> 52 作为选择，可使用 YEARWEEK()函数: mysql> ** SELECT YEARWEEK('2000-01-01');** -> 199952 mysql> ** SELECT MID(YEARWEEK('2000-01-01'),5,2);** -> '52' - WEEKDAY(*date*) 返回*date* (0 = 周一, 1 = 周二, ... 6 = 周日)对应的工作日索引 weekday index for mysql> ** SELECT WEEKDAY('1998-02-03 22:23:00');** -> 1 mysql> ** SELECT WEEKDAY('1997-11-05');** -> 2 - WEEKOFYEAR(*date*) 将该日期的阳历周以数字形式返回，范围是从1到53。它是一个兼容度函数，相当于WEEK(*date*,3)。 mysql> ** SELECT WEEKOFYEAR('1998-02-20');** -> 8 - YEAR(*date*) 返回*date*对应的年份,范围是从1000到9999。 mysql> ** SELECT YEAR('98-02-03');** -> 1998 - YEARWEEK(*date*), YEARWEEK(*date*,*start*) 返回一个日期对应的年或周。*start*参数的工作同 *start*参数对 WEEK()的工作相同。结果中的年份可以和该年的第一周和最后一周对应的日期参数有所不同。 mysql> ** SELECT YEARWEEK('1987-01-01');** -> 198653 注意，周数和WEEK()函数队可选参数0或 1可能会返回的(0) w有所不同，原因是此时 WEEK() 返回给定年份的语境中的周。 - ~~~ -> '1997-10-07' ~~~ ### 12.6. MySQL使用什么日历？ MySQL 使用通常所说的 *proleptic *阳历*。* 每个将日历由朱利安改为阳历的国家在改变日历期间都不得不删除至少10天。为了了解其运作，让我们看看1582年10月，这是由朱利安日历转换为阳历的第一次: <table border="1" cellpadding="0" id="table8"><tr><td> 周一</td> <td> 周二</td> <td> 周三</td> <td> 周四</td> <td> 周五</td> <td> 周六</td> <td> 周日</td> </tr><tr><td> 1</td> <td> 2</td> <td> 3</td> <td> 4</td> <td> 15</td> <td> 16</td> <td> 17</td> </tr><tr><td> 18</td> <td> 19</td> <td> 20</td> <td> 21</td> <td> 22</td> <td> 23</td> <td> 24</td> </tr><tr><td> 25</td> <td> 26</td> <td> 27</td> <td> 28</td> <td> 29</td> <td> 30</td> <td> 31</td> </tr></table> 在10月4 日到10月15日之间的日期为空白。这个中断被称为*接入*。接入前的日期均使用朱利安日历, 而接入后的日期均使用阳历。接入期间的日期是不存在的。当一个用于日期的日历并为得到实际使用时被称为 *proleptic*。因此, 若我们假设从来没有接入期的存在，而阳历历法则始终被使用，我们会有一个预期的阳历。这就是MySQL 所使用的，正如标准SQL所要求的。鉴于这个原因，作为MySQL DATE 或 DATETIME值而被储存的接入前的日期必须调整这个差异。我们必须明白，接入的发生时间在不同的国家有所不同，而接入的时间越晚，遗失的日期越多。例如，在大不列颠, 接入发生在 1752年,这时9月2日，周三后的第二天为9月14日，周二；俄罗斯结束使用朱利安日历的时间为1918年,接入过程中遗失天数为 13天, 根据阳历，其普遍被称为“10月革命”的发生时间实际上是11月。 ### 12.7. 全文搜索功能 [ 12.7.1. 布尔全文搜索](#)[ 12.7.2. 全文搜索带查询扩展](#)[ 12.7.3. 全文停止字](#)[ 12.7.4. 全文限定条件](#)[ 12.7.5. 微调MySQL全文搜索](#) - [MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION]) ]() MySQL支持全文索引和搜索功能。MySQL中的全文索引类型FULLTEXT的索引。 FULLTEXT 索引仅可用于 MyISAM 表；他们可以从CHAR、 VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或 CREATE INDEX被添加。对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。关于全文搜索的限制列表，请参见 [ 12.7.4节，“全文限定条件”](# "12.7.4. Full-Text Restrictions"). 全文搜索同MATCH()函数一起执行。 ~~~ mysql> CREATE TABLE articles ( ~~~ ~~~ -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, ~~~ ~~~ -> title VARCHAR(200), ~~~ ~~~ -> body TEXT, ~~~ ~~~ -> FULLTEXT (title,body) ~~~ ~~~ -> ); ~~~ ~~~ Query OK, 0 rows affected (0.00 sec) ~~~ ~~~ ~~~ ~~~ mysql> INSERT INTO articles (title,body) VALUES ~~~ ~~~ -> ('MySQL Tutorial','DBMS stands for DataBase ...'), ~~~ ~~~ -> ('How To Use MySQL Well','After you went through a ...'), ~~~ ~~~ -> ('Optimizing MySQL','In this tutorial we will show ...'), ~~~ ~~~ -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ~~~ ~~~ -> ('MySQL vs. YourSQL','In the following database comparison ...'), ~~~ ~~~ -> ('MySQL Security','When configured properly, MySQL ...'); ~~~ ~~~ Query OK, 6 rows affected (0.00 sec) ~~~ ~~~ Records: 6 Duplicates: 0 Warnings: 0 ~~~ ~~~ ~~~ ~~~ mysql> SELECT * FROM articles ~~~ ~~~ -> WHERE MATCH (title,body) AGAINST ('database'); ~~~ ~~~ +----+-------------------+------------------------------------------+ ~~~ ~~~ | id | title | body | ~~~ ~~~ +----+-------------------+------------------------------------------+ ~~~ ~~~ | 5 | MySQL vs. YourSQL | In the following database comparison ... | ~~~ ~~~ | 1 | MySQL Tutorial | DBMS stands for DataBase ... | ~~~ ~~~ +----+-------------------+------------------------------------------+ ~~~ ~~~ 2 rows in set (0.00 sec) ~~~ MATCH()函数对于一个字符串执行资料库内的自然语言搜索。一个资料库就是1套1个或2个包含在FULLTEXT内的列。搜索字符串作为对AGAINST()的参数而被给定。对于表中的每一行, MATCH() 返回一个相关值，即, 搜索字符串和 MATCH()表中指定列中该行文字之间的一个相似性度量。在默认状态下, 搜索的执行方式为不区分大小写方式。然而,你可以通过对编入索引的列使用二进制排序方式执行区分大小写的全文搜索。例如，可以向一个使用latin1字符集的列给定latin1_bin 的排序方式，对于全文搜索区分大小写。如上述所举例子，当MATCH()被用在一个WHERE 语句中时，相关值是非负浮点数。零相关的意思是没有相似性。相关性的计算是基于该行中单词的数目, 该行中独特子的数目，资料库中单词的总数，以及包含特殊词的文件(行)数目。对于自然语言全文搜索，要求MATCH() 函数中命名的列和你的表中一些FULLTEXT索引中包含的列相同。对于前述问讯，注意，MATCH()函数(题目及全文)中所命名的列和文章表的FULLTEXT索引中的列相同。若要分别搜索题目和全文，应该对每个列创建FULLTEXT索引。或者也可以运行布尔搜索或使用查询扩展进行搜索。关于这些搜索类型的说明见[12.7.1节，“布尔全文搜索”](# "12.7.1. Boolean Full-Text Searches")和[12.7.2节，“全文搜索带查询扩展”](# "12.7.2. Full-Text Searches with Query Expansion")。上面的例子基本上展示了怎样使用返回行的相关性顺序渐弱的MATCH()函数。而下面的例子则展示了怎样明确地检索相关值。返回行的顺序是不定的，原因是 SELECT 语句不包含 WHERE或ORDER BY 子句： ~~~ mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial') ~~~ ~~~ -> FROM articles; ~~~ ~~~ +----+-----------------------------------------+ ~~~ ~~~ | id | MATCH (title,body) AGAINST ('Tutorial') | ~~~ ~~~ +----+-----------------------------------------+ ~~~ ~~~ | 1 | 0.65545833110809 | ~~~ ~~~ | 2 | 0 | ~~~ ~~~ | 3 | 0.66266459226608 | ~~~ ~~~ | 4 | 0 | ~~~ ~~~ | 5 | 0 | ~~~ ~~~ | 6 | 0 | ~~~ ~~~ +----+-----------------------------------------+ ~~~ ~~~ 6 rows in set (0.00 sec) ~~~ 下面的例子则更加复杂。询问返回相关值，同时对行按照相关性渐弱的顺序进行排序。为实现这个结果，你应该两次指定 MATCH()：一次在 SELECT 列表中而另一次在 WHERE子句中。这不会引起额外的内务操作，原因是MySQL 优化程序注意到两个MATCH()调用是相同的，从而只会激活一次全文搜索代码。 ~~~ mysql> SELECT id, body, MATCH (title,body) AGAINST ~~~ ~~~ -> ('Security implications of running MySQL as root') AS score ~~~ ~~~ -> FROM articles WHERE MATCH (title,body) AGAINST ~~~ ~~~ -> ('Security implications of running MySQL as root'); ~~~ ~~~ +----+-------------------------------------+-----------------+ ~~~ ~~~ | id | body | score | ~~~ ~~~ +----+-------------------------------------+-----------------+ ~~~ ~~~ | 4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 | ~~~ ~~~ | 6 | When configured properly, MySQL ... | 1.3114095926285 | ~~~ ~~~ +----+-------------------------------------+-----------------+ ~~~ ~~~ 2 rows in set (0.00 sec) ~~~ 表中有2行(0.00 秒) MySQL FULLTEXT 执行将任何单字字符原形 (字母、数字和下划线部分)的序列视为一个单词。这个序列或许也包含单引号 ('),但在一行中不会超过一个。这意味着 aaa'bbb 会被视为一个单词，而 aaa''bbb则被视为2个单词。位于单词之前或其后的单引号会被FULLTEXT分析程序去掉； 'aaa'bbb' 会变成 aaa'bbb。 FULLTEXT分析程序会通过寻找某些分隔符来确定单词的起始位置和结束位置，例如' ' (间隔符号)、 , (逗号)以及 . (句号 )。假如单词没有被分隔符分开，(例如在中文里 ), 则 FULLTEXT 分析程序不能确定一个词的起始位置和结束位置。为了能够在这样的语言中向FULLTEXT 索引添加单词或其它编入索引的术语，你必须对它们进行预处理，使其被一些诸如"之类的任意分隔符分隔开。一些词在全文搜索中会被忽略： - 任何过于短的词都会被忽略。全文搜索所能找到的词的默认最小长度为 4个字符。 - 停止字中的词会被忽略。禁用词就是一个像“the” 或“some” 这样过于平常而被认为是不具语义的词。存在一个内置的停止字，但它可以通过用户自定义列表被改写。请参见[12.7.5节，“微调MySQL全文搜索”](# "12.7.5. Fine-Tuning MySQL Full-Text Search")。默认的停止字在[12.7.3节，“全文停止字”](# "12.7.3. Full-Text Stopwords")中被给出。默认的最小单词长度和停止字可以被改变，如[12.7.5节，“微调MySQL全文搜索”](# "12.7.5. Fine-Tuning MySQL Full-Text Search")中所述。词库和询问中每一个正确的单词根据其在词库和询问中的重要性而被衡量。通过这种方式，一个出现在许多文件中的单词具有较低的重要性(而且甚至很多单词的重要性为零),原因是在这个特别词库中其语义价值较低。反之，假如这个单词比较少见，那么它会得到一个较高的重要性。然后单词的重要性被组合，从而用来计算该行的相关性。这项技术最适合同大型词库一起使用 (事实上, 此时它经过仔细的调整 )。对于很小的表，单词分布并不能充分反映它们的语义价值，而这个模式有时可能会产生奇特的结果。例如, 虽然单词 “MySQL” 出现在文章表中的每一行，但对这个词的搜索可能得不到任何结果： mysql> ** SELECT * FROM articles** -> ** WHERE MATCH (title,body) AGAINST ('MySQL');** 找不到搜索的词(0.00 秒) 这个搜索的结果为空，原因是单词 “MySQL” 出现在至少全文的50%的行中。因此, 它被列入停止字。对于大型数据集，使用这个操作最合适不过了----一个自然语言问询不会从一个1GB 的表每隔一行返回一次。对于小型数据集，它的用处可能比较小。一个符合表中所有行的内容的一半的单词查找相关文档的可能性较小。事实上, 它更容易找到很多不相关的内容。我们都知道，当我们在因特网上试图使用搜索引擎寻找资料的时候，这种情况发生的频率颇高。可以推论，包含该单词的行因*其所在特别数据集 *而被赋予较低的语义价值。一个给定的词有可能在一个数据集中拥有超过其50%的域值，而在另一个数据集却不然。当你第一次尝试使用全文搜索以了解其工作过程时，这个50% 的域值提供重要的蕴涵操作：若你创建了一个表，并且只将文章的1、2行插入其中，而文中的每个单词在所有行中出现的机率至少为 50% 。那么结果是你什么也不会搜索到。一定要插入至少3行，并且多多益善。需要绕过该50% 限制的用户可使用布尔搜索代码；见[12.7.1节，“布尔全文搜索”](# "12.7.1. Boolean Full-Text Searches")。 ### 12.7.1. 布尔全文搜索利用IN BOOLEAN MODE修改程序， MySQL 也可以执行布尔全文搜索： ~~~ mysql> SELECT * FROM articles WHERE MATCH (title,body) ~~~ ~~~ -> AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); ~~~ ~~~ +----+-----------------------+-------------------------------------+ ~~~ ~~~ | id | title | body | ~~~ ~~~ +----+-----------------------+-------------------------------------+ ~~~ ~~~ | 1 | MySQL Tutorial | DBMS stands for DataBase ... | ~~~ ~~~ | 2 | How To Use MySQL Well | After you went through a ... | ~~~ ~~~ | 3 | Optimizing MySQL | In this tutorial we will show ... | ~~~ ~~~ | 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... | ~~~ ~~~ | 6 | MySQL Security | When configured properly, MySQL ... | ~~~ ~~~ +----+-----------------------+-------------------------------------+ ~~~ 这个问询检索所有包含单词“MySQL”的行，但*不*检索包含单词“YourSQL”的行。布尔全文搜索具有以下特点： - 它们不使用 50% 域值。. - 它们不会按照相关性渐弱的顺序将行进行分类。你可以从上述问询结果中看到这一点：相关性最高的行是一个包含两个“MySQL” 的行，但它被列在最后的位置，而不是开头位置。 - 即使没有FULLTEXT，它们仍然可以工作，尽管这种方式的搜索执行的速度非常之慢。 - 最小单词长度全文参数和最大单词长度全文参数均适用。 - 停止字适用。布尔全文搜索的性能支持以下操作符： - + 一个前导的加号表示该单词*必须* 出现在返回的每一行的开头位置。 - - 一个前导的减号表示该单词*一定不能*出现在任何返回的行中。 - (无操作符) 在默认状态下(当没有指定 + 或–的情况下)，该单词可有可无，但含有该单词的行等级较高。这和MATCH() ... AGAINST()不使用IN BOOLEAN MODE修改程序时的运作很类似。 - > < 这两个操作符用来改变一个单词对赋予某一行的相关值的影响。 > 操作符增强其影响，而 <操作符则减弱其影响。请参见下面的例子。 - ( ) 括号用来将单词分成子表达式。括入括号的部分可以被嵌套。 - ~ 一个前导的代字号用作否定符，用来否定单词对该行相关性的影响。这对于标记“noise（无用信息）”的单词很有用。包含这类单词的行较其它行等级低，但因其可能会和-号同时使用，因而不会在任何时候都派出所有无用信息行。 - * 星号用作截断符。于其它符号不同的是，它应当被*追加*到要截断的词上。 - " 一个被括入双引号的短语 (‘"’) 只和*字面上*包含该短语*输入格式*的行进行匹配。全文引擎将短语拆分成单词，在FULLTEXT索引中搜索该单词。非单词字符不需要严密的匹配：短语搜索只要求符合搜索短语包含的单词且单词的排列顺序相同的内容。例如， "test phrase" 符合 "test, phrase"。若索引中不存在该短语包含的单词，则结果为空。例如，若所有单词都是禁用词，或是长度都小于编入索引单词的最小长度，则结果为空。以下例子展示了一些使用布尔全文符号的搜索字符串： - 'apple banana' 寻找包含至少两个单词中的一个的行。 - '+apple +juice' 寻找两个单词都包含的行。 - '+apple macintosh' 寻找包含单词“apple”的行，若这些行也包含单词“macintosh”，则列为更高等级。 - '+apple -macintosh' 寻找包含单词“apple” 但不包含单词 “macintosh”的行。 - '+apple +(>turnover <strudel)' 寻找包含单词“apple”和“turnover” 的行，或包含“apple” 和“strudel”的行 (无先后顺序),然而包含 “apple turnover”的行较包含“apple strudel”的行排列等级更为高。 - 'apple*' 寻找包含“apple”、“apples”、“applesauce”或“applet”的行。 - '"some words"' 寻找包含原短语“some words”的行 (例如,包含“some words of wisdom” 的行，而非包含 “some noise words”的行)。注意包围词组的‘"’ 符号是界定短语的操作符字符。它们不是包围搜索字符串本身的引号。 ### 12.7.2. 全文搜索带查询扩展全文搜索支持查询扩展功能 (特别是其多变的“盲查询扩展功能” )。若搜索短语的长度过短, 那么用户则需要依靠全文搜索引擎通常缺乏的内隐知识进行查询。这时，查询扩展功能通常很有用。例如, 某位搜索 “database” 一词的用户，可能认为“MySQL”、“Oracle”、“DB2” and “RDBMS”均为符合 “databases”的项，因此都应被返回。这既为内隐知识。在下列搜索短语后添加WITH QUERY EXPANSION，激活盲查询扩展功能(即通常所说的自动相关性反馈)。它将执行两次搜索，其中第二次搜索的搜索短语是同第一次搜索时找到的少数顶层文件连接的原始搜索短语。这样,假如这些文件中的一个含有单词 “databases” 以及单词 “MySQL”, 则第二次搜索会寻找含有单词“MySQL” 的文件，即使这些文件不包含单词 “database”。下面的例子显示了这个不同之处： ~~~ mysql> SELECT * FROM articles ~~~ ~~~ -> WHERE MATCH (title,body) AGAINST ('database'); ~~~ ~~~ +----+-------------------+------------------------------------------+ ~~~ ~~~ | id | title | body | ~~~ ~~~ +----+-------------------+------------------------------------------+ ~~~ ~~~ | 5 | MySQL vs. YourSQL | In the following database comparison ... | ~~~ ~~~ | 1 | MySQL Tutorial | DBMS stands for DataBase ... | ~~~ ~~~ +----+-------------------+------------------------------------------+ ~~~ ~~~ 2 rows in set (0.00 sec) ~~~ ~~~ ~~~ ~~~ mysql> SELECT * FROM articles ~~~ ~~~ -> WHERE MATCH (title,body) ~~~ ~~~ -> AGAINST ('database' WITH QUERY EXPANSION); ~~~ ~~~ +----+-------------------+------------------------------------------+ ~~~ ~~~ | id | title | body | ~~~ ~~~ +----+-------------------+------------------------------------------+ ~~~ ~~~ | 1 | MySQL Tutorial | DBMS stands for DataBase ... | ~~~ ~~~ | 5 | MySQL vs. YourSQL | In the following database comparison ... | ~~~ ~~~ | 3 | Optimizing MySQL | In this tutorial we will show ... | ~~~ ~~~ +----+-------------------+------------------------------------------+ ~~~ ~~~ 3 rows in set (0.00 sec) ~~~ 另一个例子是Georges Simenon 搜索关于Maigret的书籍, 这个用户不确定“Maigret”一词的拼法。若不使用查询扩展而搜索“Megre and the reluctant witnesses” 得到的结果只能是的“Maigret and the Reluctant Witnesses” 。而带有查询扩展的搜索会在第二遍得到带有“Maigret”一词的所有书名。 **注释**：盲查询扩展功能很容易返回非相关文件而增加无用信息，因此只有在查询一个长度很短的短语时才有必要使用这项功能。 ### 12.7.3. 全文停止字以下表列出了默认的全文停止字： | a's | able | about | above | according | |-----|-----|-----|-----|-----| | accordingly | across | actually | after | afterwards | | again | against | ain't | all | allow | | allows | almost | alone | along | already | | also | although | always | am | among | | amongst | an | and | another | any | | anybody | anyhow | anyone | anything | anyway | | anyways | anywhere | apart | appear | appreciate | | appropriate | are | aren't | around | as | | aside | ask | asking | associated | at | | available | away | awfully | be | became | | because | become | becomes | becoming | been | | before | beforehand | behind | being | believe | | below | beside | besides | best | better | | between | beyond | both | brief | but | | by | c'mon | c's | came | can | | can't | cannot | cant | cause | causes | | certain | certainly | changes | clearly | co | | com | come | comes | concerning | consequently | | consider | considering | contain | containing | contains | | corresponding | could | couldn't | course | currently | | definitely | described | despite | did | didn't | | different | do | does | doesn't | doing | | don't | done | down | downwards | during | | each | edu | eg | eight | either | | else | elsewhere | enough | entirely | especially | | et | etc | even | ever | every | | everybody | everyone | everything | everywhere | ex | | exactly | example | except | far | few | | fifth | first | five | followed | following | | follows | for | former | formerly | forth | | four | from | further | furthermore | get | | gets | getting | given | gives | go | | goes | going | gone | got | gotten | | greetings | had | hadn't | happens | hardly | | has | hasn't | have | haven't | having | | he | he's | hello | help | hence | | her | here | here's | hereafter | hereby | | herein | hereupon | hers | herself | hi | | him | himself | his | hither | hopefully | | how | howbeit | however | i'd | i'll | | i'm | i've | ie | if | ignored | | immediate | in | inasmuch | inc | indeed | | indicate | indicated | indicates | inner | insofar | | instead | into | inward | is | isn't | | it | it'd | it'll | it's | its | | itself | just | keep | keeps | kept | | know | knows | known | last | lately | | later | latter | latterly | least | less | | lest | let | let's | like | liked | | likely | little | look | looking | looks | | ltd | mainly | many | may | maybe | | me | mean | meanwhile | merely | might | | more | moreover | most | mostly | much | | must | my | myself | name | namely | | nd | near | nearly | necessary | need | | needs | neither | never | nevertheless | new | | next | nine | no | nobody | non | | none | noone | nor | normally | not | | nothing | novel | now | nowhere | obviously | | of | off | often | oh | ok | | okay | old | on | once | one | | ones | only | onto | or | other | | others | otherwise | ought | our | ours | | ourselves | out | outside | over | overall | | own | particular | particularly | per | perhaps | | placed | please | plus | possible | presumably | | probably | provides | que | quite | qv | | rather | rd | re | really | reasonably | | regarding | regardless | regards | relatively | respectively | | right | said | same | saw | say | | saying | says | second | secondly | see | | seeing | seem | seemed | seeming | seems | | seen | self | selves | sensible | sent | | serious | seriously | seven | several | shall | | she | should | shouldn't | since | six | | so | some | somebody | somehow | someone | | something | sometime | sometimes | somewhat | somewhere | | soon | sorry | specified | specify | specifying | | still | sub | such | sup | sure | | t's | take | taken | tell | tends | | th | than | thank | thanks | thanx | | that | that's | thats | the | their | | theirs | them | themselves | then | thence | | there | there's | thereafter | thereby | therefore | | therein | theres | thereupon | these | they | | they'd | they'll | they're | they've | think | | third | this | thorough | thoroughly | those | | though | three | through | throughout | thru | | thus | to | together | too | took | | toward | towards | tried | tries | truly | | try | trying | twice | two | un | | under | unfortunately | unless | unlikely | until | | unto | up | upon | us | use | | used | useful | uses | using | usually | | value | various | very | via | viz | | vs | want | wants | was | wasn't | | way | we | we'd | we'll | we're | | we've | welcome | well | went | were | | weren't | what | what's | whatever | when | | whence | whenever | where | where's | whereafter | | whereas | whereby | wherein | whereupon | wherever | | whether | which | while | whither | who | | who's | whoever | whole | whom | whose | | why | will | willing | wish | with | | within | without | won't | wonder | would | | would | wouldn't | yes | yet | you | | you'd | you'll | you're | you've | your | | yours | yourself | yourselves | zero | | ### 12.7.4. 全文限定条件 - 全文搜索只适用于 MyISAM 表。 - 全文搜索可以同大多数多字节字符集一起使用。Unicode属于例外情况; 可使用utf8 字符集 , 而非ucs2字符集。 - 诸如汉语和日语这样的表意语言没有自定界符。因此， FULLTEXT分析程序*不能确定在这些或其它的这类语言中词的起始和结束的位置。*其隐含操作及该问题的一些工作区在[12.7节，“全文搜索功能”](# "12.7. Full-Text Search Functions")有详细论述。 - 若支持在一个单独表中使用多字符集，则所有 FULLTEXT索引中的列必须使用同样的字符集和库。 - MATCH()列列表必须同该表中一些 FULLTEXT索引定义中的列列表完全符合,除非MATCH()在IN BOOLEAN MODE。 - 对AGAINST() 的参数必须是一个常数字符串。 ### 12.7.5. 微调MySQL全文搜索 MySQL的全文搜索容量几乎不具有用户调节参数。假如你拥有一个 MySQL源分布，你就能对全文搜索性能行使更多控制，原因是一些变化需要源代码修改。请参见[2.8节，“使用源码分发版安装MySQL”](# "2.8. MySQL Installation Using a Source Distribution")。注意，为了更加有效，需要对全文搜索谨慎调节。实际上，在大多数情况下修改默认性能只能降低其性能。* 除非你知道自己在做什么，否则不要改变 MySQL源。 * 下述的大多数全文变量必须在服务器启动时被设置。为了改变它们，还要重新启动服务器；在服务器正在运行期间，他们不会被改变。一些变量的改变需要你重建表中的 FULLTEXT 索引。本章结尾部分给出了其有关操作说明。 - ft_min_word_len and ft_max_word_len系统自变量规定了被编入索引单词的最小长度和最大长度。(见[5.3.3节，“服务器系统变量”](# "5.3.3. Server System Variables").) 默认的最小值为四个字符；默认的最大值取决于使用的 MySQL 版本。假如你改变任意一个值，那么你必须重建你的 FULLTEXT索引。例如，若你希望一个3字符的单词变为可查找项，则可以通过将以下行移动到一个供选择文件里，从而设置 ft_min_word_len 变量： · [mysqld] · ft_min_word_len=3 然后重新启动服务器，重建你的 FULLTEXT索引。同时还要特别注意该表后面的说明中的关于**myisamchk**的注释。 - 若要覆盖默认停止字，则可设置 ft_stopword_file 系统变量。 (见[5.3.3节，“服务器系统变量”](# "5.3.3. Server System Variables")）。变量值应为包含停止字的文件路径名, 或是用来截止禁用词过滤的空字符串。在改变了这个变量的值或禁用词文件的内容后, 重建你的 FULLTEXT索引。停止字是自由形态的，换言之，你可使用任何诸如newline、 space或comma这样的非字母数字字符来分隔禁用词。下划线字符(_) 和被视为单词的一部分的单引号 (')例外。停止字字符集为服务器默认字符集；见[10.3.1节，“服务器字符集和校对”](# "10.3.1. Server Character Set and Collation"). - 自然语言查询的50%阈值由所选择的特别权衡方案所决定。若要阻止它，myisam/ftdefs.h 中寻找以下行： · #define GWS_IN_USE GWS_PROB 将该行改为： #define GWS_IN_USE GWS_FREQ 然后重新编译 MySQL。此时不需要重建索引。注释：这样做你会*严重的*By 降低 MySQL为MATCH()函数提供合适的相关值得能力。假如你争得需要搜索这样的普通词，而使用IN BOOLEAN MODE代替的效果更好，因为它不遵循 50% 阈值。 - 要改变用于布尔全文搜索的操作符，设置 ft_boolean_syntax 系统变量。这个变量也可以在服务器运行时被改变，但你必须有SUPER 特权才能这么做。在这种情况下不需要重建索引。见[5.3.3节，“服务器系统变量”](# "5.3.3. Server System Variables"), 它向我们说明了怎样使用这个变量的规则。假如你改变了影响索引的全文变量 (ft_min_word_len、 ft_max_word_len或ft_stopword_file),或假如你改变了禁用词文件本身，则你必须在改变和重新启动服务器后重建你的 FULLTEXT索引。这时，要重建索引，只需进行一个 QUICK 修理操作： mysql> ** REPAIR TABLE *tbl_name* QUICK;** 注意，假如你使用 **myisamchk**来执行一项修改表索引的操作 (诸如修理或分析 ), 则使用最小单词长度和最大单词长度以及停止字的默认全文参数值重建FULLTEXT索引，除非你已另外指定。这会导致问询失败。发生这个问题的原因是只有服务器认识这些参数。它们的存储位置不在 MyISAM 索引文件中。若你已经修改了最小单词长度或最大单词长度或服务器中的停止字，为避免这个问题，为你对**mysqld**所使用的**myisamchk**指定同样的 ft_min_word_len、 ft_max_word_len和ft_stopword_file值。例如，假如你已经将最小单词长度设置为 3, 则你可以这样修改一个带有**myisamchk**的表： shell> ** myisamchk --recover --ft_min_word_len=3 *tbl_name*.MYI** 为保证 **myisamchk** 及服务器对全文参数使用相同的值，可将每一项都放在供选文件中的 [mysqld]和 [myisamchk] 部分： [mysqld] ft_min_word_len=3 [myisamchk] ft_min_word_len=3 使用 REPAIR TABLE、 ANALYZE TABLE、OPTIMIZE TABLE或ALTER TABLE来代替使用 **myisamchk** 。这些语句通过服务器来执行，服务器知道使用哪个全文参数值更加合适。 ### 12.8. Cast函数和操作符 - BINARY BINARY操作符将后面的字符串抛给一个二进制字符串。这是一种简单的方式来促使逐字节而不是逐字符的进行列比较。这使得比较区分大小写，即使该列不被定义为 BINARY或 BLOB。BINARY也会产生结尾空白，从而更加显眼。 mysql> **SELECT 'a' = 'A';** -> 1 mysql> **SELECT BINARY 'a' = 'A';** -> 0 mysql> **SELECT 'a' = 'a ';** -> 1 mysql> **SELECT BINARY 'a' = 'a ';** -> 0 BINARY影响整个比较；它可以在任何操作数前被给定，而产生相同的结果。 BINARY *str*是CAST(*str* AS BINARY)的缩略形式。注意，在一些语境中，假如你将一个编入索引的列派给BINARY, MySQL 将不能有效使用这个索引。假如你想要将一个 BLOB值或其它二进制字符串进行区分大小写的比较，你可利用二进制字符串没有字符集这一事实实现这个目的，这样就不会有文书夹的概念。为执行一个区分大小写的比较，可使用 CONVERT()函数将一个字符串值转化为一个不区分大小写的字符集。其结果为一个非二进制字符串，因此 LIKE 操作也不会区分大小写： SELECT 'A' LIKE CONVERT(*blob_col* USING latin1) FROM *tbl_name*; 若要使用一个不同的字符集, 替换其在上述语句中的latin1名。 CONVERT()一般可用于比较出现在不同字符集中的字符串。 - CAST(*expr* AS *type*), CONVERT(*expr*,*type*) , CONVERT(*expr* USING *transcoding_name*) CAST() 和CONVERT() 函数可用来获取一个类型的值，并产生另一个类型的值。这个*类型* 可以是以下值其中的一个： - BINARY[(*N*)] - CHAR[(*N*)] - DATE - DATETIME - DECIMAL - SIGNED [INTEGER] - TIME - UNSIGNED [INTEGER] BINARY 产生一个二进制字符串。关于它怎样影响比较结果的说明见本章中 BINARY操作符项。假如给定了随意长度*N*，则 BINARY[*N*] 使 cast使用该参数的不多于 *N*个字节。同样的， CHAR[*N*]会使 cast 使用该参数的不多于*N*个字符。 CAST() and CONVERT(... USING ...) 是标准 SQL语法。CONVERT()的非USING 格式是ofis ODBC语法。带有USING的CONVERT() 被用来在不同的字符集之间转化数据。在 MySQL中, 自动译码名和相应的字符集名称相同。例如。这个语句将服务器的默认字符集中的字符串 'abc'转化为utf8字符集中相应的字符串： SELECT CONVERT('abc' USING utf8); 当你想要在一个CREATE ... SELECT 语句中创建一个特殊类型的列，则cast函数会很有用： CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE); 该函数也用于ENUM 列按词法顺序的排序。通常ENUM列的排序在使用内部数值时发生。将这些值按照词法顺序派给 CHAR 结果： SELECT *enum_col* FROM *tbl_name* ORDER BY CAST(*enum_col* AS CHAR); CAST(*str* AS BINARY)和BINARY *str*相同*。* CAST(*expr* AS CHAR) 将表达式视为一个带有默认字符集的字符串。若用于一个诸如 CONCAT('Date: ',CAST(NOW() AS DATE))这样的比较复杂的表达式的一部分，CAST()也会改变结果。你不应在不同的格式中使用 CAST() 来析取数据，但可以使用诸如LEFT() 或 EXTRACT() 的样的字符串函数来代替。请参见[12.5节，“日期和时间函数”](# "12.5. Date and Time Functions")。若要在数值语境中将一个字符串派给一个数值, 通常情况下，除了将字符串值作为数字使用外，你不需要做任何事： mysql> **SELECT 1+'1';** -> 2 若要在一个字符串语境中使用一个数字，该数字会被自动转化为一个BINARY 字符串。 mysql> **SELECT CONCAT('hello you ',2);** -> 'hello you 2' MySQL 支持带符号和无符号的64比特值的运算。若你正在使用数字操作符 (如 +) 而其中一个操作数为无符号整数，则结果为无符号。可使用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 若你在一个算术运算中使用了一个字符串，它会被转化为一个浮点数。 ### 12.9. 其他函数 [ 12.9.1. 位函数](#)[ 12.9.2. 加密函数](#)[ 12.9.3. 信息函数](#)[ 12.9.4. 其他函数](#) ### 12.9.1. 位函数对于比特运算，MySQL 使用 BIGINT (64比特) 算法，因此这些操作符的最大范围是 64 比特。 - | Bitwise OR: mysql> **SELECT 29 | 15;** -> 31 其结果为一个64比特无符号整数。 - & Bitwise AND: mysql> **SELECT 29 & 15;** -> 13 其结果为一个64比特无符号整数。 - ^ Bitwise XOR: mysql> **SELECT 1 ^ 1;** -> 0 mysql> **SELECT 1 ^ 0;** -> 1 mysql> **SELECT 11 ^ 3;** -> 8 结果为一个64比特无符号整数。 - << 把一个longlong (BIGINT)数左移两位。 mysql> **SELECT 1 << 2;** -> 4 其结果为一个64比特无符号整数。 - >> 把一个longlong (BIGINT)数右移两位。 mysql> **SELECT 4 >> 2;** -> 1 其结果为一个64比特无符号整数。 - ~ 反转所有比特。 mysql> **SELECT 5 & ~1;** -> 4 其结果为一个64比特无符号整数。 - BIT_COUNT(*N*) 返回参数*N*中所设置的比特数 mysql> **SELECT BIT_COUNT(29);** -> 4 ### 12.9.2. 加密函数本节介绍了加密和加密值。若你想要储存一些由可能包含任意字节值的加密函数返回的结果，使用BLOB列而不是 CHAR 或VARCHAR 列，从而避免由于结尾空格的删除而改变一些数据值的潜在问题。 - AES_ENCRYPT(*str*,*key_str*) , AES_DECRYPT(*crypt_str*,*key_str*) 这些函数允许使用官方AES进行加密和数据加密 (高级加密标准 ) 算法, 即以前人们所熟知的 “Rijndael”。保密关键字的长度为128比特，不过你可以通过改变源而将其延长到 256 比特。我们选择了 128比特的原因是它的速度要快得多，且对于大多数用途而言这个保密程度已经够用。输入参数可以为任何长度。若任何一个参数为NULL,则函数的结果也是NULL。因为 AES 是块级算法，使用填充将不均衡长度字符串编码，这样结果字符串的长度的算法为 16 * (trunc(*string_length* / 16) + 1)。若 AES_DECRYPT()检测到无效数据或不正确填充，它会返回 NULL。然而，若输入的资料或密码无效时， AES_DECRYPT()有可能返回一个非 NULL 值 (可能为无用信息 )。你可以通过修改你的问询，从而使用AES函数以加密形式来存储数据： INSERT INTO t VALUES (1,AES_ENCRYPT('text','password')); AES_ENCRYPT()和AES_DECRYPT() 可以被看作MySQL中普遍通用的密码最安全的加密函数。 - DECODE(*crypt_str*,*pass_str*) 使用 *pass_str* 作为密码，解密加密字符串 *crypt_str*， *crypt_str*应该是由ENCODE()返回的字符串。 - ENCODE(*str*,*pass_str*) 使用*pass_str*作为密码，解密 *str*。使用DECODE()解密结果。结果是一个和*str*长度相同的二进制字符串。若你想要将其保留在一个列中，可使用 BLOB 列类型。 - DES_DECRYPT(*crypt_str*[,*key_str*]) 使用DES_ENCRYPT()加密一个字符串。若出现错误，这个函数会返回 NULL。注意，这个函数只有当MySQL在SSL 的支持下配置完毕时才会运作。请参见[5.8.7节，“使用安全连接”](# "5.8.7. Using Secure Connections"). 假如没有给定 *key_str* 参数， DES_DECRYPT() 会首先检查加密字符串的第一个字节，从而确定用来加密原始字符串的DES密码关键字数字，之后从DES关键字文件中读取关键字从而解密信息。为使其运行，用户必须享有 SUPER 特权。可以选择--des-key-file服务器指定关键字文件。假如你向这个函数传递一个*key_str*参数，该字符串被用作解密信息的关键字。若 *crypt_str* 参数看起来不是一个加密字符串， MySQL 会返回给定的 *crypt_str*。 - DES_ENCRYPT(*str*[,(*key_num*|*key_str*)]) 用Triple-DES 算法给出的关键字加密字符串。若出现错误，这个函数会返回NULL。注意，这个函数只有当MySQL 在SSL的支持下配置完毕后才会运行。请参见[5.8.7节，“使用安全连接”](# "5.8.7. Using Secure Connections"). 使用的加密关键字的选择基于第二个到 DES_ENCRYPT()的参数，假如给定： <table border="1" cellpadding="0" id="table9"><tr><td> 参数</td> <td> 说明</td> </tr><tr><td> 无参数</td> <td> 使用来自DES关键字文件的第一个关键字。</td> </tr><tr><td> key_num</td> <td> 使用DES 关键字文件给出的关键字数字(0-9)。 </td> </tr><tr><td> key_str</td> <td> 使用给出的关键字字符串为 str 加密。</td> </tr></table> 选择--des-key-file服务器指定关键字文件。返回字符串是一个二进制字符串，其中第一个字符为 CHAR(128 | key_num)。加上 128使得识别加密关键字更加容易。若你使用一个字符串关键字，则 *key_num*为127。结果的字符串长度为 *new_len* = *orig_len* + (8-(*orig_len* % 8))+1。 DES关键字文件中的每一行都具有如下格式： * key_num**des_key_str* 每个*key_num*必须是一个从0到0范围内的数字。文件中行的排列顺序是任意的。 *des_key_str* 是用来加密信息的字符串。在数字和关键字之间应该至少有一个空格。若你未指定任何到DES_ENCRYPT()的关键字参数，则第一个关键字为默认的使用关键字。使用FLUSH DES_KEY_FILE语句，你可以让 MySQL从关键字文件读取新的关键字值。这要求你享有 RELOAD特权。拥有一套默认关键字的一个好处就是它向应用程序提供了一个检验加密列值的方式，而无须向最终用户提供解密这些值的权力。 mysql> **SELECT customer_address FROM customer_table ** > WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number'); - ENCRYPT(*str*[,*salt*]) 使用Unix crypt() 系统调用加密 *str*。 *salt*参数应为一个至少包含2个字符的字符串。若没有给出 *salt*参数，则使用任意值。 mysql> **SELECT ENCRYPT('hello');** -> 'VxuFAJXVARROc' 至少在一些系统中，ENCRYPT()除了s*tr*的前八位字符之外会忽略所有内容。这个行为由下划线的crypt() 系统调用的执行所决定。假如crypt()在你的系统中不可用(正如在 Windows系统), ENCRYPT() 则会始终返回NULL。鉴于这个原因，我们向你推荐使用 MD5() 或SHA1() 来代替,因为这两个函数适合所有的平台。 - MD5(*str*) 为字符串算出一个 MD5 128比特检查和。该值以32位十六进制数字的二进制字符串的形式返回, 若参数为 NULL 则会返回 NULL。例如，返回值可被用作散列关键字。 mysql> **SELECT MD5('testing');** -> 'ae2b1fca515949e5d54fb22b8ed95575' 这是"RSA Data Security, Inc. MD5 Message-Digest Algorithm." 假如你想要将这个值转化为大写字母，参见[12.8节，“Cast函数和操作符”](# "12.8. Cast Functions and Operators")中BINARY操作符项中给出的二进制字符串转换。 - OLD_PASSWORD(*str*) 当PASSWORD()的执行变为改善安全性时，OLD_PASSWORD()会被添加到 MySQL。OLD_PASSWORD()返回从前的PASSWORD()执行值( 4.1之前)，同时允许你为任何4.1 之前的需要连接到你的5.1 版本MySQL服务器前客户端设置密码，从而不至于将它们切断。请参见[5.7.9节，“MySQL 4.1中的密码哈希处理”](# "5.7.9. Password Hashing in MySQL 4.1")。 - PASSWORD(*str*) 从原文密码*str*计算并返回密码字符串，当参数为 NULL 时返回 NULL。这个函数用于用户授权表的Password列中的加密MySQL密码存储 mysql> **SELECT PASSWORD('badpwd');** -> '7f84554057dd964b' PASSWORD() 加密是单向的 (不可逆 )。 PASSWORD() 执行密码加密与Unix 密码被加密的方式不同。请参见ENCRYPT()。 **注释**： PASSWORD()函数在MySQL服务器中的鉴定系统使用；你*不*应将它用在你个人的应用程序中。为达到同样目的，可使用 MD5()或SHA1() 代替。更多关于在您的应用程序中处理密码及安全鉴定的信息见RFC 2195 - SHA1(*str*) SHA(*str*) 为字符串算出一个 SHA1 160比特检查和，如RFC 3174 (安全散列算法 )中所述。该值被作为40位十六进制数字返回，而当参数为NULL 时则返回 NULL。这个函数的一个可能的用处就在于其作为散列关键字。你也可以将其作为存储密码的密码安全函数使用。 mysql> **SELECT SHA1('abc');** -> 'a9993e364706816aba3e25717850c26c9cd0d89d' SHA1()可以被视为一个密码更加安全的函数，相当于 MD5()。 SHA() 和SHA1()具有相同的意义。 ### 12.9.3. 信息函数 - BENCHMARK(*count*,*expr*) BENCHMARK() 函数重复*count*次执行表达式 *expr*。它可以被用于计算 MySQL 处理表达式的速度。结果值通常为 0。另一种用处来自 **mysql**客户端内部,能够报告问询执行的次数： ~~~ mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye')); ~~~ ~~~ +----------------------------------------------+ ~~~ ~~~ | BENCHMARK(1000000,ENCODE('hello','goodbye')) | ~~~ ~~~ +----------------------------------------------+ ~~~ ~~~ | 0 | ~~~ ~~~ +----------------------------------------------+ ~~~ ~~~ 1 row in set (4.74 sec) ~~~ 此处报告的时间是客户端上的共用时间，而不是服务器端上的CPU时间。建议执行多遍BENCHMARK()，并解释与服务器机器负荷程度有关的结果。 · CHARSET(*str*) 返回字符串自变量的字符集。 ~~~ mysql> SELECT CHARSET('abc'); ~~~ ~~~ -> 'latin1' ~~~ ~~~ mysql> SELECT CHARSET(CONVERT('abc' USING utf8)); ~~~ ~~~ -> 'utf8' ~~~ ~~~ mysql> SELECT CHARSET(USER()); ~~~ ~~~ -> 'utf8' ~~~ · COERCIBILITY(*str*) 返回字符串自变量的整序可压缩性值。 ~~~ mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci); ~~~ ~~~ -> 0 ~~~ ~~~ mysql> SELECT COERCIBILITY(USER()); ~~~ ~~~ -> 3 ~~~ ~~~ mysql> SELECT COERCIBILITY('abc'); ~~~ ~~~ -> 4 ~~~ 返回值具有如下意义：**** <table border="1" cellpadding="0" id="table10"><tr><td> 可压缩性 </td> <td> 意义</td> <td> 举例</td> </tr><tr><td> 0</td> <td> 明确排序</td> <td> 带有COLLATE 子句的值</td> </tr><tr><td> 1</td> <td> 无排序</td> <td> 不同排序的字符串连接 </td> </tr><tr><td> 2</td> <td> 明确排序</td> <td> 列值</td> </tr><tr><td> 3</td> <td> 系统常量</td> <td> USER()返回值</td> </tr><tr><td> 4</td> <td> 可压缩</td> <td> 文字字符串</td> </tr><tr><td> 5</td> <td> 可忽略</td> <td> NULL得来的NULL或一个表达式 </td> </tr></table> ** ** 下方值得优先级较高。 - COLLATION(*str*) 返回惠字符串参数的排序方式。 mysql> **SELECT COLLATION('abc');** -> 'latin1_swedish_ci' mysql> **SELECT COLLATION(_utf8'abc');** -> 'utf8_general_ci' - CONNECTION_ID() 返回对于连接的连接ID (线程ID)。每个连接都有各自的唯一 ID。 mysql> **SELECT CONNECTION_ID();** -> 23786 - CURRENT_USER, CURRENT_USER() 返回当前话路被验证的用户名和主机名组合。这个值符合确定你的存取权限的MySQL 账户。在被指定SQL SECURITY DEFINER特征的存储程序内， CURRENT_USER() 返回程序的创建者。 CURRENT_USER()的值可以和USER()的值有所不同。 mysql> **SELECT USER();** -> 'davida@localhost' mysql> **SELECT * FROM mysql.user;** ERROR 1044: Access denied for user ''@'localhost' to database 'mysql' mysql> **SELECT CURRENT_USER();** -> '@localhost' 这个例子解释了虽然客户端指定了一个 davida用户名 (正如USER()函数的值所指示的), 服务器却使用一个匿名的用户账户确认该客户端 (见CURRENT_USER()值得空用户名部分 )。这种情况发生的一个原因是 One 在向 davida的授权列表中没有足够的账户。 CURRENT_USER() 返回的字符串使用 utf8字符集。 - DATABASE() 返回使用 utf8 字符集的默认(当前)数据库名。在存储程序里，默认数据库是同该程序向关联的数据库，但并不一定与调用语境的默认数据库相同。 mysql> **SELECT DATABASE();** -> 'test' 若没有默认数据库, DATABASE()返回 NULL。 - FOUND_ROWS() A SELECT语句可能包括一个 LIMIT 子句，用来限制服务器返回客户端的行数。在有些情况下，需要不用再次运行该语句而得知在没有LIMIT 时到底该语句返回了多少行。为了知道这个行数, 包括在SELECT 语句中选择 SQL_CALC_FOUND_ROWS ，随后调用 FOUND_ROWS() ： mysql> **SELECT SQL_CALC_FOUND_ROWS * FROM *tbl_name*** -> **WHERE id > 100 LIMIT 10;** mysql> **SELECT FOUND_ROWS();** 第二个 SELECT返回一个数字，指示了在没有LIMIT子句的情况下，第一个SELECT返回了多少行 (若上述的 SELECT语句不包括 SQL_CALC_FOUND_ROWS 选项,则使用LIMIT 和不使用时，FOUND_ROWS() 可能会返回不同的结果）。通过 FOUND_ROWS()的有效行数是瞬时的，并且不用于越过SELECT SQL_CALC_FOUND_ROWS语句后面的语句。若你需要稍候参阅这个值，那么将其保存： mysql> **SELECT SQL_CALC_FOUND_ROWS * FROM ... ;** mysql> **SET @rows = FOUND_ROWS();** 假如你正在使用 SELECT SQL_CALC_FOUND_ROWS, MySQL 必须计算出在全部结果集合中有所少行。然而，这比不用LIMIT而再次运行问询要快，原因是结果集合不需要被送至客户端。 SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 在当你希望限制一个问询返回的行数时很有用，同时还能不需要再次运行问询而确定全部结果集合中的行数。一个例子就是提供页式显示的Web脚本，该显示包含显示搜索结果其它部分的页的连接。使用FOUND_ROWS() 使你确定剩下的结果需要多少其它的页。 SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 的应用对于UNION 问询比对于简单SELECT 语句更为复杂，原因是在UNION 中，LIMIT 可能会出现在多个位置。它可能适用于UNION中的个人 SELECT语句，或是总体上到UNION 结果的全程。 SQL_CALC_FOUND_ROWS对于 UNION的意向是它应该不需要全程LIMIT而返回应返回的行数。SQL_CALC_FOUND_ROWS 和UNION 一同使用的条件是： - SQL_CALC_FOUND_ROWS 关键词必须出现在UNION的第一个 SELECT中。 - FOUND_ROWS()的值只有在使用 UNION ALL时才是精确的。若使用不带ALL的UNION，则会发生两次删除，而 FOUND_ROWS() 的指只需近似的。 - 假若UNION 中没有出现 LIMIT ，则SQL_CALC_FOUND_ROWS 被忽略，返回临时表中的创建的用来处理UNION的行数。 - LAST_INSERT_ID() LAST_INSERT_ID(*expr*) 自动返回*最后一个*INSERT或 UPDATE 问询为 AUTO_INCREMENT列设置的*第一个 *发生的值。 mysql> **SELECT LAST_INSERT_ID();** -> 195 产生的ID *每次连接后*保存在服务器中。这意味着函数向一个给定客户端返回的值是该客户端产生对影响AUTO_INCREMENT列的最新语句第一个 AUTO_INCREMENT值的。这个值不能被其它客户端影响，即使它们产生它们自己的 AUTO_INCREMENT值。这个行为保证了你能够找回自己的 ID 而不用担心其它客户端的活动，而且不需要加锁或处理。假如你使用一个非“magic”值来更新某一行的AUTO_INCREMENT 列，则LAST_INSERT_ID() 的值不会变化(换言之, 一个不是 NULL也不是 0的值)。 **重点**: 假如你使用单INSERT语句插入多个行， LAST_INSERT_ID() *只*返回插入的*第一*行产生的值。其原因是这使依靠其它服务器复制同样的 INSERT语句变得简单。例如： ~~~ mysql> USE test; ~~~ ~~~ Database changed ~~~ ~~~ mysql> CREATE TABLE t ( ~~~ ~~~ -> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, ~~~ ~~~ -> name VARCHAR(10) NOT NULL ~~~ ~~~ -> ); ~~~ ~~~ Query OK, 0 rows affected (0.09 sec) ~~~ ~~~ ~~~ ~~~ mysql> INSERT INTO t VALUES (NULL, 'Bob'); ~~~ ~~~ Query OK, 1 row affected (0.01 sec) ~~~ ~~~ ~~~ ~~~ mysql> SELECT * FROM t; ~~~ ~~~ +----+------+ ~~~ ~~~ | id | name | ~~~ ~~~ +----+------+ ~~~ ~~~ | 1 | Bob | ~~~ ~~~ +----+------+ ~~~ ~~~ 1 row in set (0.01 sec) ~~~ ~~~ ~~~ ~~~ mysql> SELECT LAST_INSERT_ID(); ~~~ ~~~ +------------------+ ~~~ ~~~ | LAST_INSERT_ID() | ~~~ ~~~ +------------------+ ~~~ ~~~ | 1 | ~~~ ~~~ +------------------+ ~~~ ~~~ 1 row in set (0.00 sec) ~~~ ~~~ ~~~ ~~~ mysql> INSERT INTO t VALUES ~~~ ~~~ -> (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa'); ~~~ ~~~ Query OK, 3 rows affected (0.00 sec) ~~~ ~~~ Records: 3 Duplicates: 0 Warnings: 0 ~~~ ~~~ ~~~ ~~~ mysql> SELECT * FROM t; ~~~ ~~~ +----+------+ ~~~ ~~~ | id | name | ~~~ ~~~ +----+------+ ~~~ ~~~ | 1 | Bob | ~~~ ~~~ | 2 | Mary | ~~~ ~~~ | 3 | Jane | ~~~ ~~~ | 4 | Lisa | ~~~ ~~~ +----+------+ ~~~ ~~~ 4 rows in set (0.01 sec) ~~~ ~~~ ~~~ ~~~ mysql> SELECT LAST_INSERT_ID(); ~~~ ~~~ +------------------+ ~~~ ~~~ | LAST_INSERT_ID() | ~~~ ~~~ +------------------+ ~~~ ~~~ | 2 | ~~~ ~~~ +------------------+ ~~~ ~~~ 1 row in set (0.00 sec) ~~~ 虽然第二个问询将3 个新行插入 t, 对这些行的第一行产生的 ID 为 2, 这也是 LAST_INSERT_ID()返回的值。假如你使用 INSERT IGNORE而记录被忽略，则AUTO_INCREMENT 计数器不会增量，而 LAST_INSERT_ID() 返回0, 这反映出没有插入任何记录。若给出作为到LAST_INSERT_ID()的参数*expr*，则参数的值被函数返回，并作为被LAST_INSERT_ID()返回的下一个值而被记忆。这可用于模拟序列： - 创建一个表，用来控制顺序计数器并使其初始化： o mysql> **CREATE TABLE sequence (id INT NOT NULL);** o mysql> **INSERT INTO sequence VALUES (0);** - 使用该表产生这样的序列数： o mysql> **UPDATE sequence SET id=LAST_INSERT_ID(id+1);** o mysql> **SELECT LAST_INSERT_ID();** UPDATE 语句会增加顺序计数器并引发向LAST_INSERT_ID() 的下一次调用，用来返回升级后的值。 SELECT 语句会检索这个值。 mysql_insert_id() C API函数也可用于获取这个值。见[25.2.3.36节，“mysql_insert_id()”](# "25.2.3.36. mysql_insert_id()"). 你可以不用调用LAST_INSERT_ID()而产生序列，但这样使用这个函数的效用在于 ID值被保存在服务器中，作为自动产生的值。它适用于多个用户，原因是多个用户均可使用 UPDATE语句并用SELECT语句(或mysql_insert_id()),得到他们自己的序列值，而不会影响其它产生他们自己的序列值的客户端或被其它产生他们自己的序列值的客户端所影响。注意， mysql_insert_id() 仅会在INSERT 和UPDATE语句后面被升级，因此你不能在执行了其它诸如SELECT或 SET 这样的SQL语句后使用 C API 函数来找回 LAST_INSERT_ID(*expr*) 对应的值。 - ROW_COUNT() ROW_COUNT()返回被前面语句升级的、插入的或删除的行数。这个行数和 **mysql** 客户端显示的行数及 mysql_affected_rows() C API 函数返回的值相同。 mysql> **INSERT INTO t VALUES(1),(2),(3);** 问询完成, 表中有3 行 (0.00秒) 记录: 3 重复: 0 警告: 0 mysql> **SELECT ROW_COUNT();** +-------------+ | ROW_COUNT() | +-------------+ | 3 | +-------------+ 表中有1行 (0.00 秒) mysql> **DELETE FROM t WHERE i IN(1,2);** 问询完成, 找到2 行 (0.00 秒) mysql> **SELECT ROW_COUNT();** +-------------+ | ROW_COUNT() | +-------------+ | 2 | +-------------+ 表中有1行 (0.00 秒) - SCHEMA() 这个函数和 DATABASE()具有相同的意义。 - SESSION_USER() SESSION_USER()和 USER()具有相同的意义。 - SYSTEM_USER() SYSTEM_USER()合 USER()具有相同的意义。 - USER() 返回当前 MySQL用户名和机主名/ mysql> **SELECT USER();** -> 'davida@localhost' 这个值指示了你指定的连接服务器时的用户名,及你所连接的客户主机。这个值可以和CURRENT_USER() 的值不同。你可以这样提取用户名部分： mysql> **SELECT SUBSTRING_INDEX(USER(),'@',1);** -> 'davida' 由于 USER() 返回一个utf8 字符集中的值，你也应确保'@' 字符串文字在该字符集中得到解释： mysql> **SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);** -> 'davida' - VERSION() 返回指示 MySQL 服务器版本的字符串。这个字符串使用 utf8 字符集。 mysql> **SELECT VERSION();** -> '5.1.2-alpha-standard' 注意，假如你的版本字符串以-log结尾，这说明登录已被激活。 ### 12.9.4. 其他函数 - DEFAULT(*col_name*) 返回一个表列的默认值。若该列没有默认值则会产生错误。 mysql> **UPDATE t SET i = DEFAULT(i)+1 WHERE id < 100;** - FORMAT(*X*,*D*) 将数字*X*的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 *D*位，并将结果以字符串的形式返回。若 *D*为 0, 则返回结果不带有小数点，或不含小数部分。 mysql> **SELECT FORMAT(12332.123456, 4);** -> '12,332.1235' mysql> **SELECT FORMAT(12332.1,4);** -> '12,332.1000' mysql> **SELECT FORMAT(12332.2,0);** -> '12,332' - GET_LOCK(*str*,*timeout*) 设法使用字符串*str* 给定的名字得到一个锁，超时为*timeout*秒。若成功得到锁，则返回 1，若操作超时则返回0 (例如,由于另一个客户端已提前封锁了这个名字 ),若发生错误则返回NULL (诸如缺乏记忆或线程**mysqladmin kill**被断开 )。假如你有一个用GET_LOCK()得到的锁，当你执行RELEASE_LOCK()或你的连接断开(正常或非正常)时，这个锁就会解除。这个函数可用于执行应用程序锁或模拟记录锁定。名称被锁定在服务器范围内。假如一个名字已经被一个客户端封锁， GET_LOCK() 会封锁来自另一个客户端申请封锁同一个名字的任何请求。这使对一个封锁名达成协议的客户端使用这个名字合作执行建议锁。然而要知道它也允许不在一组合作客户端中的一个客户端封锁名字，不论是服役的还是非故意的，这样阻止任何合作中的客户端封锁这个名字。一个减少这种情况发生的办法就是使用数据库特定的或应用程序特定的封锁名。例如，使用*db_name.str*或 *app_name.str* 形式的封锁名。 mysql> **SELECT GET_LOCK('lock1',10);** -> 1 mysql> **SELECT IS_FREE_LOCK('lock2');** -> 1 mysql> **SELECT GET_LOCK('lock2',10);** -> 1 mysql> **SELECT RELEASE_LOCK('lock2');** -> 1 mysql> **SELECT RELEASE_LOCK('lock1');** -> NULL 注意，第二个 RELEASE_LOCK()调用返回 NULL ，原因是锁'lock1' 杯第二个GET_LOCK()调用解开。 - INET_ATON(*expr*) 给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。地址可以是4或8比特地址。 mysql> **SELECT INET_ATON('209.207.224.40');** -> 3520061480 产生的数字总是按照网络字节顺序。如上面的例子，数字按照 209×2563 + 207×2562 + 224×256 + 40 进行计算。 INET_ATON() 也能理解短格式 IP 地址： mysql> **SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');** -> 2130706433, 2130706433 **注释**：在存储由INET_ATON() 产生的值时，推荐你使用 INT UNSIGNED 列。假如你使用 (带符号) INT列, 则相应的第一个八位组大于127的IP 地址值会被截至 2147483647 (即, INET_ATON('127.255.255.255') 所返回的值)。请参见[11.2节，“数值类型”](# "11.2. Numeric Types")。 - INET_NTOA(*expr*) 给定一个数字网络地址 (4 或 8 比特),返回作为字符串的该地址的电地址表示。 mysql> **SELECT INET_NTOA(3520061480);** -> '209.207.224.40' - IS_FREE_LOCK(*str*) 检查名为*str*的锁是否可以使用 (换言之,没有被封锁)。若锁可以使用，则返回 1 (没有人在用这个锁), 若这个锁正在被使用，则返回0 ，出现错误则返回 NULL (诸如不正确的参数 )。 - IS_USED_LOCK(*str*) 检查名为*str*的锁是否正在被使用(换言之,被封锁)。若被封锁，则返回使用该锁的客户端的连接标识符。否则返回 NULL。 - MASTER_POS_WAIT(*log_name*,*log_pos*[,*timeout*]) 该函数对于控制主从同步很有用处。它会持续封锁，直到从设备阅读和应用主机记录中所有补充资料到指定的位置。返回值是其为到达指定位置而必须等待的记录事件的数目。若从设备SQL线程没有被启动、从设备主机信息尚未初始化、参数不正确或出现任何错误，则该函数返回 NULL。若超时时间被超过，则返回-1。若在MASTER_POS_WAIT() 等待期间，从设备SQL线程中止，则该函数返回 NULL。若从设备由指定位置通过，则函数会立即返回结果。假如已经指定了一个*超时时间*值，当 *超时时间*秒数经过后MASTER_POS_WAIT()会停止等待。*超时时间* 必须大于 0；一个为零或为负值的 *超时时间* 表示没有超市时间。 - NAME_CONST(*name*,*value*) 返回给定值。当用来产生一个结果集合列时, NAME_CONST()促使该列使用给定名称。 mysql> **SELECT NAME_CONST('myname', 14);** +--------+ | myname | +--------+ | 14 | +--------+ 这个函数被添加进 MySQL 5.0.12。它只做内部使用。服务器在书写来自包含局部程序变量的存储程序的语句时会用到它，详见[20.4节，“存储子程序和触发程序的二进制日志功能”](# "20.4. Binary Logging of Stored Routines and Triggers")。你可能会在**mysqlbinlog**的书橱中看到这个函数。 - RELEASE_LOCK(*str*) 解开被GET_LOCK()获取的，用字符串*str*所命名的锁。若锁被解开，则返回 1，若改线程尚未创建锁，则返回0 (此时锁没有被解开 ), 若命名的锁不存在，则返回 NULL。若该锁从未被对GET_LOCK()的调用获取，或锁已经被提前解开，则该锁不存在。 DO 语句和RELEASE_LOCK()同时使用很方便。请参见[13.2.2节，“DO语法”](# "13.2.2. DO Syntax")。 - SLEEP(*duration*) 睡眠(暂停) 时间为*duration*参数给定的秒数，然后返回 0。若 SLEEP() 被中断,它会返回 1。 duration 或许或包括一个给定的以微秒为单位的分数部分。 - UUID() 返回一个通用唯一标识符(UUID) ，其产生的根据是《DCE 1.1: 远程过程调用》 (附录A) CAE (公共应用软件环境) 的说明，该作品于1997年10月由 The Open Group 出版 (文件编号 C706, [ http://www.opengroup.org/public/pubs/catalog/c706.htm](http://www.opengroup.org/public/pubs/catalog/c706.htm)). UUID被设计成一个在时间和空间上都独一无二的数字。2个对UUID() 的调用应产生2个不同的值，即使这些调用的执行是在两个互不相连的单独电脑上进行。 UUID 是一个由5位十六进制数的字符串表示的128比特数字，其格式为 aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee ： - 前3个数字从一个时间戳产生。 - 第4 个数字保持暂时唯一性，以防时间戳值失去单一性 (例如, 由于经济时)。 - 第5个数字是一个 IEEE 802 节点号，它提供空间唯一性。若后者不可用，则用一个随机数字替换。 (例如, 由于主机没有以太网卡，或我们不知道怎样在你的操作系统上找到界面的机器地址 )。假若这样，空间唯一性就不能得到保证。尽管如此，一个冲突的发生机率还是*非常*低的。目前,一个界面的 MAC 地址尽被FreeBSD 和 Linux考虑到。在其它操作系统中, MySQL使用随机产生的 48比特数字。 mysql> **SELECT UUID();** -> '6ccd780c-baba-1026-9564-0040f4311e29' 注意， UUID() 不支持复制功能。 - VALUES(*col_name*) 在一个INSERT … ON DUPLICATE KEY UPDATE …语句中，你可以在UPDATE 子句中使用 VALUES(*col_name*)函数，用来访问来自该语句的INSERT 部分的列值。换言之，UPDATE 子句中的 VALUES(*col_name*) 访问需要被插入的*col_name *的值,并不会发生重复键冲突。这个函数在多行插入中特别有用。 VALUES()函数只在INSERT ... UPDATE 语句中有意义，而在其它情况下只会返回 NULL。请参见[13.2.4节，“INSERT语法”](# "13.2.4. INSERT Syntax"). mysql> **INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)** -> **ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);** ### 12.10. 与GROUP BY子句同时使用的函数和修改程序`` [12.10.1. GROUP BY（聚合）函数](#)[12.10.2. GROUP BY修改程序](#)[12.10.3. 具有隐含字段的GROUP BY](#) ### 12.10.1. GROUP BY（聚合）函数本章论述了用于一组数值操作的 group (集合)函数。除非另作说明， group 函数会忽略 NULL 值。假如你在一个不包含 ROUP BY子句的语句中使用一个 group函数，它相当于对所有行进行分组。 - AVG([DISTINCT] *expr*) 返回*expr*的平均值。 DISTINCT 选项可用于返回 *expr*的不同值的平均值。若找不到匹配的行，则AVG()返回 NULL 。 mysql> **SELECT student_name, AVG(test_score)** -> **FROM student** -> **GROUP BY student_name;** - BIT_AND(*expr*) 返回*expr*中所有比特的 bitwise AND 。计算执行的精确度为64比特(BIGINT) 。若找不到匹配的行，则这个函数返回 18446744073709551615 。(这是无符号 BIGINT 值，所有比特被设置为 1）。 - BIT_OR(*expr*) 返回*expr*中所有比特的bitwise OR。计算执行的精确度为64比特(BIGINT) 。若找不到匹配的行，则函数返回 0 。 - BIT_XOR(*expr*) 返回*expr*中所有比特的bitwise XOR。计算执行的精确度为64比特(BIGINT) 。若找不到匹配的行，则函数返回 0 。 - COUNT(*expr*) 返回SELECT语句检索到的行中非NULL值的数目。若找不到匹配的行，则COUNT() 返回 0 。 mysql> **SELECT student.student_name,COUNT(*)** -> **FROM student,course** -> **WHERE student.student_id=course.student_id** -> **GROUP BY student_name;** COUNT(*) 的稍微不同之处在于，它返回检索行的数目，不论其是否包含 NULL值。 SELECT 从一个表中检索，而不检索其它的列，并且没有 WHERE子句时， COUNT(*)被优化到最快的返回速度。例如： mysql> **SELECT COUNT(*) FROM student;** 这个优化仅适用于 MyISAM表, 原因是这些表类型会储存一个函数返回记录的精确数量，而且非常容易访问。对于事务型的存储引擎(InnoDB, BDB), 存储一个精确行数的问题比较多，原因是可能会发生多重事物处理, 而每个都可能会对行数产生影响。 - COUNT(DISTINCT *expr*,[*expr*...]) 返回不同的非NULL值数目。若找不到匹配的项，则COUNT(DISTINCT)返回 0 。 mysql> **SELECT COUNT(DISTINCT results) FROM student;** 在MySQL中, 你通过给定一个表达式列表而获取不包含NULL 不同表达式组合的数目。在标准 SQL中,你将必须在COUNT(DISTINCT ...)中连接所有表达式。 - GROUP_CONCAT(*expr*) 该函数返回带有来自一个组的连接的非NULL值的字符串结果。其完整的语法如下所示： GROUP_CONCAT([DISTINCT] *expr* [,*expr* ...] [ORDER BY {*unsigned_integer* | *col_name* | *expr*} [ASC | DESC] [,*col_name* ...]] [SEPARATOR *str_val*]) mysql> **SELECT student_name,** -> **GROUP_CONCAT(test_score)** -> **FROM student** -> **GROUP BY student_name;** Or: mysql> **SELECT student_name,** -> **GROUP_CONCAT(DISTINCT test_score** -> **ORDER BY test_score DESC SEPARATOR ' ')** -> **FROM student** -> **GROUP BY student_name;** 在MySQL中,你可以获取表达式组合的连接值。你可以使用DISTINCT删去重复值。假若你希望多结果值进行排序，则应该使用 ORDER BY子句。若要按相反顺序排列，将 DESC (递减) 关键词添加到你要用ORDER BY 子句进行排序的列名称中。默认顺序为升序；可使用ASC将其明确指定。 SEPARATOR 后面跟随应该被插入结果的值中间的字符串值。默认为逗号 (‘,’)。通过指定SEPARATOR '' ，你可以删除所有分隔符。使用group_concat_max_len系统变量，你可以设置允许的最大长度。程序中进行这项操作的语法如下，其中 val 是一个无符号整数： SET [SESSION | GLOBAL] group_concat_max_len = val; 若已经设置了最大长度，则结果被截至这个最大长度。 - MIN([DISTINCT] *expr*), MAX([DISTINCT] *expr*) 返回*expr* 的最小值和最大值。 MIN() 和 MAX() 的取值可以是一个字符串参数；在这些情况下，它们返回最小或最大字符串值。请参见[7.4.5节，“MySQL如何使用索引”](# "7.4.5. How MySQL Uses Indexes")。 DISTINCT关键词可以被用来查找*expr*的不同值的最小或最大值，然而，这产生的结果与省略DISTINCT 的结果相同。若找不到匹配的行，MIN()和MAX()返回 NULL 。 mysql> **SELECT student_name, MIN(test_score), MAX(test_score)** -> **FROM student** -> **GROUP BY student_name;** 对于MIN()、 MAX()和其它集合函数， MySQL当前按照它们的字符串值而非字符串在集合中的相关位置比较 ENUM和 SET 列。这同ORDER BY比较二者的方式有所不同。这一点应该在MySQL的未来版本中得到改善。 - STD(*expr*) STDDEV(*expr*) 返回*expr*的总体标准偏差。这是标准 SQL 的延伸。这个函数的STDDEV() 形式用来提供和Oracle 的兼容性。可使用标准SQL函数 STDDEV_POP() 进行代替。若找不到匹配的行，则这些函数返回 NULL 。 - STDDEV_POP(*expr*) 返回*expr*的总体标准偏差(VAR_POP()的平方根)。你也可以使用 STD() 或STDDEV(), 它们具有相同的意义，然而不是标准的 SQL。若找不到匹配的行，则STDDEV_POP()返回 NULL。 - STDDEV_SAMP(*expr*) 返回*expr*的样本标准差 ( VAR_SAMP()的平方根)。若找不到匹配的行，则STDDEV_SAMP() 返回 NULL 。 - SUM([DISTINCT] *expr*) 返回*expr*的总数。若返回集合中无任何行，则 SUM() 返回NULL。DISTINCT 关键词可用于 MySQL 5.1 中，求得*expr *不同值的总和。若找不到匹配的行，则SUM()返回 NULL。 - VAR_POP(*expr*) 返回*expr*总体标准方差。它将行视为总体，而不是一个样本，所以它将行数作为分母。你也可以使用 VARIANCE(),它具有相同的意义然而不是标准的 SQL。若找不到匹配的项，则VAR_POP()返回NULL。 - VAR_SAMP(*expr*) 返回*expr*的样本方差。更确切的说，分母的数字是行数减去1。若找不到匹配的行，则VAR_SAMP()返回NULL。 - VARIANCE(*expr*) 返回*expr*的总体标准方差。这是标准SQL 的延伸。可使用标准SQL 函数 VAR_POP() 进行代替。若找不到匹配的项，则VARIANCE()返回NULL。 ### 12.10.2. GROUP BY修改程序 GROUP BY子句允许一个将额外行添加到简略输出端 WITH ROLLUP 修饰符。这些行代表高层(或高聚集)简略操作。ROLLUP 因而允许你在多层分析的角度回答有关问询的问题。例如，它可以用来向OLAP (联机分析处理) 操作提供支持。设想一个名为sales 的表具有年份、国家、产品及记录销售利润的利润列： CREATE TABLE sales ( year INT NOT NULL, country VARCHAR(20) NOT NULL, product VARCHAR(32) NOT NULL, profit INT ); 可以使用这样的简单GROUP BY，每年对表的内容做一次总结： mysql> **SELECT year, SUM(profit) FROM sales GROUP BY year;** +------+-------------+ | year | SUM(profit) | +------+-------------+ | 2000 | 4525 | | 2001 | 3010 | +------+-------------+ 这个输出结果显示了每年的总利润，但如果你也想确定所有年份的总利润，你必须自己累加每年的单个值或运行一个加法询问。或者你可以使用 ROLLUP, 它能用一个问询提供双层分析。将一个 WITH ROLLUP修饰符添加到GROUP BY 语句，使询问产生另一行结果，该行显示了所有年份的总价值： mysql> **SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;** +------+-------------+ | year | SUM(profit) | +------+-------------+ | 2000 | 4525 | | 2001 | 3010 | | NULL | 7535 | +------+-------------+ 总计高聚集行被年份列中的NULL值标出。当有多重 GROUP BY 列时，ROLLUP产生的效果更加复杂。这时，每次在除了最后一个分类列之外的任何列出现一个 “break” (值的改变) ，则问讯会产生一个高聚集累计行。例如，在没有 ROLLUP的情况下，一个以年、国家和产品为基础的关于 sales 表的一览表可能如下所示： mysql> **SELECT year, country, product, SUM(profit)** -> **FROM sales** -> **GROUP BY year, country, product;** +------+---------+------------+-------------+ | year | country | product | SUM(profit) | +------+---------+------------+-------------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | | 2000 | USA | Calculator | 75 | | 2000 | USA | Computer | 1500 | | 2001 | Finland | Phone | 10 | | 2001 | USA | Calculator | 50 | | 2001 | USA | Computer | 2700 | | 2001 | USA | TV | 250 | +------+---------+------------+-------------+ 表示总值的输出结果仅位于年/国家/产品的分析级别。当添加了 ROLLUP后，问询会产生一些额外的行： mysql> **SELECT year, country, product, SUM(profit)** -> **FROM sales** -> **GROUP BY year, country, product WITH ROLLUP;** +------+---------+------------+-------------+ | year | country | product | SUM(profit) | +------+---------+------------+-------------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | Finland | NULL | 1600 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | | 2000 | India | NULL | 1350 | | 2000 | USA | Calculator | 75 | | 2000 | USA | Computer | 1500 | | 2000 | USA | NULL | 1575 | | 2000 | NULL | NULL | 4525 | | 2001 | Finland | Phone | 10 | | 2001 | Finland | NULL | 10 | | 2001 | USA | Calculator | 50 | | 2001 | USA | Computer | 2700 | | 2001 | USA | TV | 250 | | 2001 | USA | NULL | 3000 | | 2001 | NULL | NULL | 3010 | | NULL | NULL | NULL | 7535 | +------+---------+------------+-------------+ 对于这个问询，添加ROLLUP 子句使村输出结果包含了四层分析的简略信息，而不只是一个下面是怎样解释 ROLLUP输出： - 一组给定的年份和国家的每组产品行后面, 会产生一个额外的总计行，显示所有产品的总值。这些行将产品列设置为 NULL。 - 一组给定年份的行后面，会产生一个额外的总计行，显示所有国家和产品的总值。这些行将国家和产品列设置为 NULL。 - 最后, 在所有其它行后面，会产生一个额外的总计列，显示所有年份、国家及产品的总值。这一行将年份、国家和产品列设置为 NULL。 **使用ROLLUP 时的其它注意事项 ** 以下各项列出了一些MySQL执行ROLLUP的特殊状态：当你使用 ROLLUP时, 你不能同时使用 ORDER BY子句进行结果排序。换言之， ROLLUP 和ORDER BY 是互相排斥的。然而，你仍可以对排序进行一些控制。在 MySQL中， GROUP BY 可以对结果进行排序，而且你可以在GROUP BY列表指定的列中使用明确的 ASC和DESC关键词，从而对个别列进行排序。 (不论如何排序被ROLLUP添加的较高级别的总计行仍出现在它们被计算出的行后面）。 LIMIT可用来限制返回客户端的行数。LIMIT 用在 ROLLUP后面, 因此这个限制会取消被ROLLUP添加的行。例如: mysql> **SELECT year, country, product, SUM(profit)** -> **FROM sales** -> **GROUP BY year, country, product WITH ROLLUP** -> **LIMIT 5;** +------+---------+------------+-------------+ | year | country | product | SUM(profit) | +------+---------+------------+-------------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | Finland | NULL | 1600 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | +------+---------+------------+-------------+ 将ROLLUP同 LIMIT一起使用可能会产生更加难以解释的结果，原因是对于理解高聚集行，你所掌握的上下文较少。在每个高聚集行中的NULL 指示符会在该行被送至客户端时产生。服务器会查看最左边的改变值后面的GROUP BY子句指定的列。对于任何结果集合中的，有一个词匹配这些名字的列, 其值被设为 NULL。(若你使用列数字指定了分组列，则服务器会通过数字确定将哪个列设置为 NULL）。由于在高聚集行中的 NULL值在问询处理阶段被放入结果集合中，你无法将它们在问询本身中作为NULL值检验。例如，你无法将 HAVING product IS NULL 添加到问询中，从而在输出结果中删去除了高聚集行以外的部分。另一方面, NULL值在客户端不以 NULL 的形式出现，因而可以使用任何MySQL客户端编程接口进行检验。 ### 12.10.3. 具有隐含字段的GROUP BY MySQL 扩展了 GROUP BY的用途，因此你可以使用SELECT 列表中不出现在GROUP BY语句中的列或运算。这代表 “对该组的任何可能值 ”。你可以通过避免排序和对不必要项分组的办法得到它更好的性能。例如，在下列问询中，你无须对customer.name 进行分组： mysql> **SELECT order.custid, customer.name, MAX(payments)** -> **FROM order,customer** -> **WHERE order.custid = customer.custid** -> **GROUP BY order.custid;** 在标准SQL中, 你必须将 customer.name添加到 GROUP BY子句中。在MySQL中, 假如你不在ANSI模式中运行，则这个名字就是多余的。假如你从 GROUP BY 部分省略的列在该组中不是唯一的，那么*不要使用这个功能*! 你会得到非预测性结果。在有些情况下,你可以使用MIN()和MAX() 获取一个特殊的列值，即使他不是唯一的。下面给出了来自包含排序列中最小值的列中的值： SUBSTR(MIN(CONCAT(RPAD(sort,6,' '),column)),7) See [ 3.6.4节，“拥有某个字段的组间最大值的行”](# "3.6.4. The Rows Holding the Group-wise Maximum of a Certain Field"). 注意，假如你正在尝试遵循标准 SQL, 你不能使用GROUP BY或 ORDER BY子句中的表达式。你可以通过使用表达式的别名绕过这一限制： mysql> **SELECT id,FLOOR(value/100) AS val ** -> **FROM *tbl_name*** -> **GROUP BY id, val ORDER BY val;** 然而, MySQL允许你使用GROUP BY 及 ORDER BY 子句中的表达式。例如： mysql> **SELECT id, FLOOR(value/100) FROM *tbl_name* ORDER BY RAND();** 这是MySQL参考手册的翻译版本，关于MySQL参考手册，请访问[dev.mysql.com](http://dev.mysql.com/doc/mysql/en)。原始参考手册为英文版，与英文版参考手册相比，本翻译版可能不是最新的。