1、比较运算符
空格问题:
当比较字符串时,MySQL忽视末尾空格、制表符和换行符,然而前面的空格、制表符和换行符在比较运算中被保留和使用。
mysql> select 'za'='za';
+-----------+
| 'za'='za' |
+-----------+
|1 |
+-----------+
1 row in set (0.00 sec)
mysql> select 'za'='za';
+---------------+
| 'za'='za' |
+---------------+
|1 |
+---------------+
1 row in set (0.00 sec)
mysql> select 'za'='za';
+-------------+
| 'za'='za' |
+-------------+
|0 |
+-------------+
1 row in set (0.00 sec)
通常包含一个NULL值得任何一个比较运算往往产生一个NULL结果。
mysql> select 11=null,1<2
+---------+----------+--------------------+----------------------+
| 11=null | 1<2
+---------+----------+--------------------+----------------------+
|NULL |NULL |NULL |NULL |
+---------+----------+--------------------+----------------------+
1 row in set (0.00 sec)
可以使用特殊的<=>运算符,称为“NULL安全的等于”运算符。这意味着即使当包含在比较运算中的表达式含有一个NULL值时,在一个正常的偏差下,MySQL也会为比较运算返回一个真值或假值。
mysql> select 2=null,2<=>null,null=null,null<=>null,0=null,0<=>null;
+--------+----------+-----------+-------------+--------+----------+
| 2=null | 2<=>null | null=null | null<=>null | 0=null | 0<=>null |
+--------+----------+-----------+-------------+--------+----------+
|NULL |0 |NULL |1 |NULL |0 |
+--------+----------+-----------+-------------+--------+----------+
1 row in set (0.00 sec)
2、逻辑运算符
运算符
作用
NOT或!
逻辑非
AND或&&
逻辑和
OR或||
逻辑或
XOR
逻辑异或
mysql> select (2=2)or(900<100),('a'='a') XOR not (100>200);
+------------------+-----------------------------+
| (2=2)or(900<100) | ('a'='a') XOR not (100>200) |
+------------------+-----------------------------+
|1 |0 |
+------------------+-----------------------------+
1 row in set (0.00 sec)