維基教科書

MySQL/Language/Operators

< MySQL

操作符用於寫表達式，可作用於常量值、變量值、列名等。

比較操作符

編輯

相等與不等

編輯 
 SELECT True = True -- returns 1
 SELECT True = False -- returns 0



 SELECT True <> False -- returns 1
 SELECT True != True -- returns 0

IS以及NULL安全的比較

編輯

NULL值與非空值比較，結果為NULL。判斷一個值是否為null，應該用IS: 

 SELECT (NULL IS NULL) -- returns 1
 SELECT (1 IS NULL) -- returns 0
 SELECT (True IS True) -- returns 1

檢查一個值 非空： 

 SELECT (True IS NOT NULL) -- returns 1

運算符<=>同時兼顧了空值比較與普通值比較： 

 SELECT NULL <=> NULL -- 1
  SELECT NULL <=> True -- NULL
 SELECT True <=> True -- 1
 SELECT col1 <=> col2 FROM myTable

沒有空值安全的不等比較運算符，變通辦法是： 

 SELECT NOT (col1 <=> col2) FROM myTable

IS與布爾比較

編輯

IS與IS NOT可用於布爾比較，用於保留字TRUE, FALSE 與 UNKNOWN (同義詞NULL). 

 SELECT 1 IS TRUE -- returns 1
 SELECT 1 IS NOT TRUE -- returns 0
 SELECT 1 IS FALSE -- returns 0
 SELECT 0 IS FALSE -- returns 1
 SELECT (NULL IS NOT FALSE) -- returns 1: unknown is not false
 SELECT (NULL IS UNKNOWN) -- returns 1
 SELECT (NULL IS NOT UNKNOWN) -- returns 0

大於，小於...

編輯 
 SELECT 100 > 0 -- returns 1
 SELECT 4 > 5 -- return 0

 SELECT 1 < 2 -- returns 1
 SELECT 2 < 2 -- returns 0

也可用於文本： 

 SELECT 'a' < 'b' -- returns 1

實際上，字符比較使用COLLATION定義的排序規則。 

<= 或>=操作符。

BETWEEN

編輯 
 SELECT 2 BETWEEN 10 AND 100    -- 0
 SELECT 10 BETWEEN 10 AND 100   -- 1
 SELECT 20 BETWEEN 10 AND 100   -- 1

上述範圍是閉區間。

也可以用NOT BETWEEN： 

 SELECT 8 NOT BETWEEN 5 AND 10 -- returns 0

IN

編輯 
 SELECT 5 IN (5, 6, 7) -- returns 1
 SELECT 1 IN (5, 6, 7) -- returns 0
 SELECT 5 IN ('a', 'z', '5')  -- returns 1  ？？

值的列表個數沒有限制。

可以使用NOT IN: 

 SELECT 1 NOT IN (1, 2, 3) -- returns 0

邏輯運算符

編輯

MySQL布爾邏輯

編輯

MySQL沒有真正的BOOLEAN類型。FALSE是0的同義詞。在布爾上下文中空字符串等效於FALSE。TRUE是1的同義詞。在不二上下文中所有非空且非FALSE的數據被認為是TRUE

UNKNOWN是NULL的同義詞。

特殊日期0/0/0是NULL.

NOT

編輯

NOT是單元運算符 

 SELECT NOT 1 -- returns 0
 SELECT NOT FALSE -- returns 1
 SELECT NOT NULL -- returns NULL
 SELECT NOT UNKNOWN -- returns NULL

!是NOT的同義符號： 

 SELECT !1

AND

編輯

如果一個操作數為NULL，結果為NULL 

 SELECT 1 AND 1 -- returns 1
 SELECT 1 AND '' -- return 0
 SELECT '' AND NULL -- returns NULL

&&是AND的同義符號； 

 SELECT 1 && 1

OR

編輯

如果兩個操作數都為NULL，結果為NULL. 

 SELECT TRUE OR FALSE -- returns 1
 SELECT 1 OR 1 -- returns 1
 SELECT FALSE OR FALSE -- returns 0
 SELECT NULL OR TRUE -- returns 1

||是OR的同義： 

 SELECT 1 || 0

XOR

編輯

NULL值

編輯

如果一個操作數為NULL，結果為NULL 

 SELECT 1 XOR 0 -- returns 1
 SELECT FALSE XOR TRUE -- returns 1
 SELECT 1 XOR TRUE -- returns 0
 SELECT 0 XOR FALSE -- returns 0
 SELECT NULL XOR 1 -- returns NULL

算術運算符

編輯

可以指定數的正號 '+'： 

 SELECT +1 -- return 1

可以指定數的負號 '-' 

 SELECT -1 -- returns -1
 SELECT -+1 -- returns -1
 SELECT --1 -- returns 1

求和： 

 SELECT 1 + 1 -- returns 2

減法 

 SELECT True - 1 -- returns 0

乘法： 

 SELECT 1 * 1 -- returns 1

除法，返回浮點數： 

 SELECT 10 / 2 -- returns 5.0000
 SELECT 1 / 1 -- returns 1.0000
 SELECT 1 / 0 -- returns NULL (not an error)

整數除法DIV，返回整數，沒有餘數： 

 SELECT 10 DIV 3 -- returns 3

求餘數，用 '%' 或 MOD: 

 SELECT 10 MOD 3 -- returns 1

用 + 做數據類型轉換

編輯

整型到浮點類型： 

 SELECT 1 + 0.0 -- returns 1.0
 SELECT 1 + 0.000 -- returns 1.000
 SELECT TRUE + 0.000 -- returns 1.000

