第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伺服器模式」

為節省時間,本章中對大多數例子使用簡寫形式展示了 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. 操作符優先級

以下列資料表顯示了操作符優先級的由低到高的順序。排列在同一行的操作符具有相同的優先級。

:=

||, 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伺服器模式」

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函數和操作符」

以下例子說明了比較運算中將字串轉為數字的過程:

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

根據一個布爾值來檢驗一個值,在這裡,布爾值可以是TRUEFALSEUNKNOWN

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語法」

    • 對於命名為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進行子查詢」

·         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中,所有邏輯 操作符的求值所得結果均為 TRUEFALSE 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.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; 否則返回值則為 expr3IF() 的返回值為數字值或字串值,具體情況視其所在語境而定。

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() (這一點在其被儲存到臨時資料表時很重要 ) 的預設返回值類型按照以下方式計算:

資料表達式

返回值

expr2 expr3 返回值為一個字串。

字串

expr2 expr3 返回值為一個浮點值。

浮點

expr2 expr3 返回值為一個整數。 

整數

假如expr2 expr3 都是字串,且其中任何一個字串區分大小寫,則返回結果是區分大小寫。

  • IFNULL(expr1,expr2)

假如expr1 不為 NULL,則 IFNULL() 的返回值為 expr1; 否則其返回值為 expr2IFNULL()的返回值是數字或是字串,具體情況取決於其所使用的語境。

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. 字串函數

假如結果的長度大於 max_allowed_packet 系統變數的最大值時,字串值函數的返回值為NULL。請參見7.5.2節,「調節伺服器參數」

對於在字串位置操作的函數,第一個位置的編號為 1

返回值為字串str 的最左字元的數值。假如str為空字串,則返回值為 0 。假如str NULL,則返回值為 NULL ASCII()用於帶有從 0255的數值的字元。

mysql> SELECT ASCII('2');

        -> 50

mysql> SELECT ASCII(2);

        -> 50

mysql> SELECT ASCII('dx');

        -> 100

ORD()函數。

返回值為N的二進制值的字串資料表示,其中  N 為一個longlong (BIGINT) 數字。這等同於 CONV(N,10,2)。假如N NULL,則返回值為 NULL

mysql> SELECT BIN(12);

        -> '1100'

返回值為二進制的字串str 長度。

mysql> SELECT BIT_LENGTH('text');

        -> 32

CHAR()將每個參數N理解為一個整數,其返回值為一個包含這些整數的代碼值所給出的字元的字串。NULL值被省略。

mysql> SELECT CHAR(77,121,83,81,'76');

        -> 'MySQL'

mysql> SELECT CHAR(77,77.3,'77.3');

        -> 'MMM'

大於 255CHAR()參數被轉換為多結果字元。 例如,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

返回值為字串str 的長度,長度的單位為字元。一個多字節字元算作一個單字元。對於一個包含五個二字節字元編碼, LENGTH()返回值為 10, CHAR_LENGTH()的返回值為5

CHARACTER_LENGTH()CHAR_LENGTH()的同義詞。

壓縮一個字串。這個函數要求 MySQL已經用一個諸如zlib的壓縮庫壓縮過。   否則,返回值始終是NULLUNCOMPRESS() 可將壓縮過的字串進行解壓縮。

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

壓縮後的字串的內容按照以下方式儲存:

返回結果為連接參數產生的字串。如有任何一個參數為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() 代資料表 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

不同數基間轉換數字。返回值為數字的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'

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'

返回值為一個字串,其中對於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'

返回值為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

假如字串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

number X設置為格式 '#,###,###.##', 以四捨五入的方式保留到小數點後D, 而返回結果為一個字串。詳見12.9.4節,「其他函數」.

如果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

返回字串 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'

這個函數支援多字節字元

返回字串 str 中子字串的第一個出現位置。這和LOCATE()的雙參數形式相同,除非參數的順序被顛倒。 

mysql> SELECT INSTR('foobarbar', 'bar');

        -> 4

mysql> SELECT INSTR('xbar', 'foobar');

        -> 0

這個函數支援多字節字元,並且只有當至少有一個參數是二進制字串時區分大小寫。

LCASE() LOWER()的同義詞。

返回從字串str 開始的len 最左字元。

mysql> SELECT LEFT('foobarbar', 5);

        -> 'fooba'

返回值為字串str 的長度,單位為字節。一個多字節字元算作多字節。這意味著 對於一個包含52字節字元的字串, LENGTH() 的返回值為 10, CHAR_LENGTH()的返回值則為5

mysql> SELECT LENGTH('text');

        -> 4

讀取檔案並將這一檔案按照字串的格式返回。 檔案的位置必須在伺服器上,您必須為檔案制定路徑全名,而且您還必須擁有FILE 特許權。檔案必須可讀取,檔案容量必須小於 max_allowed_packet字節。

若檔案不存在,或因不滿足上述條件而不能被讀取, 則函數返回值為 NULL

mysql> UPDATE tbl_name

           SET blob_column=LOAD_FILE('/tmp/picture')

           WHERE id=1;

第一個語法返回字串 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

這個函數支援多字節字元,並且只有當至少有一個參數是二進制字串時區分大小寫。 

返回字串 str 以及所有根據最新的字元編碼映射資料表變為小寫字母的字元 (預設為  cp1252 Latin1)

mysql> SELECT LOWER('QUADRATICALLY');

        -> 'quadratically'

這個函數支援多字節字元。

返回字串 str, 其左邊由字串padstr 填補到len 字元長度。假如str 的長度大於len, 則返回值被縮短至 len 字元。

mysql> SELECT LPAD('hi',4,'??');

        -> '??hi'

mysql> SELECT LPAD('hi',1,'??');

        -> 'h'

返回字串 str ,其引導空格字元被刪除。

mysql> SELECT LTRIM('  barbar');

        -> 'barbar'

這個函數支援多字節字元。

返回一個設定值 (一個包含被『,』號分開的字字串的字串) ,由在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) SUBSTRING(str,pos,len)的同義詞。

