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 ('john@example.com', 'John Doe')
ON DUPLICATE KEY UPDATE
name = VALUES(name);
  • 如果 john@example.comemail 尚未存在于 users 表中,那么会插入一条新记录。
  • 如果 john@example.comemail 已存在于 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);

@漏刻有时

03-28 05:52