php 在 mysqli 准备好的语句中使用空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/371644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
using nulls in a mysqli prepared statement
提问by ceejayoz
In a mysqli prepared statement, a NULL gets turned into '' (in the case of a string) or 0 (in the case of an integer). I would like to store it as a true NULL. Is there any way of doing this?
在 mysqli 准备好的语句中，NULL 被转换为 ''（在字符串的情况下）或 0（在整数的情况下）。我想将它存储为真正的 NULL。有没有办法做到这一点？
回答by creatio
I know this is an old thread, but it's possible to bind a true NULL value to the prepared statements (read this).
我知道这是一个旧线程，但是可以将真正的 NULL 值绑定到准备好的语句（请阅读此内容）。
You can, in fact, use mysqli_bind_parameter to pass a NULL value to the database. simply create a variable and store the NULL value (see the manpage for it) to the variable and bind that. Works great for me anyway.
实际上，您可以使用 mysqli_bind_parameter 将 NULL 值传递给数据库。只需创建一个变量并将 NULL 值（参见手册页）存储到该变量并绑定它。反正对我来说效果很好。
Thus it'll have to be something like:
因此它必须是这样的：
<?php
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');
// person is some object you have defined earlier
$name = $person->name();
$age = $person->age();
$nickname = ($person->nickname() != '') ? $person->nickname() : NULL;
// prepare the statement
$stmt = $mysqli->prepare("INSERT INTO Name, Age, Nickname VALUES (?, ?, ?)");
$stmt->bind_param('sis', $name, $age, $nickname);
?>
This should insert a NULL value into the database.
这应该在数据库中插入一个 NULL 值。
回答by random_user_name
For anyone coming looking at this because they are having problems binding NULL in their WHEREstatement, the solution is this:
对于任何因为在WHERE语句中绑定 NULL 时遇到问题而查看此内容的人，解决方案是：
There is a mysql NULL safe operatorthat must be used:
有一个必须使用的 mysql NULL 安全运算符：
<=>
<=>
Example:
例子：
<?php
$price = NULL; // NOTE: no quotes - using php NULL
$stmt = $mysqli->prepare("SELECT id FROM product WHERE price <=> ?"); // Will select products where the price is null
$stmt->bind_param($price);
?>
回答by Tomalak
The comments to the PHP documentation on mysqli_stmt::bind_paramindicate that passing in NULLwas not easily possible.
对PHP 文档mysqli_stmt::bind_param的注释表明传入NULL并不容易。
Please see @creatio's answer: https://stackoverflow.com/a/6892491/18771
请参阅@creatio 的回答：https://stackoverflow.com/a/6892491/18771
Solutions offered in the comments do some pre-preparation work on the prepared statement, replacing the "?"markers with "NULL"for every param that has the PHP nullvalue. The modified query string is then used.
评论中提供的解决方案对准备好的语句做一些准备工作，用具有 PHP值的每个参数替换"?"标记。然后使用修改后的查询字符串。"NULL"null
The following function is from user comment 80119:
以下功能来自用户评论 80119：
function preparse_prepared($sQuery, &$saParams)
{
$nPos = 0;
$sRetval = $sQuery;
foreach ($saParams as $x_Key => $Param)
{
//if we find no more ?'s we're done then
if (($nPos = strpos($sQuery, '?', $nPos + 1)) === false)
{
break;
}
//this test must be done second, because we need to
//increment offsets of $nPos for each ?.
//we have no need to parse anything that isn't NULL.
if (!is_null($Param))
{
continue;
}
//null value, replace this ? with NULL.
$sRetval = substr_replace($sRetval, 'NULL', $nPos, 1);
//unset this element now
unset($saParams[$x_Key]);
}
return $sRetval;
}
(It's not really the coding style I would have done it in, but if it works...)
（这不是我本来会用的编码风格，但如果它有效......）
回答by Tomalak
by my side, i store every parameters in an array and pass them in Bind_param function by array_shift($myArray). NULL is accepted like that.. S.
在我身边，我将每个参数存储在一个数组中，并通过 array_shift($myArray) 在 Bind_param 函数中传递它们。NULL 是这样接受的.. S.
回答by Robert
<?php
$mysqli=new mysqli('localhost','root','','test');
$mysqli->query("CREATE TABLE test_NULL (id int(11))");
if($query=$mysqli->prepare("insert into test_NULL VALUES(?)")){
$query->bind_param('i',$null); //note that $null is undefined
$query->execute();
}else{
echo __LINE__.' '.$mysqli->error;
}
?>
