Comparison of strings and numbers in MySQL (with examples)

The content of this article is about the comparison of strings and numbers in MySQL (with examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

In projects, we often use fuzzy search, but if we mistakenly compare string types and numeric types, sometimes the search results are not what we expected. For example:

The xml statement in mybatis is as follows:

<if test="criteria != null and criteria.length()>0">
    AND (name like concat("%",#{criteria},"%") OR id = #{criteria})
</if>

When searching, we enter "884 test", and the result will include the record with id=884, but the name does not match. This is the pitfall of MySQL string and number comparison: the comparison will convert the string type into an integer type, starting from the first letter and ending when it encounters a non-numeric type.

Let’s take a look at a few examples:

SELECT "abc"=1;
结果：0

SELECT "1abc"=1;
结果：1

SELECT "abc"=0;
结果：1

SELECT "a2bc"=2;
结果：0

So how to solve this problem? In fact, you only need to do a type conversion, as follows:

SELECT "2bc"=cast(2 as CHAR);
结果：0

The above is the detailed content of Comparison of strings and numbers in MySQL (with examples). For more information, please follow other related articles on the PHP Chinese website!