programing

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

firstcheck 2022. 11. 27. 15:39
반응형

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

테이블에서 최신 N(id description으로 정렬됨)을 제외한 모든 레코드를 삭제하는 단일 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 절에서 업데이트할 대상 테이블 '테이블'을 지정할 수 없습니다. MySQL에서는 직접 하위 쿼리 내에서 삭제할 테이블을 참조할 수 없습니다.
  2. SQL 오류(1235): 이 버전의 MySQL은 아직 'LIMIT & IN / ALL / ANY / SOME 서브쿼리'를 지원하지 않습니다.NOT IN 연산자의 직접 서브쿼리 내에서 LIMIT 구를 사용할 수 없습니다.

다행히 중간 서브쿼리를 사용하면 이 두 가지 제한을 모두 우회할 수 있습니다.

Nicole은 이 쿼리가 특정 사용 사례(예: 이 사용 사례)에서 상당히 최적화될 수 있다고 지적했습니다.저는 그 답도 읽어보고 당신의 답과 맞는지 확인해 보는 것을 추천합니다.

꽤 오래된 질문을 부활시키고 있는 것은 알고 있습니다만, 최근 이 문제에 부딪혔지만, 대규모로 확장할 수 있는 것이 필요했습니다.기존의 퍼포먼스 데이터는 없었고, 이 질문이 꽤 주목을 받았기 때문에, 발견한 것을 투고하려고 합니다.

실제로 효과가 있었던 솔루션은 Alex Barrett의 이중 서브쿼리/NOT IN방식(Bill Karwin의 방법과 유사)과 Quassnoi의 방식이었다.

유감스럽게도 위의 두 방법 모두 매우 큰 중간 임시 테이블을 생성하고 삭제되지 않는 레코드의 수가 증가함에 따라 성능이 빠르게 저하됩니다.

한 것은 알렉스 고맙다하지만 알렉스 배럿을 사용한다.<=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
  );

it it를 한다.OFFSETN번째 레코드의 ID를 취득하여 해당 레코드와 모든 이전 레코드를 삭제합니다.

이하고 있기 에 (주문)ORDER BY id DESC<=딱 맞습니다.

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

테스트 케이스

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

어느 테스트 케이스에서도 기존 행이 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 주입의 위험이 발생하지만 이 경우 값은 신뢰할 수 없는 소스로부터의 값이 아니며 데이터베이스 자체로부터의 정수 값이라고 알려져 있습니다).

주의: 이렇게 하면 한 번의 쿼리로 작업을 완료할 수 없지만, 때로는 보다 심플하고 사용하기 쉬운 솔루션이 가장 효과적일 수 있습니다.

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은 유지할 행이고 테이블은 작업 중인 테이블입니다.

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;

이 접근방식의 장점은 퍼포먼스입니다.약 13,000개의 기록을 가진 로컬 DB에서 마지막 1,000개를 테스트했습니다.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 MacBookPro (SSD 탑재))

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개를 제외한 모든 레코드를 삭제합니다.

그럼 어떻게 되는 거야?

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

이전에 N개 이상의 행이 있는 행을 반환합니다.도움이 될까요?

대부분의 경우 이 작업에 ID를 사용할 수 없습니다.예를 들어, twitter 상태가 있는 테이블입니다.다음은 타임스탬프 필드가 지정된 변형입니다.

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

MySQL 대신 Microsoft SQL Server를 사용하는 모든 사용자에게 이 기능을 제공하고 싶습니다.'제한' 키워드는 MSQL에서 지원되지 않으므로 다른 키워드를 사용해야 합니다.이 코드는 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)

인정하건대, 이것은 우아하지 않다.Microsoft SQL용으로 최적화할 수 있다면 솔루션을 공유해 주십시오.감사합니다!

다른 열에 따라 레코드를 삭제해야 하는 경우 다음과 같은 해결책이 있습니다.

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

이것도 동작합니다.

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]

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

그거 좋지

DELETE FROM table ORDER BY id DESC LIMIT 1, 123456789

첫 번째 행(순서는 DESC!)을 제외한 모든 행을 삭제하고 매우 큰 숫자를 두 번째 LIMIT 인수로 사용합니다.여기를 참조해 주세요.

오랜만에 대답하니까...같은 상황에 부딪혀, 그 답을 사용하는 대신에, 다음과 같은 것을 생각해 냈습니다.

DELETE FROM table_name order by ID limit 10

그러면 처음 10개 레코드가 삭제되고 최신 레코드가 유지됩니다.

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

반응형
저작자표시

'programing' 카테고리의 다른 글

mysql의 타임스탬프에서 날짜만 가져옵니다.  (0) 2022.11.27
JavaScript에서 정수 나눗셈을 수행하고 나머지를 별도로 얻는 방법은 무엇입니까?  (0) 2022.11.27
MyISAM의 모든 테이블을 InnoDB로 변환하려면 어떻게 해야 합니까?  (0) 2022.11.07
키를 모르는 상태에서 JavaScript 객체의 모든 속성 값을 가져오려면 어떻게 해야 합니까?  (0) 2022.11.07
input() error - NameError: 이름 '...'이 정의되지 않았습니다.  (0) 2022.11.07

'programing'의 다른글

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

관련글

댓글

티스토리툴바