MySQL/語言/運算子

MySQL 使用一些標準 SQL 運算子和一些非標準運算子。它們可用於編寫包含常量值、變數、欄位中包含的值和/或其他表示式的表示式。

比較運算子

相等

如果要檢查兩個值是否相等，必須使用 = 運算子

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

如果要檢查兩個值是否不同，可以使用 <> 或 != 運算子，它們具有相同的含義

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

<> 在 = 返回 0 的地方返回 1，反之亦然。

IS 和 NULL 安全比較

當將 NULL 值與非 NULL 值進行比較時，將得到 NULL。如果要檢查一個值是否為空，可以使用 IS

 SELECT (NULL IS NULL) -- returns 1
 SELECT (1 IS NULL) -- returns 0
 SELECT (True IS True) -- returns an error!

可以檢查一個值是否為非 NULL

 SELECT (True IS NOT NULL) -- returns 1

還有一個相等運算子，它將 NULL 視為一個正常的值，因此如果兩個值都是 NULL，它將返回 1（非 NULL）；如果其中一個值是 NULL，它將返回 0（非 NULL）。

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

沒有 NULL 安全的不等運算子，但可以鍵入以下內容

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

這種型別的比較也適用於 TEXT 值

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

一般來說，TEXT 比較使用字母順序。但是，確切的規則由使用的 COLLATION 定義。COLLATION 定義了給定字元集的排序規則。例如，一個 COLLATION 可能是區分大小寫的，而另一個 COLLATION 可能是區分大小寫的。

可以檢查一個值是否等於或大於另一個值。例如，以下查詢具有相同的含義

 SELECT `a` >= `b` FROM `myTable`
 SELECT NOT (`a` < `b`) FROM `myTable`

類似地，可以檢查一個值是否小於或等於另一個值

 SELECT `a` <= `b` FROM `myTable`

BETWEEN

如果要檢查一個值是否包含在給定的範圍內（包括邊界），可以使用 BETWEEN ... AND ... 運算子。AND 沒有其通常的含義。例子

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

BETWEEN 後的值和 AND 後的值都包含在範圍內。

也可以使用 NOT BETWEEN 來檢查一個值是否未包含在範圍內

 SELECT 8 NOT BETWEEN 5 AND 10 -- returns 0

IN

可以使用 IN 運算子來檢查一個值是否包含在一個值列表中

 SELECT 5 IN (5, 6, 7) -- returns 1
 SELECT 1 IN (5, 6, 7) -- returns 0

不要在列表中同時包含數字和字串，否則結果可能不可預測。如果有數字，應該用引號括起來

 SELECT 4 IN ('a', 'z', '5')

IN 運算子中包含的值的數量沒有理論上的限制。

也可以使用 NOT IN

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

邏輯運算子

MySQL 布林邏輯

MySQL 沒有真正的 BOOLEAN 資料型別。

FALSE 是 0 的同義詞。空字串在布林上下文中被視為 FALSE。

TRUE 是 1 的同義詞。所有非 NULL 和非 FALSE 資料在布林上下文中被視為 TRUE。

UNKNOWN 是 NULL 的同義詞。特殊日期 0/0/0 是 NULL。

NOT

NOT 是唯一隻有一個運算元的運算子。如果運算元為 TRUE，它返回 0；如果運算元為 FALSE，它返回 1；如果運算元為 NULL，它返回 NULL。

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

! 是 NOT 的同義詞。

 SELECT !1

AND

AND 如果兩個運算元都為 TRUE，則返回 1；否則返回 0；如果至少一個運算元為 NULL，則返回 NULL。

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

&& 是 AND 的同義詞。

 SELECT 1 && 1

OR

OR 如果至少一個運算元為 TRUE，則返回 TRUE；否則返回 FALSE；如果兩個運算元都是 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 NULL

|| 是 OR 的同義詞。

 SELECT 1 || 0

XOR

XOR（eXclusive OR）如果只有一個運算元為 TRUE，而另一個運算元為 FALSE，則返回 1；如果兩個運算元都為 TRUE 或兩個運算元都為 FALSE，則返回 0；如果其中一個運算元為 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

同義詞

AND 可以寫成 &&
OR 可以寫成 ||
NOT 可以寫成 !

只有 NOT（通常）與它的同義詞有不同的優先順序。有關詳細資訊，請參閱運算子優先順序。

算術運算子

MySQL 支援執行所有基本算術運算的運算元。

如果需要，可以使用 '+' 鍵入正值

 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

