Select * from tablea where xxno = 170325171202362928; select * from tablea where xxno = 170325171202362928; Xxno 170325171202362928 and 170325171202362930 appear in the results.
Why would an equivalent query have another record with a different value?
Let’s find out!
Analysis of the
Xxno = varchAR; xxno = varchar; xxno = varchar; The official document is as follows: dev.mysql.com/doc/refman/…
The following rules describe how conversion occurs for comparison operations: …. Omit ten thousand words…. In all other cases, the arguments are compared as floating-point (real) numbers.
That is, he will convert both sides of the equals sign to floating point numbers for comparison.
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:
If a floating-point comparison is used, the comparison will be approximate, causing the results to look inconsistent, which can lead to incorrect query results.
Let’s test the example we just produced:
mysql > select '170325171202362928' = 170325171202362930; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | '170325171202362928' = 170325171202362930 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00) sec)```Copy the codeYou can see that the string ‘170325171202362928’ is actually equal to the value 170325171202362930. Let’s look at the string ‘170325171202362928’ and the string ‘170325171202362930’ converted to float
Mysql > select '170325171202362928' + 0.0; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | '170325171202362928' + 0.0 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1.7032517120236294 e17 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > select '170325171202362930' + 0.0; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | '170325171202362930' + 0.0 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1.7032517120236294 e17 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the codeWe found that when we converted two different strings to floating point numbers, the result was the same,
So as long as the values converted to floating-point are equal, the implicit comparison will be equal, and we continue to test the results of other strings converted to floating-point equality
Mysql > select '170325171202362931' + 0.0; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | '170325171202362931' + 0.0 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1.7032517120236294 e17 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > select '170325171202362941' + 0.0; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | '170325171202362941' + 0.0 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1.7032517120236294 e17 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the codeThe strings ‘170325171202362931’ and ‘170325171202362941’ are converted to floating-point values. Let’s see how they compare to values
mysql > select '170325171202362931' = 170325171202362930; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | '170325171202362931' = 170325171202362930 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > select '170325171202362941' = 170325171202362930; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | '170325171202362941' = 170325171202362930 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the codeThe results were also in line with expectations.
Therefore, when MySQL encounters a field type mismatch, it does all kinds of implicit conversions, which can cause loss of accuracy.
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:
If the field is character and has an index, the SQL will not take advantage of the index on the field if the query criteria are numeric filtered
SELECT * FROM tbl_name WHERE str_col=1;
Copy the codeThe reason for this is that there are many different strings that may convert to the value 1, such as ‘1’, ‘ 1’, or ‘1a’.
We test it
mysql > create table tbl_name(id int ,str_col varchar(10),c3 varchar(5),primary key(id),key idx_str(str_col)); Query OK, 0 rows affected (0.02sec) mysql > insert into Tbl_name (id,str_col) VALUES (1,'a'),(2,'b'); Query OK, 2 rows affected (0.01 SEC)Records: 2 Duplicates: 0 Warnings: 0 mysql > insert into tbl_name(id,str_col) values(3,'3c'),(4,'4d'); Query OK, 2 rows affected (0.00 SEC)Records: 2 Duplicates: 0 Warnings: 0 mysql > desc select * from tbl_name where str_col='a'; +----+-------------+----------+------+---------------+---------+---------+-------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | SIMPLE | tbl_name | ref | idx_str | idx_str | 13 | const | 1 | Using where; Using index |+----+-------------+----------+------+---------------+---------+---------+-------+------+--------------------------+ mysql > desc select * from tbl_name where str_col=3; +----+-------------+----------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | tbl_name | ALL | idx_str | NULL | NULL | NULL | 4 | Using where | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00) sec) mysql [localhost] {msandbox} (test) > select * from tbl_name where str_col=3; +----+---------+------+| id | str_col | c1 |+----+---------+------+| 3 | 3c | NULL |+----+---------+------+1 row in set, 2 warnings (0.00 SEC)Copy the codeWe can also see that when we compare 3 with str_col, it can’t use the index, and it gets the wrong value.
mysql > show warnings; +---------+------+----------------------------------------+| Level | Code | Message |+---------+------+----------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: '3c' || Warning | 1292 | Truncated incorrect DOUBLE value: '4d' |+---------+------+----------------------------------------+Copy the codeMySQL converts the values 3c and 4D to 3 and 4
summary
When you perform a query in a database, be it Oracle or MySQL, pay attention to the field type to avoid implicit conversion, which not only slows the query but also leads to error results.