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, 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 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 * 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 B.5.5.8, “Problems with Floating-Point Values”.
As of MySQL 5.5.3, the server includes dtoa
,
a conversion library that provides the basis for improved
conversion between string or
DECIMAL
values and
approximate-value (FLOAT/DOUBLE) numbers:
Consistent conversion results across platforms, which eliminates, for example, Unix versus Windows conversion differences.
Accurate representation of values in cases where results previously did not provide sufficient precision, such as for values close to IEEE limits.
Conversion of numbers to string format with the best
possible precision. The precision of dtoa
is always the same or better than that of the standard C
library functions.
Because the conversions produced by this library differ in some cases from previous results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision.
The dtoa
library provides conversions with
the following properties. D
represents a value with a DECIMAL
or string representation, and F
represents a floating-point number in native binary (IEEE)
format.
F
->
D
conversion is done with the
best possible precision, returning
D
as the shortest string that
yields F
when read back in and
rounded to the nearest value in native binary format as
specified by IEEE.
D
->
F
conversion is done such that
F
is the nearest native binary
number to the input decimal string
D
.
These properties imply that F
->
D
-> F
conversions are lossless unless F
is
-inf
, +inf
, or
NaN
. The latter values are not supported
because the SQL standard defines them as invalid values for
FLOAT
or
DOUBLE
.
For D
->
F
-> D
conversions, a sufficient condition for losslessness is that
D
uses 15 or fewer digits of
precision, is not a denormal value, -inf
,
+inf
, or NaN
. In some
cases, the conversion is lossless even if
D
has more than 15 digits of
precision, but this is not always the case.
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.