你可以使用 '/' 進行除法運算。返回一個 FLOAT 型別的數字。

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

你可以使用 DIV 進行整數除法。結果是一個 INTEGER 型別的數字。沒有餘數。該功能在 MySQL 4.1 中新增。

 SELECT 10 DIV 3 -- returns 3

你可以使用 '%' 或 MOD 獲取除法的餘數。

 SELECT 10 MOD 3 -- returns 1

使用 + 進行資料型別轉換

你可以透過這種方式將 INTEGER 轉換為 FLOAT。

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

你不能透過新增 0.0 將字串轉換為 FLOAT 值，但你可以將其轉換為 INTEGER。

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

文字運算子

MySQL 中沒有連線運算子。

算術運算子將值轉換為數字，然後執行算術運算，因此你不能使用 + 連線字串。

你可以使用 CONCAT() 函式來代替。

LIKE

LIKE 運算子可以用來檢查一個字串是否匹配一個模式。一個簡單的例子

 SELECT * FROM articles WHERE title LIKE 'hello world'

模式匹配通常不區分大小寫。有兩個例外情況

當 LIKE 比較與使用 BINARY 標誌宣告的列進行比較時（請參見 CREATE TABLE）；
當表示式包含 BINARY 子句時

 SELECT * 'test' LIKE BINARY 'TEST' -- returns 0

你可以在 LIKE 比較中使用兩個特殊字元

_ 表示“任何字元”（但必須是 1 個字元，不能是 0 個或 2 個字元）
% 表示“任何字元序列”（即使是 0 個字元或 1000 個字元）

注意，“\” 也會轉義引號（“'”），這種行為不能透過 ESCAPE 子句更改。此外，跳脫字元本身不會被轉義。

LIKE 的常見用法

查詢以單詞“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 1
 SELECT 'word' LIKE 'word ' -- returns 0

LIKE 也適用於數字。

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

如果你想檢查一個模式是否不匹配，你可以使用 NOT LIKE

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

SOUNDS LIKE

你可以使用 SOUNDS LIKE 來檢查兩個文字值是否以相同的方式發音。SOUNDS LIKE 使用 SOUNDEX 演算法，該演算法基於英語規則，非常近似（但簡單，因此速度快）。

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

SOUNDS LIKE 是 MySQL 對 SQL 的特有擴充套件。它在 MySQL 4.1 中新增。

正則表示式

你可以使用 REGEXP 來檢查一個字串是否使用正則表示式匹配一個模式。

 SELECT 'string' REGEXP 'pattern'

你可以使用 RLIKE 作為 REGEXP 的同義詞。

位運算子

位非

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

位與

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

位或

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

位異或

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

左移

 SELECT 1 << 2 -- returns 4

右移

 SELECT 1 >> 2 -- 0

條件

IF

IF ... THEN ... ELSE ... END IF; 結構僅在儲存過程中起作用。為了在儲存過程之外管理條件，我們可以使用^[1]: IF(condition, ifTrue, ifFalse);。

示例：SELECT IF(-1 < 0, 0, 1); 返回 0。

包含多個條件的示例（switch）^[2]^[3]

    IF n > m THEN SET s = '>';
    ELSEIF n = m THEN SET s = '=';
    ELSE SET s = '<';
    END IF;

CASE

SELECT CASE WHEN condition THEN ifTrue ELSE ifFalse END;

示例：SELECT CASE WHEN '-1 < 0' THEN 0 ELSE 1 END; 返回 0。

包含多個條件的示例^[4]

    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 與 ! 的優先順序相同。

使用括號

你可以使用括號強制 MySQL 在運算子優先順序之外，先評估一個子表示式，然後再評估另一個。

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

你也可以使用括號使表示式對人類更易讀，即使它們不影響優先順序。

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

賦值運算子

你可以使用 = 運算子將值賦予列。

 UPDATE `myTable` SET `uselessField`=0

當你想將值賦予變數時，你必須使用 := 運算子，因為使用 = 會產生歧義（它是賦值還是比較？）。

 SELECT @myvar := 1

你也可以使用 SELECT INTO 將值賦予一個或多個變數。

[1] ttps://dev.mysql.com.tw/doc/refman/5.7/en/control-flow-functions.html

[2] ttps://dev.mysql.com.tw/doc/refman/5.7/en/if.html

[3] ttps://dev.mysql.com.tw/doc/refman/5.7/en/case.html

[4] ttps://dev.mysql.com.tw/doc/refman/5.7/en/case.html

[1]

[2]

[3]

[4]