网站开发技术方案与设施,wordpress出选择题,可画设计软件下载,网页代码在线生成INSERT INTO ... ON DUPLICATE KEY UPDATE 是 MySQL 中的一个非常有用的语法#xff0c;它允许你在插入新记录时#xff0c;如果记录的唯一键#xff08;如主键或唯一索引#xff09;已存在#xff0c;则执行更新操作而不是插入。这可以帮助你避免在插入数据时产生的重复键…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 (johnexample.com, John Doe)
ON DUPLICATE KEY UPDATE
name VALUES(name);如果 johnexample.com 的 email 尚未存在于 users 表中那么会插入一条新记录。如果 johnexample.com 的 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);漏刻有时
