用於 SELECT 和 WHERE 子句的函數 ...
1 一個 SQL 語句中的 select_expression 或 where_definition 可由任何使用了下麵所描述函數的表達式組成。 2 3 包含 NULL 的表達式總是得出一個 NULL 值結果,除非表達式中的操作和函數在文檔中有另外的說明。 4 5 註意:在一個函數名和跟隨它的括弧之間必須不存在空格。這有助於 MySQL 語法分析程式區分函數調用和對恰巧與函數同名錶或列的引用。然而,參數左右兩邊的空格卻是允許的。 6 7 你可以強制 MySQL 接受函數名後存在空格的形式,這需要通過以 --ansi 選項啟動 mysqld,或在 mysql_connect() 中使用 CLIENT_IGNORE_SPACE,但是,在這種情況下,所有的函數名均將成為保留字。查看章節 1.8.2 以 ANSI 模式運行 MySQL. 8 9 為了簡潔,從 mysql 程式輸出的例子以縮寫的形式顯示。因此: 10 11 mysql> SELECT MOD(29,9); 12 1 rows in set (0.00 sec) 13 14 +-----------+ 15 | mod(29,9) | 16 +-----------+ 17 | 2 | 18 +-----------+ 19 20 將被顯示為這樣: 21 22 mysql> SELECT MOD(29,9); 23 -> 2 24 25 6.3.1 無類型的特殊運算符和函數 26 6.3.1.1 圓括弧 27 ( ... ) 28 29 括弧,使用它來強制一個表達式的計算順序。 30 31 mysql> SELECT 1+2*3; 32 -> 7 33 mysql> SELECT (1+2)*3; 34 -> 9 35 36 6.3.1.2 比較運算符 37 比較運算符的結果是 1 (TRUE)、0 (FALSE) 或 NULL。這些函數可工作於數字和字元串上。根據需要,字元串將會自動地被轉換到數字,以及數字被轉換成字元串(比如在 Perl 中)。 38 39 MySQL 使用下列規則進行比較: 40 41 如果一個或兩個參數是 NULL,比較的結果是 NULL,除了 <=> 運算符。 42 如果在一個比較操作中兩個參數均是字元串,他們將作為字元串被比較。 43 如果兩個參數均是整數,他們作為整數被比較。 44 十六進位值如果不與一個數字進行比較,那麼它將當作一個二進位字元串。 45 如果參數之一是一個 TIMESTAMP 或 DATETIME 列,而另一參數是一個常數,在比較執行之前,這個常數被轉換為一個時間戳。這樣做是為了對 ODBC 更友好。 46 在所有其它情況下,參數作為浮點(real)數字被比較。 47 預設地,字元串使用當前字元集以忽略字母大小寫的方式進行比較(預設的字元集為 ISO-8859-1 Latin1,它對英語處理得很出色)。 48 49 下麵的例子演示了對於比較操作字元串到數字的轉換: 50 51 mysql> SELECT 1 > '6x'; 52 -> 0 53 mysql> SELECT 7 > '6x'; 54 -> 1 55 mysql> SELECT 0 > 'x6'; 56 -> 0 57 mysql> SELECT 0 = 'x6'; 58 -> 1 59 60 = 61 等於: 62 mysql> SELECT 1 = 0; 63 -> 0 64 mysql> SELECT '0' = 0; 65 -> 1 66 mysql> SELECT '0.0' = 0; 67 -> 1 68 mysql> SELECT '0.01' = 0; 69 -> 0 70 mysql> SELECT '.01' = 0.01; 71 -> 1 72 73 <> 74 != 75 不等於: 76 mysql> SELECT '.01' <> '0.01'; 77 -> 1 78 mysql> SELECT .01 <> '0.01'; 79 -> 0 80 mysql> SELECT 'zapp' <> 'zappp'; 81 -> 1 82 83 <= 84 小於或等於: 85 mysql> SELECT 0.1 <= 2; 86 -> 1 87 88 < 89 小於: 90 mysql> SELECT 2 < 2; 91 -> 0 92 93 >= 94 大於或等於: 95 mysql> SELECT 2 >= 2; 96 -> 1 97 98 > 99 大於: 100 mysql> SELECT 2 > 2; 101 -> 0 102 103 <=> 104 NULL 值安全等於: 105 mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL; 106 -> 1 1 0 107 108 IS NULL 109 IS NOT NULL 110 測試一個值是或不是 NULL: 111 mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL; 112 -> 0 0 1 113 mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL; 114 -> 1 1 0 115 116 為了能夠與其它程式更好的工作,在使用 IS NULL 是 MySQL 支持下列額外選擇性: 117 通過它,你可以找到最後插入的記錄行: 118 SELECT * FROM tbl_name WHERE auto_col IS NULL 119 120 這個操作可以通過設置 SQL_AUTO_IS_NULL=0 來禁止。查看章節 5.5.6 SET 句法. 121 對於 NOT NULL 的 DATE 和 DATETIME 列,可以通過使用下列語句找到特殊的值 0000-00-00: 122 SELECT * FROM tbl_name WHERE date_column IS NULL 123 124 這需要通過某些 ODBC 應用程式才能工作(因為 ODBC 不支持一個 0000-00-00 日期) 125 126 expr BETWEEN min AND max 127 如果 expr 大於或等於 min ,並且 expr 小於或等於 max,BETWEEN 返回 1,否則返回 0。它等價於表達式 (min <= expr AND expr <= max) ,只要所有的參數均是相同的類型。 否則類型會依照上面的規則發生轉換,但是應用於所有三個參數。註意,在 MySQL 4.0.5 之前,參數被轉換到 expr 的類型。 128 mysql> SELECT 1 BETWEEN 2 AND 3; 129 -> 0 130 mysql> SELECT 'b' BETWEEN 'a' AND 'c'; 131 -> 1 132 mysql> SELECT 2 BETWEEN 2 AND '3'; 133 -> 1 134 mysql> SELECT 2 BETWEEN 2 AND 'x-3'; 135 -> 0 136 137 expr NOT BETWEEN min AND max 138 等同於 NOT (expr BETWEEN min AND max)。 139 140 expr IN (value,...) 141 如果 expr 是 IN 列表中的作一值,它將返回 1,否則返回 0。如果所有的值均是常數,那麼所有的值被依照 expr 的類型進行計算和排序。然後以一個二進位搜索方式完成項目的搜索。這就意味著,如果 IN 列表完全由常數組成,IN 將是非常快的。如果 expr 是一個字母大小寫敏感的字元串表達式,字元串比較將以大小寫敏感方式執行: 142 mysql> SELECT 2 IN (0,3,5,'wefwf'); 143 -> 0 144 mysql> SELECT 'wefwf' IN (0,3,5,'wefwf'); 145 -> 1 146 147 從 MySQL 4.1 開始(符合 SQL-99 標準),如果左手邊的表達式是 NULL,或者在列表中沒有發現相匹配的值並且列表中的一個表達式是 NULL,IN 均返回 NULL。 148 149 expr NOT IN (value,...) 150 等同於 NOT (expr IN (value,...))。 151 152 ISNULL(expr) 153 如果 expr 是 NULL,ISNULL() 返回 1,否則返回 0: 154 mysql> SELECT ISNULL(1+1); 155 -> 0 156 mysql> SELECT ISNULL(1/0); 157 -> 1 158 159 註意,對 NULL 值使用 = 進行比較總是為 false ! 160 COALESCE(list) 161 返回列表中第一個非 NULL 的元素: 162 mysql> SELECT COALESCE(NULL,1); 163 -> 1 164 mysql> SELECT COALESCE(NULL,NULL,NULL); 165 -> NULL 166 167 INTERVAL(N,N1,N2,N3,...) 168 Returns 如果 N < N1 返回 0,如果 N < N2 返回 1,等等。所有的參數均被當作整數。為了函數能正確地工作,它要求 N1 < N2 < N3 < ... < Nn。這是因為它使用的是一個二進位的搜索(非常地快): 169 mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200); 170 -> 3 171 mysql> SELECT INTERVAL(10, 1, 10, 100, 1000); 172 -> 2 173 mysql> SELECT INTERVAL(22, 23, 30, 44, 200); 174 -> 0 175 176 如果以任何一個標準運算符(=, <>..., 但除了 LIKE)對一個忽略大小寫的字元串進行比較,尾部的空白空間(空格、TAB 和換行)均被忽略。 177 178 mysql> SELECT "a" ="A \n"; 179 -> 1 180 181 6.3.1.3 邏輯運算符 182 183 在 SQL 中,所有的邏輯運算符返回的值均為 TRUE、FALSE 或 NULL (未知)。在 MySQL 中,它們由 1 (TRUE)、0 (FALSE) 和 NULL 實現。這些大部分在不同的 SQL 資料庫間是相同的,然而某些可能會以一個非零值返回 TRUE。 184 185 NOT 186 ! 187 邏輯非。如果操作數為 0,返回 1;如果操作數為非零,返回 0;如果操作數為 NOT NULL,返回 NULL。 188 mysql> SELECT NOT 10; 189 -> 0 190 mysql> SELECT NOT 0; 191 -> 1 192 mysql> SELECT NOT NULL; 193 -> NULL 194 mysql> SELECT ! (1+1); 195 -> 0 196 mysql> SELECT ! 1+1; 197 -> 1 198 199 最後一個例子返回 1,因為表達式是與 (!1)+1 一樣被計算的。 200 201 AND 202 && 203 邏輯與。如果所有的操作數都是非零或非 NULL 的,返回 1;如果有一個或多個操作數為 0 ,則返回 0,只要操作數中有 NULL 返回值就為 NULL。 204 mysql> SELECT 1 && 1; 205 -> 1 206 mysql> SELECT 1 && 0; 207 -> 0 208 mysql> SELECT 1 && NULL; 209 -> NULL 210 mysql> SELECT 0 && NULL; 211 -> 0 212 mysql> SELECT NULL && 0; 213 -> 0 214 215 請註意,在 MySQL 4.0.5 以前版本中,當遇到一個 NULL 時就停止計算,而不是繼續進程檢查可能存在的0。這就意味著,在這些版本中,SELECT (NULL AND 0) 返回 NULL,而不是 0。在 4.0.5 中,代碼已被重新設計了,已便於在任何仍然使用優化情況下,返回值總是能如 ANSI 所規定的那樣。 216 OR 217 || 218 邏輯或。如果任何一個操作數是非零的,返回值為 1,如果任一操作數為 NULL,返回值為 NULL,否則返回 0。 219 mysql> SELECT 1 || 1; 220 -> 1 221 mysql> SELECT 1 || 0; 222 -> 1 223 mysql> SELECT 0 || 0; 224 -> 0 225 mysql> SELECT 0 || NULL; 226 -> NULL 227 mysql> SELECT 1 || NULL; 228 -> 1 229 230 XOR 231 邏輯異或。如果任一操作數為 NULL,返回值為 NULL。對於非 NULL 的操作數,如果有奇數個非零的操作數,結果返回為 1,否則返回 0。 232 mysql> SELECT 1 XOR 1; 233 -> 0 234 mysql> SELECT 1 XOR 0; 235 -> 1 236 mysql> SELECT 1 XOR NULL; 237 -> NULL 238 mysql> SELECT 1 XOR 1 XOR 1; 239 -> 1 240 241 a XOR b 算術相等於 (a AND (NOT b)) OR ((NOT a) and b)。 XOR 在 MySQL 4.0.2 中被添加。 242 6.3.1.4 控制流函數 243 244 IFNULL(expr1,expr2) 245 如果 expr1 為非 NULL 的,IFNULL() 返回 expr1,否則返回 expr2。IFNULL() 返回一個數字或字元串值,這取決於它被使用的語境: 246 mysql> SELECT IFNULL(1,0); 247 -> 1 248 mysql> SELECT IFNULL(NULL,10); 249 -> 10 250 mysql> SELECT IFNULL(1/0,10); 251 -> 10 252 mysql> SELECT IFNULL(1/0,'yes'); 253 -> 'yes' 254 255 在 MySQL 4.0.6 和更新版本中,IFNULL(expr1,expr2) 的預設返回值以 STRING、REAL 或 INTEGER 順序更加'general'了兩個表達式。當你基於一個表達式創建一個表或在一個臨時表中 MySQL 不得不存儲一個從 IFNULL() 返回的值時,這個與較早 MySQL 版本的不同將更加值得註意。 256 CREATE TABLE foo SELECT IFNULL(1,"test") as test; 257 258 在 MySQL 4.0.6 中,列 'test' 的類型為 CHAR(4),然而在較早的版本中,你得到的卻是 BIGINT。 259 260 NULLIF(expr1,expr2) 261 如果 expr1 = expr2 為真,返回 NULL,否則返回 expr1。它等同於 CASE WHEN x = y THEN NULL ELSE x END: 262 mysql> SELECT NULLIF(1,1); 263 -> NULL 264 mysql> SELECT NULLIF(1,2); 265 -> 1 266 267 註意,如果參數不相等,在 MySQL 中,expr1 被求值兩次。 268 269 IF(expr1,expr2,expr3) 270 如果 expr1 為真(expr1 <> 0 以及 expr1 <> NULL),那麼 IF() 返回 expr2,否則返回 expr3。IF() 返回一個數字或字元串,這取決於它被使用的語境: 271 mysql> SELECT IF(1>2,2,3); 272 -> 3 273 mysql> SELECT IF(1<2,'yes','no'); 274 -> 'yes' 275 mysql> SELECT IF(STRCMP('test','test1'),'no','yes'); 276 -> 'no' 277 278 如果 expr2 或 expr3 明確地為 NULL,那麼函數 IF() 的返回值類型為非 NULL 列的類型。(這在選擇在 MySQL 4.0.3 中新加入)。 expr1 是作為一個整數值被計算的,這就意味著,如果測試的是一個浮點型或字元串值,就必須進行比較操作: 279 mysql> SELECT IF(0.1,1,0); 280 -> 0 281 mysql> SELECT IF(0.1<>0,1,0); 282 -> 1 283 284 在上面第一種情況下,IF(0.1) 返回 0,是因為 0.1 被轉換為一個整數值,返回 IF(0) 的測試結果。這可能不是你所期望的。在第二種情況下,比較測試原浮點數是否為一個非零值。比較的結果被作為整數使用。 預設的 IF() 返回值類型 (當結果存儲在臨時表中時,這是非常重要的) 在 MySQL 3.23 中按下列方式