SQL 쿼리 : 테이블에서 최신 N을 제외한 모든 레코드를 삭제 하시겠습니까?

SQL 쿼리 : 테이블에서 최신 N을 제외한 모든 레코드를 삭제 하시겠습니까?

---

최신 N (id desc로 정렬 됨)을 제외하고 테이블에서 모든 레코드를 제거하기 위해 단일 mysql 쿼리 (변수 없음)를 빌드 할 수 있습니까?

이런 식으로 만 작동하지 않습니다. :)

delete from table order by id ASC limit ((select count(*) from table ) - N)

감사.

---

그런 식으로 레코드를 삭제할 수 없습니다. 주요 문제는 하위 쿼리를 사용하여 LIMIT 절의 값을 지정할 수 없다는 것입니다.

이것은 작동합니다 (MySQL 5.0.67에서 테스트 됨).

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT 42 -- keep this many records
  ) foo
);

중간 하위 쿼리 가 필요합니다. 그것 없이는 두 가지 오류가 발생합니다.

1. SQL 오류 (1093) : FROM 절에서 업데이트 할 대상 테이블 'table'을 지정할 수 없습니다. MySQL은 직접 하위 쿼리 내에서 삭제중인 테이블을 참조 할 수 없습니다.
2. SQL 오류 (1235) :이 버전의 MySQL은 아직 'LIMIT & IN / ALL / ANY / SOME 하위 쿼리'를 지원하지 않습니다 .-NOT IN 연산자의 직접 하위 쿼리 내에서 LIMIT 절을 사용할 수 없습니다.

다행히 중간 하위 쿼리를 사용하면 이러한 제한 사항을 모두 우회 할 수 있습니다.

---

Nicole은이 쿼리가 특정 사용 사례 (예 :이 사례)에 대해 상당히 최적화 될 수 있다고 지적했습니다. 귀하의 답변에 맞는지 확인하기 위해 그 답변 을 읽는 것이 좋습니다 .

---

나는 꽤 오래된 질문을 부활시키고 있다는 것을 알고 있지만 최근 에이 문제가 발생했지만 많은 수로 확장되는 것이 필요했습니다 . 기존 성능 데이터가 없었고,이 질문에 많은 관심이 있었기 때문에 찾은 것을 게시 할 것이라고 생각했습니다.

실제로 작동 한 솔루션은 Alex Barrett의 double sub-query /NOT IN 메서드 ( Bill Karwin의 )와 Quassnoi의LEFT JOIN 방법이었습니다.

불행히도 위의 두 방법 모두 매우 큰 중간 임시 테이블을 만들고 삭제 되지 않는 레코드 수가 많아지면 성능이 빠르게 저하 됩니다.

내가 정한 것은 Alex Barrett의 이중 하위 쿼리 (감사합니다!)를 사용하지만 <=대신 사용 합니다 NOT IN.

DELETE FROM `test_sandbox`
  WHERE id <= (
    SELECT id
    FROM (
      SELECT id
      FROM `test_sandbox`
      ORDER BY id DESC
      LIMIT 1 OFFSET 42 -- keep this many records
    ) foo
  )

그것은 사용 OFFSET의 ID를 얻기 위해 N 번째 기록과 그 기록 및 이전의 모든 기록을 삭제합니다.

주문은 이미이 문제 ( ORDER BY id DESC) 의 가정이므로 <=완벽하게 적합합니다.

서브 쿼리에 의해 생성 된 임시 테이블에 N 개의 레코드 대신 하나의 레코드 만 포함되어 있기 때문에 훨씬 빠릅니다 .

테스트 케이스

위의 세 가지 작업 방법과 두 가지 테스트 사례에서 새로운 방법을 테스트했습니다.

두 테스트 사례 모두 10000 개의 기존 행을 사용하는 반면 첫 번째 테스트는 9000 개 (가장 오래된 1000 개 삭제)를 유지하고 두 번째 테스트는 50 개 (가장 오래된 9950 개 삭제)를 유지합니다.

+-----------+------------------------+----------------------+
|           | 10000 TOTAL, KEEP 9000 | 10000 TOTAL, KEEP 50 |
+-----------+------------------------+----------------------+
| NOT IN    |         3.2542 seconds |       0.1629 seconds |
| NOT IN v2 |         4.5863 seconds |       0.1650 seconds |
| <=,OFFSET |         0.0204 seconds |       0.1076 seconds |
+-----------+------------------------+----------------------+

흥미로운 점은이 <=방법이 전반적으로 더 나은 성능을 보이지만 실제로는 더 나쁘지 않고 더 많이 유지할수록 더 좋아진다는 것입니다.

---

불행하게도, 당신은 할 수 없습니다 다른 사람에 의해 주어진 모든 답변 DELETE과 SELECT같은 쿼리에서 특정 테이블에서.

DELETE FROM mytable WHERE id NOT IN (SELECT MAX(id) FROM mytable);

ERROR 1093 (HY000): You can't specify target table 'mytable' for update 
in FROM clause

MySQL LIMIT은 하위 쿼리에서도 지원할 수 없습니다 . 이것은 MySQL의 한계입니다.

DELETE FROM mytable WHERE id NOT IN 
  (SELECT id FROM mytable ORDER BY id DESC LIMIT 1);

ERROR 1235 (42000): This version of MySQL doesn't yet support 
'LIMIT & IN/ALL/ANY/SOME subquery'

내가 생각 해낼 수있는 가장 좋은 대답은 두 단계로 수행하는 것입니다.

SELECT id FROM mytable ORDER BY id DESC LIMIT n; 

ID를 수집하여 쉼표로 구분 된 문자열로 만듭니다.

