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 1+'1';-> 2 mysql>
SELECT CONCAT(2,' test');-> '2 test'
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
result of the comparison is
equality comparison operator. For
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
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
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:
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
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
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:
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:
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
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”.