=

相等：Equal:

mysql> SELECT 1 = 0;
        -> 0
mysql> SELECT '0' = 0;
        -> 1
mysql> SELECT '0.0' = 0;
        -> 1
mysql> SELECT '0.01' = 0;
        -> 0
mysql> SELECT '.01' = 0.01;
        -> 1

对于行比较，(a, b) = (x, y) 等价于：For row comparisons, (a, b) = (x, y) is equivalent to:

(a = x) AND (b = y)

<=>

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL，以及0 rather than NULL if one operand is NULL.NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

<=> 操作符等同于标准 SQL 中的 IS NOT DISTINCT FROM 操作符。The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

对于行比较，(a, b) <=> (x, y) 等价于：For row comparisons, (a, b) <=> (x, y) is equivalent to:

(a <=> x) AND (b <=> y)

<>, !=

不相等：Not equal:

mysql> SELECT '.01' <> '0.01';
        -> 1
mysql> SELECT .01 <> '0.01';
        -> 0
mysql> SELECT 'zapp' <> 'zappp';
        -> 1

对于行比较，(a, b) <> (x, y) 和 (a, b) != (x, y) 等价于：For row comparisons, (a, b) <> (x, y) and (a, b) != (x, y) are equivalent to:

(a <> x) OR (b <> y)

<=

小于或等于：Less than or equal:

mysql> SELECT 0.1 <= 2;
        -> 1

对于行比较，(a, b) <= (x, y) 等价于：For row comparisons, (a, b) <= (x, y) is equivalent to:

(a < x) OR ((a = x) AND (b <= y))

<<

小于：Less than:

mysql> SELECT 2 < 2;
        -> 0

对于行比较，(a, b) < (x, y) 等价于：For row comparisons, (a, b) < (x, y) is equivalent to:

(a < x) OR ((a = x) AND (b < y))

>=>=

大于或等于：Greater than or equal:

mysql> SELECT 2 >= 2;
        -> 1

对于行比较，(a, b) >= (x, y) 等价于：For row comparisons, (a, b) >= (x, y) is equivalent to:

(a > x) OR ((a = x) AND (b >= y))

>>

大于：Greater than:

mysql> SELECT 2 > 2;
        -> 0

对于行比较，(a, b) > (x, y) 相当于：For row comparisons, (a, b) > (x, y) is equivalent to:

(a > x) OR ((a = x) AND (b > y))

expr BETWEEN min AND maxexpr BETWEEN min AND max

如果 expr 大于或等于 min 且 expr 小于或等于 max，BETWEEN 返回 1，否则返回 0。如果所有参数类型相同，这相当于表达式 (min <= expr AND expr <= max)。否则，将根据 第 14.3 节，“表达式求值中的类型转换” 中描述的规则进行类型转换，但适用于所有三个参数。If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 14.3, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.

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

在使用 BETWEEN 处理日期或时间值时，为获得最佳效果，请使用 CAST() 将值显式转换为所需的数据类型。示例：如果将 DATETIME 与两个 DATE 值进行比较，请将 DATE 值转换为 DATETIME 值。如果在与 DATE 比较时使用字符串常量，例如 '2001-1-1'，请将该字符串转换为 DATE。For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.

expr NOT BETWEEN min AND maxexpr NOT BETWEEN min AND max

这与 NOT (expr BETWEEN min AND max) 相同。This is the same as NOT (expr BETWEEN min AND max).

COALESCE(value,...)

返回列表中第一个非 NULL 的值，如果没有非 NULL 值，则返回 NULL。Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

的返回类型COALESCE()是参数类型的聚合类型。The return type of COALESCE() is the aggregated type of the argument types.

mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL

EXISTS(query)EXISTS(query)

查询结果是否包含任何行。Whether the result of a query contains any rows.

CREATE TABLE t (col VARCHAR(3));
INSERT INTO t VALUES ('aaa', 'bbb', 'ccc', 'eee');

SELECT EXISTS (SELECT * FROM t WHERE col LIKE 'c%');
        -> 1

SELECT EXISTS (SELECT * FROM t WHERE col LIKE 'd%');
        -> 0

NOT EXISTS(query)NOT EXISTS(query)

查询结果是否不包含任何行：Whether the result of a query contains no rows:

SELECT NOT EXISTS (SELECT * FROM t WHERE col LIKE 'c%');
        -> 0

SELECT NOT EXISTS (SELECT * FROM t WHERE col LIKE 'd%');
        -> 1

GREATEST(value1,value2,...)GREATEST(value1,value2,...)

当有两个或更多参数时，返回最大的（数值最大的）参数。参数的比较规则与 LEAST() 相同。With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST().

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'

GREATEST() 如果任何参数为 NULL，则返回 NULL。GREATEST() returns NULL if any argument is NULL.

expr IN (value,...)expr IN (value,...)

如果 expr 等于 IN() 列表中的任意值，则返回 1（真），否则返回 0（假）。Returns 1 (true) if expr is equal to any of the values in the IN() list, else returns 0 (false).

