I don’t know if a database framework is used. If so, there are usually corresponding solutions. For example, in mybatis, you can use iftest to determine whether the like statement needs to be executed.
Another method is to use the functions of the database itself, such as IF(expr1,expr2,expr3) or IF ELSE
where IFNULL(name, '') like'%xx%' and IFNULL(method, '') like '%xx%' 如果name为null将其转换为empty string那%%It will match.
But I think the best way is to use SQL的地方增加逻辑判断会比较好一些，改动量应该非常少，只需要几句代码就搞定了。题主可以看看@家常菜 的SQL增加一个where 1=1的条件，那ifconditions and it will be very easy to add conditions.
It’s best to write it in the business logic layer (this layer is what this layer is supposed to do). Don’t handle this kind of thing in the database layer. This is very unreasonable and inefficient.
The simple way is to convert the null value in the table into '' where ifnull(name,'') like'%%' and ifnull(method,'') like '%%' If you don’t consider efficiency, this is enough
The questioner wants to simply splice the query conditions on the interface into the Where condition of the SQL statement. This is indeed very convenient for program processing. However, for security reasons, it is best not to do this because there is a risk of SQL injection.
If it is an internal application, the environment is indeed very safe. Considering the performance, it is still recommended to process the code logic of splicing SQL. Without this parameter, do not spell the corresponding where clause. For example, if it is Java, it can be written as:
String whereClause = "where xxxxxx"
+("".equals(nameStr) ? "" : "name like '%"+nameStr+"%'")
+("".equals(methodStr) ? "" : "method like '%"+methodStr+"%'")
*Here are the assumptions:
nameStr and methodStr are the user input content obtained from the interface respectively. If the user does not input, the result will be an empty string instead of null
xxxxxx are other where conditions. If there are really no other conditions, then the "where" string itself needs to be processed*
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
SELECT
FROM
WHERE
AND NAME LIKE concat(
I wrote a troubleshooting for name, you can check the other one yourself, it may not be the best solution
You can write it in business logic, not necessarily in sql
I don’t know if a database framework is used. If so, there are usually corresponding solutions.
For example, in mybatis, you can use iftest to determine whether the like statement needs to be executed.
Another method is to use the functions of the database itself, such as IF(expr1,expr2,expr3) or IF ELSE
If there are no input parameters at the front end, why do we need to query the results of this field? Doesn't this violate business logic?
where IFNULL(name, '') like'%xx%' and IFNULL(method, '') like '%xx%'如果
name为null将其转换为empty string那%%It will match.But I think the best way is to use
SQL的地方增加逻辑判断会比较好一些，改动量应该非常少，只需要几句代码就搞定了。题主可以看看@家常菜 的SQL增加一个where 1=1的条件，那ifconditions and it will be very easy to add conditions.It’s best to write it in the business logic layer (this layer is what this layer is supposed to do). Don’t handle this kind of thing in the database layer. This is very unreasonable and inefficient.
The simple way is to convert the null value in the table into ''
where ifnull(name,'') like'%%' and ifnull(method,'') like '%%'If you don’t consider efficiency, this is enough
The questioner wants to simply splice the query conditions on the interface into the Where condition of the SQL statement. This is indeed very convenient for program processing. However, for security reasons, it is best not to do this because there is a risk of SQL injection.
If it is an internal application, the environment is indeed very safe. Considering the performance, it is still recommended to process the code logic of splicing SQL. Without this parameter, do not spell the corresponding where clause. For example, if it is Java, it can be written as:
*Here are the assumptions:
nameStr and methodStr are the user input content obtained from the interface respectively. If the user does not input, the result will be an empty string instead of null
xxxxxx are other where conditions. If there are really no other conditions, then the "where" string itself needs to be processed*
If the database default value is null, or when the obtained value is null, change it to nu. .
where name='' or method ='' or name like'%%' and method like '%%'