Laravel: Which one is better to "reset" a table: ->truncate() or ->delete()?-CodePudding

I'm using ->truncate() to delete the data, but DB::rollback() in a try catch doesn't executed when I use it. Meanwhile the ->delete() is executed by DB::rollback(), but with it my auto increment doesn't reset to 1, I tried to use DB::statement('ALTER TABLE table_name AUTO_INCREMENT = 1') after ->delete() but alter table doesn't supported by DB::rollback() too, so I don't know what should I do?

Code 1:

\DB::beginTransaction();
try {
    \DB::table('table_name')->truncate();
    \DB::table('table_name')->insert($data);
    \DB::commit();
} catch (\Exception $e){
    \DB::rollback();
}

Code 2:

\DB::beginTransaction();
try {
    \DB::table('table_name')->delete();
    \DB::statement('ALTER TABLE table_name AUTO_INCREMENT = 1');
    \DB::table('table_name')->insert($data);
    \DB::commit();
} catch (\Exception $e){
    \DB::rollback();
}

TLDR: I want to delete all data in a table and then insert the table with new data starting from id = 1, but those two codes don't work with DB::rollback()

CodePudding user response：

for mysql

13.3.2 Statements That Cannot Be Rolled Back Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines. see this link

It is a fairly common SQL Server belief that Truncate Cannot Be Rolled Back Because It Is Not Logged

TRUNCATE is a logged operation, but SQL Server doesn’t log every single row as it TRUNCATEs the table. SQL Server only logs the fact that the TRUNCATE operation happened. It also logs the information about the pages and extents that were deallocated. However, there’s enough information to roll back, by just re-allocating those pages. A log backup only needs the information that the TRUNCATE TABLE occurred. To restore the TRUNCATE TABLE, the operation is just reapplied. The data involved is not needed during RESTORE (like it would be for a true ‘minimally logged’ operation like a BULK INSERT).

hopefully that is help you

CodePudding user response：

I found how to do it myself, by doing this:

\DB::beginTransaction();
try {
    $status = \DB::table('table_name')->insert($data[0]); // to test whenever the insertion is successful or not
    
    if ($status){
        \DB::table('table_name')->delete();
        \DB::statement('ALTER TABLE table_name AUTO_INCREMENT = 1');

        \DB::table('table_name')->insert($data);
    }
    \DB::commit();
} catch (\Exception $e){
    \DB::rollback();
}