返回一個 N的八進制值的字串資料表示,其中 N 是一個longlong (BIGINT)數。這等同於CONV(N,10,8)。若N NULL ,則返回值為NULL

mysql> SELECT OCT(12);

        -> '14'

OCTET_LENGTH() LENGTH()的同義詞。

若字串str 的最左字元是一個多字節字元,則返回該字元的代碼, 代碼的計算通過使用以下公式計算其組成字節的數值而得出:

   (1st byte code)
+ (2nd byte code × 256)
+ (3rd byte code × 2562) ...

 

假如最左字元不是一個多字節字元,那麼 ORD()和函數ASCII()返回相同的值。

mysql> SELECT ORD('2');

        -> 50

POSITION(substr IN str) LOCATE(substr,str)同義詞。

引證一個字串,由此產生一個在SQL語句中可用作完全轉義數據值的結果。  返回的字串由單引號標注,每例都帶有單引號 (')、 反斜線符號 (\) ASCII NUL以及前面有反斜線符號的Control-Z 。如果自變數的值為NULL, 則返回不帶單引號的單詞 「NULL」。

mysql> SELECT QUOTE('Don\'t!');

        -> 'Don\'t!'

mysql> SELECT QUOTE(NULL);

        -> NULL

返回一個由重複的字串str 組成的字串,字串str的數目等於count 。 若 count <= 0,則返回一個空字串。若str count NULL,則返回 NULL

mysql> SELECT REPEAT('MySQL', 3);

        -> 'MySQLMySQLMySQL'

返回字串str 以及所有被字串to_str替代的字串from_str

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');

        -> 'WwWwWw.mysql.com'

這個函數支援多字節字元。

返回字串 str ,順序和字元順序相反。

mysql> SELECT REVERSE('abc');

        -> 'cba'

這個函數支援多字節字元。

從字串str 開始,返回最右len 字元。

mysql> SELECT RIGHT('foobarbar', 4);

        -> 'rbar'

這個函數支援多字節字元。

返回字串str, 其右邊被字串 padstr填補至len 字元長度。假如字串str 的長度大於 len,則返回值被縮短到與 len 字元相同長度。

mysql> SELECT RPAD('hi',5,'?');

        -> 'hi???'

mysql> SELECT RPAD('hi',1,'?');

        -> 'h'

這個函數支援多字節字元。

返回字串 str ,結尾空格字元被刪去。

mysql> SELECT RTRIM('barbar   ');

        -> 'barbar'

這個函數支援多字節字元。

str返回一個soundex字串。 兩個具有幾乎同樣探測的字串應該具有同樣的 soundex 字串。一個標準的soundex 字串的長度為4個字元,然而SOUNDEX() 函數會返回一個人以長度的字串。 可使用結果中的SUBSTRING() 來得到一個標準 soundex 字串。在str會忽略所有未按照字母順序排列的字元。 所有不在A-Z範圍之內的國際字母符號被視為元音字母。

mysql> SELECT SOUNDEX('Hello');

        -> 'H400'

mysql> SELECT SOUNDEX('Quadratically');

        -> 'Q36324'

注意:這個函數執行原始的Soundex算法,而非更加流行的加強版本(D. Knuth所述)。其區別在於原始版本首先會刪去元音,其次是重複,而加強版則首先刪去重複,而後刪去元音。

這相當於SOUNDEX(expr1) = SOUNDEX(expr2)

返回一個由N 間隔符號組成的字串。

mysql> SELECT SPACE(6);

        -> '      '

不帶有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()的同義詞。

在定界符 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'

這個函數支援多字節字元。

返回字串 str , 其中所有remstr 前綴和/或後綴都已被刪除。若分類符BOTHLEADINTRAILING中沒有一個是給定的,則假設為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()UPPER()的同義詞。

對經COMPRESS()函數壓縮後的字串進行解壓縮。若參數為壓縮值,則結果為 NULL。這個函數要求  MySQL 已被諸如zlib 之類的壓縮庫編譯過。否則, 返回值將始終是 NULL

mysql> SELECT UNCOMPRESS(COMPRESS('any string'));

        -> 'any string'

mysql> SELECT UNCOMPRESS('any string');

        -> NULL

返回壓縮字串壓縮前的長度。

mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));

        -> 30

執行從HEX(str)的反向操作。就是說,它將參數中的每一對十六進制數字理解為一個數字,並將其轉化為該數字代資料表的字元。結果字元以二進制字串的形式返回。

mysql> SELECT UNHEX('4D7953514C');

        -> 'MySQL'

mysql> SELECT 0x4D7953514C;

        -> 'MySQL'

mysql> SELECT UNHEX(HEX('string'));

        -> 'string'

mysql> SELECT HEX(UNHEX('1267'));

        -> '1267'

返回字串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一起使用以下兩種通配符:

字元

說明

%

匹配任何數目的字元,甚至包括零字元 

_

只能匹配一種字元

mysql> SELECT 'David!' LIKE 'David_';

        -> 1

mysql> SELECT 'David!' LIKE '%D%v%';

        -> 1

若要對通配符的文字實例進行檢驗, 可將轉義字元放在該字元前面。如果沒有指定 ESCAPE字元, 則假設為『\』。

字串

說明

\%

匹配一個  %』字元

\_

匹配一個 『_』 字元

mysql> SELECT 'David!' LIKE 'David\_';

        -> 0

mysql> SELECT 'David_' LIKE 'David\_';

        -> 1

要指定一個不同的轉義字元,可使用ESCAPE語句:

mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';

        -> 1

轉義序列可以為空,也可以是一個字元的長度。 從 MySQL 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正則資料表達式中有詳細討論。若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. 算術操作符

可使用常見的算術操作符。注意就 - + *而言, 若兩個參數均為正數,則其計算結果的精確度為 BIGINT (64比特),若其中一個參數為無符號整數, 而其它參數也是整數, 則結果為無符號整數。請參見12.8節,「Cast函數和操作符」

  • +

加號:

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節,「數值類型」.)

  • /

除號:

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)

