MySQL是否有SQL模式允许"WHERE x = NULL"? (意思是"WHERE x IS NULL")? [英] Is there a SQL mode for MySQL allowing "WHERE x = NULL" (meaning "WHERE x IS NULL")?

问题描述

我知道在SQL语句的WHERE子句中使用column = NULL会导致结果集为空.

I know that using column = NULL in an SQL statement´s WHERE clause results in an empty result set.

由于这在编程环境中效率很低，我正在寻找一种让MySQL像column IS NULL那样解释column = NULL的方法，也许是通过设置SQL模式来实现的?

As this is very inefficient in programming environments, I´m searching for a way to let MySQL interpret column = NULL like column IS NULL, maybe by setting an SQL mode?

推荐答案

好吧，至少PostgreSQL确实为此提供了一个参数

Well, at least PostgreSQL does have a parameter for that

transform_null_equals = true

设置为 true 时，查询解析器将进行转换

When is set to true, the query parser will transform

field = NULL

到

field is NULL

事先.某事告诉我，在MySQL中，有一个等效参数隐藏在文档较少的参数中.

beforehand. Something tells me there's an equivalent parameter hidden in MySQL less documented params.

截至2020-07-15的修改

我的不是真正的解决方案"可能是我在SO上的第一个答案之一.我对回答另一个DBRMS引擎而不是OP的要求感到有些ham愧.

My "not really a solution" might be one of my first answers at SO. I'm somewhat ashamed for answering something about another DBRMS engine instead of what I was asked by the OP.

无论如何，正确的答案将是使用<=>运算符，就像其他人已经在其答案中所说的那样.替换为=，它将表现为其他比较:

Anyway, the correct answer would be using the <=> operator as others have said in their answers already. You replace = for that and it will behave as other comparisons:

 A = B  // zero if different, one if equals, this is treated as a boolean result
 A != NULL // always null, therefore not false nor true.  
 A <=> B // zero if different, one if equals, zero is one of the variables are null, 1 if both are. 

这不是设置，因此它的行为永远不会是会话方式或DDBB方式.

This is not a setting and therefore it's behavior would never be session-wise or DDBB wise.

这篇关于MySQL是否有SQL模式允许"WHERE x = NULL"? (意思是"WHERE x IS NULL")?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！