使用 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.
相关文章