返回eX乘方後的值(自然對數的底)

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節,「其他函數」

  • 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 的範圍(包括712)內得到一個隨機整數, 可使用以下語句:

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章:精度數學

  • SIGN(X)

返回參數作為-1 01的符號,該符號取決於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節,「日期和時間類型」

下面的例子使用了時間函數。以下詢問選擇了最近的 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伺服器時區支援」

以下函數的論述中返回值的範圍會請求完全日期。 若一個日期為「零」 值,或者是一個諸如'2001-11-00'之類的不完全日期, 提取部分日期值的函數可能會返回 0。 例如, DAYOFMONTH('2001-11-00') 會返回0

當被第二個參數的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()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給出的時區,然後返回結果值。關於可能指定的時區的詳細論述,請參見5.10.8節,「MySQL伺服器時區支援」。若自變數無效,則這個函數會返回 NULL

在從若from_tz UTC的轉化過程中,該值超出 TIMESTAMP 類型的被支援範圍,那麼轉化不會發生。關於 TIMESTAMP 範圍的論述,請參見11.1.2節,「日期和時間類型概述」

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伺服器時區支援」。 

將當前日期按照'YYYY-MM-DD' YYYYMMDD 格式的值返回,具體格式根據函數用在字串或是數字語境中而定。

mysql> SELECT CURDATE();

        -> '1997-12-15'

mysql> SELECT CURDATE() + 0;

        -> 19971215

CURRENT_DATECURRENT_DATE()是的同義詞.

將當前時間以'HH:MM:SS' HHMMSS 的格式返回, 具體格式根據函數用在字串或是數字語境中而定。 

mysql> SELECT CURTIME();

        -> '23:50:26'

mysql> SELECT CURTIME() + 0;

        -> 235026

CURRENT_TIME CURRENT_TIME() CURTIME()的同義詞。

CURRENT_TIMESTAMP CURRENT_TIMESTAMP()NOW()的同義詞。

提取日期或時間日期資料表達式expr中的日期部分。

mysql> SELECT DATE('2003-12-31 01:02:03');

        -> '2003-12-31'

DATEDIFF() 返回起始時間 expr和結束時間expr2之間的天數。Exprexpr2 為日期或 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 是一個 DATETIME DATE值,用來指定起始時間。 expr 是一個資料表達式,用來指定從起始日期新增或減去的時間間隔值。  Expr是一個字串;對於負值的時間間隔,它可以以一個 『-』開頭。 type 為關鍵詞,它指示了資料表達式被解釋的方式。 

關鍵詞INTERVAtype 分類符均不區分大小寫。

以下資料表顯示了type expr 參數的關係:

type

預期的 expr 格式

MICROSECOND

MICROSECONDS

SECOND

SECONDS

MINUTE

MINUTES

HOUR

HOURS

DAY

DAYS

WEEK

WEEKS

MONTH

MONTHS

QUARTER

QUARTERS

YEAR

YEARS

SECOND_MICROSECOND

'SECONDS.MICROSECONDS'

MINUTE_MICROSECOND

'MINUTES.MICROSECONDS'

MINUTE_SECOND

'MINUTES:SECONDS'

HOUR_MICROSECOND

'HOURS.MICROSECONDS'

HOUR_SECOND

'HOURS:MINUTES:SECONDS'

HOUR_MINUTE

'HOURS:MINUTES'

DAY_MICROSECOND

'DAYS.MICROSECONDS'

DAY_SECOND

'DAYS HOURS:MINUTES:SECONDS'

DAY_MINUTE

'DAYS HOURS:MINUTES'

DAY_HOUR

'DAYS HOURS'

YEAR_MONTH

'YEARS-MONTHS'

MySQL 允許任何expr 格式中的標點分隔符。資料表中所顯示的是建議的 分隔符。若 date 參數是一個 DATE 值,而您的計算只會包括 YEARMONTHDAY部分(, 沒有時間部分), 其結果是一個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。這相當於 MySQLTIME 值解釋為所耗費的時間而不是日時的解釋方式。             

假如您對一個日期值新增或減去一些含有時間部分的內容,則結果自動轉化為一個日期時間值:

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。假如您新增了  MONTHYEAR_MONTHYEAR ,而結果日期中有一天的日期大於新增的月份的日期最大限度,則這個日期自動被調整為新增月份的最大日期:

mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);

        -> '1998-02-28'

根據format 字串安排date 值的格式。

以下說明符可用在 format 字串中:

說明符

說明

%a

工作日的縮寫名稱  (Sun..Sat)

%b

月份的縮寫名稱  (Jan..Dec)

%c

月份,數字形式(0..12)

%D

帶有英語後綴的該月日期  (0th, 1st, 2nd, 3rd, ...)

%d

該月日期, 數字形式 (00..31)

%e

該月日期, 數字形式(0..31)

%f

微秒 (000000..999999)

%H

小時(00..23)

%h

小時(01..12)

%I

小時 (01..12)

%i

分鐘,數字形式 (00..59)

%j

一年中的天數 (001..366)

%k

小時 (0..23)

%l

小時 (1..12)

%M

月份名稱 (January..December)

%m

月份, 數字形式 (00..12)

%p

