MySQL 更新資料相關語法整理

這次在開發系統的過程中，遇到需要更新語法的SQL，順便做個筆記，避免忘記



如果不存在才 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 不同個。

如果不存在才 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);

如果不存在就 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)

但我遇到一種情況，我想用來判斷是否重複的欄位 有兩個，可以個別重復(無法設定成 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;

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 的欄位。