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, but is
unavailable before MySQL 4.0.2):
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, or 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 quickly look up the
value. If str_col
is an indexed
string column, the index cannot be used when performing the
lookup in the following statement:
SELECT * FROMtbl_name
WHEREstr_col
=1;
The reason for this is that there are many different strings
that may convert to the value 1
, such as
'1'
, ' 1'
, or
'1a'
.
Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent:
mysql>SELECT '18015376320243458' = 18015376320243458;
-> 1 mysql>SELECT '18015376320243459' = 18015376320243459;
-> 0
Such results can occur because the values are converted to floating-point numbers, which have only 53 bits of precision and are subject to rounding:
mysql> SELECT '18015376320243459'+0.0;
-> 1.8015376320243e+16
Furthermore, the conversion from string to floating-point and from integer to floating-point do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications.
The results shown will vary on different systems, and can be
affected by factors such as computer architecture or the
compiler version or optimization level. One way to avoid such
problems is to use CAST()
so that
a value will not be converted implicitly to a float-point
number:
mysql> SELECT CAST('18015376320243459' AS UNSIGNED) = 18015376320243459;
-> 1
For more information about floating-point comparisons, see Section A.5.5.8, “Problems with Floating-Point Values”.
User Comments
Note the following paragraph from the documentation on BETWEEN (http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_between) when using date/time values:
For best results when using BETWEEN with date or time values, you should 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.
Add your own comment.