如果我希望使用PDO接受INT和NULL值，如何绑定值? [英] How to bind a value if I want it to accept both INT and NULL with PDO?

问题描述

$stmt = $dbh->prepare( 'SELECT id, name FROM folders WHERE parent_folder_id = :id' );
$stmt->bindValue( ':id', $folder_id, PDO::PARAM_INT );

我有上面的代码.如果一个文件夹有一个parent_folder_id，则意味着它在另一个文件夹中.如果此列为NULL，则表示它是一个根文件夹.

I have the code above. If a folder has a parent_folder_id then it means it is inside another folder. If this column is NULL then it means that it is a root folder.

据我了解，如果$ folder_id为NULL，它将被视为0(所以我没有得到任何结果，因为该列中的值为NULL而不是0).如果我将第三个参数更改为PDO :: PARAM_NULL，仍然没有任何结果.我认为这是因为它将查询评估为"WHERE parent_folder_id = NULL"，这与"WHERE parent_folder_id为NULL"不相同.

From my understanding if $folder_id is NULL then it will treat it as 0 (so I get no results with the code as it is because the value in that column is NULL and not 0). If I change the third argument to PDO::PARAM_NULL I still get no results. I believe this is because it evaluates the query as "WHERE parent_folder_id = NULL" which doesn't equal the same as "WHERE parent_folder_id is NULL".

是否有办法让PDO正确处理此问题，或者是否应该使用内联创建我的SQL语句(如果将"="替换为"is"并用正确的值交换bindValue第三个参数)?

Is there a way to have PDO treat this correctly or should I create my SQL statement with an inline if to change the "=" with "is" and swap the bindValue third parameter with the correct one?

推荐答案

如果您使用的是MySQL，则可以使用非标准的

If you're using MySQL, you can use the nonstandard NULL-safe equal operator <=>, which can compare either null or non-null values.

$stmt = $dbh->prepare( 'SELECT id, name FROM folders WHERE parent_folder_id <=> :id' );
$stmt->bindValue( ':id', $folder_id, PDO::PARAM_INT );

此运算符始终返回true或false，而不是NULL，这是您尝试将等于=的任何内容与NULL进行比较时得到的结果.

This operator always returns true or false, not NULL, which is what you get if you try to compare anything to NULL with equals =.

ANSI SQL定义了谓词IS [NOT] DISTINCT FROM，它的工作原理类似，但是还不是所有供应商都支持的谓词.

ANSI SQL defines a predicate IS [NOT] DISTINCT FROM which works similarly, but it's not supported by all vendors (yet).

这篇关于如果我希望使用PDO接受INT和NULL值，如何绑定值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！