MYSQL之REPLACE INTO和INSERT … ON DUPLICATE KEY UPDATE用法

標籤：mysql   replace into   

REPLACE INTO的用法與INSERT很相似，最終在表中的目的是插入一行新的資料。不同的是，當插入時出現主鍵或者唯一索引衝突的時候，會刪除原有記錄，重新插入新的記錄。因此，除非表具有主鍵或者唯一索引，否則使用REPLACE INTO無任何意義。

以下建立了一個表來進行測試，並添加觸發檢視REPLACE INTO是如何工作的：

CREATE TABLE `replace_into` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `uid` int(11) NOT NULL,  `name` char(10) CHARACTER SET utf8 NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `unique_uid` (`uid`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1334 DEFAULT CHARSET=utf8;CREATE TRIGGER `insert_before_trigger` BEFORE INSERT ON `replace_into` FOR EACH ROW insert into `trigger_log` (`id`,`table_name`,`action`) values(NULL,'replace_into','insert_before');CREATE TRIGGER `insert_after_trigger` AFTER INSERT ON `replace_into` FOR EACH ROW insert into `trigger_log`(`id`,`table_name`,`action`) values(NULL,'replace_into','insert_after');CREATE TRIGGER `update_after_trigger` AFTER UPDATE ON `replace_into` FOR EACH ROW insert into `trigger_log`(`id`,`table_name`,`action`) values(NULL,'replace_into','update_after');CREATE TRIGGER `delete_after_trigger` AFTER DELETE ON `replace_into` FOR EACH ROW insert into `trigger_log`(`id`,`table_name`,`action`) values(NULL,'replace_into','delete_after');

插入一些測試資料後，執行REPLACE INTO

[SQL] replace into `replace_into` values (1333,313,'dd')受影響的行: 2時間: 0.036ms

可以發現，執行成功後受影響的行數為2，讓我們來看看觸發器的記錄

第一步：嘗試插入，但發現唯一性衝突，插入失敗

第二步：刪除表中唯一性衝突的舊記錄

第三步：插入新的行

總結：REPLACE INTO會先刪除原有的行，再插入新的行，如果為了偷懶這樣寫，會導致效能低下。還有一點，由於第三步插入了新的行，如果沒有指定自增主鍵，那麼自增ID也會隨之改變。

INSERT … ON DUPLICATE KEY UPDATE在mysql 4.1版本以後開始支援，
執行語句：

[SQL] insert into `replace_into` values (NULL,313,'aaaaaaaaaa') ON DUPLICATE KEY UPDATE `name`='asd'受影響的行: 2時間: 0.043ms

看看觸發器的記錄：

第一步：嘗試插入，但發現唯一性衝突，插入失敗

第二步：執行更新語句

總結：使用INSERT … ON DUPLICATE KEY UPDATE，可以將原本3條sql語句整合成一條，而且不會造成delete舊資料。

MYSQL之REPLACE INTO和INSERT … ON DUPLICATE KEY UPDATE用法