java - 如何解决where name like '%%' 当name 为null时，查询不到数据?

Answer 1

SELECT

*

FROM

test

WHERE

1 = 1

AND NAME LIKE concat(

'%',
(
    CASE
    WHEN NAME（传进来的name） IS NULL THEN
        '#################'（数据库name不可能的值）
    ELSE
        NAME（传进来的name）
    END
),
'%')

I wrote a troubleshooting for name, you can check the other one yourself, it may not be the best solution

Answer 2

You can write it in business logic, not necessarily in sql

Answer 3

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

Answer 4

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?

Answer 5

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.

Answer 6

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.

Answer 7

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

Answer 8

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:

1. 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

2. xxxxxx are other where conditions. If there are really no other conditions, then the "where" string itself needs to be processed*

Answer 9

If the database default value is null, or when the obtained value is null, change it to nu. .

Answer 10

where name='' or method ='' or name like'%%' and method like '%%'