1. 导入数据时重复条目的挑战
在web应用开发中，从外部文件（如excel）导入大量数据到数据库是一个常见需求。然而，在多次导入同一文件或包含部分重复数据的文件时，如何避免在数据库中产生重复记录成为了一个关键问题。简单的insert_batch操作会不加区分地插入所有数据，导致数据冗余和不一致。
考虑以下场景：您有一个用于导入Excel数据的CodeIgniter控制器方法，它读取Excel文件并将数据批量插入到excel_files表中。
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);
}
}这段代码能够成功导入数据，但如果第二次导入包含相同email地址的记录，数据库中将出现重复条目。为了解决这个问题，我们需要一种机制来识别并处理重复数据。
2. 核心解决方案：SQL 的 ON DUPLICATE KEY UPDATE
MySQL提供了一个强大的SQL语句扩展：INSERT ... ON DUPLICATE KEY UPDATE ...。这个语句允许您在尝试插入数据时，如果遇到与现有唯一索引（包括主键）冲突的记录，则不执行插入操作，而是转而执行一个UPDATE操作。
2.1. 工作原理
当您执行一个INSERT语句，并且该语句尝试插入的行会导致某个UNIQUE索引（或PRIMARY KEY）的重复值时，ON DUPLICATE KEY UPDATE子句就会被激活。此时，它会根据指定的UPDATE逻辑来修改已存在的冲突行，而不是抛出错误或插入新行。
2.2. 前提条件：唯一索引
ON DUPLICATE KEY UPDATE的工作前提是目标表上必须存在一个或多个UNIQUE索引（或主键）。这些索引用于数据库识别“重复”的定义。例如，如果希望根据email字段判断是否重复，那么email字段必须被定义为UNIQUE。
示例：为 email 字段添加唯一索引
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)
);3. 在 CodeIgniter 中实现数据去重批量插入
CodeIgniter 3 的查询构建器本身没有直接提供insert_batch与ON DUPLICATE KEY UPDATE结合的方法（即没有内置的"upsert_batch"功能）。然而，我们可以通过一些技巧来生成并执行包含ON DUPLICATE KEY UPDATE子句的批量插入SQL语句。
3.1. CodeIgniter insert_batch 的局限性
$this->db->insert_batch('table_name', $data); 语句仅执行简单的批量插入。如果遇到唯一键冲突，它会根据数据库配置抛出错误（例如，MySQL会返回一个Duplicate entry错误），而不是更新现有记录。
3.2. 利用 get_compiled_insert 构建自定义 SQL
CodeIgniter 的查询构建器允许您在不实际执行查询的情况下，获取生成的SQL字符串。这正是我们需要的，我们可以先让CI生成基础的INSERT BATCH语句，然后手动在其末尾追加ON DUPLICATE KEY UPDATE子句。
步骤：
- 使用$this->db->set_insert_batch($data)准备批量插入数据。
- 调用$this->db->get_compiled_insert('table_name')获取编译后的INSERT BATCH SQL字符串。
- 手动拼接ON DUPLICATE KEY UPDATE子句到获取的SQL字符串末尾。
- 使用$this->db->query($sql)执行完整的SQL语句。
3.3. 示例代码
以下是修改后的import_excel方法，它将利用ON DUPLICATE KEY UPDATE实现去重批量插入：
<?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'];
}
}
}3.4. ON DUPLICATE KEY UPDATE 策略选择
在ON DUPLICATE KEY UPDATE子句中，您可以根据业务逻辑选择不同的更新策略：
-
更新所有相关字段： 如示例所示，当检测到重复时，更新除了唯一键之外的其他字段。
ON DUPLICATE KEY UPDATE username = VALUES(username), address = VALUES(address), contact_no = VALUES(contact_no) -
仅更新特定字段（例如，更新时间戳）： 如果您只想记录重复发生的时间，而不改变其他数据。
ON DUPLICATE KEY UPDATE last_updated_at = NOW() -
不更新任何字段（“ID Trick”）： 如果您只想跳过重复项而不进行任何更新，可以利用id = id的技巧。这会使数据库执行一个无意义的更新操作，从而避免插入新行，但不会修改现有数据。
ON DUPLICATE KEY UPDATE id = id这种方法特别适用于只希望“插入不存在的，忽略已存在的”场景。
4. 注意事项
4.1. 确保唯一索引存在
这是ON DUPLICATE KEY UPDATE正常工作的基石。在执行代码之前，请务必确认您的数据库表上已经为用于去重的字段（例如email）创建了UNIQUE索引或将其设为主键。否则，该语句将不会按预期工作，而是可能导致错误。
4.2. 数据验证
尽管ON DUPLICATE KEY UPDATE处理了数据库层面的重复，但在将数据传递给数据库之前，进行必要的服务器端数据验证仍然至关重要。这包括检查数据类型、格式、非空约束等，以确保数据的质量和安全性。
4.3. 性能考量
对于极大规模的数据导入（例如，数十万甚至数百万条记录），虽然ON DUPLICATE KEY UPDATE比逐条查询再插入/更新效率高，但仍可能面临性能瓶颈。在这种情况下，可以考虑更高级的ETL（抽取、转换、加载）策略，例如：
- 将导入数据先存入一个临时表。
- 使用SQL的INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE语句从临时表批量处理到目标表。
- 利用数据库的LOAD DATA INFILE命令（如果适用）。
总结
通过结合SQL的ON DUPLICATE KEY UPDATE语句和CodeIgniter的get_compiled_insert功能，我们能够有效地在批量数据导入时处理重复条目。这种方法既保证了数据的完整性，又提高了导入效率。关键在于理解ON DUPLICATE KEY UPDATE的机制，并确保数据库表上存在正确的唯一索引。根据实际业务需求，灵活选择ON DUPLICATE KEY UPDATE的更新策略，可以使您的数据导入流程更加健壮和智能。
