在 web 应用开发中，从外部文件（如 excel）批量导入数据是常见需求。然而，在多次导入同一文件或包含部分重复数据的文件时，如何避免数据库中出现重复记录，同时又能顺利导入新数据，是一个需要妥善解决的问题。codeigniter 框架的 insert_batch 方法虽然方便，但它默认不提供直接处理重复键的机制。本文将详细介绍如何通过结合原生 sql 的 on duplicate key update 语句与 codeigniter 的查询构建器功能，实现这一目标。
理解 ON DUPLICATE KEY UPDATE
ON DUPLICATE KEY UPDATE 是 MySQL 数据库提供的一种强大功能，它允许在插入数据时，如果遇到主键（PRIMARY KEY）或唯一索引（UNIQUE INDEX）冲突，则不执行插入操作，而是转为执行更新操作。这对于实现“插入或更新”（upsert）逻辑非常有用。
其基本语法如下：
INSERT INTO your_table (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
column1 = new_value1,
column2 = new_value2,
...;关键点：
- 唯一约束： ON DUPLICATE KEY UPDATE 的生效前提是表上必须存在 PRIMARY KEY 或 UNIQUE INDEX。当插入的数据在这些键上与现有记录发生冲突时，才会触发更新。
- 更新行为： 你可以指定当冲突发生时，哪些字段需要被更新。
- 无操作： 如果你希望在冲突时什么都不做（即跳过该条记录），可以简单地将更新语句设置为 id = id（假设 id 是主键），这样实际上没有改变任何数据，但避免了插入错误。
-- 示例：如果email重复，则更新username、address和contact_no
INSERT INTO excel_files (`username`, `email`, `address`, `contact_no`)
VALUES (:username, :email, :address, :contact_no)
ON DUPLICATE KEY UPDATE
`username` = VALUES(`username`), -- VALUES() 函数引用待插入的值
`address` = VALUES(`address`),
`contact_no` = VALUES(`contact_no`);
-- 示例：如果email重复，则什么都不做（跳过该条记录）
INSERT INTO excel_files (`username`, `email`, `address`, `contact_no`)
VALUES (:username, :email, :address, :contact_no)
ON DUPLICATE KEY UPDATE `email` = VALUES(`email`); -- 或者 `id` = `id`在上述示例中，VALUES(column_name) 函数用于引用当前尝试插入的对应列的值。
在 CodeIgniter 中实现批量“插入或更新”
由于 CodeIgniter 的 insert_batch 方法没有直接集成 ON DUPLICATE KEY UPDATE 功能，我们需要通过生成 SQL 语句并手动执行的方式来实现。这主要利用了 CodeIgniter 查询构建器的 get_compiled_insert() 方法。
以下是一个完整的 CodeIgniter 控制器方法示例，演示如何从 Excel 文件导入数据并处理重复项：
load->database(); // 加载数据库库
$this->load->library('phpexcel'); // 假设你已加载了PHPExcel库
}
/**
* 从Excel导入数据并处理重复项
*/
public function import_excel() {
if (empty($_FILES["file"]["name"])) {
echo "请选择要上传的 Excel 文件！";
return;
}
$path = $_FILES["file"]["tmp_name"];
try {
$objPHPExcel = PHPExcel_IOFactory::load($path);
} catch (Exception $e) {
echo "加载 Excel 文件失败: " . $e->getMessage();
return;
}
$data_to_insert = [];
foreach ($objPHPExcel->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();
// 确保email不为空，作为唯一标识
if (!empty($email)) {
$data_to_insert[] = [
'username' => $username,
'email' => $email,
'address' => $address,
'contact_no' => $contact_no,
];
}
}
}
if (empty($data_to_insert)) {
echo "Excel 文件中没有可导入的数据或数据格式不正确。";
return;
}
$table_name = 'excel_files'; // 你的目标表名
// 批量构建 SQL 语句
$sql_parts = [];
foreach ($data_to_insert as $row_data) {
// 使用 set() 方法设置单个插入的数据
$this->db->set($row_data);
// 获取编译后的 INSERT SQL 语句（不带 ON DUPLICATE KEY UPDATE）
$insert_sql = $this->db->get_compiled_insert($table_name, FALSE); // FALSE 表示不重置查询构建器
// 拼接 ON DUPLICATE KEY UPDATE 子句
// 假设 'email' 是表的 UNIQUE KEY
// 这里我们选择在重复时更新 username, address, contact_no
// 如果你希望重复时什么都不做，可以使用 `email` = VALUES(`email`)
$on_duplicate_update_clause = " ON DUPLICATE KEY UPDATE " .
"`username` = VALUES(`username`), " .
"`address` = VALUES(`address`), " .
"`contact_no` = VALUES(`contact_no`)";
$sql_parts[] = $insert_sql . $on_duplicate_update_clause;
}
// 将所有独立的 INSERT ... ON DUPLICATE KEY UPDATE 语句合并成一个大字符串，用分号分隔
// 注意：这种方式适用于 MySQL 的多语句执行，但通常建议逐条执行或构建一个更复杂的单条语句
// 对于大量数据，更高效的方式是构建一个 VALUES(...) 列表，然后一次性执行
// 鉴于 CodeIgniter 的限制，这里采用逐条构建SQL然后合并的方式，但实际执行时仍是逐条执行
$success_count = 0;
$failed_count = 0;
$this->db->trans_start(); // 开启事务
foreach ($sql_parts as $sql) {
if ($this->db->query($sql)) {
$success_count++;
} else {
$failed_count++;
// 记录失败的 SQL 或数据，以便调试
log_message('error', 'Failed to execute SQL: ' . $sql . ' Error: ' . $this->db->error()['message']);
}
}
$this->db->trans_complete(); // 完成事务
if ($this->db->trans_status() === FALSE) {
echo "数据导入过程中发生错误，事务已回滚。";
} else {
echo "数据导入完成。
";
echo "成功处理 " . $success_count . " 条记录。
";
echo "失败 " . $failed_count . " 条记录。";
}
}
}代码解析与注意事项：
- PHPExcel 库： 示例中假设你已经正确加载了 PHPExcel 库。如果使用 Composer 管理依赖，可以考虑使用 PhpSpreadsheet（PHPExcel 的继任者）。
- 数据准备： 从 Excel 读取数据并整理成数组格式，确保每个数组元素对应数据库表的一行数据。
-
get_compiled_insert()：
- $this->db->set($row_data); 用于设置要插入的单行数据。
- $this->db->get_compiled_insert($table_name, FALSE); 会生成一个标准的 INSERT INTO ... VALUES (...) 语句。第二个参数 FALSE 是关键，它指示 CodeIgniter 不要重置查询构建器，这使得你可以在循环中重复使用 set() 而不会干扰前一个查询的状态（尽管这里每次循环都用 set() 重新设置了）。
- 拼接 ON DUPLICATE KEY UPDATE： 将生成的 INSERT 语句与自定义的 ON DUPLICATE KEY UPDATE 子句拼接起来。请根据你的业务逻辑，精确定义在发生重复时需要更新哪些字段。VALUES(column_name) 函数在这里非常重要，它引用的是当前尝试插入的对应列的值。
- 事务处理： 对于批量数据库操作，强烈建议使用事务（$this->db->trans_start() 和 $this->db->trans_complete()）。这可以确保要么所有操作都成功提交，要么在任何一个操作失败时全部回滚，维护数据的一致性。
- 错误处理与日志： 示例中包含了基本的错误输出和日志记录，这在实际应用中对于调试和监控非常重要。
-
性能考量： 尽管上述方法有效，但它本质上是为每一行数据生成并执行一条独立的 SQL 语句。对于非常庞大的数据集（例如数十万行），这可能会导致性能问题。在这种极端情况下，可以考虑以下更高级的策略：
- 临时表： 将所有导入数据首先插入到一个临时表，然后使用一条 INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 语句从临时表将数据导入到目标表。
- 分块处理： 将数据分成小块，分批次进行处理，每次处理一个合理数量的记录。
- 原生 LOAD DATA INFILE： 如果数据源是 CSV 文件，并且服务器环境允许，MySQL 的 LOAD DATA INFILE 是最快的导入方式，并且也可以结合 REPLACE 或 IGNORE 关键字处理重复项。
总结
通过灵活运用 CodeIgniter 的查询构建器和 MySQL 的 ON DUPLICATE KEY UPDATE 语句，我们能够高效地处理批量数据导入时的重复条目问题。这种方法不仅保证了数据的完整性，避免了冗余，也为开发人员提供了精确控制重复数据处理逻辑的能力。在实际应用中，务必根据业务需求和数据量大小，选择最适合的策略并进行充分的测试。
