So behalten Sie die ersten X Zeilen bei und löschen Tabellenzeilen

Question

Ich sortiere Zeilen in der MySQL-Spalte incident_archive 中有一个包含数百万条记录的大表，我想按 created, behalte die ersten X Zeilen und lösche den Rest, was am effizientesten ist.

Bisher habe ich diese Lösung in Python gefunden:

def do_delete_archive(rowsToKeep):

    if rowsToKeep > 0:
        db_name = find_environment_value('DB_NAME', False, "dbname")
        db_host = find_environment_value('DB_HOST', False, "host")
        db_user = find_environment_value('DB_USER', False, "username")
        db_pass = find_environment_value('DB_PASS', False, "password")
        db = MySQLdb.connect(host=db_host,user=db_user,passwd=db_pass,db=db_name)
        cursor = db.cursor()            
        
        sql = f"""DELETE FROM `incident_archive` 
                    WHERE incident_id NOT IN 
                    ( SELECT incident_id FROM 
                    ( SELECT incident_id FROM `incident_archive` ORDER BY created DESC LIMIT {rowsToKeep}) foo) LIMIT 10000;"""
        
        try:
            rowcount = rowsToKeep+ 1
            while rowcount > rowsToKeep:
                cursor.execute(sql)
                db.commit()
                rowcount = cursor.rowcount
                print(f"--- Affected Rows: {rowcount} ---")

        except:
            db.rollback()

Das Problem, das ich hier habe, ist, dass diese Methode nicht funktioniert, wenn rowsToKeep 的值大于或等于 10000, was ist ein besserer Weg, diesen Prozess durchzuführen?

**Hinweis: Der rowsToKeep-Wert ist dynamisch, was bedeutet, dass er sich ändern kann.

Answer 1

我想出了以下解决方案：

注意：阈值是包含我们希望在示例中保留 1000 条的最大记录数的变量

sqlCreate = f"""CREATE TABLE new_incident_archive LIKE incident_archive;"""
print(f"Running query is: {sqlCreate}")
cursor.execute(sqlCreate)
print(f"Done with: {sqlCreate}")

sqlInsert = f"""INSERT INTO new_incident_archive SELECT * FROM `incident_archive` ORDER BY created DESC LIMIT {threshold}"""
print(f"Running query is: {sqlInsert}")
cursor.execute(sqlInsert)
db.commit()
print(f"Done with: {sqlInsert}")

sqlDrop = f"""DROP TABLE incident_archive"""
print(f"Running query is: {sqlDrop}")
cursor.execute(sqlDrop)
print(f"Done with: {sqlDrop}")

sqlRename = f"""RENAME TABLE `new_incident_archive` TO `incident_archive`;"""
print(f"Running query is: {sqlRename}")
cursor.execute(sqlRename)
print(f"Done with: {sqlRename}")