上午(AM)或下午( PM

%r

時間 , 12小時制 (小時hh:分鐘mm:秒數ss 後加 AMPM)

%S

(00..59)

%s

(00..59)

%T

時間 , 24小時制 (小時hh:分鐘mm:秒數ss)

%U

(00..53), 其中週日為每週的第一天

%u

(00..53), 其中週一為每週的第一天 

%V

(01..53), 其中週日為每週的第一天 ; %X同時使用

%v

(01..53), 其中週一為每週的第一天 ; %x同時使用

%W

工作日名稱 (週日..週六)

%w

一周中的每日 (0=週日..6=週六)

%X

該周的年份,其中週日為每週的第一天, 數字形式,4位數;%V同時使用

%x

該周的年份,其中週一為每週的第一天, 數字形式,4位數;%v同時使用

%Y

年份, 數字形式,4位數

%y

年份, 數字形式 (2位數)

%%

%』文字字元

所有其它字元都被複製到結果中,無需作出解釋。

注意, 『%』字元要求在格式指定符之前。

月份和日期說明符的範圍從零開始,原因是 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() DAYOFMONTH()的意義相同。

返回date 對應的工作日名稱。

mysql> SELECT DAYNAME('1998-02-05');

        -> '週四'

返回date 對應的該月日期,範圍是從 131

mysql> SELECT DAYOFMONTH('1998-02-03');

        -> 3

返回date (1 = 週日, 2 = 週一, ..., 7 = 週六)對應的工作日索引。這些索引值符合 ODBC標準。

mysql> SELECT DAYOFWEEK('1998-02-03');

        -> 3

返回date 對應的一年中的天數,範圍是從 1366

mysql> SELECT DAYOFYEAR('1998-02-03');

        -> 34

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

給定一個天數  N, 返回一個DATE值。

mysql> SELECT FROM_DAYS(729669);

        -> '1997-10-07'

使用 FROM_DAYS()處理古老日期時,務必謹慎。他不用於處理陽曆出現前的日期(1582)。請參見12.6節,「MySQL使用什麼日曆?」

返回'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'

返回一個格式字串。這個函數在同DATE_FORMAT() STR_TO_DATE()函數結合時很有用。

第一個參數的3個可能值和第二個參數的5個可能值產生 15 個可能格式字串 (對於使用的說明符,請參見DATE_FORMAT()函數說明資料表 )

函數使用

結果

GET_FORMAT(DATE,'USA')

'%m.%d.%Y'

GET_FORMAT(DATE,'JIS')

'%Y-%m-%d'

GET_FORMAT(DATE,'ISO')

'%Y-%m-%d'

GET_FORMAT(DATE,'EUR')

'%d.%m.%Y'

GET_FORMAT(DATE,'INTERNAL')

'%Y%m%d'

GET_FORMAT(DATETIME,'USA')

'%Y-%m-%d-%H.%i.%s'

GET_FORMAT(DATETIME,'JIS')

'%Y-%m-%d %H:%i:%s'

GET_FORMAT(DATETIME,'ISO')

'%Y-%m-%d %H:%i:%s'

GET_FORMAT(DATETIME,'EUR')

'%Y-%m-%d-%H.%i.%s'

GET_FORMAT(DATETIME,'INTERNAL')

'%Y%m%d%H%i%s'

GET_FORMAT(TIME,'USA')

'%h:%i:%s %p'

GET_FORMAT(TIME,'JIS')

'%H:%i:%s'

GET_FORMAT(TIME,'ISO')

'%H:%i:%s'

GET_FORMAT(TIME,'EUR')

'%H.%i.%S'

GET_FORMAT(TIME,'INTERNAL')

'%H%i%s'

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語法」

返回time 對應的小時數。對於日時值的返回值範圍是從 0 23

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

        -> 10

然而,  TIME 值的範圍實際上非常大, 所以HOUR可以返回大於23的值。

mysql> SELECT HOUR('272:59:59');

        -> 272

獲取一個日期或日期時間值,返回該月最後一天對應的值。若參數無效,則返回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()NOW()具有相同意義。

LOCALTIMESTAMPLOCALTIMESTAMP()NOW()具有相同意義。

給出年份值和一年中的天數值,返回一個日期。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

返回由hourminutesecond 參數計算得出的時間值。

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

        -> '12:15:30'

從時間或日期時間資料表達式expr返回微秒值,其數字範圍從 0 999999

mysql> SELECT MICROSECOND('12:00:00.123456');

        -> 123456

mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010');

        -> 10

返回 time 對應的分鐘數,範圍是從 0 59

mysql> SELECT MINUTE('98-02-03 10:05:03');

        -> 5

返回date 對應的月份,範圍時從 1 12

mysql> SELECT MONTH('1998-02-03');

        -> 2

返回date 對應月份的全名。

mysql> SELECT MONTHNAME('1998-02-05');

        -> 'February '

返回當前日期和時間值,其格式為 'YYYY-MM-DD HH:MM:SS' YYYYMMDDHHMMSS , 具體格式取決於該函數是否用在字串中或數字語境中。

mysql> SELECT NOW();

        -> '1997-12-15 23:50:26'

mysql> SELECT NOW() + 0;

        -> 19971215235026

在一個儲存程式或觸發器內, NOW() 返回一個常數時間,該常數指示了該程式或觸發語句開始執行的時間。這同SYSDATE()的運行有所不同。

新增 N 個月至週期P (格式為YYMM YYYYMM),返回值的格式為 YYYYMM。注意週期參數 P 不是日期值。 

mysql> SELECT PERIOD_ADD(9801,2);

        -> 199803

返回週期P1P2 之間的月份數。P1 P2 的格式應該為YYMMYYYYMM。注意週期參數 P1P2 不是日期值。

mysql> SELECT PERIOD_DIFF(9802,199703);

        -> 11

返回date 對應的一年中的季度值,範圍是從 1 4

mysql> SELECT QUARTER('98-04-01');

        -> 2

返回time 對應的秒數, 範圍是從 059

mysql> SELECT SECOND('10:05:03');

        -> 3

返回被轉化為小時、 分鐘和秒數的seconds參數值, 其格式為 'HH:MM:SS' HHMMSS,具體格式根據該函數是否用在字串或數字語境中而定。

mysql> SELECT SEC_TO_TIME(2378);

        -> '00:39:38'

mysql> SELECT SEC_TO_TIME(2378) + 0;

        -> 3938

這是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類型」有詳細說明。其意義是,例如, 只要具體日期部分的範圍時從 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'

當被第二個參數的 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 ,然後返回結果。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'

返回當前日期和時間值,格式為'YYYY-MM-DD HH:MM:SS' YYYYMMDDHHMMSS, 具體格式根據函數是否用在字串或數字語境而定。

在一個儲存程式或觸發器中, SYSDATE()返回其執行的時間, 而非儲存成都或觸發語句開始執行的時間。這個NOW()的運作有所不同。

提取一個時間或日期時間資料表達式的時間部分,並將其以字串形式返回。

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 之間的時間。 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'

對於一個單參數,該函數將日期或日期時間資料表達式 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'

將整型資料表達式int_expr 新增到日期或日期時間資料表達式 datetime_expr中。 int_expr 的單位被時間間隔參數給定,該參數必須是以下值的其中一個: FRAC_SECONDSECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR

可使用所顯示的關鍵詞指定Interval值,或使用SQL_TSI_前綴。例如, DAYSQL_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'

返回日期或日期時間資料表達式datetime_expr1 datetime_expr2the 之間的整數差。其結果的單位由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

其使用和 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參數。

mysql> SELECT TIME_TO_SEC('22:23:00');

        -> 80580

mysql> SELECT TIME_TO_SEC('00:39:38');

        -> 2378

給定一個日期date, 返回一個天數 (從年份0開始的天數 )

mysql> SELECT TO_DAYS(950501);

        -> 728779

mysql> SELECT TO_DAYS('1997-10-07');

        -> 729669

TO_DAYS() 不用於陽曆出現(1582)前的值,原因是當日曆改變時,遺失的日期不會被考慮在內。請參見12.6節,「MySQL使用什麼日曆?」

請記住, MySQL使用11.3節,「日期和時間類型」中的規則將日期中的二位數年份值轉化為四位。例如,  '1997-10-07' '97-10-07' 被視為同樣的日期:

mysql> SELECT TO_DAYS('1997-10-07'), TO_DAYS('97-10-07');

        -> 729669, 729669

對於1582 年之前的日期(或許在其它地區為下一年 ), 該函數的結果實不可靠的。詳見12.6節,「MySQL使用什麼日曆?」 

若無參數使用,則返回一個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, 月份從0112,日期從  01 31)

假如您想要減去 UNIX_TIMESTAMP() , 您或許希望刪去帶符號整數的結果。請參見12.8節,「Cast函數和操作符」

返回當前 UTC日期值,其格式為 'YYYY-MM-DD' YYYYMMDD,具體格式取決於函數是否用在字串或數字語境中。 

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;

        -> '2003-08-14', 20030814

返回當前 UTC 值,其格式為  'HH:MM:SS' HHMMSS,具體格式根據該函數是否用在字串或數字語境而定。

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;

        -> '18:07:53', 180753

返回當前UTC日期及時間值,格式為 'YYYY-MM-DD HH:MM:SS' YYYYMMDDHHMMSS,具體格式根據該函數是否用在字串或數字語境而定。

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;

        -> '2003-08-14 18:08:04', 20030814180804

該函數返回date 對應的星期數。WEEK() 的雙參數形式允許您指定該星期是否起始於週日或週一, 以及返回值的範圍是否為從0 53 或從1 53。若 mode參數被省略,則使用default_week_format系統自變數的值。請參見5.3.3節,「伺服器系統變數」

以下資料表說明了mode 參數的工作過程:d

 

第一天

 

 

Mode

工作日

範圍

Week 1 為第一周 ...

0

週日

0-53

本年度中有一個週日

1

週一

0-53

本年度中有3天以上

2

週日

1-53

本年度中有一個週日

3

週一

1-53

本年度中有3天以上

4

週日

0-53

本年度中有3天以上

5

週一

0-53

本年度中有一個週一

6

週日

1-53

本年度中有3天以上

7

週一

1-53

本年度中有一個週一

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

注意,假如有一個日期位於前一年的最後一周, 若您不使用2367作為mode 參數選擇,則MySQL返回 0

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);

        -> 2000, 0

有人或許會提出意見,認為 MySQL 對於WEEK() 函數應該返回 52 ,原因是給定的日期實際上發生在1999年的第52周。我們決定返回0作為代替的原因是我們希望該函數能返回「給定年份的星期數」。這使得WEEK() 函數在同其它從日期中抽取日期部分的函數結合時的使用更加可靠。

假如您更希望所計算的關於年份的結果包括給定日期所在周的第一天,則應使用 025 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'

返回date (0 = 週一, 1 = 週二, ... 6 = 週日)對應的工作日索引  weekday index for

mysql> SELECT WEEKDAY('1998-02-03 22:23:00');

        -> 1

mysql> SELECT WEEKDAY('1997-11-05');

        -> 2

將該日期的陽曆周以數字形式返回,範圍是從153。它是一個兼容度函數,相當於WEEK(date,3)

mysql> SELECT WEEKOFYEAR('1998-02-20');

        -> 8

返回date 對應的年份,範圍是從10009999

mysql> SELECT YEAR('98-02-03');

        -> 1998

返回一個日期對應的年或周。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天。 為了瞭解其運作,讓我們看看158210月,這是由朱利安日曆轉換為陽曆的第一次:

週一

週二

週三

週四

週五

週六

週日

1

2

3

4

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

104 日到1015日之間的日期為空白。這個中斷被稱為接入。接入前的日期均使用朱利安日曆, 而接入後的日期均使用陽曆。接入期間的日期是不存在的。

當一個用於日期的日曆並為得到實際使用時被稱為 proleptic。因此, 若我們假設從來沒有接入期的存在,而陽曆曆法則始終被使用,我們會有一個預期的陽曆 。這就是MySQL 所使用的,正如標準SQL所要求的。 鑒於這個原因,作為MySQL DATE DATETIME值而被儲存的接入前的日期必須調整這個差異。我們必須明白,接入的發生時間在不同的國家有所不同,而接入的時間越晚,遺失的日期越多。例如,在大不列顛, 接入發生在 1752,這時92日,週三後的第二天為914日,週二; 俄羅斯結束使用朱利安日曆的時間為1918,接入過程中遺失天數為 13, 根據陽曆,其普遍被稱為「10月革命」的發生時間實際上是11月。

12.7. 全文搜索功能

12.7.1. 布爾全文搜索
12.7.2. 全文搜索帶查詢延伸
12.7.3. 全文停止字
12.7.4. 全文限定條件
12.7.5. 微調MySQL全文搜索

