Home > Article > Backend Development > Incorrect mysql query results
Most of my order queries are queried according to the first sql statement query, and the order results are unique; but when it comes to this order, there are two query results. What is the reason? Under normal circumstances, if the field attribute is a string, isn't the value the same with or without quotes? Please give me some advice
The result of query using the first sql statement
Most of my order queries are queried according to the first sql statement query, and the order results are unique; but when it comes to this order, there are two query results. What is the reason? Under normal circumstances, if the field attribute is a string, isn't the value the same with or without quotes? Please give me some advice
The result of query using the first sql statement
Of course it is different. MySQL will perform implicit type conversion. When comparing numbers and varchar, MySQL will convert varchar into numbers. If the password type is a string and the query condition is int 0, it will match. show warnings;
View
Post the results of your query, I’m very curious too
How many digits does your varchar have? Is it not long enough and the following quotation marks are automatically ignored?
out_trade_no are definitely all numeric characters, right? It may be that the value is too large. During the implicit type conversion, part of it was intercepted, resulting in an extra piece of data when matching. I remind you that you should pay attention to things when writing SQL. If the string data type should be quoted, add it. , to avoid unnecessary loopholes
It should be truncated when varchar is converted to a number.
Without quotation marks, MySQL will first query and scan the entire table, and then convert the query results into varchars one by one.
Added single quote type matching and directly used index scanning (if there is an index on the column).
So not adding quotes can also cause performance issues.