Mysql更新或插入空值

A quick question.

一个简单的问题。

I'm using php to insert new records in a database table - if the record exists, an update command is run instead.

我正在使用php在数据库表中插入新记录 - 如果记录存在,则运行更新命令。

My question is, if the incoming record has fields that are empty but the data already in the database is not empty, does the update command overwrite the existing data with the empty values?

我的问题是,如果传入记录的字段为空但数据库中已存在的数据不为空,则update命令是否会用空值覆盖现有数据?

Much appreciated.

4 个解决方案

#1

Choog, it depends on your update query. If your table has the fields named: some_id, foo, bar, baz, qux - and you have an UPDATE in your PHP script like such:

Choog,这取决于您的更新查询。如果你的表有以下字段:some_id,foo,bar,baz,qux - 你的PHP脚本中有一个UPDATE,如下所示:

"UPDATE table SET foo = '$foo', bar = '$bar', baz = '$baz', qux = '$qux' WHERE some_id = '$id'"

This will update (overwrite) all the fields you specified. If any of those variables are NULL, or empty strings, then yes you will overwrite the existing data with a NULL (if allowed) or an empty string.

这将更新(覆盖)您指定的所有字段。如果这些变量中的任何一个是NULL或空字符串,则是,您将使用NULL(如果允许)或空字符串覆盖现有数据。

If you only update the fields you need to, say foo and bar for example, then it will not change the values of baz and qux. e.g.

如果你只更新你需要的字段,例如foo和bar,那么它不会改变baz和qux的值。例如

"UPDATE table SET foo = '$foo', bar = '$bar' WHERE some_id = '$id'"

I don't know the specifics of what you're doing, but you may want to look into REPLACE INTO (http://dev.mysql.com/doc/refman/5.1/en/replace.html) and INSERT IGNORE (http://dev.mysql.com/doc/refman/5.1/en/insert.html) queries as well. They may be more suitable for what you're doing.

我不知道你在做什么的具体细节,但你可能想看看REPLACE INTO(http://dev.mysql.com/doc/refman/5.1/en/replace.html)和INSERT忽略( http://dev.mysql.com/doc/refman/5.1/en/insert.html)查询。它们可能更适合您正在做的事情。

#2

The update would overwrite if it is a simple MySQL update

如果它是简单的MySQL更新,则更新将覆盖

UPDATE table SET field = '$newValue' WHERE id = '$id'

It is better to validate the data first.

最好先验证数据。

#3

If you only want to update values that aren't empty you may want to do something like this

如果您只想更新非空的值,则可能需要执行类似的操作


$updates = array();
if ($var1 != '') $updates[] = sprintf("`var1` = '%s'", mysql_real_escape_string($var1));
if ($var2 != '') $updates[] = sprintf("`var2` = '%s'", mysql_real_escape_string($var2));
if (count($updates) > 0) {
     $query = sprintf("UPDATE table SET %s WHERE id = '%d' ", implode(", ", $updates), $id);
}

#4

You can use an INSERT ... ON DUPLICATE statement.
And CoALESCE to keep an old value if null has been passed as "new" value.

您可以使用INSERT ... ON DUPLICATE语句。如果null已作为“新”值传递,则CoALESCE保留旧值。

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', '...', '...');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// example table
$pdo->exec('CREATE TEMPORARY TABLE foo (
  id int auto_increment,  
  x int,  
  y int,  
  z int,
  primary key(id), 
  unique key(x) )
');

$stmt = $pdo->prepare("
    INSERT INTO
      foo (x,y,z)
    VALUES
      (:x,:y,:z)
    ON DUPLICATE KEY UPDATE
      y=COALESCE(:y, y),
      z=COALESCE(:z, z)
");
$stmt->bindParam(':x', $x);
$stmt->bindParam(':y', $y);
$stmt->bindParam(':z', $z);


$x = 1; $y = 1; $z=1;
$stmt->execute();
// duplicate key x=1
// overwriting y and z
$x = 1; $y = 2; $z=2;
$stmt->execute();


$x = 2; $y = 20; $z=17;
$stmt->execute();
// duplicate key x=2
// overwriting only z
$x = 2; $y = null; $z=21;
$stmt->execute();

unset($stmt);

foreach($pdo->query('SELECT * FROM foo', PDO::FETCH_NAMED) as $row) {
    foreach($row as $k=>$v) {
        echo $k, '=', $v, ' ';
    }
    echo "\n";
}

prints

id=1 x=1 y=2 z=2 
id=2 x=2 y=20 z=21

#1