MySQL支援全文索引和搜索功能。MySQL中的全文索引類型FULLTEXT的索引。  FULLTEXT 索引僅可用於 MyISAM 資料表;他們可以從CHAR VARCHARTEXT列中作為CREATE TABLE語句的一部分被建立,或是隨後使用ALTER TABLE CREATE INDEX被新增。對於較大的數據集,將您的資料輸入一個沒有FULLTEXT索引的資料表中,然後建立索引, 其速度比把資料輸入現有FULLTEXT索引的速度更為快。

關於全文搜索的限制列資料表,請參見 12.7.4節,「全文限定條件」.

全文搜索同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()函數對於一個字串執行資料庫內的自然語言搜索。一個資料庫就是11個或2個包含在FULLTEXT內的列。搜索字串作為對AGAINST()的參數而被給定。對於資料表中的每一行, MATCH() 返回一個相關值,即, 搜索字串和 MATCH()資料表中指定列中該行文字之間的一個相似性度量。

在預設狀態下, 搜索的執行方式為不區分大小寫方式。然而,您可以通過對編入索引的列使用二進制排序方式執行區分大小寫的全文搜索。 例如,可以向一個使用latin1字元編碼的列給定latin1_bin 的排序方式,對於全文搜索區分大小寫。

如上述所舉例子,當MATCH()被用在一個 WHERE 語句中時,相關值是非負浮點數。零相關的意思是沒有相似性。相關性的計算是基於該行中單詞的數目, 該行中獨特子的數目,資料庫中單詞的總數,以及包含特殊詞的檔案()數目。

對於自然語言全文搜索,要求MATCH() 函數中命名的列和您的資料表中一些FULLTEXT索引中包含的列相同。對於前述問訊, 注意,MATCH()函數(題目及全文)中所命名的列和文章資料表的FULLTEXT索引中的列相同。若要分別搜索題目和全文,應該對每個列建立FULLTEXT索引。

或者也可以運行布爾搜索或使用查詢延伸進行搜索。關於這些搜索類型的說明見12.7.1節,「布爾全文搜索」12.7.2節,「全文搜索帶查詢延伸」

上面的例子基本上展示了怎樣使用返回行的相關性順序漸弱的MATCH()函數。而下面的例子則展示了怎樣明確地檢索相關值。返回行的順序是不定的,原因是  SELECT 語句不包含 WHEREORDER 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 索引新增單詞或其它編入索引的術語,您必須對它們進行預處理,使其被一些諸如"之類的任意分隔符分隔開。

一些詞在全文搜索中會被忽略: 

預設的停止字在12.7.3節,「全文停止字」中被給出。預設的最小單詞長度和 停止字可以被改變,如12.7.5節,「微調MySQL全文搜索」中所述。

詞庫和詢問中每一個正確的單詞根據其在詞庫和詢問中的重要性而被衡量。  通過這種方式,一個出現在許多檔案中的單詞具有較低的重要性(而且甚至很多單詞的重要性為零),原因是在這個特別詞庫中其語義價值較低。反之,假如這個單詞比較少見,那麼它會得到一個較高的重要性。然後單詞的重要性被組合,從而用來計算該行的相關性。 

這項技術最適合同大型詞庫一起使用 (事實上, 此時它經過仔細的調整 )。對於很小的資料表,單詞分佈並不能充分反映它們的語義價值, 而這個模式有時可能會產生奇特的結果。例如, 雖然單詞 「MySQL」 出現在文章資料表中的每一行,但對這個詞的搜索可能得不到任何結果:

mysql> SELECT * FROM articles

    -> WHERE MATCH (title,body) AGAINST ('MySQL');

找不到搜索的詞(0.00 )

這個搜索的結果為空,原因是單詞 「MySQL」 出現在至少全文的50%的行中。 因此, 它被列入停止字。對於大型數據集,使用這個操作最合適不過了----一個自然語言問詢不會從一個1GB 的資料表每隔一行返回一次。對於小型數據集,它的用處可能比較小。

一個符合資料表中所有行的內容的一半的單詞搜尋相關文檔的可能性較小。事實上, 它更容易找到很多不相關的內容。我們都知道,當我們在因特網上試圖使用搜索引擎尋找資料的時候,這種情況發生的頻率頗高。可以推論,包含該單詞的行因其所在特別數據集 而被賦予較低的語義價值。 一個給定的詞有可能在一個數據集中擁有超過其50%的域值,而在另一個數據集卻不然。 

