2012/05/31

MySQL INSERT 與 UPDATE 的一些特殊情況用法

最近剛好在設計 social network 的 database schema,遇到很多 special query 的需求,在此一一記錄下來。

如果不存在才 INSERT,存在就 skip
舉例來說,如果想記錄某 user 是否去過某國家
-----------------
 user  | country
-------+---------
 tony  | US
 tony  | HK
 kelly | US
Query:
INSERT INTO `trip` (`user`, `country`) 
SELECT 'tony', 'US' FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `trip` WHERE `user` = "tony" AND `country` = "US" LIMIT 1
);
* 想 INSERT 的 table 可以跟檢查存在性的 table 不同個。

參考資料
mysql - Insert Where Not Exists-Without Primary Key - Stack Overflow



如果不存在才 UPDATE,否則 skip
舉例來說,你想讓 user 更新 email 欄位(unique),但 email 又不能跟其他人一樣:
------------------------------
 user      | email
-----------+------------------
 olivia    | olivia@email.com
 andy_wang | andy@email.com
 andy_lin  | (NULL)
Query:
UPDATE `profile` SET `email` = "andy@email.com" WHERE `user` = "andy_lin" AND NOT EXISTS
(SELECT 1 FROM `profile` WHERE `email` = "andy@email.com");
但你會發現 mysql execute query 會有下面錯誤:
You can't specify target table 'profile' for update in FROM clause
應該改成:
UPDATE `profile` SET `email` = "andy@email.com" 
WHERE `user` = "andy_lin" AND NOT EXISTS
  (SELECT * FROM (SELECT 1 FROM `profile` WHERE `email` = "andy@email.com") temp);

參考資料
MySQL can’t specify target table for update in FROM clause | VerySimple



如果不存在就 INSERT,存在就 UPDATE
  (INSERT ON DUPLICATE KEY UPDATE)
這邊存在與不存在的依據是 primary key、unique key 是否重複。
INSERT INTO `users` (username, email) VALUES (‘jo’, ‘jo@email.com’)
ON DUPLICATE KEY UPDATE email = ‘jo@email.com’;
(username is primary key)

參考資料
MySQL–Update and Insert if not exists | Chief of the System Blog

但我遇到一種情況,我想用來判斷是否重複的欄位 有兩個,可以個別重復(無法設定成 unique),但又不能存在兩個欄位都一樣的資料列。遇到這種情況,建議可以額外插入一個 unique key,用來儲存兩個欄位的串聯。
舉個例子來說,如果想記錄某個 user 某天花多少錢:
-------------------------------
 uniq    | user | date | money
---------+------+------+-------
 13:0530 | 13   | 0530 | 100
 13:0531 | 13   | 0531 | 200
 15:0530 | 15   | 0530 | 100
於是當你要新增一筆資料或修改 money 的欄位,就可以用下面的語法:
INSERT INTO `log` (`uniq`, `user`, `date`, `money`) VALUES (‘13:8’, ‘13’, '5/30', '500')
ON DUPLICATE KEY UPDATE money = money + ‘500’;

如果你覺得多一個欄位很佔空間,當然也是可以把檢查跟新增/更新拆開來,只是就沒辦法一個query就達到了(以下非正式語法)
IF (SELECT COUNT(*) FROM `log` WHERE `user` = 13 AND `date` = 0530) = 0
  INSERT INTO `log` (`user`, `date`, `money`) VALUES (‘13’, '5/30', '500');
ELSE
  UPDATE `log` SET `user` = 13, `date` = "5/30", `money` = 500;
ENDIF



如果 COUNT(*) = N 就 INSERT
這跟第一題很像。舉例來說,如果想記錄兩個人的成為朋友,會先判斷"這兩個人"是否存在:
[profile]
------------
 id | user  
----+-------
 8  | emma
 9  | lili
 10 | logan

[friend]
-------------------
 my_id | friend_id
-------+-----------
 8     | 9
 9     | 10
Query:
INSERT INTO `friend`(`my_id`, `friend_id`) 
SELECT 8, 10 FROM DUAL WHERE
  (SELECT COUNT(*) FROM `profile` WHERE `id` = 8 OR `id` = 10) = 2;

參考資料
sql - Exists Count(*) - Stack Overflow



INSERT INTO、INSERT IGNORE、REPLACE INTO的差別
INSERT INTO:若遇到 primary key 或 unique key 重複時則會發生錯誤。
INSERT IGNORE:若遇到 primary key 或 unique key 重複時則略過。
REPLACE INTO:如果遇到 primary key 或 unique key 重複時就覆蓋,不存在就像 INSERT INTO

REPLACE INTO 與 INSERT ON DUPLICATE KEY UPDATE 很像,但後者可以做的變化比較多。
INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO - MySQL Performance Blog

INSERT IGNORE 與 INSERT INTO WHERE NOT EXISTS 很像,但後者可以判斷不同 table 的欄位。

參考資料
insert into和replace into以及insert ignore - qiang106 - ITeye技术网站


7 則留言: