MySQL 大量刪除資料的問題 (續)

Posted on June 9, 2009 by

剛剛和同事小菜哥討論了一下昨天我問的MySQL 大量刪除資料的問題，想到一個比較好的解法：


$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 筆資料 */

$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 筆，但這方面我並不是很熟悉。)

Posted in DB

3 comments on “MySQL 大量刪除資料的問題 (續)”

kong0107 says:

January 19, 2010 at 17:32

第10行的SQL後面，LIMIT 1499, 1即可
然後第13行的function name應是小寫….XD

Reply
- roga says:
  
  January 20, 2010 at 02:07
  
  已經修正，謝謝你 😛
  
  Reply
kong0107 says:

January 19, 2010 at 18:26

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));
}
連臨界值也不用求出來

Reply