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.
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() is preferable):
SELECT 38.8, CAST(38.8 AS CHAR);
-> 38.8, '38.8'
SELECT 38.8, CONCAT(38.8);
-> 38.8, '38.8'
The following rules describe how conversion occurs for
If one or both arguments are
result of the comparison is
<=> equality comparison operator.
NULL <=> NULL, the result is
If both arguments in a comparison operation are strings,
they are compared as strings.
If both arguments are integers, they are compared as
Hexadecimal values are treated as binary strings if not
compared to a number.
If one of the arguments is a
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
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:
SELECT 1 > '6x';
SELECT 7 > '6x';
SELECT 0 > 'x6';
SELECT 0 = 'x6';
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
str_col is an indexed
string column, the index cannot be used when performing the
lookup in the following statement:
SELECT * FROM
The reason for this is that there are many different strings
that may convert to the value