æ¬æç« æ¥ç»å¤§å®¶æä¾ä¸ç§å¨mysqlä¸é¿å
éå¤æå
¥è®°å½æ¹æ³ï¼ä¸»è¦æ¯è®²å°äºignore,Replace,ON DUPLICATE KEY UPDATEä¸ç§æ¹æ³ï¼æéè¦çæåå¯ä»¥åèä¸ä¸
æ¹æ¡ä¸ï¼ä½¿ç¨ignoreå
³é®å
å¦ææ¯ç¨ä¸»é®primaryæè
å¯ä¸ç´¢å¼uniqueåºåäºè®°å½çå¯ä¸æ§,é¿å
éå¤æå
¥è®°å½å¯ä»¥ä½¿ç¨ï¼
代ç å¦ä¸:
INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('
[email protected]', '99999', '9999');
è¿æ ·å½æéå¤è®°å½å°±ä¼å¿½ç¥,æ§è¡åè¿åæ°å0
è¿æ个åºç¨å°±æ¯å¤å¶è¡¨,é¿å
éå¤è®°å½ï¼
代ç å¦ä¸:
INSERT IGNORE INTO `table_1` (`name`) SELECT `name` FROM `table_2`;
æ¹æ¡äºï¼ä½¿ç¨Replace
è¯æ³æ ¼å¼ï¼
代ç å¦ä¸:
REPLACE INTO `table_name`(`col_name`, ...) VALUES (...);
REPLACE INTO `table_name` (`col_name`, ...) SELECT ...;
REPLACE INTO `table_name` SET `col_name`='value',
...ç®æ³è¯´æï¼
REPLACEçè¿è¡ä¸INSERTå¾ç¸å,ä½æ¯å¦ææ§è®°å½ä¸æ°è®°å½æç¸åçå¼ï¼åå¨æ°è®°å½è¢«æå
¥ä¹åï¼æ§è®°å½è¢«å é¤ï¼å³ï¼
å°è¯ææ°è¡æå
¥å°è¡¨ä¸
å½å 为对äºä¸»é®æå¯ä¸å
³é®ååºç°éå¤å
³é®åé误èé ææå
¥å¤±è´¥æ¶ï¼
ä»è¡¨ä¸å é¤å«æéå¤å
³é®åå¼çå²çªè¡
å次å°è¯ææ°è¡æå
¥å°è¡¨ä¸
æ§è®°å½ä¸æ°è®°å½æç¸åçå¼çå¤ææ åå°±æ¯ï¼
表æä¸ä¸ªPRIMARY KEYæUNIQUEç´¢å¼ï¼å¦åï¼ä½¿ç¨ä¸ä¸ªREPLACEè¯å¥æ²¡ææä¹ã该è¯å¥ä¼ä¸INSERTç¸åï¼å 为没æç´¢å¼è¢«ç¨äºç¡®å®æ¯å¦æ°è¡å¤å¶äºå
¶å®çè¡ã
è¿åå¼ï¼
REPLACEè¯å¥ä¼è¿åä¸ä¸ªæ°ï¼æ¥æ示åå½±åçè¡çæ°ç®ã该æ°æ¯è¢«å é¤å被æå
¥çè¡æ°çå
åå½±åçè¡æ°å¯ä»¥å®¹æå°ç¡®å®æ¯å¦REPLACEåªæ·»å äºä¸è¡ï¼æè
æ¯å¦REPLACEä¹æ¿æ¢äºå
¶å®è¡ï¼æ£æ¥è¯¥æ°æ¯å¦ä¸º1ï¼æ·»å ï¼ææ´å¤§ï¼æ¿æ¢ï¼ã
示ä¾:
# eg:(phoneå段为å¯ä¸ç´¢å¼)
代ç å¦ä¸:
REPLACE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test569', '99999', '123');
å¦å¤,å¨ SQL Server ä¸å¯ä»¥è¿æ ·å¤çï¼
代ç å¦ä¸:
if not exists (select phone from t where phone= '1') insert into t(phone, update_time) values('1', getdate()) else update t set update_time = getdate() where phone= '1'
æ´å¤ä¿¡æ¯è¯·çï¼
http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#replaceæ¹æ¡ä¸ï¼ON DUPLICATE KEY UPDATE
å¦ä¸æåï¼ä½ ä¹å¯ä»¥å¨INSERT INTOâ¦..åé¢å ä¸ ON DUPLICATE KEY UPDATEæ¹æ³æ¥å®ç°ãå¦ææ¨æå®äºON DUPLICATE KEY UPDATEï¼å¹¶ä¸æå
¥è¡åä¼å¯¼è´å¨ä¸ä¸ªUNIQUEç´¢å¼æPRIMARY KEYä¸åºç°éå¤å¼ï¼åæ§è¡æ§è¡UPDATEã
ä¾å¦ï¼å¦æåa被å®ä¹ä¸ºUNIQUEï¼å¹¶ä¸å
å«å¼1ï¼å以ä¸ä¸¤ä¸ªè¯å¥å
·æç¸åçææï¼
代ç å¦ä¸:
INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=`c`+1;
UPDATE `table` SET `c`=`c`+1 WHERE `a`=1;
å¦æè¡ä½ä¸ºæ°è®°å½è¢«æå
¥ï¼ååå½±åè¡çå¼ä¸º1ï¼å¦æåæçè®°å½è¢«æ´æ°ï¼ååå½±åè¡çå¼ä¸º2ã
注éï¼å¦æåbä¹æ¯å¯ä¸åï¼åINSERTä¸æ¤UPDATEè¯å¥ç¸å½ï¼
代ç å¦ä¸:
UPDATE `table` SET `c`=`c`+1 WHERE `a`=1 OR `b`=2 LIMIT 1;
å¦æa=1 OR b=2ä¸å¤ä¸ªè¡åå¹é
ï¼ååªæä¸ä¸ªè¡è¢«æ´æ°ãé常ï¼æ¨åºè¯¥å°½éé¿å
对带æå¤ä¸ªå¯ä¸å
³é®åç表使ç¨ON DUPLICATE KEYåå¥ã
æ¨å¯ä»¥å¨UPDATEåå¥ä¸ä½¿ç¨VALUES(col_name)å½æ°ä»INSERTâ¦UPDATEè¯å¥çINSERTé¨åå¼ç¨åå¼ãæ¢å¥è¯è¯´ï¼å¦æ没æåçéå¤å
³é®åå²çªï¼åUPDATEåå¥ä¸çVALUES(col_name)å¯ä»¥å¼ç¨è¢«æå
¥çcol_nameçå¼ãæ¬å½æ°ç¹å«éç¨äºå¤è¡æå
¥ãVALUES()å½æ°åªå¨INSERTâ¦UPDATEè¯å¥ä¸ææä¹ï¼å
¶å®æ¶åä¼è¿åNULLã
代ç å¦ä¸:
INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3), (4, 5, 6) ON DUPLICATE KEY UPDATE `c`=VALUES(`a`)+VALUES(`b`);
æ¬è¯å¥ä¸ä»¥ä¸ä¸¤ä¸ªè¯å¥ä½ç¨ç¸åï¼
代ç å¦ä¸:
INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=3;
INSERT INTO `table` (`a`, `b`, `c`) VALUES (4, 5, 6) ON DUPLICATE KEY UPDATE c=9;
注éï¼å½æ¨ä½¿ç¨ON DUPLICATE KEY UPDATEæ¶ï¼DELAYEDé项被忽ç¥ã
示ä¾ï¼
è¿ä¸ªä¾åæ¯æå¨å®é
项ç®ä¸ç¨å°çï¼æ¯å°ä¸ä¸ªè¡¨çæ°æ®å¯¼å
¥å°å¦å¤ä¸ä¸ªè¡¨ä¸ï¼æ°æ®çéå¤æ§å°±å¾èè(å¦ä¸)ï¼å¯ä¸ç´¢å¼ä¸ºï¼emailï¼
代ç å¦ä¸:
INSERT INTO `table_name1` (`title`, `first_name`, `last_name`, `email`, `phone`, `user_id`, `role_id`, `status`, `campaign_id`)
SELECT '', '', '', `table_name2`.`email`, `table_name2`.`phone`, NULL, NULL, 'pending', 29 FROM `table_name2`
WHERE `table_name2`.`status` = 1
ON DUPLICATE KEY UPDATE `table_name1`.`status`='pending'
åè´´ä¸ä¸ªä¾åï¼
代ç å¦ä¸:
INSERT INTO `class` SELECT * FROM `class1` ON DUPLICATE KEY UPDATE `class`.`course`=`class1`.`course`
å
¶å®å
³é®ï¼DELAYED å为快éæå
¥ï¼å¹¶ä¸æ¯å¾å
³å¿å¤±ææ§ï¼æé«æå
¥æ§è½ã
IGNORE åªå
³æ³¨ä¸»é®å¯¹åºè®°å½æ¯ä¸åå¨ï¼æ åæ·»å ï¼æå忽ç¥ã
ç¹å«è¯´æï¼å¨MYSQLä¸UNIQUEç´¢å¼å°ä¼å¯¹nullå段失æï¼ä¹å°±æ¯è¯´(aå段ä¸å»ºç«å¯ä¸ç´¢å¼)ï¼
代ç å¦ä¸:
INSERT INTO `test` (`a`) VALUES (NULL);
æ¯å¯ä»¥éå¤æå
¥çï¼èåå¯ä¸ç´¢å¼ä¹ä¸æ ·ï¼ã