12.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 strings to numbers as necessary, and vice versa.

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
statements.

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. 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
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. 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
BETWEEN with date or time values,
use CAST() to explicitly
convert the values to the desired data type.

A single-row subquery from a table or tables is not considered
a constant. For example, if a subquery returns an integer to
be compared to a DATETIME
value, the comparison is done as two integers. The integer is
not converted to a temporal value. To compare the operands as
DATETIME values, use
CAST() to explicitly convert
the subquery value to DATETIME.

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.

Comparison of JSON values takes place at two levels. The first
level of comparison is based on the JSON types of the compared
values. If the types differ, the comparison result is determined
solely by which type has higher precedence. If the two values have
the same JSON type, a second level of comparison occurs using
type-specific rules. For comparison of JSON and non-JSON values,
the non-JSON value is converted to JSON and the values compared as
JSON values. For details, see Comparison and Ordering of JSON Values.

The following examples illustrate conversion of strings to numbers
for comparison operations:

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
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, 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:

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 is
not converted implicitly to a float-point number:

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 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.

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.

This means that such a conversion results in a character
(nonbinary) string (a CHAR,
VARCHAR, or
LONGTEXT value), except in the case
that the connection character set is set to
binary. In that case, the conversion result is
a binary string (a BINARY,
VARBINARY, or
LONGBLOB value).

For integer expressions, the preceding remarks about expression
evaluation apply somewhat differently for
expression assignment; for example, in a
statement such as this:

CREATE TABLE t SELECT integer_expr;

In this case, the table in the column resulting from the
expression has type INT or
BIGINT depending on the length of
the integer expression. If the maximum length of the expression
does not fit in an INT,
BIGINT is used instead. The length
is taken from the max_length value of the
SELECT result set metadata (see
Section 28.7.5, “C API Data Structures”). This means that you can
force a BIGINT rather than
INT by use of a sufficiently long
expression: