mysql CLAUSE 中的 NULL 值
如何处理 mysql where in 子句中的 NULL 值
我尝试像
SELECT * FROM mytable WHERE field IN(1,2,3,NULL)
它不起作用
一样：
SELECT * FROM mytable WHERE field IN(1,2,3) OR field IS NULL
我怎样才能让它在 WHERE IN ?有可能吗？
how to deal with NULL value in mysql where in CLAUSE
i try like
SELECT * FROM mytable WHERE field IN(1,2,3,NULL)
it not working
only work like :
SELECT * FROM mytable WHERE field IN(1,2,3) OR field IS NULL
how can i get it work in WHERE IN ? it is possible ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
有一个名为
COALESCE。它返回列表中的第一个非NULL值，如果没有非NULL值，则返回NULL。例如，如果您运行
SELECT COALESCE(NULL, NULL, -1);，您将得到-1，因为它是第一个非NULL价值。因此，这里的技巧是将表达式包装在
COALESCE中，并添加一个值作为最后一个参数，该参数也在IN函数中添加。仅当字段为 1,2 或 3，或者字段为
NULL时才会匹配。There is a MySQL function called
COALESCE. It returns the first non-NULLvalue in the list, orNULLif there are no non-NULLvalues.If you for example run
SELECT COALESCE(NULL, NULL, -1);you will get-1back because it's the first non-NULLvalue.So the trick here is to wrap your expression in
COALESCE, and add a value as the last parameter that you also add in yourINfunction.It will only match if field is 1,2 or 3, or if field is
NULL.也许MySQL参考手册中的信息有帮助:
Maybe this information from the MySQL Reference Manual helps:
据我了解，您希望提取具有 1、2、3 和 null 值的每条记录。
我认为不可能将 null 放入 IN 运算符中。它需要值，而 null 很好..不是一个值。因此，您确实必须将“或”与“空”进行“或”运算才能获得所需的结果。
As by my understanding you want to pull every record with 1,2,3 and null value.
I don't think its possible to put null in the IN operator. Its expects values and null is well.. not a value. So You really have to put the OR with the null to get the desired result.
在 IN 运算符中使用 UNION 作为子查询可以获取 tableIds 作为列表，并从中获取具有 NULL 值的结果。
例如：
SELECT * FROM mytable WHERE mytable.id IN( SELECT mytable.id FROM mytable where mytable.field IS NULL UNION SELECT mytable.id FROM mytable WHERE mytable.field IN(1,2,3) )Using UNION as a subquery in IN operator can get tableIds as a list and from that can get results with the NULL value.
eg:
SELECT * FROM mytable WHERE mytable.id IN( SELECT mytable.id FROM mytable where mytable.field IS NULL UNION SELECT mytable.id FROM mytable WHERE mytable.field IN(1,2,3) )以下声明应该有所帮助：
Following statement should help: