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技术网站