如何在mysql中选择NULL的值列表中的所有行'IN'？

(IMPORTANT: I don't know the list of values, the IN values is a parameter.. )

(重要提示:我不知道值列表,IN值是一个参数..)

I want to select all the rows in a table using IN, that values are of type varchar but maybe had null values, how can I query all rows including the null value?

我想使用IN选择表中的所有行,这些值是varchar类型但可能有空值,如何查询所有行,包括空值?

Example:

SELECT * FROM people WHERE name IN('Paul', 'Daniel', 'Yakov', null)

This query will return Paul or Daniel or Yakov, but never returns the rows with the name null.

此查询将返回Paul或Daniel或Yakov,但永远不会返回名称为null的行。

A solution maybe make an union:

一个解决方案可能是一个联盟:

SELECT * FROM people WHERE name IN('Paul', 'Daniel', 'Yakov', null)
UNION ALL
SELECT * FROM people WHERE name IN(??????)

With ?????? I'm meaning WHAT I NEED TO PUT THERE TO SELECT THE NULLS?

用??????我的意思是我需要选择NULLS?

Can you help?, thanks.

你能帮忙吗?谢谢。

IMPORTANT: How can I check the null value is present in the IN list?

重要提示:如何检查IN列表中是否存在空值?

2 个解决方案

#1

This query will return Paul or Daniel or Yakov or the rows with the name null. Something = or IN null will always return null. So you have to use IS NULL

此查询将返回Paul或Daniel或Yakov或名称为null的行。 Something =或IN null将始终返回null。所以你必须使用IS NULL

SELECT * 
FROM people 
WHERE name IN('Paul', 'Daniel', 'Yakov') 
OR name IS NULL

For parameter send null differently.

对于参数send null不同。

DECLARE @params AS VARCHAR(20) = '''Paul', 'Daniel', 'Yakov'''
DECLARE @willNULLShow AS BIT = 1

DECLARE @Query AS VARCHAR(400)

SET @Query = 'SELECT * FROM people WHERE name IN '+ @params
IF @willNULLShow = 1
BEGIN
    SET @Query = @Query + ' OR name IS NULL'
END

#2

I found the solution:

我找到了解决方案:

Remember that in the documentation of CONCAT in MySQL says: CONCAT() returns NULL if any argument is NULL.

请记住,在MySQL的CONCAT文档中说:如果任何参数为NULL,CONCAT()将返回NULL。

SELECT * FROM  people 
    WHERE (IF (CONCAT( 'Paul', 'Daniel', 'Yakov', null  ) IS NULL, 
         name IS NULL, name IN( 'Paul', 'Daniel', 'Yakov', null ) )) 
        OR name IN('Paul', 'Daniel', 'Yakov', null)

IF name IS NULL:

IF名称为空:

SELECT * FROM  people 
    WHERE (IF (CONCAT( null  ) IS NULL, 
         name IS NULL, name IN( null ) )) 
        OR name IN(null)

IF name is 'Paul':

如果名称是'保罗':

SELECT * FROM  people 
    WHERE (IF (CONCAT( 'Paul'  ) IS NULL, 
         name IS NULL, name IN( 'Paul' ) )) 
        OR name IN('Paul')

What do you think?

你怎么看?

What I really want is to use this query in PHP using PDO like:

我真正想要的是使用PDO在PHP中使用此查询,如:

$query = "SELECT * FROM  people 
        WHERE (IF (CONCAT( :name  ) IS NULL, 
             name IS NULL, name IN( :name ) )) 
            OR name IN(:name)";

$stmt->bindParam( "name", "Paul" );

//or

$stmt->bindParam( "name", null );

#1