對比操作符詳解 1、=號對比操作符 ~~~ mysql> select '0.01'=1; ###不相等結果為0，相當于false +----------+ | '0.01'=1 | +----------+ | 0 | +----------+ ~~~ ~~~ mysql> select 1=1; ####相當結果為1，相當于true +-----+ | 1=1 | +-----+ | 1 | +-----+ ~~~ 2、!=/<> 不等號操作符 ~~~ mysql> SELECT '.01' <> '0.02'; +-----------------+ | '.01' <> '0.02' | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT '0.01' <> '0.01'; +------------------+ | '0.01' <> '0.01' | +------------------+ | 0 | +------------------+ 1 row in set (0.01 sec) ~~~ 3、is操作符 is操作符 ~~~ mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN; -> 1, 1, 1 ~~~ Is not操作符 ~~~ mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN; -> 1, 1, 0 ~~~ Is null對比操作符 ~~~ mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL; -> 0, 0, 1 ~~~ Is not null對比操作符 ~~~ mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL; -> 1, 1, 0 ~~~ 4、Expr between min and max對比操作符 相當于min <= expr AND expr <= max ~~~ mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1; -> 1, 0 mysql> SELECT 1 BETWEEN 2 AND 3; -> 0 mysql> SELECT 'b' BETWEEN 'a' AND 'c'; -> 1 mysql> SELECT 2 BETWEEN 2 AND '3'; -> 1 mysql> SELECT 2 BETWEEN 2 AND 'x-3'; -> 0 ~~~ expr NOT BETWEEN min AND max 相當于NOT (expr BETWEEN min AND max) 5、COALESCE(value,...)對比操作符 返回第一個非NULL的值，如果沒有非null值，則返回NULL ~~~ mysql> SELECT COALESCE(NULL,2); -> 2 mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL ~~~ 6、GREATEST(value1,value2,...) 返回其中最大的值 ~~~ mysql> SELECT GREATEST(2,0); -> 2 mysql> SELECT GREATEST(34.0,3.0,5.0,767.0); -> 767.0 mysql> SELECT GREATEST('B','A','C'); -> 'C' ~~~ 7、expr IN (value,...)對比操作符 當expr值能在values中找到，則返回1，否則返回0 ~~~ mysql> SELECT 2 IN (0,3,5,7); -> 0 mysql> SELECT 'wefwf' IN ('wee','wefwf','weg'); -> 1 mysql> SELECT (3,4) IN ((1,2), (3,4)); -> 1 mysql> SELECT (3,4) IN ((1,2), (3,5)); -> 0 ~~~ expr NOT IN (value,...)對比操作符 8、Isnull(expr)操作符 如果expr是null，則返回1，否則返回0 ~~~ mysql> SELECT ISNULL(1+1); -> 0 mysql> SELECT ISNULL(1/0); -> 1 ~~~ 9、LEAST(value1,value2,...) 返回最小值，如果其中有值為null，則返回null ~~~ mysql> SELECT LEAST(2,0); -> 0 mysql> SELECT LEAST(34.0,3.0,5.0,767.0); -> 3.0 mysql> SELECT LEAST('B','A','C'); -> 'A' ~~~