维基教科书

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”