(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
automatically generated byinfo2html