Summary of MySQL database like statement wildcard fuzzy query

MySQL error: Parameter index out of range (1 > number of parameters, which is 0)——Summary of MySQL database like statement wildcard fuzzy query

Introduction Today, when using MySQL statements to perform add, delete, modify and query operations, the console reported the following error: Parameter index out of range (1 > number of parameters, which is 0). Translated, it means: the result data is 1, the true value should be 0, the parameter is out of bounds, and an error occurs. This also makes it clear where we made a mistake - the parameter, that is, the handling of the wildcard "?". This article analyzes the errors encountered when executing common SQL statements and using the like statement for wildcard fuzzy queries, and provides solutions.

1. Analyze SQL statements

1. Query analysis of common SQL statements

First, let's analyze the SQL statement I used before:

String sql = "select count(*) from tab_route where cid = ?";

Note : We directly use the wildcard "?" to replace the parameter and use the Spring framework's JdbcTemplate to perform add, delete, modify and query operations. There is no problem here.

2. How to process common SQL query statements

If an error occurs here, please resolve it as follows:

Check the format of the wildcard "?" . Note that it is entered in the English input method, not the Chinese question mark. Check the SQL statement. The parameters passed using the wildcard are not quoted. For example, the following is wrong:

String sql = "select count(*) from tab_route where cid = '?'";

3. Use the like wildcard fuzzy query statement analysis

Similarly, we still use the above method to perform fuzzy query operations and take a look at the SQL statement that fails:

String sql = "select count(*) from tab_route where rname like '%?%';

The execution result is an error : Parameter index out of range (1 ＞ number of parameters, which is 0) .

Result analysis : The result data is 1, the true value should be 0, the parameter is out of bounds, and an error occurs.

We make it clear that there is no problem with the following statement:

String sql = select * from tab_route where rname like '%张%';

This also makes it clear where we made a mistake - the parameter, that is, the handling of the wildcard "?" in the like statement .

2. Analysis of fuzzy query using wildcards in like statements

1. Application scenarios of like statements

Using the like wildcard to perform fuzzy queries is something we often encounter in projects, such as fuzzy queries for data in the search box.

2. Fuzzy query analysis

It is not possible to parse '%?%' directly in the SQL statement, so we need to process the connected string, use the dynamic concatenation concat() method to connect the contents of '%?%' , and then perform the add, delete, modify and query operations.

concat(str1, str2, str3...) will generate a new string

3. Correct sentences

String sql = "select count(*) from tab_route where rname like concat('%',?,'%')";

3. MyBatis like fuzzy query and keyword distinction

When using like fuzzy query in MyBatis, please note that the keyword is: #{str} and the query clause is:

select * from table where name like concat('%',#{name},'%');

If you add a sort field, please note that the keyword is: ${str} and the query clause is:

select * from table where name like concat('%',#{name},'%') order by ${id};

Summary This article analyzes the errors encountered when executing ordinary SQL statements and using like statements for wildcard fuzzy queries, and gives corresponding solutions. At the same time, the operation of fuzzy query in MyBatis and the distinction between different keywords are supplemented. The road to coding is long, so I hope you will be careful.

This is the end of this article about the summary of MySQL database like statement wildcard fuzzy query. For more relevant MySQL fuzzy query like content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!