(mysql.info) type-conversion

Info Catalog

(mysql.info) operator-precedence

(mysql.info) non-typed-operators

(mysql.info) comparison-operators

 
 12.1.2 Type Conversion in Expression Evaluation
 -----------------------------------------------
 
 When an operator is used with operands of different types, type
 conversion occurs to make the operands compatible. Some conversions
 occur implicitly. For example, MySQL automatically converts numbers to
 strings as necessary, and vice versa.
 
      mysql> SELECT 1+'1';
              -> 2
      mysql> SELECT CONCAT(2,' test');
              -> '2 test'
 
 It is also possible to perform explicit conversions. If you want to
 convert a number to a string explicitly, use the `CAST()' or `CONCAT()'
 function (`CAST()' is preferable):
 
      mysql> SELECT 38.8, CAST(38.8 AS CHAR);
              -> 38.8, '38.8'
      mysql> SELECT 38.8, CONCAT(38.8);
              -> 38.8, '38.8'
 
 The following rules describe how conversion occurs for comparison
 operations:
 
    * If one or both arguments are `NULL', the result of the comparison
      is `NULL', except for the `NULL'-safe `<=>' equality comparison
      operator.  For `NULL <=> NULL', the result is true.
 
    * If both arguments in a comparison operation are strings, they are
      compared as strings.
 
    * If both arguments are integers, they are compared as integers.
 
    * Hexadecimal values are treated as binary strings if not compared
      to a number.
 
    * If one of the arguments is a `TIMESTAMP' or `DATETIME' column and
      the other argument is a constant, the constant is converted to a
      timestamp before the comparison is performed. This is done to be
      more ODBC-friendly. Note that this is not done for the arguments
      to `IN()'! To be safe, always use complete datetime/date/time
      strings when doing comparisons.
 
    * In all other cases, the arguments are compared as floating-point
      (real) numbers.
 
 The following examples illustrate conversion of strings to numbers for
 comparison operations:
 
      mysql> SELECT 1 > '6x';
              -> 0
      mysql> SELECT 7 > '6x';
              -> 1
      mysql> SELECT 0 > 'x6';
              -> 0
      mysql> SELECT 0 = 'x6';
              -> 1
 
 Note that when you are comparing a string column with a number, MySQL
 cannot use an index on the column to look up the value quickly. If
 STR_COL is an indexed string column, the index cannot be used when
 performing the lookup in the following statement:
 
      SELECT * FROM TBL_NAME WHERE STR_COL=1;
 
 The reason for this is that there are many different strings that may
 convert to the value `1': `'1'', `' 1'', `'1a'', ...
 

Info Catalog

(mysql.info) operator-precedence

(mysql.info) non-typed-operators

(mysql.info) comparison-operators