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