MySQL dynamic multi-condition query

Dynamic multi-condition query is a kind of frequently encountered problem.

In MySQL, a simple sentence can be solved.

SELECT * FROM product where price = if (' {0} ' = ', price, ' {0} ') and the name like IF (' {1} ' = ' ', name, '%{1}% ') Here's price and name For each of the two field names in the product table, the {} flag bit is where we want to replace the parameters, so that the query page simply calls the SQL template statement and replaces the corresponding parameters. (assuming that the user does not filter the price, then {0} is empty, then the resulting SQL statement is similar to the following: SELECT * from product WHERE price = Price and the name like ' P ', where value = Price does not filter, which The purpose of the dynamic generation of multi-conditional query statements, the page will not need to do a tedious combination of SQL statements work.

The example code is given below:

DELIMITER $$CREATEDefiner=' Root ' @ ' localhost 'PROCEDURE' pc_select_tb_position_01 ' (inch' In_cardid 'int,inch' in_bs_id 'int,inch' In_posi_date1 'int,inch' In_posi_date2 'int)BEGINSELECT' tb_position '. ' Cardid ', ' tb_position ', ' bs_id ', ' tb_position '. ' Distance ', ' tb_position '. ' Posi_date ', ' tb_ ' Position '. ' Msecond ' from' db_pps_test1 '. ' Tb_position 'where' Tb_position '. ' Cardid '= if(' In_cardid ' is NULL, ' tb_position '. ' Cardid ', ' In_cardid ') and' Tb_position '. ' bs_id '= if(' in_bs_id ' is NULL, ' tb_position '. ' bs_id ', ' in_bs_id ') and' Tb_position '. ' Posi_date '>= if(' In_posi_date1 ' is NULL, ' tb_position '. ' Posi_date ', ' in_posi_date1 ') and' Tb_position '. ' Posi_date '<= if(' In_posi_date2 ' is NULL, ' tb_position '. ' Posi_date ', ' in_posi_date2 ');END$ $DELIMITER;

What I'm setting here is that the input value is a null value and ignores this field.

So this is the time to call:

Call ' Db_pps_test1 '. ' Pc_select_tb_position_01 ' (1,null, 2,6);

Or

Call ' Db_pps_test1 '. ' Pc_select_tb_position_01 ' (1,null, 2,null);

Or

Call ' Db_pps_test1 '. ' pc_select_tb_position_01 ' (null,3,null,6);

Wait a minute

But then there is a problem. is when I have four parameters that are null. The data for the full-scale search is executed. So avoid this situation at the application level.

MySQL dynamic multi-condition query