作者roga (凝视)
看板Database
标题Re: [SQL ] 大量删除资料的问题
时间Tue Jun 9 15:44:53 2009
※ 引述《roga (凝视)》之铭言：
解出来了，结果还是得用 PHP 来帮忙一下..
$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;"
@see:
http://blog.roga.tw/2009/06/09/2220
谢谢楼上的 bobju 和另一位 java 朋友 ^^
--
凝视着，却看不见光景。
--
※ 发信站: 批踢踢实业坊(ptt.cc)
◆ From: 220.130.223.205
※ 编辑: roga 来自: 220.130.223.205 (06/09 15:45)
※ 编辑: roga 来自: 220.130.223.205 (06/09 15:46)