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'
mysql> SELECT 38.8, CAST(38.8 AS CHAR); -> 38.8, '38.8' mysql> SELECT 38.8, CONCAT(38.8); -> 38.8, '38.8'
See later in this section for information about the character set
of implicit number-to-string conversions, and for modified rules
that apply to
CREATE TABLE ... SELECT
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
<=>equality comparison operator. For
NULL <=> NULL, the result is true. No conversion is needed.
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
DATETIMEcolumn 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. For example, to achieve best results when using
BETWEENwith date or time values, use
CAST()to explicitly convert the values to the desired data type.
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
In all other cases, the arguments are compared as floating-point (real) numbers.
For information about conversion of values from one temporal type to another, see Section 11.3.7, “Conversion Between Date and Time Types”.
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
For comparisons of 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
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, such as
' 1', or
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
CAST() so that a value is
not 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.4.8, “Problems with Floating-Point Values”.
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
dtoais 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 non-
dtoa 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.
dtoa library provides conversions with the
D represents a
value with a
DECIMAL or string
F represents a
floating-point number in native binary (IEEE) format.
Dconversion is done with the best possible precision, returning
Das the shortest string that yields
Fwhen read back in and rounded to the nearest value in native binary format as specified by IEEE.
Fconversion is done such that
Fis the nearest native binary number to the input decimal string
These properties imply that
conversions are lossless unless
NaN. The latter values are not supported
because the SQL standard defines them as invalid values for
conversions, a sufficient condition for losslessness is that
D uses 15 or fewer digits of precision,
is not a denormal value,
NaN. In some cases,
the conversion is lossless even if
has more than 15 digits of precision, but this is not always the
As of MySQL 5.5.3, implicit conversion of a numeric or temporal
value to string produces a value that has a character set and
collation determined by the
variables. (These variables commonly are set with
SET NAMES. For information about
connection character sets, see
Section 10.1.4, “Connection Character Sets and Collations”.)
This change means that such a conversion results in a character
(nonbinary) string (a
LONGTEXT value), except when the
connection character set is set to
that case, the conversion result is a binary string (a
Before MySQL 5.5.3, an implicit conversion always produced a
binary string, regardless of the connection character set. Such
implicit conversions to string typically occur for functions that
are passed numeric or temporal values when string values are more
usual, and thus could have effects beyond the type of the
converted value. Consider the expression
CONCAT(1, 'abc'). The numeric
1 was converted to the binary string
'1' and the concatenation of that value with
the nonbinary string
'abc' produced the binary
Some functions are unaffected by this change in behavior:
USINGclause still returns
Encryption and compression functions that expect string arguments and previously returned binary strings are unaffected if the return value can contain non-ASCII characters. Examples include
COMPRESS(). If the return value contains only ASCII characters, the function now returns a character string with the connection character set and collation. Examples include