字符到整型： 

 SELECT '1' + 0 -- returns 1
 SELECT '1' + FALSE -- returns 1
 SELECT <nowiki>''</nowiki> + <nowiki>''</nowiki> -- returns 0

文本操作符

編輯

MySQL沒有文本操作符。

加號+把兩個文本轉換為整型再相加。

應該用CONCAT()函數連接兩個字符串。

LIKE

編輯

字符串模糊匹配： 

 SELECT * FROM articles WHERE title LIKE 'hello world'

匹配是大小寫不敏感的，例外情況：

  • 如果列被聲明為BINARY
  • 當表達式包含BINARY子句：
 SELECT * 'test' LIKE BINARY 'TEST' -- returns 0

通配符：

  • _ 任何單個字符
  • % 任何長度的字符序列

"\"轉義引號 ("'")不受ESCAPE子句影響。"\"不能轉義自身。

例子：

  • 以"hello"開頭的詞:
 SELECT * FROM articles WHERE title LIKE 'hello%'
  • 以"world"結尾的詞:
 SELECT * FROM articles WHERE title LIKE '%world'
  • 包含"gnu"的詞:
 SELECT * FROM articles WHERE title LIKE '%gnu%'

轉義保留字符： 

 SELECT * FROM articles WHERE title LIKE '\_%' -- titles starting with _
 SELECT * FROM articles WHERE title LIKE '\%%' -- titles starting with %

使用其他指定的轉義字符： 

 SELECT * FROM articles WHERE title LIKE '/_%' ESCAPE '/'

使用LIKE就不會忽略兩端空格符： 

 SELECT 'word' = 'word ' -- returns 0
 SELECT 'word' LIKE 'word ' -- returns 0


LIKE也用於數值： 

 SELECT 123 LIKE '%2%' -- returns 1

NOT LIKE: 

 SELECT 'a' NOT LIKE 'b' -- returns 1

SOUNDS LIKE

編輯 
 SELECT `word1` SOUNDS LIKE `word2` FROM `wordList` -- short form
 SELECT SOUNDEX(`word1`) = SOUNDEX(`word2`) FROM `wordList` -- long form

正則表達式

編輯

使用REGEXP表示正則表達式模式 

 SELECT 'string' REGEXP 'pattern'

RLIKE是REGEXP的同義。

位運算符

編輯

Bit-NOT: 

 SELECT ~0 -- returns 18446744073709551615
 SELECT ~1 -- returns 18446744073709551614

Bit-AND: 

 SELECT 1 & 1 -- returns 1
 SELECT 1 & 3 -- returns 1
 SELECT 2 & 3 -- returns 2

Bit-OR: 

 SELECT 1 | 0 -- returns 1
 SELECT 3 | 0 -- returns 3
 SELECT 4 | 2 -- returns 6

Bit-XOR: 

 SELECT 1 ^ 0 -- returns 1
 SELECT 1 ^ 1 -- returns 0
 SELECT 3 ^ 1 -- returns 2

Left shift: 

 SELECT 1 << 2 -- returns 4

Right shift: 

 SELECT 1 >> 2 -- 0

條件運算符

編輯

IF

編輯

結構IF ... THEN ... [ELSEIF ... THEN ...] ELSE ... END IF;只能用於存儲過程。表達式中應該使用[1]: IF(condition, ifTrue, ifFalse);.

例如SELECT IF(-1 < 0, 0, 1); returns 0.

CASE

編輯

SELECT CASE WHEN condition THEN ifTrue ELSE ifFalse END;

例子: SELECT CASE WHEN '-1 < 0' THEN 0 ELSE 1 END; renvoie 0. 

    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE
        BEGIN
        END;
    END CASE;



SELECT CASE v
    WHEN 1 THEN 'a'
    WHEN 2 THEN 'b'
    WHEN 3 THEN 'c'
    WHEN 4 THEN 'd'
    ELSE 0
    END as value

優先級

編輯

運算符優先級

編輯

優先級列表： 

 INTERVAL
 BINARY, COLLATE
 !
 - (unary minus), ~ (unary bit inversion)
 ^
 *, /, DIV, %, MOD
 -, +
 <<, >>
 &
 |
 =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
 BETWEEN, CASE, WHEN, THEN, ELSE
 NOT
 &&, AND
 XOR
 ||, OR
 :=

修改器：

  • PIPES_AS_CONCAT - 如果打開這種SQL模式，||優先級比^高，但比 - 與 ~ 低。
  • HIGH_NOT_PRECEDENCE - 如果打開這種SQL模式， NOT 與 ! 優先級相同。

使用括號

編輯 
 SELECT (1 + 1) * 5 -- returns 10

為了可讀性： 

 SELECT 1 + (2 * 5) -- the same as 1 + 2 * 5

INTERVAL

編輯

兩種用法： 

SELECT INTERVAL(6,1,2,3,4,5,6,7,8,9,10); --返回不小于第一个数的的索引。从零开始的索引。这里返回结果为6

SELECT NOW()-INTERVAL 24 HOUR  --  返回 前一天的时刻

賦值運算符

編輯 
 UPDATE `myTable` SET `uselessField`=0

 SELECT @myvar := 1

SELECT INTO用於一個或多個同時賦值。

參考文獻

編輯
  1. ↑ http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html
取自「https://zh.wikibooks.org/w/index.php?title=MySQL/Language/Operators&oldid=141983」