MySQL 实验室 因为专注,所以专业。

  • 首页
  • 博客
  • 下载
  • 文档
  • 工具
  • 知识库
  • 培训及服务
  • MySQL 5.1 Reference Manual :: 11 Functions and Operators :: 11.2 Operators :: 11.2.3 Comparison Functions and Operators
    • MySQL 5.1 Reference Manual
    • Preface, Notes, Licenses
    • 1 General Information
    • 2 Installing and Upgrading MySQL
    • 3 Tutorial
    • 4 MySQL Programs
    • 5 MySQL Server Administration
    • 6 Backup and Recovery
    • 7 Optimization
    • 8 Language Structure
    • 9 Internationalization and Localization
    • 10 Data Types
    • 11 Functions and Operators
    • 12 SQL Statement Syntax
    • 13 Storage Engines
    • 14 High Availability and Scalability
    • 15 MySQL Enterprise Monitor
    • 16 Replication
    • 17 MySQL Cluster NDB 6.X/7.X
    • 18 Partitioning
    • 19 Stored Programs and Views
    • 20 INFORMATION_SCHEMA Tables
    • 21 Connectors and APIs
    • 22 Extending MySQL
    • A MySQL 5.1 Frequently Asked Questions
    • B Errors, Error Codes, and Common Problems
    • C MySQL Change History
    • D Restrictions and Limits
    • Index
    • Standard Index
    • C Function Index
    • Command Index
    • Function Index
    • INFORMATION_SCHEMA Index
    • Transaction Isolation Level Index
    • JOIN Types Index
    • Operator Index
    • Option Index
    • Privileges Index
    • SQL Modes Index
    • Status Variable Index
    • Statement/Syntax Index
    • System Variable Index

    11.2.3. Comparison Functions and Operators

    Table 11.3. Comparison Operators

    Name Description
    BETWEEN ... AND ... Check whether a value is within a range of values
    COALESCE() Return the first non-NULL argument
    <=> NULL-safe equal to operator
    = Equal operator
    >= Greater than or equal operator
    > Greater than operator
    GREATEST() Return the largest argument
    IN() Check whether a value is within a set of values
    INTERVAL() Return the index of the argument that is less than the first argument
    IS NOT NULL NOT NULL value test
    IS NOT Test a value against a boolean
    IS NULL NULL value test
    IS Test a value against a boolean
    ISNULL() Test whether the argument is NULL
    LEAST() Return the smallest argument
    <= Less than or equal operator
    < Less than operator
    LIKE Simple pattern matching
    NOT BETWEEN ... AND ... Check whether a value is not within a range of values
    !=, <> Not equal operator
    NOT IN() Check whether a value is not within a set of values
    NOT LIKE Negation of simple pattern matching
    STRCMP() Compare two strings

    Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.

    The following relational comparison operators can be used to compare not only scalar operands, but row operands:

    =  >  <  >=  <=  <>  !=
    

    For examples of row comparisons, see Section 12.2.9.5, “Row Subqueries”.

    Some of the functions in this section return values other than 1 (TRUE), 0 (FALSE), or NULL. For example, LEAST() and GREATEST(). However, the value they return is based on comparison operations performed according to the rules described in Section 11.2.2, “Type Conversion in Expression Evaluation”.

    To convert a value to a specific type for comparison purposes, you can use the CAST() function. String values can be converted to a different character set using CONVERT(). See Section 11.9, “Cast Functions and Operators”.

    By default, string comparisons are not case sensitive and use the current character set. The default is latin1 (cp1252 West European), which also works well for English.

    • =

      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
      
    • <=>

      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.

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

      Not equal:

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

      Less than or equal:

      mysql> SELECT 0.1 <= 2;
              -> 1
      
    • <

      Less than:

      mysql> SELECT 2 < 2;
              -> 0
      
    • >=

      Greater than or equal:

      mysql> SELECT 2 >= 2;
              -> 1
      
    • >

      Greater than:

      mysql> SELECT 2 > 2;
              -> 0
      
    • IS boolean_value

      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

      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 NULL

      Tests whether a value is NULL.

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

      To work well with ODBC programs, MySQL supports the following extra features when using IS NULL:

      • If sql_auto_is_null variable is set to 1 (the default), then after a statement that successfully inserts an automatically generated AUTO_INCREMENT value, you can find that value by issuing a statement of the following form:

        SELECT * FROM tbl_name WHERE auto_col IS NULL
        

        If the statement returns a row, the value returned is the same as if you invoked the LAST_INSERT_ID() function. For details, including the return value after a multiple-row insert, see Section 11.11.3, “Information Functions”. If no AUTO_INCREMENT value was successfully inserted, the SELECT statement returns no row.

        The behavior of retrieving an AUTO_INCREMENT value by using an IS NULL comparison can be disabled by setting sql_auto_is_null = 0. See Section 5.1.4, “Server System Variables”.

      • For DATE and DATETIME columns that are declared as NOT NULL, you can find the special date '0000-00-00' by using a statement like this:

        SELECT * FROM tbl_name WHERE date_column IS NULL
        

        This is needed to get some ODBC applications to work because ODBC does not support a '0000-00-00' date value.

        See Section 21.1.7.1.1, “Obtaining Auto-Increment Values”, and the description for the FLAG_AUTO_IS_NULL option at Section 21.1.4.2, “Connector/ODBC Connection Parameters”.

    • IS NOT 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
      
    • expr BETWEEN min AND max

      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 11.2.2, “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
      

      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 max

      This is the same as NOT (expr BETWEEN min AND max).

    • COALESCE(value,...)

      Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

      mysql> SELECT COALESCE(NULL,1);
              -> 1
      mysql> SELECT COALESCE(NULL,NULL,NULL);
              -> NULL
      
    • GREATEST(value1,value2,...)

      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() returns NULL if any argument is NULL.

    • expr IN (value,...)

      Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Otherwise, type conversion takes place according to the rules described in Section 11.2.2, “Type Conversion in Expression Evaluation”, but applied to all the arguments.

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

      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');
      

      The number of values in the IN list is only limited by the max_allowed_packet value.

      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() syntax can also be used to write certain types of subqueries. See Section 12.2.9.3, “Subqueries with ANY, IN, or SOME”.

    • expr NOT IN (value,...)

      This is the same as NOT (expr IN (value,...)).

    • ISNULL(expr)

      If expr is NULL, ISNULL() returns 1, otherwise it returns 0.

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

      ISNULL() can be used instead of = to test whether a value is NULL. (Comparing a value to NULL using = always yields false.)

      The ISNULL() function shares some special behaviors with the IS NULL comparison operator. See the description of IS NULL.

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

      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
      
    • LEAST(value1,value2,...)

      With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:

      • If the return value is used in an INTEGER context or all arguments are integer-valued, they are compared as integers.

      • If the return value is used in a REAL context or all arguments are real-valued, they are compared as reals.

      • If any argument is a case-sensitive string, the arguments are compared as case-sensitive strings.

      • In all other cases, the arguments are compared as case-insensitive strings.

      LEAST() returns NULL if any argument is 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'
      

      Note that the preceding conversion rules can produce strange results in some borderline cases:

      mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);
              -> -9223372036854775808
      

      This happens because MySQL reads 9223372036854775808.0 in an integer context. The integer representation is not good enough to hold the value, so it wraps to a signed integer.