使用 PHP PDO 准备好的语句和 MySQL 选择字段为空的行

问题描述

我一直在将应用程序转换为使用 PDO 准备好的语句而不是 mysqli，但我遇到了一个奇怪的问题.我在数据库中有一些记录，预计字段将为空.不是 'null'(字符串)或 ''(空字符串)，而是 NULL.我动态地构建我的查询，所以过去当我在一个对象中遇到一个空变量时，我会像这样构建查询:

I've been converting an app to use PDO prepared statements rather than mysqli and I'm running into a strange problem. I have some records in the database where it's expected that a field will be null. Not 'null' (string), or '' (empty string), but NULL. I build my queries dynamically, so in the past when I came across a null variable in an object, I'd build the query like this:

WHERE fieldName is null;

当字段为空时会得到预期的结果.

And would get the expected results when the field was null.

现在使用 PDO，我的查询不会返回任何结果，也不会出现任何错误.它只是没有返回我期望的记录.当我回显构建的查询并直接在 MySQL 中运行它们时，我得到了预期的结果，但在应用程序中没有返回任何结果.

Now with PDO, my queries aren't returning any results and I'm not getting any errors. It just simply isn't returning the records I would expect. When I echo the built queries and run them directly in MySQL I get the expected results, but within the application there are no results returned.

我尝试过的一些事情包括构建如下所示的查询:

Some of the things I've tried include building queries that look like this:

WHERE fieldName is null;

或

WHERE fieldName <=> null;

我也试过标准的准备语句:

I have also tried the standard prepared statement of:

WHERE fieldName = :fieldName

然后绑定这些类型的语句:

and then binding with these kinds of statements:

$stmt->bindParam(":$field", $value);
$stmt->bindParam(":$field", $value, PDO::PARAM_NULL);
$stmt->bindParam(":$field", null, PDO::PARAM_NULL);
$stmt->bindValue(":$field", null, PDO::PARAM_NULL);
$stmt->bindValue(":$field", null, PDO::PARAM_INT);

对此的任何帮助将不胜感激.我的 PHP 版本是 5.3.10，MySQL 是 5.5.22.作为一个附带问题，我仍然不清楚 bindParam 和 bindValue 之间的区别，所以如果在你的答案中包含它是有意义的，我真的很感激你对这个主题的一些澄清......

Any help with this would be greatly appreciated. My PHP version is 5.3.10 and MySQL is 5.5.22. As a side question, I still am not clear on the difference between bindParam and bindValue, so if it makes sense to include in your answer I would really appreciate some clarification on the subject...

推荐答案

既然已经写了这个问题，mysql 引入了一个 spaceship operator 允许我们使用常规查询来匹配空值

Since this question has been written, mysql introduced a spaceship operator that allows us to use a regular query to match a null value

WHERE fieldName <=> :fieldName;

将匹配 null 或任何非空值.

will match both a null or any not null value.

所以只需立即编写您的查询并照常执行

So just write your query right away and execute it as usual

$stmt = $db->prepare('SELECT field FROM table WHERE fieldName <=> :fieldName;');
$stmt->execute(['fieldName' => null]);
$result = $stmt->fetchAll(); // whatever fetch method is suitable

对于动态构建的查询，一切都是一样的.

And with dynamically built queries it's all the same.

这篇关于使用 PHP PDO 准备好的语句和 MySQL 选择字段为空的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！