默認函數名和左括號之間不能存在空格，用來幫助 MySQL 解釋器區分函數調用和表名列名引用

為了簡潔表示，接下來所有的示例將采用簡短輸出

mysql> SELECT MOD(29,9); +-----------+ | mod(29,9) | +-----------+ | 2 | +-----------+ 1 rows in set (0.00 sec)簡化如下輸出mysql> SELECT MOD(29,9);-> 2

操作符操作不同類型，將發生類型轉化，有時會隱式轉化

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' mysql> SELECT 1 > '6x';-> 0 mysql> SELECT 7 > '6x';-> 1 mysql> SELECT 0 > 'x6';-> 0 mysql> SELECT 0 = 'x6';-> 1

用數字查詢字符串列，MySQL 無法使用索引，因為很多不同的字符串可以轉換成該數字，譬如 '1', ' 1', or '1a'
SELECT * FROM tbl_name WHERE str_col=1;

mysql> SELECT '18015376320243458' = 18015376320243458;-> 1 mysql> SELECT '18015376320243459' = 18015376320243459;-> 0

之所以會發生這種情況是因為轉換成 floating-point numbers 進行比較，只有 53 bits of precision （精度）

mysql> SELECT '18015376320243459'+0.0;-> 1.8015376320243e+16

顯式轉換解決這個問題

mysql> SELECT CAST('18015376320243459' AS UNSIGNED) = 18015376320243459;-> 1

通過括號改變優先級

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

比較的結果是 1 (TRUE), 0 (FALSE), or NULL

默認字符串比較是不區分大小寫

=

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 安全比較，和 = 類似，當都為 NULL 時返回1，任意一個為 NULL 時返回0

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

<=,<,>=,>

表達式等價

(a, b) = (x, y)	(a = x) AND (b = y)
(a, b) <=> (x, y)	(a <=> x) AND (b <=> y)
(a, b) <> (x, y) and (a, b) != (x, y)	(a <> x) OR (b <> y)
(a, b) <= (x, y)	(a < x) OR ((a = x) AND (b <= y))
(a, b) < (x, y)	(a < x) OR ((a = x) AND (b < y))
(a, b) >= (x, y)	(a > x) OR ((a = x) AND (b >= y))
(a, b) > (x, y)	(a > x) OR ((a = x) AND (b > y))
expr BETWEEN min AND max	(min <= expr AND expr <= max)
expr NOT BETWEEN min AND max	NOT (expr BETWEEN min AND max)

IS and IS NOT and IS NULL and IS NOT NULL

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

COALESCE(value,…) 返回第一個不為 NULL 的值

mysql> SELECT COALESCE(NULL,1);-> 1 mysql> SELECT COALESCE(NULL,NULL,NULL);-> NULL

LEAST(value1,value2,…) 返最小值
GREATEST(value1,value2,…) 返最大值，返回 NULL 若其中一個為 NULL

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'

expr IN (value,…) expr NOT IN (value,…) 存在返回1，不存在返回0

mysql> SELECT 2 IN (0,3,5,7);-> 0 mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');-> 1mysql> SELECT (3,4) IN ((1,2), (3,4));-> 1 mysql> SELECT (3,4) IN ((1,2), (3,5));-> 0

不要混合帶引號和不帶引號

SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');Instead, write it like this:SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');

ISNULL(expr)

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

INTERVAL(N,N1,N2,N3,…) Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is NULL

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

NOT, !

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

AND, &&

mysql> SELECT 1 AND 1;-> 1 mysql> SELECT 1 AND 0;-> 0 mysql> SELECT 1 AND NULL;-> NULL mysql> SELECT 0 AND NULL;-> 0 mysql> SELECT NULL AND 0;-> 0

OR, ||

mysql> SELECT 1 OR 1;-> 1 mysql> SELECT 1 OR 0;-> 1 mysql> SELECT 0 OR 0;-> 0 mysql> SELECT 0 OR NULL;-> NULL mysql> SELECT 1 OR NULL;-> 1

XOR

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 equal (a AND (NOT b)) OR ((NOT a) and b)

= 在 SET 語法中賦值
:= 直接賦值

不像 = 符號，:= 符號永遠不會解析成比較運算符，這就意味你可以使用它在任何合法的 SQL 語法里

mysql> SELECT @var1, @var2;-> NULL, NULL mysql> SELECT @var1 := 1, @var2;-> 1, NULL mysql> SELECT @var1, @var2;-> 1, NULL mysql> SELECT @var1, @var2 := @var1;-> 1, 1 mysql> SELECT @var1, @var2;-> 1, 1mysql> SELECT @var1:=COUNT(*) FROM t1;-> 4 mysql> SELECT @var1;-> 4mysql> SELECT @var1;-> 4 mysql> SELECT * FROM t1;-> 1, 3, 5, 7mysql> UPDATE t1 SET c1 = 2 WHERE c1 = @var1:= 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql> SELECT @var1;-> 1 mysql> SELECT * FROM t1;-> 2, 3, 5, 7

= 符號只在兩種場景中使用

• SET statement
• SET clause of an UPDATE statement

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

IF(expr1,expr2,expr3)

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'

IFNULL(expr1,expr2)

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'

NULLIF(expr1,expr2)

mysql> SELECT NULLIF(1,1);-> NULL mysql> SELECT NULLIF(1,2);-> 1

字符串操作函數
返回字符串長度超過限制，返回 NULL
字符位置從 1 開始
需要長度參數的函數，非整形參數將就近取整

很多函數聲明 略

字符串比較 略

正則表達式 略

函數返回值字符集 略

算術運算符 略

數學函數 略

隨機數生成 RAND(), RAND(N) N為隨機數種子
若想獲得隨機數 R i <= R < j 用如下表達式 FLOOR(i + RAND() * (j ? i))

你可以檢索隨機行通過這種方式

SELECT * FROM tbl_name ORDER BY RAND() SELECT * FROM table1, table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000

CEIL(X) CEILING(X) 向上取整
FLOOR(X) 向下取整

ROUND(X), ROUND(X,D) 四舍五入

mysql> SELECT ROUND(-1.23);-> -1 mysql> SELECT ROUND(-1.58);-> -2 mysql> SELECT ROUND(1.58);-> 2 mysql> SELECT ROUND(1.298, 1);-> 1.3 mysql> SELECT ROUND(1.298, 0);-> 1 mysql> SELECT ROUND(23.298, -1);-> 20 mysql> SELECT ROUND(150.000,2), ROUND(150,2); +------------------+--------------+ | ROUND(150.000,2) | ROUND(150,2) | +------------------+--------------+ | 150.00 | 150 | +------------------+--------------+

日期和時間函數

一次查詢中多次調用獲取時間函數將為同一值

SELECT NOW(), NOW(); -> '2016-12-01 16:53:16', '2016-12-01 16:53:16'

MySQL 中使用的日歷 略

全文搜索函數 略

類型轉換函數
BINARY 字符串轉二進制字符串
CAST() 轉換到任何指定類型
CONVERT() 轉換到任何指定類型（多了字句用于不同字符集之間轉換）
SELECT CONVERT(_latin1'Müller' USING utf8);

XML 函數 略

位操作符和函數 略

加密和壓縮函數 略

信息函數 略
效率測試函數

mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye')); +----------------------------------------------+ | BENCHMARK(1000000,ENCODE('hello','goodbye')) | +----------------------------------------------+ | 0 | +----------------------------------------------+ 1 row in set (4.74 sec)

略 略 略

總結

以上是生活随笔為你收集整理的MySQL 5.7 Reference Manual Chapter 13 Functions and Operators 参考手册第十三章函数与操作符内容总结的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。