MySQL/Language/Operators

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

比較操作符

相等與不等

 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

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;只能用於存儲過程。表達式中應該使用: 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用於一個或多個同時賦值。