mysql：on duplicate key update與replace into

標籤：

在往表裡面插入資料的時候，經常需要：a.先判斷資料是否存在於庫裡面；b.不存在則插入；c.存在則更新

一、replace into

　　前提：資料庫裡面必須有主鍵或唯一索引，不然replace into 會直接插入新資料，導致資料表裡面有重複資料

　　執行時先嘗試插入資料：

　　　　a.當資料表裡面存在(通過主鍵或唯一索引來判斷)該資料，則先將表裡的資料刪除，再插入新的資料

　　　　b.如果資料表裡面不存在該資料，則直接插入資料

　　replace into是insert into的增強版，文法跟insert iton差不多

　　　　replace into table_name(columns)values(values1,values2);

　　　　replace into table_name(columns) select columns from table_name2

　　測試資料(該表建立了一個複合的唯一索引user_add)：

　　　　CREATE TABLE `relace_on` (

　　　　　　`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
　　　　　　`user_id` int(11) unsigned NOT NULL,
　　　　　　`interal` tinyint(3) unsigned NOT NULL,
　　　　　　`add_time` date NOT NULL,
　　　　　　PRIMARY KEY (`id`),
　　　　　　UNIQUE KEY `user_add` (`user_id`,`add_time`) USING BTREE
　　　　) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

　　插入測試資料：　　　　

　　　　INSERT INTO relace_on (user_id, interal, add_time)
　　　　VALUES
　　　　(1,20,‘2016-05-06‘),
　　　　(2,20,‘2016-05-06‘),
　　　　(3,20,‘2016-05-06‘),
　　　　(1,20,‘2016-05-07‘),
　　　　(2,20,‘2016-05-07‘),
　　　　(3,20,‘2016-05-07‘)

　　現在資料庫資料：

　　　　

　　接下來執行一下replace into語句（存在）：replace INTO relace_on(user_id, interal, add_time)values(1,40,‘2016-05-06‘),(2,60,‘2016-05-06‘),(3,80,‘2016-05-06‘)

　　此時sql執行成功，受影響行數為6行（刪除三條，插入三條）

　　對比一下你會發現user_id(1,2,3)的賬戶在2016-05-06這一天原先都是有資料的，並且id為（1，2，3）；現在執行了replace into後，id變成了（7,8,9），並且interal欄位的值為執行語句的值，此時replace into語句根據資料表中的user_add這個複合的唯一索引發現在資料表中user_id為（1，2，3）的使用者在2016-05-06這天各存在一條記錄，這時就把原先的三條資料刪除了，重新插入了三條，所以id從1，2，3變成了7，8，9；並且interal的值也變了

　　接下來執行一下replace into語句（不存在）：replace INTO relace_on(user_id, interal, add_time)values(4,40,‘2016-05-06‘),(5,60,‘2016-05-06‘),(6,80,‘2016-05-06‘)

　　此時sql執行成功，受影響行數為3行（插入三條）

　　對比，你會發現原先的資料沒變，只是新增了三條資料，同樣是2016-05-06這天的，但是user_id是（4，5，6）根據user_add這個複合的唯一索引，這三條資料不存在資料表中，所以直接插入即可

　　　　

二、on duplicate key update

　　它也是可以用於更新資料的，跟replace into有點相似，但是on duplicate key update是資料表裡面存在該資料就更新，不存在則插入，；而replace into則是存在就刪除，再插入，不存在則插入

　　依舊使用上面現有的資料來測試：

　　先添加一個欄位，用於等下更新多個欄位之用：ALTER TABLE `relace_on` ADD COLUMN `copy_interal` tinyint(3) UNSIGNED NOT NULL AFTER `interal`;

　　文法：

　　　　更新單個欄位：insert into table_name(columns)values(values1,values2) on duplicate key update column=values(column)或者column=value(1,‘zgw‘)

　　　　更新多個欄位：insert into table_name(columns)values(values1,values2) on duplicate key update column1=values(column1),column2=values(column2)

　　執行一條語句（存在）：insert into relace_on(user_id, interal,copy_interal, add_time)values(6,100,200,‘2016-05-06‘) on duplicate KEY update interal=values(interal),copy_interal=values(copy_interal)

　　

　　，user_id=6，add_time=‘2016-05-06‘這條資料存在，則更新interal和copy_interal兩個欄位的值（interal原先為80，copy_interal新增欄位預設為0）

　　再次執行一條語句（不存在）：insert into relace_on(user_id, interal,copy_interal, add_time)values(7,100,200,‘2016-05-06‘) on duplicate KEY update interal=values(interal),copy_interal=values(copy_interal)

　　

 

mysql：on duplicate key update與replace into