DELETE FROM mytable WHERE id NOT IN ( ...comma-separated string... );

(일반적으로 쉼표로 구분 된 목록을 SQL 문에 삽입하면 SQL 삽입 위험이 발생하지만이 경우 값은 신뢰할 수없는 소스에서 제공되지 않으며 데이터베이스 자체의 id 값으로 알려져 있습니다.)

참고 : 이 방법으로 단일 쿼리로 작업을 수행 할 수는 없지만 때로는 더 간단한 완료 솔루션이 가장 효과적입니다.

---

DELETE  i1.*
FROM    items i1
LEFT JOIN
        (
        SELECT  id
        FROM    items ii
        ORDER BY
                id DESC
        LIMIT 20
        ) i2
ON      i1.id = i2.id
WHERE   i2.id IS NULL

---

ID가 증분이면 다음과 같은 것을 사용하십시오.

delete from table where id < (select max(id) from table)-N

---

마지막 N 을 제외한 모든 레코드를 삭제하려면 아래보고 된 쿼리를 사용할 수 있습니다.

단일 쿼리이지만 많은 문이 포함되어 있으므로 실제로 원래 질문에서 의도 한 방식대로 단일 쿼리 가 아닙니다 .

또한 MySQL의 버그로 인해 변수와 내장 (쿼리에) 준비된 문이 필요합니다.

어쨌든 유용 할 수 있기를 바랍니다 ...

nnn 은 보관할 행 이고 theTable 은 작업중인 테이블입니다.

나는 당신이 id 라는 이름의 자동 증가 레코드를 가지고 있다고 가정하고 있습니다.

SELECT @ROWS_TO_DELETE := COUNT(*) - nnn FROM `theTable`;
SELECT @ROWS_TO_DELETE := IF(@ROWS_TO_DELETE<0,0,@ROWS_TO_DELETE);
PREPARE STMT FROM "DELETE FROM `theTable` ORDER BY `id` ASC LIMIT ?";
EXECUTE STMT USING @ROWS_TO_DELETE;

이 접근 방식의 좋은 점은 성능입니다 . 마지막 1,000 개를 유지하면서 약 13,000 개의 레코드가있는 로컬 DB에서 쿼리를 테스트했습니다. 0.08 초 안에 실행됩니다.

받아 들여진 답변의 스크립트 ...

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT 42 -- keep this many records
  ) foo
);

0.55 초가 걸립니다. 약 7 배 더.

테스트 환경 : SSD가 장착 된 2011 년 후반 i7 MacBookPro의 mySQL 5.5.25

---

DELETE FROM table WHERE ID NOT IN
(SELECT MAX(ID) ID FROM table)

---

아래 쿼리를 시도하십시오.

DELETE FROM tablename WHERE id < (SELECT * FROM (SELECT (MAX(id)-10) FROM tablename ) AS a)

내부 하위 쿼리는 상위 10 개 값을 반환하고 외부 쿼리는 상위 10 개를 제외한 모든 레코드를 삭제합니다.

---

DELETE FROM table WHERE id NOT IN (SELECT id FROM table ORDER BY id, desc LIMIT 0, 10)

---

This should work as well:

DELETE FROM [table] INNER JOIN (SELECT [id] FROM (SELECT [id] FROM [table] ORDER BY [id] DESC LIMIT N) AS Temp) AS Temp2 ON [table].[id] = [Temp2].[id]

---

What about :

SELECT * FROM table del 
         LEFT JOIN table keep
         ON del.id < keep.id
         GROUP BY del.* HAVING count(*) > N;

It returns rows with more than N rows before. Could be useful ?

---

Using id for this task is not an option in many cases. For example - table with twitter statuses. Here is a variant with specified timestamp field.

delete from table 
where access_time >= 
(
    select access_time from  
    (
        select access_time from table 
            order by access_time limit 150000,1
    ) foo    
)

---

Just wanted to throw this into the mix for anyone using Microsoft SQL Server instead of MySQL. The keyword 'Limit' isn't supported by MSSQL, so you'll need to use an alternative. This code worked in SQL 2008, and is based on this SO post. https://stackoverflow.com/a/1104447/993856

-- Keep the last 10 most recent passwords for this user.
DECLARE @UserID int; SET @UserID = 1004
DECLARE @ThresholdID int -- Position of 10th password.
SELECT  @ThresholdID = UserPasswordHistoryID FROM
        (
            SELECT ROW_NUMBER()
            OVER (ORDER BY UserPasswordHistoryID DESC) AS RowNum, UserPasswordHistoryID
            FROM UserPasswordHistory
            WHERE UserID = @UserID
        ) sub
WHERE   (RowNum = 10) -- Keep this many records.

DELETE  UserPasswordHistory
WHERE   (UserID = @UserID)
        AND (UserPasswordHistoryID < @ThresholdID)

Admittedly, this is not elegant. If you're able to optimize this for Microsoft SQL, please share your solution. Thanks!

---

If you need to delete the records based on some other column as well, then here is a solution:

DELETE
FROM articles
WHERE id IN
    (SELECT id
     FROM
       (SELECT id
        FROM articles
        WHERE user_id = :userId
        ORDER BY created_at DESC LIMIT 500, 10000000) abc)
  AND user_id = :userId

---

Why not

DELETE FROM table ORDER BY id DESC LIMIT 1, 123456789

Just delete all but the first row (order is DESC!), using a very very large nummber as second LIMIT-argument. See here

---

Answering this after a long time...Came across the same situation and instead of using the answers mentioned, I came with below -

DELETE FROM table_name order by ID limit 10

This will delete the 1st 10 records and keep the latest records.

참고URL : https://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n