一 使用'='进行相等判断
mysql> SELECT 1=0, '2'=2, 2=2,'0.02'=0, 'b'='b', (1+3) = (2+2),NULL=NULL;
+-----+-------+-----+----------+---------+---------------+-----------+
| 1=0 | '2'=2 | 2=2 | '0.02'=0 | 'b'='b' | (1+3) = (2+2) | NULL=NULL |
+-----+-------+-----+----------+---------+---------------+-----------+
| 0 | 1 | 1 | 0 | 1 | 1 | NULL |
+-----+-------+-----+----------+---------+---------------+-----------+
1 row in set (0.02 sec)
二 使用'<=>'进行相等的判断
mysql> SELECT 1<=>0, '2'<=>2, 2<=>2,'0.02'<=>0, 'b'<=>'b', (1+3) <=> (2+1),NULL<=>NULL;
+-------+---------+-------+------------+-----------+-----------------+-------------+
| 1<=>0 | '2'<=>2 | 2<=>2 | '0.02'<=>0 | 'b'<=>'b' | (1+3) <=> (2+1) | NULL<=>NULL |
+-------+---------+-------+------------+-----------+-----------------+-------------+
| 0 | 1 | 1 | 0 | 1 | 0 | 1 |
+-------+---------+-------+------------+-----------+-----------------+-------------+
1 row in set (0.00 sec)
由结果可以看到,'<=>'在执行比较操作时和'='的作用是相似的,唯一的区别是'<=>'可以用来对NULL进行判断,两者都为NULL时返回值为1。
三 使用'<>'和'!='进行不相等的判断
不等于运算符 <>或者 !=
'<>'或者'!='用于判断数字、字符串、表达式不相等的判断。如果不相等,返回值为1;否则返回值为0。这两个运算符不能用于判断空值NULL。
mysql> SELECT 'good'<>'god', 1<>2, 4!=4, 5.5!=5, (1+3)!=(2+1),NULL<>NULL;
+---------------+------+------+--------+--------------+------------+
| 'good'<