복붙노트

[SQL] SQL 쿼리 : 최신 N 제외하고는 테이블에서 모든 기록을 삭제 하시겠습니까?

SQL

SQL 쿼리 : 최신 N 제외하고는 테이블에서 모든 기록을 삭제 하시겠습니까?

그것은 N (ID 내림차순으로 분류) 최신 제외하고는 테이블에서 모든 레코드를 제거하기 위해 (변수없이) 하나의 MySQL의 쿼리를 구축 할 수 있습니까?

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

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

감사.

해결법

  1. ==============================

    1.당신은 방법, 주요 문제는 당신이 LIMIT 절의 값을 지정하는 하위 쿼리를 사용할 수 없습니다되는 기록을 삭제할 수 없습니다.

    당신은 방법, 주요 문제는 당신이 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
);

    중간 서브 쿼리 요구된다. 그게 없으면 우리는 두 가지 오류로 실행하는 것입니다 :

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

    이 쿼리 (예 : 이것과 같은) 특정 사용 사례에 대해 상당히 최적화 할 수 있습니다에서 니콜은 지적했다. 나는 당신을 맞는 있는지뿐만 아니라 그 답을 읽어 보시기 바랍니다.

  2. ==============================

    2.나는 꽤 오래된 질문을 부활하고있어 알고 있지만, 나는 최근에이 문제에 달려 있지만, 필요한 일을하는 많은 수의 비늘이 잘. 이 기존의 성능 데이터는 아니었다, 그리고이 질문은 주목 꽤 있었다 때문에, 난 내가 뭘 찾았는지 게시 할 거라고 생각했다.

    나는 꽤 오래된 질문을 부활하고있어 알고 있지만, 나는 최근에이 문제에 달려 있지만, 필요한 일을하는 많은 수의 비늘이 잘. 이 기존의 성능 데이터는 아니었다, 그리고이 질문은 주목 꽤 있었다 때문에, 난 내가 뭘 찾았는지 게시 할 거라고 생각했다.

    실제로 일한 솔루션은 NOT (빌 Karwin의 유사) 방법은 / 알렉스 바렛 더블 하위 쿼리했고, Quassnoi의 LEFT는 방법 가입하세요.

    삭제되지 않는 레코드의 수가 많을수록 불행하게도 위의 방법 모두 신속 매우 큰 중간 임시 테이블 및 성능 저하를 만듭니다.

    내가에 정착하는 것은 알렉스 바렛의 더블 서브 쿼리 (! 감사합니다)하지만 = 대신 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
  )

    그것은 N 번째 레코드의 ID를 얻기 위해 오프셋 및 해당 레코드와 이전의 모든 기록을 삭제합니다 사용합니다.

    주문이 이미이 문제 (아이디 DESC BY ORDER)의 가정이기 때문에, <= 완벽한 적합합니다.

    서브 쿼리에 의해 생성 된 임시 테이블 대신 N 기록의 한 기록을 포함하기 때문에 그것은 훨씬 빠릅니다.

    나는 세 가지 작업 방법과 두 개의 테스트의 경우 위의 새로운 방법을 시험했다.

    첫번째 시험은 9000 (삭제 오래된 1000)를 유지하고, 제 50 테스트 유지 (오래된 삭제 9950) 동안 모두 테스트 케이스 10000 개 기존 행을 사용한다.

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

    흥미로운 것은이 <= 방법은 전반적으로 더 나은 성능을 볼 수 있지만, 실제로는 더 많은 대신 악화의, 계속 얻을 수 있다는 것입니다.

  3. ==============================

    3.불행하게도 다른 사람에 의해 주어진 답변 모든, 당신은 삭제할 수 없습니다와 같은 쿼리에서 특정 테이블에서 선택합니다.

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

    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

    도 아니다 하위 쿼리에서 지원 LIMIT를 MYSQL 수 있습니다. 다음은 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;

    아이디의 수집 및 쉼표로 구분 된 문자열로합니다

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

    (일반적으로는 SQL 문에 쉼표로 분리 된 목록을 보간하는 것은 SQL 주입의 몇 가지 위험을 소개하지만, 값이 신뢰할 수없는 소스에서 오는되지 않습니다이 경우, 그들은 데이터베이스 자체에서 ID 값 것으로 알려져있다.)

    참고 :이 때로는 더 간단하게 하나의 쿼리에서 일을하지 않지만, 얻을 - 그것 - 완료 솔루션이 가장 효과적입니다.

  4. ==============================

    4.

    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
  5. ==============================

    5.당신의 ID가 증가 다음과 같은 것을 사용하는 경우

    당신의 ID가 증가 다음과 같은 것을 사용하는 경우

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

    6.당신이 사용할 수 있습니다 테 마지막 N을 제외한 모든 기록을 삭제하려면 쿼리 아래에보고했다.

    당신이 사용할 수 있습니다 테 마지막 N을 제외한 모든 기록을 삭제하려면 쿼리 아래에보고했다.

    그것은 하나의 쿼리 그러나 많은 문으로는 그래서 실제로 원래의 질문 의도 된 방식으로 단일 쿼리 아니다.

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

    유용 어쨌든있을 수 있습니다 희망 ...

    NNN은 유지하는 행이며, theTable는 작업중인 테이블입니다.

    나는 당신이 autoincrementing 기록이라는 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;

    이 방법에 대한 좋은 것은 성능입니다 : 내가 마지막으로 1000을 유지, 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 배.

    테스트 환경 : MySQL은 5.5.25 늦은 2011 i7의 맥북 프로 SSD와에

  7. ==============================

    7.

    DELETE FROM table WHERE ID NOT IN
