剛剛和同事小菜哥討論了一下昨天我問的MySQL 大量刪除資料的問題，想到一個比較好的解法：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
$sql = "SELECT `url_id`, count( url_id ) FROM `url_detail_history` GROUP BY `url_id` HAVING count(id) > 1499;"; $result = mysql_query($sql); $i = 0; while (list($url_id, $count) = mysql_fetch_row($result) ) { echo "id = $url_id and, count =$count <br />"; /* 掃描資料表，先反向排序，再用 LIMIT 來取得第 1500 筆資料，這筆資料的 ID 就是臨界值 - 重點 - */ $sql2 = "SELECT `id` FROM `url_detail_history` WHERE `url_id` = $url_id ORDER BY `id` DESC LIMIT 1499,1500"; $result2 = mysql_query($sql2); $row = mysql_fetch_array($result2); $critical = $row['id']; echo "critical: $critical"; /* 比臨界值小的都刪掉 */ $sql3 = "DELETE FROM `url_detail_history` WHERE `url_id` = $url_id and `id` < $critical"; mysql_query($sql3); echo "<br />"; $i++; } echo "<hr /> total = $i;" /* 假設資料保留 1500 筆資料 */ |
這個方法一解下去，一秒不到就弄好了，資料庫頓時少了 60 MB 的資料。不過我還是想知道，有沒有辦法用純 SQL 來解(感覺上難度不低)。
(最佳的解法應該是用 Store Procedure 存到 MySQL 內，這樣它就會自己 Maintain 數目在 1500 筆，但這方面我並不是很熟悉。)
MySQL的DELETE可以用ORDER BY，所以while迴圈可以：
while(list($url_id, $count) = mysql_fetch_row($result)) {
mysql_query(“DELETE FROM tbl WHERE url_id = ‘$url_id’ ORDER BY id ASC LIMIT ” . ($count – 1500));
}
連臨界值也不用求出來
第10行的SQL後面，LIMIT 1499, 1即可
然後第13行的function name應是小寫….XD
已經修正，謝謝你 😛