MySQL 大量刪除資料的問題
url_detail_history 資料表
+--------------+---------------------+------+
| Field | Type | Null |
+--------------+---------------------+------+
| id | bigint(11) unsigned | NO | -> 有 Auto_increment
| url_id | int(11) | NO |
| visitor_ip | varchar(15) | NO |
| visitor_fqdn | varchar(100) | NO |
| visitor_time | timestamp | NO | -> CURRENT_TIMESTAMP
+--------------+---------------------+------+
現在裡面有許多筆資料，可以用
|
1 |
SELECT COUNT(id), url_id FROM url_detail_history GROUP BY url_id |
得知每個 url_id 有幾筆資料。
我前幾天寫錯了一個地方，導致資料表裡面長了大概一百萬筆資料。由於我 SQL 很弱，所以想知道有沒有辦法直接用 SQL 語法執行刪除，條件是：每個 url_id 只要總數超過 N 筆資料就刪除到只剩下 N 筆為止， 當然， id 越大代表資料越新，必須能從小的 id 開始刪起。
我有寫一段程式來跑但是效能不彰，系統 loading 飆到 100 多，拉牛上樹慘不忍睹..(以下是虛擬碼)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
/* 先取出符合條件的資料 */ SELECT count(id), url_id FROM url_detail_history GROUP BY url_id HAVING count(id) > 1500 用此法個別取出 $url_id ，然後 foreach( $url_id ) { do { $id= SELECT min(id) FROM table WHERE url_id = $url_id DELETE FROM table WHERE id = $id $count = "SELECT COUNT(id), url_id FROM url_detail_history WHERE url_id = $url_id } while( $count < 1501) } |
這樣作法效能很差，不想這麼暴力解這個問題… 😥
在下想到一法能用一個SQL指令做到您的需求：
DELETE FROM tbl
USING tbl INNER JOIN (
SELECT id, count(*) AS place
FROM (
SELECT t1.id
FROM tbl AS t1, tbl AS t2
WHERE t1.url_id = t2.url_id
AND t1.id 1500
tbl就是您的url_detail_history
此法受限到Multiple-table DELETE的支援度
詳細說明我晚點會發在我的blog上
以上，獻醜了。
啊，大於和小於的顯示好像有狀況….
DELETE FROM tblUSING tbl INNER JOIN (
SELECT id, count(*) AS place
FROM (
SELECT t1.id
FROM tbl AS t1, tbl AS t2
WHERE t1.url_id = t2.url_id
AND t1.id <= t2.id
) AS t3
GROUP BY id
) AS t4
WHERE tbl.id = t4.id
AND t4.place > 1500