如何在CodeIgniter中实现高效的去重批量插入

public function import_excel(){
    if (!$_FILES["file"]["name"]) {
        echo "Please upload excel file !";
    } else {
        $path = $_FILES["file"]["tmp_name"];
        $object = PHPExcel_IOFactory::load($path);
        foreach ($object->getWorksheetIterator() as $worksheet) {
            $highestRow = $worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            for ($row = 2; $row <= $highestRow; $row++) {
                $username = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
                $email = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
                $address = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
                $contact_no = $worksheet->getCellByColumnAndRow(4, $row)->getValue();
                $data[] = array(
                    'username' => $username,
                    'email' => $email,
                    'address' => $address,
                    'contact_no' => $contact_no,
                );
            }
        }
        // 这里的 insert_batch 会直接插入，不检查重复
        $this->db->insert_batch('excel_files', $data);
    }
}

ALTER TABLE `excel_files` ADD UNIQUE INDEX `idx_unique_email` (`email`);

CREATE TABLE `excel_files` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(255),
    `email` VARCHAR(255) UNIQUE, -- 定义为唯一
    `address` VARCHAR(255),
    `contact_no` VARCHAR(20)
);

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Import_controller extends CI_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->database(); // 确保数据库已加载
        $this->load->helper('url'); // 如果需要URL辅助函数
        // 确保你的PHPExcel库已正确集成，例如通过Composer或手动加载
        // require_once APPPATH . 'third_party/PHPExcel/Classes/PHPExcel/IOFactory.php';
    }

    public function import_excel_with_deduplication() {
        if (!$_FILES["file"]["name"]) {
            echo "请上传Excel文件！";
            return;
        }

        $path = $_FILES["file"]["tmp_name"];
        try {
            $object = PHPExcel_IOFactory::load($path);
        } catch (Exception $e) {
            echo "加载Excel文件失败: " . $e->getMessage();
            return;
        }

        $data_to_insert = [];
        foreach ($object->getWorksheetIterator() as $worksheet) {
            $highestRow = $worksheet->getHighestRow();
            // 假设第一行是标题，从第二行开始读取数据
            for ($row = 2; $row <= $highestRow; $row++) {
                $username = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
                $email = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
                $address = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
                $contact_no = $worksheet->getCellByColumnAndRow(4, $row)->getValue();

                // 简单的非空检查，根据实际需求调整
                if (!empty($email)) {
                    $data_to_insert[] = array(
                        'username' => $username,
                        'email' => $email,
                        'address' => $address,
                        'contact_no' => $contact_no,
                    );
                }
            }
        }

        if (empty($data_to_insert)) {
            echo "Excel文件中没有可导入的数据。";
            return;
        }

        // 1. 准备批量插入数据
        $this->db->set_insert_batch($data_to_insert);

        // 2. 获取编译后的 INSERT BATCH SQL
        $sql = $this->db->get_compiled_insert('excel_files');

        // 3. 拼接 ON DUPLICATE KEY UPDATE 子句
        // 假设 'email' 是唯一键。当email重复时，我们更新username, address, contact_no。
        // 注意：这里需要根据实际需求决定更新哪些字段。
        // 如果不想更新任何字段，可以使用 `id` = `id` 的技巧。
        $sql .= " ON DUPLICATE KEY UPDATE "
              . "username = VALUES(username), "
              . "address = VALUES(address), "
              . "contact_no = VALUES(contact_no)";
              // VALUES() 函数用于引用当前 INSERT 语句中为该列指定的值。

        // 4. 执行完整的SQL语句
        if ($this->db->query($sql)) {
            echo "数据导入成功，重复条目已处理。";
        } else {
            echo "数据导入失败: " . $this->db->error()['message'];
        }
    }
}