Codeigniter中使用PHPexcel导入Excel如何防止重复数据插入

前置准备 #

先确定业务中可以唯一标识单条数据的字段组合，比如本例中可选用merchant_id+login_id+play_id三个字段作为唯一判定依据，先给数据库表加联合唯一索引，作为底层防重复的兜底保障：

ALTER TABLE `excel_files` ADD UNIQUE INDEX `uniq_merchant_login_play` (`merchant_id`, `login_id`, `play_id`);

加索引后就算代码逻辑出现漏洞，数据库也会直接拒绝重复数据的插入，不会产生脏数据。 #

方案1：使用INSERT IGNORE/ON DUPLICATE KEY UPDATE（推荐，性能更高） #

直接基于数据库唯一索引，修改插入逻辑，重复数据自动跳过，或者按需更新已有数据，修改后的代码如下：

public function import_excel(){
    if (!$_FILES["file"]["name"]) {
        echo "Please upload excel file !";
        return;
    }
    $path = $_FILES["file"]["tmp_name"];
    $object = PHPExcel_IOFactory::load($path);
    $data = [];
    foreach ($object->getWorksheetIterator() as $worksheet) {
        $highestRow = $worksheet->getHighestRow();
        for ($row = 2; $row <= $highestRow; $row++) {
            $group = $worksheet->getCellByColumnAndRow(0, $row)->getValue();
            $merchant_id = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
            $login_id = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
            $play_id = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
            $mem_name = $worksheet->getCellByColumnAndRow(4, $row)->getValue();
            $data[] = [
                'group' => $group,
                'merchant_id' => $merchant_id,
                'login_id' => $login_id,
                'play_id' => $play_id,
                'mem_name' => $mem_name,
            ];
        }
    }
    // 方式1：重复数据直接跳过，适合不需要更新旧数据的场景，Codeigniter 3.1.11+ 支持on_duplicate方法
    $this->db->on_duplicate('uniq_merchant_login_play');
    $this->db->insert_batch('excel_files', $data);

    // 方式2：重复时更新指定字段，适合需要用新上传的Excel数据覆盖旧数据的场景，按需二选一
    // $updateFields = ['group', 'mem_name']; // 要更新的字段
    // $this->db->on_duplicate($updateFields);
    // $this->db->insert_batch('excel_files', $data);
}

如果你的Codeigniter版本较低不支持on_duplicate方法，也可以直接写原生SQL实现：

// 重复时跳过的写法
$sql = "INSERT IGNORE INTO excel_files (`group`, merchant_id, login_id, play_id, mem_name) VALUES ";
$valueArr = [];
foreach ($data as $item) {
    $valueArr[] = "('".$this->db->escape_str($item['group'])."', '".$this->db->escape_str($item['merchant_id'])."', '".$this->db->escape_str($item['login_id'])."', '".$this->db->escape_str($item['play_id'])."', '".$this->db->escape_str($item['mem_name'])."')";
}
$sql .= implode(',', $valueArr);
$this->db->query($sql);

方案2：先查询过滤再插入（适合需要统计重复数的场景） #

如果需要统计本次上传有多少条新数据、多少条重复数据，可先提取所有Excel数据的唯一键，查询数据库中已存在的记录，再过滤后插入：

public function import_excel(){
    if (!$_FILES["file"]["name"]) {
        echo "Please upload excel file !";
        return;
    }
    $path = $_FILES["file"]["tmp_name"];
    $object = PHPExcel_IOFactory::load($path);
    $data = [];
    $uniqueKeys = [];
    foreach ($object->getWorksheetIterator() as $worksheet) {
        $highestRow = $worksheet->getHighestRow();
        for ($row = 2; $row <= $highestRow; $row++) {
            $group = $worksheet->getCellByColumnAndRow(0, $row)->getValue();
            $merchant_id = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
            $login_id = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
            $play_id = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
            $mem_name = $worksheet->getCellByColumnAndRow(4, $row)->getValue();
            // 拼接唯一键标识
            $key = $merchant_id . '_' . $login_id . '_' . $play_id;
            // 先做Excel内部的去重，避免同一个Excel里就有重复数据
            if (!isset($uniqueKeys[$key])) {
                $uniqueKeys[$key] = true;
                $data[$key] = [
                    'group' => $group,
                    'merchant_id' => $merchant_id,
                    'login_id' => $login_id,
                    'play_id' => $play_id,
                    'mem_name' => $mem_name,
                ];
            }
        }
    }
    // 查询数据库中已存在的记录
    $existed = $this->db->select('merchant_id, login_id, play_id')
        ->where_in('merchant_id', array_column($data, 'merchant_id'))
        ->where_in('login_id', array_column($data, 'login_id'))
        ->where_in('play_id', array_column($data, 'play_id'))
        ->get('excel_files')
        ->result_array();
    // 过滤掉已存在的
    foreach ($existed as $item) {
        $key = $item['merchant_id'] . '_' . $item['login_id'] . '_' . $item['play_id'];
        if (isset($data[$key])) {
            unset($data[$key]);
        }
    }
    // 只剩下新增数据再批量插入
    if (!empty($data)) {
        $this->db->insert_batch('excel_files', array_values($data));
        echo '本次新增' . count($data) . '条数据';
    } else {
        echo '本次无新增数据';
    }
}

注意事项：

• 若导入数据量较大（超过1000行），建议分批处理，避免内存溢出
• PHPExcel已经停止维护，后续可考虑升级为PhpSpreadsheet获得更好的兼容性和性能

内容的提问来源于stack exchange，提问作者Makuri David