PHP/MySQL/PDO绑定null参数不起作用 [英] PHP/MySQL/PDO binding null parameter doesn't work

问题描述

在以下代码中绑定空参数时遇到问题

I am having trouble binding a null parameter in the following code

$nullVariable = NULL;
$sql = new PDO('mysql:host=' . $Server, $User, $Password);
$sql->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$statement = $sql->prepare("SELECT * FROM Table WHERE Binary16Column = :uuid");
$statement->bindParam(":uuid", $nullVariable, PDO::PARAM_NULL);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);

结果变量将是一个空数组.如果我不使用参数，而是将查询修改为"WHERE Binary16Column IS NULL"，它将返回预期的行数.因此问题必须出在我如何处理参数而不是SQL查询上.

The results variable will be a empty array. If I dont use parameters and modify my query to "WHERE Binary16Column IS NULL" it returns the expected number of rows. So the problem must be with how I am handling the parameter, rather than my SQL query.

我的代码比上面列出的还要复杂，我需要能够使用可能为null的参数变量，因此检查变量是否为null以及运行其他查询不是理想的选择.从技术上讲，我有自己的函数来设置参数，在这里我要检查变量的内容是否为null，并适当地绑定参数，因此我不必编写不必要的查询.如果变量包含有效数据，并且参数类型为PARAM_LOB，则查询也可以正常工作.

My code is more complex than listed above, and I need to be able to use a parameter variable which may be null, so checking to see the variable is null and running a different query is less than ideal. Technically I have my own function for setting parameters, this is where I am checking if the contents of the variable is null, and binding the parameter appropriately, so I dont have to write an unnecessary number of queries. The query works also works fine if the variable contains valid data, and the parameter type is PARAM_LOB.

有人知道我在做什么错吗?非常感谢！

Does anyone know what i'm doing wrong? Thanks a lot!

推荐答案

在三个值逻辑. NULL不是值；它是缺少值的标记，因此NULL永远不能等于任何东西，包括自身.

Read up on three-valued logic. NULL is not a value; it is a marker for the absence of a value, and so NULL can never be equal to anything, including itself.

但是，有一个空安全比较运算符，也称为太空飞船运算符"，它确实将两个空值视为等效.

However, there is a null-safe comparison operator also known as the "spaceship operator," which does consider two nulls to be equivalent.

WHERE Binary16Column <=> :uuid

...应该按照您的预期进行.

... should do what you expected.

这篇关于PHP/MySQL/PDO绑定null参数不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！