php 使用 Codeigniter 插入忽略

INSERT IGNORE using Codeigniter

提问by Nyxynyx

I am trying to insert a few rows into the MySQL table using Codeigniter and Active Records.

我正在尝试使用 Codeigniter 和 Active Records 在 MySQL 表中插入几行。

PHP Code

PHP代码

$data = array('......');  // some rows of data to insert
$this->db->insert_batch('my_table', $data);

However this may cause duplicate rows to be inserted into the table. To handle the insertion of duplicate data, I plan to use the INSERT IGNOREcommand to not insert the row if the row is a duplicate.

但是，这可能会导致将重复的行插入到表中。为了处理重复数据的INSERT IGNORE插入，如果行是重复的，我计划使用该命令不插入该行。

Problem:I cannot find the INSERT IGNOREequivalent in Active Records and do not want to edit the Active Record class. Are there any other alternatives?

问题：我INSERT IGNORE在 Active Records 中找不到等效项，并且不想编辑 Active Record 类。还有其他选择吗？

The following looks interesting, but if I do the following, wont the query be run twice?

以下看起来很有趣，但是如果我执行以下操作，查询不会运行两次吗？

$insert_query = $this->db->insert_batch('my_table', $data);  // QUERY RUNS ONCE
$insert_query = str_replace('INSERT INTO','INSERT IGNORE INTO',$insert_query);
$this->db->query($insert_query); // QUERY RUNS A SECOND TIME

采纳答案by user1117332

Using the technique of your second idea, you could generate a query by looping over the array and using:

使用您的第二个想法的技术，您可以通过遍历数组并使用以下方法生成查询：

$this->db->query($query_string);

回答by Rocket Hazmat

Don't use insert_batch, as it actually runs the query. You want insert_string

不要使用insert_batch，因为它实际上运行查询。你要insert_string

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

UPDATE: This doesn't work for batch queries, only one row at a time.

更新：这不适用于批量查询，一次只能查询一行。

回答by sotoz

As I also encountered a similar problem, I finally chose a little bit more "elegant" solution like the one below. A complete insert_batch query that is something that uses Rocket's answer AND transactions:

由于我也遇到了类似的问题，我最终选择了一个更“优雅”的解决方案，如下所示。一个完整的 insert_batch 查询，它使用了 Rocket 的回答 AND 交易：

$this->db->trans_start();
foreach ($items as $item) {
       $insert_query = $this->db->insert_string('table_name', $item);
       $insert_query = str_replace('INSERT INTO', 'INSERT IGNORE INTO', $insert_query);
       $this->db->query($insert_query);
    }
$this->db->trans_complete();

That will also wrap everything on a transaction resulting on a faster query like doing a insert_batch(). Well not as fast as insert_batch() but faster than a single query for each entry of course. I hope it will help someone.

这也将包装事务中的所有内容，从而导致更快的查询，例如执行 insert_batch()。不如 insert_batch() 快，但当然比每个条目的单个查询快。我希望它会帮助某人。

回答by Algy Taylor

For batch uploads you might need something more like:

对于批量上传，您可能需要更多类似的东西：

foreach ($data as $data_item) {
    $insert_query = $this->db->insert_string('my_table', $data_item);
    $insert_query = str_replace('INSERT INTO', 'INSERT IGNORE INTO', $insert_query);
    $this->db->query($insert_query);
}

UPDATE:Use dcostalis's version (in the comments below this one), it will scale much better :-)

更新：使用 dcostalis 的版​​本（在这个版本下面的评论中），它会更好地扩展:-)

回答by Jacques Marcotte

This is basically a modification of Rocket Hazmat's suggestion, which is great, but doesn't take into account the fact that str_replace operates on the entire string and might inadvertently affect the data.

这基本上是对 Rocket Hazmat 的建议的修改，这很好，但没有考虑到 str_replace 对整个字符串进行操作并可能无意中影响数据的事实。

$insert_query = $this->db->insert_string('my_table', $data);
$insert_query = preg_replace('/INSERT INTO/','INSERT IGNORE INTO',$insert_query,1);
$this->db->query($insert_query);

回答by Valentin Ghica

For this purpose i made this helper function:

为此，我制作了这个辅助函数：

function insert_batch_string($table='',$data=[],$ignore=false){
    $CI = &get_instance();
    $sql = '';

    if ($table && !empty($data)){
        $rows = [];

        foreach ($data as $row) {
            $insert_string = $CI->db->insert_string($table,$row);
            if(empty($rows) && $sql ==''){
                $sql = substr($insert_string,0,stripos($insert_string,'VALUES'));
            }
            $rows[] = trim(substr($insert_string,stripos($insert_string,'VALUES')+6));
        }

        $sql.=' VALUES '.implode(',',$rows);

        if ($ignore) $sql = str_ireplace('INSERT INTO', 'INSERT IGNORE INTO', $sql);
    }
    return $sql;
}

It can do batch insert and batch insert with ignore. To avoid duplicate rows you must set a unique key in the database table for a primary field.

它可以执行批量插入和批量插入忽略。为避免重复行，您必须在数据库表中为主字段设置唯一键。

回答by asimov

solution :

解决方案 ：

$sql = $this->db->set($data)->get_compiled_insert($table);
$sql = str_replace('INSERT INTO', 'INSERT IGNORE INTO', $sql);
$this->db->query($sql);

works in codeigniter 3.0.6 :)

适用于 codeigniter 3.0.6 :)

回答by Rahul

If still someone is struggling with insert_batch to use ignore,

如果仍然有人在为 insert_batch 使用 ignore 而苦苦挣扎，

Check this path,

检查这个路径，

system/database/DB_query_builder.php

Search for function

搜索功能

protected function _insert_batch

Change

改变

INSERT INTO => INSERT IGNORE INTO

Thank me later. :)

晚点再谢我。:)

回答by Gulzar Ali

Avoid duplicate data insertion in codeigniter is a tricky task. But you can ignore duplicate values insertion using customized library method in very simple and accurate way. I have found a suitable solution at: how-to-avoid-duplicate-data-insertion-in-codeigniter-php

避免在 codeigniter 中插入重复数据是一项棘手的任务。但是您可以以非常简单和准确的方式使用自定义库方法忽略重复值插入。我在以下位置找到了合适的解决方案： how-to-avoid-duplicate-data-insertion-in-codeigniter-php

回答by Sunny Khanuja

I too had same issue what helped me is :

我也有同样的问题对我有帮助的是：

Open : Codeigniter/system/database/DB_query_builder.php:

打开：Codeigniter/system/database/DB_query_builder.php：

find

找

protected function _insert_batch($table, $keys, $values)
    {
        return 'INSERT INTO '.$table.' ('.implode(', ', $keys).') VALUES '.implode(', ', $values);
    }

and replace with :

并替换为：

protected function _insert_batch($table, $keys, $values)
{
    return 'INSERT IGNORE INTO '.$table.' ('.implode(', ', $keys).') VALUES '.implode(', ', $values);
}