批量删除数据库表中数据行

 

Python MySQL版本

def delete_mysql_expired_data(db_name, table_name, time_field):
    """删除MySQL指定库指定表指定字段的数据"""

    time_end = get_font_6_last_second(t_type='timestamp_int')

    exists_sql = text("""
        SELECT
            1
        FROM
            %s
        WHERE
            %s <= %s
        LIMIT 1;
    """ % (table_name, time_field, time_end))

    delete_sql = text("""
        DELETE FROM
            %s
        WHERE
            %s <= %s
        LIMIT 20000;
    """ % (table_name, time_field, time_end))

    exists = db.engine.execute(exists_sql).scalar()
    try:
        while True:
            if exists is None:
                break
            db.engine.execute(delete_sql)
            exists = db.engine.execute(exists_sql).scalar()
    except:
        return

    optimize_table_tablespace(db_name, table_name)

    return None