类型转换根据第 14.3 节，“表达式求值中的类型转换”中描述的规则进行，适用于所有参数。如果IN()列表中的值不需要类型转换，它们都是相同类型的非JSON常量，且expr可以作为相同类型的值（可能在类型转换之后）与它们中的每一个进行比较，则会进行优化。列表中的值会被排序，并且expr的搜索使用二分查找，这使得IN()操作非常快速。Type conversion takes place according to the rules described in Section 14.3, “Type Conversion in Expression Evaluation”, applied to all the arguments. If no type conversion is needed for the values in the IN() list, they are all non-JSON constants of the same type, and expr can be compared to each of them as a value of the same type (possibly after type conversion), an optimization takes place. The values the list are sorted and the search for expr is done using a binary search, which makes the IN() operation very quick.

mysql> SELECT 2 IN (0,3,5,7);
        -> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
        -> 1

IN()可以用于比较行构造器：IN() can be used to compare row constructors:

mysql> SELECT (3,4) IN ((1,2), (3,4));
        -> 1
mysql> SELECT (3,4) IN ((1,2), (3,5));
        -> 0

您绝不应该在 IN() 列表中混合使用带引号和不带引号的值，因为带引号的值（例如字符串）和不带引号的值（例如数字）的比较规则不同。因此，混合类型可能导致结果不一致。例如，不要像这样编写 IN() 表达式：You should never mix quoted and unquoted values in an IN() list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an IN() expression like this:

SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');

相反，应这样写：Instead, write it like this:

SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');

隐式类型转换可能产生不直观的结果：Implicit type conversion may produce nonintuitive results:

mysql> SELECT 'a' IN (0), 0 IN ('b');
        -> 1, 1

在这两种情况下，比较值都被转换为浮点值，每种情况下都得到 0.0，并且比较结果为 1（真）。In both cases, the comparison values are converted to floating-point values, yielding 0.0 in each case, and a comparison result of 1 (true).

IN() 列表中的值的数量仅受 max_allowed_packet 值的限制。The number of values in the IN() list is only limited by the max_allowed_packet value.

为了符合 SQL 标准，IN()在以下情况下返回NULL不仅在左侧表达式为NULL时返回，而且当列表中没有找到匹配并且列表中的某个表达式为NULL.To comply with the SQL standard, IN() returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

IN() 语法也可用于编写某些类型的子查询。请参见 第 15.2.15.3 节，“使用 ANY、IN 或 SOME 的子查询”。IN() syntax can also be used to write certain types of subqueries. See Section 15.2.15.3, “Subqueries with ANY, IN, or SOME”.

expr NOT IN (value,...)expr NOT IN (value,...)

这与 NOT (expr IN (value,...)) 相同。This is the same as NOT (expr IN (value,...)).

INTERVAL(N,N1,N2,N3,...)

如果 N ≤ N1 则返回 0，如果 N ≤ N2 则返回 1，依此类推；如果 N 为 NULL 则返回 -1。所有参数都被视为整数。要求 N1 ≤ N2 ≤ N3 ≤ ... ≤ Nn 才能使此函数正确工作。这是因为使用了二分查找（非常快）。Returns 0 if N ≤ N1, 1 if N ≤ N2 and so on, or -1 if N is NULL. All arguments are treated as integers. It is required that N1 ≤ N2 ≤ N3 ≤ ... ≤ Nn for this function to work correctly. This is because a binary search is used (very fast).

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
        -> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
        -> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
        -> 0

IS boolean_value

将一个值与布尔值进行比较，其中 boolean_value 可以是 TRUE、FALSE 或 UNKNOWN。Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.

mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
        -> 1, 1, 1

IS NOT boolean_value

将一个值与布尔值进行比较，其中 boolean_value 可以是 TRUE、FALSE 或 UNKNOWN。Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.

mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
        -> 1, 1, 0

IS NULLIS NULL

测试一个值是否为 NULL。Tests whether a value is NULL.

mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
        -> 0, 0, 1

为了与 ODBC 程序良好配合，MySQL 在使用 IS NULL 时支持以下额外功能：To work well with ODBC programs, MySQL supports the following extra features when using IS NULL:

IS NOT NULL

测试一个值是否不是NULL。Tests whether a value is not NULL.

mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1, 1, 0

ISNULL(expr)

如果 expr 为 NULL，ISNULL() 返回 1，否则返回 0。If expr is NULL, ISNULL() returns 1, otherwise it returns 0.

mysql> SELECT ISNULL(1+1);
        -> 0
mysql> SELECT ISNULL(1/0);
        -> 1

ISNULL() 可以用来代替 = 来测试某个值是否为 NULL。（使用 = 将值与 NULL 比较总是返回 NULL。）ISNULL() can be used instead of = to test whether a value is NULL. (Comparing a value to NULL using = always yields NULL.)

ISNULL() 函数与 IS NULL 比较运算符有一些特殊的共同行为。请参阅 IS NULL 的描述。The ISNULL() function shares some special behaviors with the IS NULL comparison operator. See the description of IS NULL.

LEAST(value1,value2,...)

当有两个或更多参数时，返回最小值（数值最小）的参数。使用以下规则比较参数：With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:

LEAST() 的返回类型是比较参数类型的聚合类型。The return type of LEAST() is the aggregated type of the comparison argument types.

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'