SQL：只将新行/记录插入表中？

I'm parsing a json feed routinely and need to insert only the newest users from the feed and ignore existing users.

我正在定期解析json feed并且只需要从feed中插入最新用户并忽略现有用户。

I think what I need is ON DUPLICATE KEY UPDATE or INSERT IGNORE based on some searching but I'm not quite sure which is why I'm asking - so for example:

我认为我需要的是基于一些搜索的ON DUPLICATE KEY UPDATE或INSERT IGNORE但是我不太确定我问的是什么 - 例如：

users
1     John
2     Bob

Partial JSON:

部分JSON：

{ userid:1, name:'John' },
{ userid:2, name:'Bob' },
{ userid:3, name:'Jeff' }

From this feed I only want to insert Jeff. I could do a simple loop through all users and do a simple SELECT query and see if the user id is already in the table, if not I do an INSERT, however I suspect it won't be an efficient and practical method.

从这个饲料我只想插入杰夫。我可以通过所有用户做一个简单的循环，并做一个简单的SELECT查询，看看用户id是否已经在表中，如果不是我做INSERT，但我怀疑它不是一个有效和实用的方法。

By the way, I'm using Zend_Db for the database interaction if anyone would like to cater a specific answer :) I don't mind a generic strategic solution though.

顺便说一句，如果有人愿意提供具体的答案，我正在使用Zend_Db进行数据库交互:)我不介意通用的战略解决方案。

5 个解决方案

#1

The ON DUPLICATE KEY UPDATE alternative allows you to refer the update vs. insert decision to the database:

ON DUPLICATE KEY UPDATE选项允许您将更新与插入决策引用到数据库：

INSERT INTO table (userid, name) VALUES (2, 'Bobby');
  ON DUPLICATE KEY UPDATE name = 'Bobby';

would update the name field to 'Bobby', if an entry with userid 2 already exists.

如果已存在具有用户标识2的条目，则会将名称字段更新为“Bobby”。

You can use it as an alternative to the INSERT IGNORE if you supply a noneffective operation to the UPDATE:

如果向UPDATE提供无效操作，则可以将其用作INSERT IGNORE的替代方法：

INSERT INTO table (userid, name) VALUES (2, 'Bobby');
  ON DUPLICATE KEY UPDATE name = name;

This would do nothing if userid 2 already exists, thus avoiding the warning and swallowing of other errors you'd get when using INSERT IGNORE.

如果用户标识2已经存在，则无效，因此避免了在使用INSERT IGNORE时出现的其他错误的警告和吞咽。

Another alternative would be REPLACE:

另一种选择是REPLACE：

REPLACE INTO table (userid, name) VALUES (2, 'Bobby');

This would do a normal insert if the userid 2 does not exist yet. If it does exist, it will delete the old entry first and then insert a new one.

如果用户标识2尚不存在，这将执行正常插入。如果它确实存在，它将首先删除旧条目，然后插入一个新条目。

Be aware that both versions are MySQL specific extensions to SQL.

请注意，这两个版本都是SQL特定于MySQL的扩展。

#2

For Zend Framework, what I do is a try/catch of the insert statement and take further action based on the exception code:

对于Zend Framework，我所做的是insert语句的try / catch，并根据异常代码采取进一步的操作：

class Application_Model_DbTable_MyModel extends Zend_Db_Table_Abstract

    public function insert($aData, $bIgnore = false) 
    {

        try 
        {
            $id =  parent::insert($aData);
        } 
        catch(Zend_Db_Statement_Mysqli_Exception $e) 
        {
            // code 1062: Mysqli statement execute error : Duplicate entry
            if($bIgnore && $e->getCode() == 1062) 
            {
                // continue;
            } 
            else 
            {
                throw $e;
            }
        }
        return !empty($id) ? $id : false;
    }
}

#3

You need to define userid as a PRIMARY or UNIQUE key and use something like:

您需要将userid定义为PRIMARY或UNIQUE键，并使用以下内容：

INSERT IGNORE INTO table (userid, name) VALUES (2, 'Bob');

If the userid 2 already exists it will ignore and move on to the next insert.

如果用户标识2已经存在，它将忽略并继续下一个插入。

You can also use ON DUPLICATE KEY UPDATE on your table schema. One other alternative might be to use the REPLACE INTO syntax.

您还可以在表模式上使用ON DUPLICATE KEY UPDATE。另一种替代方法可能是使用REPLACE INTO语法。

REPLACE INTO table (userid, name) VALUES (2, 'Bob');

This will try to INSERT, if the record already exists it will DELETE it before INSERTing it again.

这将尝试INSERT，如果记录已经存在，它将在再次INSERT之前删除它。

#4

My solution :

我的解决方案

/**
 * Perform an insert with the IGNORE word
 *
 * @param $data array
 * @return number the number of rows affected
 */
public function insertIgnore(array $data)
{
    // Start of query
    $sql = sprintf("INSERT IGNORE INTO %s (", $this->getAdapter()->quoteIdentifier($this->info('name')));
    // Retrieve column identifiers
    $identifiers = array_keys($data);
    foreach ($identifiers as $key => $value) {
        // Quote identifier
        $identifiers[$key] = $this->getAdapter()->quoteIdentifier($value);
    }
    // Concat column identifiers
    $sql .= implode(', ', $identifiers);
    $sql .= ") VALUES (";
    foreach ($data as $key => $value) {
        // Quote values
        $data[$key] = $this->getAdapter()->quote($value);
    }
    // Concat values identifiers
    $sql .= implode(', ', $data);
    $sql .= ")";
    // Process the query
    return $this->getAdapter()->query($sql)->rowCount();
}

#5

In the loop, you can do a update first, if no row affected, means it's a new entry. Keep track of those 'failed updates' then do an insert of them as new entries.

在循环中，如果没有行受影响，您可以先进行更新，这意味着它是一个新条目。跟踪那些“失败的更新”，然后将它们作为新条目插入。

I'm not familiar with Zend_Db but I assume it can return whether a update affected how many row(s).

我不熟悉Zend_Db，但我认为它可以返回更新是否影响了多少行。

#1