MySQL CodeIgniter-活动记录插入，如果新的或更新重复

CodeIgniter- active record insert if new or update on duplicate

提问by Parag Tyagi -morpheus-

Is it possible to do an active recordquery in CodeIgniter that will update an existing record if one already exists or insert if it doesnt, for the given key?

对于给定的键，是否可以在 CodeIgniter 中执行活动记录查询，该查询将更新现有记录（如果已经存在或插入如果不存在）？

I understand this could be done by first querying to find an existing record, but I'm seeking the most efficient approach.

我知道这可以通过首先查询找到现有记录来完成，但我正在寻找最有效的方法。

回答by Parag Tyagi -morpheus-

Basically what you are looking for might be this INSERT ... ON DUPLICATE KEY UPDATE- provided that you are using MySQL and your id is a unique key on the table.

基本上你正在寻找的可能是这个INSERT ... ON DUPLICATE KEY UPDATE- 前提是你使用的是 MySQL 并且你的 id 是表上的唯一键。

You'd have to manually construct the query and pass to the $this->db->query()function instead of any built in active record like helper functions of the DB Driver.

您必须手动构造查询并传递给$this->db->query()函数，而不是像 DB Driver 的辅助函数那样的任何内置活动记录。

Example:

例子：

$sql = 'INSERT INTO menu_sub (id, name, desc, misc)
        VALUES (?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE 
            name=VALUES(name), 
            desc=VALUES(desc), 
            misc=VALUES(misc)';

$query = $this->db->query($sql, array( $id, 
                                       $this->validation->name, 
                                       $this->validation->desc, 
                                       $this->validation->misc
                                      ));

回答by Marcos Sánchez Urquiola

If you use Codeigniter 3.0 or higher, you might want to consider using "$this->db->replace()". According to the user guide:

如果您使用 Codeigniter 3.0 或更高版本，您可能需要考虑使用“$this->db->replace()”。根据用户指南：

"This method executes a REPLACE statement, which is basically the SQL standard for (optional) DELETE + INSERT, using PRIMARY and UNIQUE keys as the determining factor. In our case, it will save you from the need to implement complex logics with different combinations of select(), update(), delete() and insert() calls."

“这个方法执行一个 REPLACE 语句，它基本上是（可选）DELETE + INSERT 的 SQL 标准，使用 PRIMARY 和 UNIQUE 键作为决定因素。在我们的例子中，它将使您免于实现具有不同组合的复杂逻辑的需要select()、update()、delete() 和 insert() 调用。”

In other words,

换句话说，

• If it doesn't exist, it inserts a new record.
• If it does exist, it updates it according to its primary or unique key.

• 如果它不存在，它会插入一个新记录。
• 如果确实存在，它会根据其主键或唯一键对其进行更新。

Just use it straight out of the box like:

开箱即用即可，例如：

$data = array(
        'title' => 'My title',
        'name'  => 'My Name',
        'date'  => 'My date'
);

$this->db->replace('table', $data);

No batteries required!!!

无需电池！！！

回答by Milaza

You could do it simpler:

你可以做得更简单：

$sql = $this->db->insert_string(table, $array) . ' ON DUPLICATE KEY UPDATE ' .
implode(', ', $array);
$this->db->query($sql);

回答by siddhesh

I doesn't know Codeigniter Active Record Class has this method or not check the codeigniter docsfor the methods containing in active record class

我不知道 Codeigniter Active Record Class 有这个方法或者不检查codeigniter docs中包含的方法包含在 active record 类中

But you can achive this throug extending core models of codigniter. By using this way you can use this method for all the models which extends this model class. Just place the MY_model.phpinto application/core/ and write the following code.

但是您可以通过扩展 codigniter 的核心模型来实现这一点。通过使用这种方式，您可以将此方法用于扩展此模型类的所有模型。只需将其MY_model.php放入 application/core/ 并编写以下代码。

Class MY_Model extends CI_Model
{
  public function insert_update($data)
  {
       // code for checking existing record.
       if(//existing record)
           fire the update query
       else
           fire the insert query

       return insert/update id;

  }
}

after creating the above file You have to change the All your models parent class to the new Extended model i.e. MY_Model

创建上述文件后，您必须将所有模型父类更改为新的扩展模型，即 MY_Model

class some_model extends MY_Model

NOTE: You have to select the primary key from results and put it into the where condition.

注意：您必须从结果中选择主键并将其放入 where 条件中。

It's very critical so what I do when I get the data from the controller I just check it have the ID or not if Id is present then I fired the update query if not then I fired The Insert Query.

这是非常关键的，所以当我从控制器获取数据时我会做什么，我只是检查它是否有 ID，如果 ID 存在，然后我触发更新查询，如果没有，我触发了插入查询。

BEST OF LUCK

祝你好运

回答by Vickel

I'm using this approach:

我正在使用这种方法：

1. configure your table mytablewith unique idand uniquekey for the column xyz, which you want to update

2. try to insert an array $data, using INSERT IGNORE INTOdo avoid duplicates

   $insert_query = $this->db->insert_string('bt_ical_list', $data);
   $insert_query = str_replace('INSERT INTO','INSERT IGNORE INTO',$insert_query);
   $this->db->query($insert_query); 
   

   this inserts a row, if value in column xyzdoesn't exist

3. use function insert_id()to return an idif row was inserted, update if no row was inserted.

   if(!$this->db->insert_id()){
       $query=$this->db    ->where('xyz', $data['xyz'])
                           ->update('my_table',$data);
   };  
   

1. 配置表mytable与unique id和unique的关键column xyz，要更新

2. 尝试插入一个数组$data，使用INSERT IGNORE INTOdo 避免重复

   $insert_query = $this->db->insert_string('bt_ical_list', $data);
   $insert_query = str_replace('INSERT INTO','INSERT IGNORE INTO',$insert_query);
   $this->db->query($insert_query); 
   

   这将插入一行，如果值column xyz不存在

3. 使用函数insert_id()返回一个idif 行被插入，如果没有行被插入则更新。

   if(!$this->db->insert_id()){
       $query=$this->db    ->where('xyz', $data['xyz'])
                           ->update('my_table',$data);
   };  
   

回答by jdialogc

Here is a method that I hope can accomplish the same

这是我希望可以完成相同的方法

   /**
    * Used to insert new row if a duplicate entry is encounter it performs an update
    * @param string $table table name as string
    * @param array $data associative array of data and columns
    * @return mixed 
    */
   private function updateOnExist($table, $data)
   {
        $columns    = array();
        $values     = array();
        $upd_values = array();
        foreach ($data as $key => $val) {
            $columns[]    = $this->db->escape_identifiers($key);
            $val = $this->db->escape($val);
            $values[]     = $val;
            $upd_values[] = $key.'='.$val;
        }
        $sql = "INSERT INTO ". $this->db->dbprefix($table) ."(".implode(",", $columns).")values(".implode(', ', $values).")ON DUPLICATE KEY UPDATE".implode(",", $upd_values);
        return $this->db->query($sql);
}

回答by WC2

Updated Milaza's answer - Simply done

更新了 Milaza 的答案 - 简单完成

    $updt_str = '';
    foreach ($array as $k => $v) {
        $updt_str = $updt_str.' '.$k.' = '.$v.',';
    }
    $updt_str = substr_replace($updt_str,";", -1);
    $this->db->query($this->db->insert_string('table_name', $array).' ON DUPLICATE KEY UPDATE '.$updt_str);