Home  >  Article  >  Backend Development  >  Incorrect mysql query results

Incorrect mysql query results

WBOY
WBOYOriginal
2016-07-06 13:51:351083browse

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
Incorrect mysql query results

Reply content:

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
Incorrect mysql query results

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.

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn