使用INSERT INTO ... ON DUPLICATE KEY UPDATE批量插入更新导入excel数据的实践场景应用

INSERT INTO ... ON DUPLICATE KEY UPDATE 是 MySQL 中的一个非常有用的语法，它允许你在插入新记录时，如果记录的唯一键（如主键或唯一索引）已存在，则执行更新操作而不是插入。这可以帮助你避免在插入数据时产生的重复键错误，并允许你以一种原子性的方式处理插入或更新的逻辑。

语法用途

• 插入新记录：当你要插入的新记录的唯一键在表中不存在时，这条记录会被正常插入。
• 更新现有记录：如果新记录的唯一键已存在于表中，那么会执行 UPDATE 部分的语句来更新该记录。

语法讲解

基本语法结构如下：

INSERT INTO table_name (column1, column2, ... columnN)
VALUES (value1, value2, ... valueN)
ON DUPLICATE KEY UPDATE
column1 = VALUES(column1), column2 = VALUES(column2), ...

• table_name：要插入或更新的表名。
• (column1, column2, ... columnN)：要插入或更新的列名。
• (value1, value2, ... valueN)：对应列的值。
• ON DUPLICATE KEY UPDATE：当遇到重复键时执行的更新操作。
• column1 = VALUES(column1), column2 = VALUES(column2), ...：要更新的列及其对应的值，VALUES(columnN) 表示的是 INSERT 语句中对应列的值。

示例

假设有一个名为 users 的表，包含 id（主键，自增）、email（唯一索引）和 name 列。

如果你想插入一个新用户，但如果该用户的 email 已存在，则更新其 name，你可以这样做：

INSERT INTO users (email, name)
VALUES ('[e-mail]', 'John Doe')
ON DUPLICATE KEY UPDATE
name = VALUES(name);

• 如果 [e-mail] 的 email 尚未存在于 users 表中，那么会插入一条新记录。
• 如果 [e-mail] 的 email 已存在于 users 表中，那么会更新该用户的 name 为 ‘John Doe’。

注意事项

• 确保你的表有定义好的主键或唯一索引，这样 MySQL 才能判断何时执行插入操作，何时执行更新操作。
• VALUES(columnN) 函数在 ON DUPLICATE KEY UPDATE 子句中用于引用 INSERT 语句中的对应列值。
• 如果有多个唯一键或唯一索引可能导致冲突，MySQL 会根据定义的顺序来决定使用哪一个。
• ON DUPLICATE KEY UPDATE 语句可以引用表中的其他列来进行更新，不一定非要用 VALUES(columnN)。

这个语法在处理需要确保唯一性的数据时非常有用，比如用户信息、订单号等，它可以减少应用逻辑中的条件判断和数据库操作的次数，提高性能和一致性。

场景应用

从Excel中批量导入数据，使用

 $sql = 'INSERT INTO ' . $db->table('school') . ' (school_id,school_name,school_short_name,school_type,school_attribute,school_teacher,school_student,school_address,school_lng,school_lat,school_leader,school_leader_phone,school_leader2,school_phone2,school_leader3,school_phone3,school_water,school_electricity,school_url,school_content) VALUES ';for ($j = 2; $j <= $highestRow; $j++) {$school_id = $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue();$school_name = $objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue();$school_short_name = $objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue();$school_type = $objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue();$school_attribute = $objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue();$school_teacher = $objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue();$school_student = $objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue();$school_address = $objPHPExcel->getActiveSheet()->getCell("H" . $j)->getValue();$school_lng = $objPHPExcel->getActiveSheet()->getCell("I" . $j)->getValue();$school_lat = $objPHPExcel->getActiveSheet()->getCell("J" . $j)->getValue();$school_leader = $objPHPExcel->getActiveSheet()->getCell("K" . $j)->getValue();$school_leader_phone = $objPHPExcel->getActiveSheet()->getCell("L" . $j)->getValue();$school_leader2 = $objPHPExcel->getActiveSheet()->getCell("M" . $j)->getValue();$school_phone2 = $objPHPExcel->getActiveSheet()->getCell("N" . $j)->getValue();$school_leader3 = $objPHPExcel->getActiveSheet()->getCell("O" . $j)->getValue();$school_phone3 = $objPHPExcel->getActiveSheet()->getCell("P" . $j)->getValue();$school_water = $objPHPExcel->getActiveSheet()->getCell("Q" . $j)->getValue();$school_electricity = $objPHPExcel->getActiveSheet()->getCell("R" . $j)->getValue();$school_url = $objPHPExcel->getActiveSheet()->getCell("S" . $j)->getValue();$school_content = $objPHPExcel->getActiveSheet()->getCell("T" . $j)->getValue();//数据入库;$itemStr = '("';$itemStr .= $school_id . '","' . $school_name . '","' . $school_short_name . '","' . $school_type . '","' . $school_attribute . '","' . $school_teacher . '","' . $school_student . '","' . $school_address . '","' . $school_lng . '","' . $school_lat . '","' . $school_leader . '","'. $school_leader_phone . '","' . $school_leader2 . '","' . $school_phone2 . '","' . $school_leader3 . '","' . $school_phone3 . '","' . $school_water . '","' . $school_electricity . '","' . $school_url . '","' . $school_content;$itemStr .= '"),';$sql .= $itemStr;}$sql = rtrim($sql, ',') . " ON DUPLICATE KEY UPDATE ";$sql .= " school_name= VALUES(school_name), school_short_name = VALUES(school_short_name),school_type = VALUES(school_type),school_attribute = VALUES(school_attribute),school_teacher = VALUES(school_teacher),school_student= VALUES(school_student), school_address = VALUES(school_address),school_lng = VALUES(school_lng),school_lat = VALUES(school_lat),school_leader = VALUES(school_leader),school_leader_phone= VALUES(school_leader_phone), school_leader2= VALUES(school_leader2), school_phone2= VALUES(school_phone2), school_leader3= VALUES(school_leader3),school_phone3= VALUES(school_phone3),school_water= VALUES(school_water), school_electricity= VALUES(school_electricity), school_url= VALUES(school_url), school_url= VALUES(school_content)";$db->query($sql);

---

@漏刻有时