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'
, …