当前位置：文江博客话题详情

在 mysqli 准备好的语句中使用 null

发布于 2024-07-11 03:43:36 字数 89 浏览 5 评论 0原文

在 mysqli 准备好的语句中，NULL 被转换为 '' （对于字符串）或 0 （对于整数）。我想将它存储为真正的 NULL。有什么办法可以做到这一点吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

迷乱花海 2024-07-18 03:43:36

对于任何因为在 WHERE 语句中绑定 NULL 时遇到问题而查看此内容的人，解决方案是这样的：

有一个 mysql NULL 安全运算符：

<=>

示例：

<?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);
?>

For anyone coming looking at this because they are having problems binding NULL in their WHERE statement, the solution is this:

There is a mysql NULL safe operator that must be used:

<=>

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);
?>

回复收藏 0 原文

浸婚纱 2024-07-18 03:43:36

可以将 true NULL 值绑定到准备好的语句（阅读这个）。

事实上，您可以使用 mysqli_bind_parameter 将 NULL 值传递给数据库。只需创建一个变量并将 NULL 值（请参阅其联机帮助页）存储到该变量并绑定它。无论如何，对我来说效果很好。

因此它必须类似于：

<?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);
?>

这应该将 NULL 值插入数据库。

It's possible to bind a true NULL value to the prepared statements (read this).

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.

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.

回复收藏 0 原文

︶葆Ⅱㄣ 2024-07-18 03:43:36

对关于 mysqli_stmt::bind_param 的 PHP 文档的注释表明传入 NULL 并不容易。

请参阅@creatio的回答：https://stackoverflow.com/a/6892491/18771

评论中提供的解决方案做了一些对准备好的语句进行预准备工作，将每个具有 PHP null 值的参数的 "?" 标记替换为 "NULL"。然后使用修改后的查询字符串。

以下函数来自用户评论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;
}

（这并不是我真正想要的编码风格，但如果它有效的话......）

The comments to the PHP documentation on mysqli_stmt::bind_param indicate that passing in NULL was not easily possible.

Please see @creatio's answer: 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 null value. The modified query string is then used.

The following function is from user comment 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...)

回复收藏 0 原文

老旧海报 2024-07-18 03:43:36

我将所有参数存储在一个数组中，并使用 array_shift($myArray) 将它们传递到 bind_param 函数中。 NULL 就这样被接受。

回复收藏 0 原文

北方的巷 2024-07-18 03:43:36

<?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;
}
?>

<?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;
}
?>

回复收藏 0 原文

~没有更多了~