MySQL 我将如何在我的 CodeIgniter 模型中使用 ON DUPLICATE KEY UPDATE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3361490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How would I use ON DUPLICATE KEY UPDATE in my CodeIgniter model?
提问by Hyman
I have a CodeIgniter/PHP Model and I want to insert some data into the database.
我有一个 CodeIgniter/PHP 模型，我想向数据库中插入一些数据。
However, I have this set in my 'raw' SQL query:
但是，我在“原始”SQL 查询中设置了此设置：
ON DUPLICATE KEY UPDATE duplicate=duplicate+1
I am using CodeIgniter and am converting all my previous in-controller SQL queries to ActiveRecord. Is there any way to do this from within the ActiveRecord-based model?
我正在使用 CodeIgniter 并将我以前的所有控制器内 SQL 查询转换为ActiveRecord。有没有办法从基于 ActiveRecord 的模型中做到这一点？
Thanks!
谢谢！
Hyman
Hyman
回答by Shaffe
You can add the "ON DUPLICATE" statement without modifying any core files.
您可以在不修改任何核心文件的情况下添加“ON DUPLICATE”语句。
$sql = $this->db->insert_string('table', $data) . ' ON DUPLICATE KEY UPDATE duplicate=LAST_INSERT_ID(duplicate)';
$this->db->query($sql);
$id = $this->db->insert_id();
I hope it's gonna help someone.
我希望它会帮助某人。
回答by Amalesh
The below process work for me in Codeigniter 3.0.6
以下过程在 Codeigniter 3.0.6 中对我有用
public function updateOnDuplicate($table, $data ) {
if (empty($table) || empty($data)) return false;
$duplicate_data = array();
foreach($data AS $key => $value) {
$duplicate_data[] = sprintf("%s='%s'", $key, $value);
}
$sql = sprintf("%s ON DUPLICATE KEY UPDATE %s", $this->db->insert_string($table, $data), implode(',', $duplicate_data));
$this->db->query($sql);
return $this->db->insert_id();
}
回答by Andrej Burcev
You can tweak the active record function with minimal addition:
您可以通过最少的添加来调整活动记录功能：
DB_driver.php add inside the class:
DB_driver.php 在类里面添加：
protected $OnlyReturnQuery = false;
public function onlyReturnQuery($return = true)
{
$this->OnlyReturnQuery = $return;
}
find function query( ...and add at the very beginning:
find 函数 query( ... 并在最开始添加：
if ($this->OnlyReturnQuery) {
$this->OnlyReturnQuery = false;
return $sql;
}
and finally in DB_active_rec.php add function:
最后在 DB_active_rec.php 添加函数：
public function insert_or_update($table='', $data=array())
{
$this->onlyReturnQuery();
$this->set($data);
$insert = $this->insert($table);
$this->onlyReturnQuery();
$this->set($data);
$update = $this->update($table);
$update = preg_replace('/UPDATE.*?SET/',' ON DUPLICATE KEY UPDATE',$update);
return $this->query($insert.$update);
}
Now you can use it as:
现在您可以将其用作：
$this->db->insert_or_update('table',array $data);
$this->db->insert_or_update('table',array $data);
Pros: uses all the active record validation Cons: it is not the best (the most proper) way of extending the function, because if you are planning to update these files, you will have to redo the procedure.
优点：使用所有活动记录验证 缺点：这不是扩展功能的最佳（最合适）方式，因为如果您计划更新这些文件，您将不得不重做该过程。
回答by Anmol Mourya
Below is a code snippet I use everytime for update on duplicates:
以下是我每次用于更新重复项的代码片段：
$sql = "insert into table_name (id, name) values(".$id.",'".$name."') on duplicate key update id=".$id.",name='".$name."'";
//Executing queries
$result = $this->db->query($sql, array($id, $name));
Note: column idmust be primary or unique
注意：列id必须是主要的或唯一的
回答by Calle
The link to the forum thread above is broken. I don't know of a better way than just using db->query for the call, if someone has a better solution, please post that.
上面论坛帖子的链接已损坏。我不知道比仅使用 db->query 进行调用更好的方法，如果有人有更好的解决方案，请发布。
$result = $this->CI->db->query(
"INSERT INTO tablename (id, name, duplicate) VALUES (1, 'Joe', 1) ".
"ON DUPLICATE KEY UPDATE duplicate=duplicate+1");
I hope this helps someone looking for a solution to this.
我希望这可以帮助寻找解决方案的人。
回答by Necrontyr
Following the snippet linked by Pickett, I made a few modifications to:
按照 Pickett 链接的代码段，我对以下内容进行了一些修改：
1) Update it to use the new Query Builder (CI 3), formerly known as Active Record.
1) 更新它以使用新的查询生成器 (CI 3)，以前称为 Active Record。
2) You can now pass an associative array (key => value) or an array of associative arrays. In the second form, it performs a multi-update.
2) 您现在可以传递关联数组（键 => 值）或关联数组数组。在第二种形式中，它执行多次更新。
I only tested it with mysqli, and it works well.
我只用 mysqli 测试过，效果很好。
This piece of code goes in system/database/drivers/mysqli/mysqli_driver.php
这段代码在system/database/drivers/mysqli/mysqli_driver.php
function _duplicate_insert($table, $values)
{
$updatestr = array();
$keystr = array();
$valstr = array();
foreach($values as $key => $val)
{
$updatestr[] = $key." = ".$val;
$keystr[] = $key;
$valstr[] = $val;
}
$sql = "INSERT INTO ".$table." (".implode(', ',$keystr).") ";
$sql .= "VALUES (".implode(', ',$valstr).") ";
$sql .= "ON DUPLICATE KEY UPDATE ".implode(', ',$updatestr);
return $sql;
}
function _multi_duplicate_insert($table, $values)
{
$updatestr = array();
$keystr = array();
$valstr = null;
$entries = array();
$temp = array_keys($values);
$first = $values[$temp[0]];
foreach($first as $key => $val)
{
$updatestr[] = $key." = VALUES(".$key.")";
$keystr[] = $key;
}
foreach($values as $entry)
{
$valstr = array();
foreach($entry as $key => $val)
{
$valstr[] = $val;
}
$entries[] = '('.implode(', ', $valstr).')';
}
$sql = "INSERT INTO ".$table." (".implode(', ',$keystr).") ";
$sql .= "VALUES ".implode(', ',$entries);
$sql .= "ON DUPLICATE KEY UPDATE ".implode(', ',$updatestr);
return $sql;
}
And this goes into the /system/database/DB_query_builder.php file:
这进入 /system/database/DB_query_builder.php 文件：
function on_duplicate($table = '', $set = NULL )
{
if ( ! is_null($set))
{
$this->set($set);
}
if (count($this->qb_set) == 0)
{
if ($this->db_debug)
{
return $this->display_error('db_must_use_set');
}
return FALSE;
}
if ($table == '')
{
if ( ! isset($this->qb_from[0]))
{
if ($this->db_debug)
{
return $this->display_error('db_must_set_table');
}
return FALSE;
}
$table = $this->qb_from[0];
}
$is_multi = false;
foreach (array_keys($set) as $k => $v) {
if ($k === $v) {
$is_multi = true; //is not assoc
break;
}
}
if($is_multi)
{
$sql = $this->_multi_duplicate_insert($this->protect_identifiers($table, TRUE, NULL, FALSE), $this->qb_set );
}
else
{
$sql = $this->_duplicate_insert($this->protect_identifiers($table, TRUE, NULL, FALSE), $this->qb_set );
}
$this->_reset_write();
return $this->query($sql);
}
Then you can do this for a single row insert/update:
然后您可以为单行插入/更新执行此操作：
$this->db->on_duplicate('table', array('column1' => 'value', 'column2' => 'value'));
Or this for a multi insert/update:
或者对于多插入/更新：
$this->db->on_duplicate('table', array(
array('column1' => 'value', 'column2' => 'value'),
array('column1' => 'value', 'column2' => 'value')
));
回答by Kinjal Dixit
Using $this->db->query()and parameters, this is how I do it. the first 4 parameters are for the insert part and the last three parameters are repeated for the on duplicate key updatepart.
使用$this->db->query()和参数，这就是我的做法。前 4 个参数用于插入零件，后三个参数用于on duplicate key update零件。
$sql = "insert into application_committee ".
"(application_id, member1_id, member2_id, member3_id) values (?, ?, ?, ?)".
" on duplicate key update member1_id = ?, member2_id = ?, member3_id = ?";
$this->db->query($sql, array($appid, $member_1, $member_2, $member_3,
$member_1, $member_2, $member_3);
回答by WC2
Simply done-
简单地完成-
$updt_str = '';
foreach ($insert_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', $insert_array).' ON DUPLICATE KEY UPDATE '.$updt_str);
