mysqli准备的语句-不更新NULL值 [英] mysqli prepared statement - do not update NULL values

问题描述

我有准备好的声明来更新几个字段.我从公式器获取数据，但并非所有字段都是必需的.因此，可能没有设置某些字段.我将它们默认设置为NULL.现在，我不想用NULL覆盖旧值.如果值是NULL，如何告诉MySql不要更新值?

I have a prepared statement to update several fields. I get the data from a formular, but not all fields are required. So it's possible that some fields are not set. I set them default to NULL. Now I don't want to overwrite the old value by NULL. How can I tell MySql not to Update the value if it's NULL?

$insert_stmt = $mysqli->prepare("
UPDATE members SET username=?, email=?,  $password=?, $random_salt=?, level=?, customerID=?, name=?, surname=?, phone=?, quantities=? WHERE id=?
");
$insert_stmt->bind_param('ssssissss', $username, $email, $password, $random_salt, $level, $customerID, $firstname, $surname, $phone);
$insert_stmt->execute();

在我的情况下，密码和random_salt值可能为NULL.仅用NULL覆盖密码将非常糟糕；)

In my case it's the password and random_salt value that could be NULL. It will be very bad to overwrite the password just by NULL ;)

推荐答案

您可以按以下方式更改查询:

You could change your query as follows:

UPDATE members SET
    username = IFNULL(?, username),
    email = IFNULL(?, email) -- and so on for all fields
WHERE...

首先检查参数的值并动态构建查询(包括仅包含您要更新非空值的字段)可能会更有效.

It could also be more efficient to check the value of your parameters first, and build the query dynamically, including only fields for which you have a non-null value to update with.

这篇关于mysqli准备的语句-不更新NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！