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
    +--------------+---------------------+------+

現在裡面有許多筆資料,可以用

得知每個 url_id 有幾筆資料。

我前幾天寫錯了一個地方,導致資料表裡面長了大概一百萬筆資料。由於我 SQL 很弱,所以想知道有沒有辦法直接用 SQL 語法執行刪除,條件是:每個 url_id 只要總數超過 N 筆資料就刪除到只剩下 N 筆為止, 當然, id 越大代表資料越新,必須能從小的 id 開始刪起。

我有寫一段程式來跑但是效能不彰,系統 loading 飆到 100 多,拉牛上樹慘不忍睹..(以下是虛擬碼)

這樣作法效能很差,不想這麼暴力解這個問題… 😥

Posted in DB
2 comments on “MySQL 大量刪除資料的問題
  1. kong0107 says:

    在下想到一法能用一個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
    以上,獻醜了。

  2. kong0107 says:

    啊,大於和小於的顯示好像有狀況….
    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 <= t2.id
    ) AS t3
    GROUP BY id
    ) AS t4
    WHERE tbl.id = t4.id
    AND t4.place > 1500

1 Pings/Trackbacks for "MySQL 大量刪除資料的問題"
  1. […] 剛剛和同事小菜哥討論了一下昨天我問的MySQL 大量刪除資料的問題,想到一個比較好的解法: […]

Leave a Reply

Your email address will not be published.