Examples compare strings and numbers in MySQL

This article mainly introduces examples to compare strings and numbers in MySQL. The content of the article is carefully selected and edited by the author. It has certain pertinence and is of great reference significance to everyone. Let's learn about the following examples to compare strings and numbers in MySQL.

In projects, we often use fuzzy search, but if we compare string types and number types incorrectly, sometimes the search results are not what we expect. Examples are as follows:

The xml statement in mybatis is as follows:

AND (name like concat("%",#{criteria},"%") OR id = #{criteria})

When searching, we enter "884 test" and the result will contain records with id=884, but no name matches. This is the pitfall of MySQL string and number comparisons: the comparison converts string types to integer types, starting with the first letter and ending with non-numeric types.

A few examples:

SELECT "abc"=1; Result: 0SELECT "1abc"=1; Result: 1SELECT "abc"=0; Result: 1SELECT "a2bc"=2; Result: 0

So how to solve this problem, in fact, only need to do a type conversion can be, as follows:

SELECT "2bc"=cast(2 as CHAR); Result: 0

After reading the above examples about comparing MySQL strings and numbers, many readers must have some understanding, if you need to get more industry knowledge information, you can continue to pay attention to our industry information column.