How to solve the pitfall of 0 value judgment in Mysql

Mysql’s Pitfalls Regarding 0 Value Judgment

Recently encountered a problem with Mysql returning multiple values, and finally found that it was judged by the 0 value in the mysql filter condition question.

The concise question is as follows

There is an INT field a, the default value is 0, and the judgment condition passed in by the program is a = 'abacd'.

The input is a string, and all the data with a=0 are returned.

Cause

Mysql will make a logical judgment. If it is an integer judgment, but the input is a string, if the string is not a number, the string will be converted to 0. Therefore,

The actual condition judged by this code is a = 0.

Solution

When making logical judgments in MySQL, you should ensure that the incoming value is consistent with the field type, that is, integer fields should be judged as integers, and string fields should be judged as strings. When cross-type judgments must be made, appropriate adjustments need to be made based on business logic to avoid getting into trouble.

Extension of the problem

If field a is of varchar type and the value is 0, then a='0' should be used for judgment instead of a = 0 for the same reason as above. , the string will be converted to 0, and the final result is 0 = 0.

Summary of Mysql Misunderstandings in Judging Value 0

In MySQL, when the data table field is of int type, you can determine whether the field is 0, and no problem will occur. If it is a char or varchar type, you need to use '0'. Obviously, strings and integers are not equivalent. So don't make such a stupid mistake.

The following is the principle of mysql search found on the Internet:

mysql will first convert the field value into an integer and then compare it.

If the first digit of the field is a string, then it will be converted into an integer and it will be 0.

Generally, quotes must be added when searching for strings.

The above is the detailed content of How to solve the pitfall of 0 value judgment in Mysql. For more information, please follow other related articles on the PHP Chinese website!