問題描述
我一直在將應用程序轉換為使用 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 選擇字段為空的行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！