mysql重新编号

from sqlalchemy import create_engine, text, inspect
from sqlalchemy.exc import SQLAlchemyError
from grant.sql_connection import jldb_conn as engine


def reset_auto_increment_with_temp_table(original_table_name):
    temp_table_name = f"temp_{original_table_name}"
    try:
        with engine.connect() as connection:
            trans = connection.begin()
            try:
                # 0. 先删除可能存在的临时表（如果存在）
                connection.execute(text(f"DROP TABLE IF EXISTS {temp_table_name}"))

                # 1. 获取原表结构信息
                inspector = inspect(engine)
                columns = inspector.get_columns(original_table_name)
                column_names = [col['name'] for col in columns]
                non_id_columns = [col['name'] for col in columns if col['name'] != 'id']
                columns_str = ', '.join(non_id_columns)

                # 2. 创建临时表（不包含自增主键）
                connection.execute(text(f"""
                    CREATE TABLE {temp_table_name} AS
                    SELECT {columns_str} FROM {original_table_name} WHERE 1=0
                """))

                # 3. 插入数据（不包含id列）
                connection.execute(text(f"""
                    INSERT INTO {temp_table_name} ({columns_str})
                    SELECT {columns_str}
                    FROM {original_table_name}
                    ORDER BY id
                """))

                # 4. 删除原表
                connection.execute(text(f"DROP TABLE {original_table_name}"))

                # 5. 重新创建原表结构（包含自增主键）
                # 先创建表结构（不含数据）
                connection.execute(text(f"""
                    CREATE TABLE {original_table_name} LIKE {temp_table_name}
                """))

                # 添加自增主键列
                connection.execute(text(f"""
                    ALTER TABLE {original_table_name} 
                    ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
                """))

                # 6. 将数据从临时表复制回原表
                connection.execute(text(f"""
                    INSERT INTO {original_table_name} ({columns_str})
                    SELECT {columns_str} FROM {temp_table_name}
                """))

                # 7. 删除临时表
                connection.execute(text(f"DROP TABLE {temp_table_name}"))

                # 8. 重置自增计数器
                connection.execute(text(f"ALTER TABLE {original_table_name} AUTO_INCREMENT = 1"))

                trans.commit()
                print("ID重新编号成功！")

            except Exception as e:
                trans.rollback()
                print(f"操作失败: {e}")

    except Exception as e:
        print(f"数据库连接错误: {e}")
    finally:
        engine.dispose()


# 使用示例
if __name__ == "__main__":
    reset_auto_increment_with_temp_table('sales_flash')