當您第一次嘗試使用全文搜索以瞭解其工作過程時,這個50% 的域值提供重要的蘊涵操作:若您建立了一個資料表,並且只將文章的12行插入其中, 而文中的每個單詞在所有行中出現的機率至少為  50% 。那麼結果是您什麼也不會搜索到。一定要插入至少3行,並且多多益善。需要繞過該50% 限制的用戶可使用布爾搜索代碼;見12.7.1節,「布爾全文搜索」

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'sableaboutaboveaccording
accordinglyacrossactuallyafterafterwards
againagainstain'tallallow
allowsalmostalonealongalready
alsoalthoughalwaysamamong
amongstanandanotherany
anybodyanyhowanyoneanythinganyway
anywaysanywhereapartappearappreciate
appropriatearearen'taroundas
asideaskaskingassociatedat
availableawayawfullybebecame
becausebecomebecomesbecomingbeen
beforebeforehandbehindbeingbelieve
belowbesidebesidesbestbetter
betweenbeyondbothbriefbut
byc'monc'scamecan
can'tcannotcantcausecauses
certaincertainlychangesclearlyco
comcomecomesconcerningconsequently
considerconsideringcontaincontainingcontains
correspondingcouldcouldn'tcoursecurrently
definitelydescribeddespitediddidn't
differentdodoesdoesn'tdoing
don'tdonedowndownwardsduring
eacheduegeighteither
elseelsewhereenoughentirelyespecially
etetceveneverevery
everybodyeveryoneeverythingeverywhereex
exactlyexampleexceptfarfew
fifthfirstfivefollowedfollowing
followsforformerformerlyforth
fourfromfurtherfurthermoreget
getsgettinggivengivesgo
goesgoinggonegotgotten
greetingshadhadn'thappenshardly
hashasn'thavehaven'thaving
hehe'shellohelphence
herherehere'shereafterhereby
hereinhereuponhersherselfhi
himhimselfhishitherhopefully
howhowbeithoweveri'di'll
i'mi'veieifignored
immediateininasmuchincindeed
indicateindicatedindicatesinnerinsofar
insteadintoinwardisisn't
itit'dit'llit'sits
itselfjustkeepkeepskept
knowknowsknownlastlately
laterlatterlatterlyleastless
lestletlet'slikeliked
likelylittlelooklookinglooks
ltdmainlymanymaymaybe
memeanmeanwhilemerelymight
moremoreovermostmostlymuch
mustmymyselfnamenamely
ndnearnearlynecessaryneed
needsneitherneverneverthelessnew
nextninenonobodynon
nonenoonenornormallynot
nothingnovelnownowhereobviously
ofoffoftenohok
okayoldononceone
onesonlyontoorother
othersotherwiseoughtourours
ourselvesoutoutsideoveroverall
ownparticularparticularlyperperhaps
placedpleasepluspossiblepresumably
probablyprovidesquequiteqv
ratherrdrereallyreasonably
regardingregardlessregardsrelativelyrespectively
rightsaidsamesawsay
sayingsayssecondsecondlysee
seeingseemseemedseemingseems
seenselfselvessensiblesent
seriousseriouslysevenseveralshall
sheshouldshouldn'tsincesix
sosomesomebodysomehowsomeone
somethingsometimesometimessomewhatsomewhere
soonsorryspecifiedspecifyspecifying
stillsubsuchsupsure
t'staketakentelltends
ththanthankthanksthanx
thatthat'sthatsthetheir
theirsthemthemselvesthenthence
therethere'sthereaftertherebytherefore
thereintheresthereuponthesethey
they'dthey'llthey'rethey'vethink
thirdthisthoroughthoroughlythose
thoughthreethroughthroughoutthru
thustotogethertootook
towardtowardstriedtriestruly
trytryingtwicetwoun
underunfortunatelyunlessunlikelyuntil
untoupuponususe
usedusefulusesusingusually
valuevariousveryviaviz
vswantwantswaswasn't
waywewe'dwe'llwe're
we'vewelcomewellwentwere
weren'twhatwhat'swhateverwhen
whencewheneverwherewhere'swhereafter
whereaswherebywhereinwhereuponwherever
whetherwhichwhilewhitherwho
who'swhoeverwholewhomwhose
whywillwillingwishwith
withinwithoutwon'twonderwould
wouldwouldn'tyesyetyou
you'dyou'llyou'reyou'veyour
yoursyourselfyourselveszero 

12.7.4. 全文限定條件

  • 全文搜索只適用於 MyISAM 資料表。
  • 全文搜索可以同大多數多字節字元編碼一起使用。Unicode屬於例外情況可使用utf8 字元編碼 , 而非ucs2字元編碼。
  • 諸如漢語和日語這樣的資料表意語言沒有自定界符。因此 FULLTEXT分析程式不能確定在這些或其它的這類語言中詞的起始和結束的位置。其隱含操作及該問題的一些工作區在12.7節,「全文搜索功能」有詳細論述。
  • 若支援在一個單獨資料表中使用多字元編碼,則所有 FULLTEXT索引中的列 必須使用同樣的字元編碼和庫。
  • MATCH()列列資料表必須同該資料表中一些 FULLTEXT索引定義中的列列資料表完全符合,除非MATCH()IN BOOLEAN MODE
  • AGAINST() 的參數必須是一個常數字串。 

12.7.5. 微調MySQL全文搜索

MySQL的全文搜索容量幾乎不具有用戶調節參數。假如您擁有一個 MySQL源分佈,您就能對全文搜索性能行使更多控制,原因是一些變化需要源代碼修改。請參見2.8節,「使用原始碼分發版安裝MySQL」

注意,為了更加有效,需要對全文搜索謹慎調節。實際上,在大多數情況下修改預設性能只能降低其性能。 除非您知道自己在做什麼,否則不要改變 MySQL源。 

下述的大多數全文變數必須在伺服器啟動時被設置。為了改變它們,還要重新啟動伺服器;在伺服器正在運行期間,他們不會被改變。 

一些變數的改變需要您重建資料表中的 FULLTEXT 索引。本章結尾部分給出了其有關操作說明。

  • ft_min_word_len and ft_max_word_len系統自變數規定了被編入索引單詞的最小長度和最大長度。(5.3.3節,「伺服器系統變數」.) 預設的最小值為四個字元;預設的最大值取決於使用的 MySQL 版本。假如您改變任意一個值,那麼您必須重建您的 FULLTEXT索引。 例如,若您希望一個3字元的單詞變為可搜尋項,則可以通過將以下行移動到一個供選擇檔案裡,從而設置 ft_min_word_len 變數:

·                [mysqld]

·                ft_min_word_len=3

