MySQL Implicit Type Conversion Leads to Unexpected Query Results

When a developer queried select * from tablea where xxno = 170325171202362928 , two rows were returned, one with a different value, prompting an investigation into MySQL's handling of type mismatches.

The column xxno is defined as VARCHAR , while the comparison value is numeric. MySQL converts both sides to floating‑point numbers for the comparison, as described in the official documentation.

The following rules describe how conversion occurs for comparison operations: ... In all other cases, the arguments are compared as floating‑point (real) numbers.

Consequently, the string '170325171202362928' and the number 170325171202362928 are considered equal after conversion to floating‑point, which can be demonstrated with the following MySQL session:

mysql> select '170325171202362928' = 170325171202362928;
+-------------------------------------------+
| '170325171202362928' = 170325171202362928 |
+-------------------------------------------+
|                                           1 |
+-------------------------------------------+
1 row in set (0.00 sec)

Both strings '170325171202362928' and '170325171202362930' convert to the same floating‑point value 1.7032517120236294e17 , making them compare as equal:

mysql> select '170325171202362928'+0.0;
+--------------------------+
| '170325171202362928'+0.0 |
+--------------------------+
| 1.7032517120236294e17   |
+--------------------------+
1 row in set (0.00 sec)

mysql> select '170325171202362930'+0.0;
+--------------------------+
| '170325171202362930'+0.0 |
+--------------------------+
| 1.7032517120236294e17   |
+--------------------------+
1 row in set (0.00 sec)

Further tests with other strings that convert to the same floating‑point value show the same behavior, confirming that MySQL's implicit conversion can cause unexpected matches.

When a string column with an index is compared to a numeric literal, MySQL cannot use the index because many different strings can convert to the same numeric value. For example:

SELECT * FROM tbl_name WHERE str_col=1;

This query results in a full table scan ( type: ALL ) instead of using the index, and MySQL may emit warnings about truncated double values when non‑numeric strings are coerced:

+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '3c' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '4d' |
+---------+------+----------------------------------------+

In summary, implicit type conversion between VARCHAR and numeric values in MySQL can lead to loss of precision, unexpected equality, and inability to use indexes, which may cause both performance degradation and incorrect query results.