(SELECT MAX(ID) ID FROM table)
  8. ==============================

    8.쿼리 아래 시도 :

    쿼리 아래 시도 :

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

    내부 서브 쿼리는 상위 10 개 값을 반환하고 외부 쿼리는 10 대를 제외한 모든 기록을 삭제합니다.

  9. ==============================

    9.(ID, 내림차순 LIMIT 0, 10 테이블 ORDER에서 선택 ID) WHERE ID NOT IN 테이블에서 삭제

    (ID, 내림차순 LIMIT 0, 10 테이블 ORDER에서 선택 ID) WHERE ID NOT IN 테이블에서 삭제

  10. ==============================

    10.이것은 잘 작동합니다 :

    이것은 잘 작동합니다 :

    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]
  11. ==============================

    11.는 어때 :

    는 어때 :

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

    그것은 전에 N 행보다 함께 행을 반환합니다. 도움이 될 수 있을까요?

  12. ==============================

    12.이 작업 ID를 사용하는 것은 많은 경우에 옵션이 아닙니다. 예를 들어 - 트위터 상태 테이블. 여기에 지정된 타임 스탬프 필드 변형이다.

    이 작업 ID를 사용하는 것은 많은 경우에 옵션이 아닙니다. 예를 들어 - 트위터 상태 테이블. 여기에 지정된 타임 스탬프 필드 변형이다.

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

    13.그냥 대신 MySQL을 Microsoft SQL Server를 사용하는 모든 사용자를위한 혼합으로이 던져 싶었어요. 당신이 대안을 사용해야합니다 있도록 키워드 '한계', MSSQL에서 지원되지 않습니다. 이 코드는 SQL 2008에서 일,이 SO 게시물을 기반으로합니다. https://stackoverflow.com/a/1104447/993856

    그냥 대신 MySQL을 Microsoft SQL Server를 사용하는 모든 사용자를위한 혼합으로이 던져 싶었어요. 당신이 대안을 사용해야합니다 있도록 키워드 '한계', MSSQL에서 지원되지 않습니다. 이 코드는 SQL 2008에서 일,이 SO 게시물을 기반으로합니다. 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)

    틀림없이이 우아하지 않습니다. 당신은 마이크로 소프트 SQL이 최적화 할 수 있다면, 솔루션을 공유하십시오. 감사!

  14. ==============================

    14.당신은뿐만 아니라 다른 열을 기준으로 레코드를 삭제해야하는 경우, 여기에 솔루션입니다 :

    당신은뿐만 아니라 다른 열을 기준으로 레코드를 삭제해야하는 경우, 여기에 솔루션입니다 :

    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
  15. ==============================

    15.왜

    왜

    DELETE FROM table ORDER BY id DESC LIMIT 1, 123456789

    그냥 모두 삭제하지만, 첫 번째 행 (순서는 DESC입니다!), 두 번째 LIMIT-인수로 아주 아주 큰 nummber를 사용하여. 여길 봐

  16. ==============================

    16.오랜만에이 응답 ... 내가 아래와 함께, 같은 상황에 걸쳐 대신 언급 한 답변을 사용하는 온 -

    오랜만에이 응답 ... 내가 아래와 함께, 같은 상황에 걸쳐 대신 언급 한 답변을 사용하는 온 -

    DELETE FROM table_name order by ID limit 10

    이는 1 (10 개) 기록을 삭제하고 최신 기록을 유지합니다.

    17. from https://stackoverflow.com/questions/578867/sql-query-delete-all-records-from-the-table-except-latest-n by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL 서버에 대한 변수에 동적 SQL의 결과를 얻기  (0) 2020.03.28
[SQL] MySQL의 쿼리는 지난 주에서 데이터를 선택하려면?  (0) 2020.03.28
[SQL] SQL은 대 SQL 하위 쿼리 (성능)에 합류?  (0) 2020.03.28
[SQL] 테이블이 사용하지 않고있는 경우 "선택"체크  (0) 2020.03.28
[SQL] 동적 SELECT TOP @var에서 SQL 서버  (0) 2020.03.28
,

티스토리툴바