然後重新啟動伺服器,重建您的 FULLTEXT索引。同時還要特別注意該資料表後面的說明中的關於myisamchk的註釋。

  • 若要覆蓋預設停止字,則可設置 ft_stopword_file 系統變數。 (5.3.3節,「伺服器系統變數」變數值應為包含停止字的檔案路徑名, 或是用來截止禁用詞過濾的空字串。在改變了這個變數的值或禁用詞檔案的內容後, 重建您的 FULLTEXT索引。

停止字是自由形態的,換言之,您可使用任何諸如newline spacecomma這樣的非字母數字字元來分隔禁用詞。 下劃線字元(_) 和被視為單詞的一部分的單引號 (')例外。停止字字元編碼為伺服器預設字元編碼;見10.3.1節,「伺服器字元編碼和校對」.

  •  自然語言查詢的50%閾值由所選擇的特別權衡方案所決定。若要阻止它,myisam/ftdefs.h 中尋找以下行:

·                #define GWS_IN_USE GWS_PROB

將該行改為:

#define GWS_IN_USE GWS_FREQ

然後重新編譯 MySQL。此時不需要重建索引。註釋這樣做您會嚴重的By 降低 MySQLMATCH()函數提供合適的相關值得能力。假如您爭得需要搜索這樣的普通詞,而使用IN BOOLEAN MODE代替的效果更好,因為它不遵循 50% 閾值。

  • 要改變用於布爾全文搜索的操作符,設置 ft_boolean_syntax 系統變數。 這個變數也可以在伺服器運行時被改變,但您必須有SUPER 特權才能這麼做。在這種情況下不需要重建索引。 見5.3.3節,「伺服器系統變數」, 它向我們說明了怎樣使用這個變數的規則。

假如您改變了影響索引的全文變數 (ft_min_word_len ft_max_word_lenft_stopword_file),或假如您改變了禁用詞檔案本身,則您必須在改變和重新啟動伺服器後重建您的 FULLTEXT索引。這時,要重建索引, 只需進行一個 QUICK 修理操作: 

mysql> REPAIR TABLE tbl_name QUICK;

注意,假如您使用  myisamchk 來執行一項修改資料表索引的操作 (諸如修理或分析 ), 則使用最小單詞長度和最大單詞長度以及停止字的預設全文參數值重建FULLTEXT索引,除非您已另外指定。這會導致問詢失敗。

發生這個問題的原因是只有伺服器認識這些參數。它們的儲存位置不在  MyISAM 索引檔案中。若您已經修改了最小單詞長度或最大單詞長度或伺服器中的停止字,為避免這個問題,為您對mysqld所使用的myisamchk 指定同樣的 ft_min_word_len ft_max_word_lenft_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 TABLEOPTIMIZE TABLEALTER TABLE來代替使用 myisamchk 。這些語句通過伺服器來執行,伺服器知道使用哪個全文參數值更加合適。

12.8. Cast函數和操作符

  • BINARY

BINARY操作符將後面的字串拋給一個二進制字串。這是一種簡單的方式來促使逐字節而不是逐字元的進行列比較。這使得比較區分大小寫,即使該列不被定義為 BINARY BLOBBINARY也會產生結尾空白,從而更加顯眼。

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語法。

帶有USINGCONVERT() 被用來在不同的字元編碼之間轉化數據。在 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節,「日期和時間函數」

若要在數值語境中將一個字串派給一個數值, 通常情況下,除了將字串值作為數字使用外,您不需要做任何事:

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. 位函數

對於比特運算,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

注意,這個函數只有當MySQLSSL 的支援下配置完畢時才會運作。請參見5.8.7節,「使用安全連接」.

假如沒有給定 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節,「使用安全連接」.

使用的加密關鍵字的選擇基於第二個到 DES_ENCRYPT()的參數,假如給定: 

參數

說明

無參數

使用來自DES關鍵字檔案的第一個關鍵字。

key_num

使用DES 關鍵字檔案給出的關鍵字數字(0-9)

key_str

使用給出的關鍵字字串為 str 加密。

選擇--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 必須是一個從00範圍內的數字。檔案中行的排列順序是任意的。 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()除了str的前八位字元之外會忽略所有內容。這個行為由下劃線的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函數和操作符」BINARY操作符項中給出的二進制字串轉換。

  • OLD_PASSWORD(str)

PASSWORD()的執行變為改善安全性時,OLD_PASSWORD()會被新增到 MySQLOLD_PASSWORD()返回從前的PASSWORD()執行值( 4.1之前),同時允許您為任何4.1 之前的需要連接到您的5.1 版本MySQL伺服器前客戶端設置密碼,從而不至於將它們切斷。請參見5.7.9節,「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

返回值具有如下意義:

可壓縮性

意義

舉例

0

明確排序

帶有COLLATE 子句的值

1

無排序

不同排序的字串連接

2

明確排序

列值

3

系統常量

USER()返回值

4

可壓縮

文字字串

5

可忽略

NULL得來的NULL或一個資料表達式

 

 

下方值得優先級較高。

  • 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時才是精確的。若使用不帶ALLUNION,則會發生兩次刪除, 而  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()」.

您可以不用使用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)

給出一個作為字串的網絡地址的點地址資料表示,返回一個代資料表該地址數值的整數。地址可以是48比特地址。

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, 則相應的第一個八位組大於127IP 地址值會被截至  2147483647 (, INET_ATON('127.255.255.255') 所返回的值)。請參見11.2節,「數值類型」

  • 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。若超時時間被超過,則返回-1MASTER_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節,「儲存子程式和觸發程式的二進制日誌功能」。您可能會在mysqlbinlog 的書櫥中看到這個函數。

  • RELEASE_LOCK(str)

解開被GET_LOCK()獲取的,用字串str 所命名的鎖。若鎖被解開,則返回  1,若改線程尚未建立鎖,則返回0 (此時鎖沒有被解開 ), 若命名的鎖不存在,則返回 NULL。若該鎖從未被對GET_LOCK()的使用獲取,或鎖已經被提前解開,則該鎖不存在。

DO 語句和RELEASE_LOCK()同時使用很方便。請參見13.2.2節,「DO語法」

  • SLEEP(duration)

睡眠(暫停) 時間為duration 參數給定的秒數,然後返回 0。若 SLEEP() 被中斷,它會返回 1 duration 或許或包括一個給定的以微秒為單位的分數部分。

  • UUID()

返回一個通用唯一標識符(UUID) ,其產生的根據是《DCE 1.1: 遠程過程使用 (附錄A) CAE (公共應用軟件環境) 的說明,該作品於199710月由 The Open Group 出版 (檔案編號 C706, 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語法」.

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(聚合)函數

本章論述了用於一組數值操作的 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如何使用索引」  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() 返回NULLDISTINCT 關鍵詞可用於 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列資料表指定的列中使用明確的 ASCDESC關鍵詞,從而對個別列進行排序。 (不論如何排序被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節,「擁有某個字段的組間最大值的行」.

注意,假如您正在嘗試遵循標準 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。 原始參考手冊為英文版,與英文版參考手冊相比,本翻譯版可能不是最新的。