truncate all tables in laravel using eloquent

问题:

Is there a way I could truncate all the tables in a db using eloquent or fluent in laravel 4? I do not want to specify table names, I just want to truncate all the tables. In other words empty all the tables.

回答1:

NOTE: doctrine/dbal Package is Required for Performing this Operations

So Make Sure that is Installed composer require doctrine/dbal

1. Get all the table names

$tableNames = Schema::getConnection()->getDoctrineSchemaManager()->listTableNames();

2. Loop through the array of table names and truncate with Schema Builder

foreach ($tableNames as $name) {
    //if you don't want to truncate migrations
    if ($name == 'migrations') {
        continue;
    }
    DB::table($name)->truncate();
}

Help: If you have Got Some Error Such as

SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint

You Can disable foriegn Key Checks

Schema::disableForeignKeyConstraints();

and make sure to ReEnable it

Schema::enableForeignKeyConstraints();

回答2:

Here is my answer based on @Hao Luo. Moreover, it has these pros:

1. You do not need to install any extra package (no need for doctrine)
2. It supports laravel 5 very well
3. It disables foreign key constraint (If you truncate without caring about the orders and enables foreign key constraint, you will likely get an error)

Here is the code:

DB::statement("SET foreign_key_checks=0");
$databaseName = DB::getDatabaseName();
$tables = DB::select("SELECT * FROM information_schema.tables WHERE table_schema = '$databaseName'");
foreach ($tables as $table) {
    $name = $table->TABLE_NAME;
    //if you don't want to truncate migrations
    if ($name == 'migrations') {
        continue;
    }
    DB::table($name)->truncate();
}
DB::statement("SET foreign_key_checks=1");

Hope you like it! :)

回答3:

In laravel 5, migrate:fresh will drop all the tables in the database (even if tables aren't related to migrate)

回答4:

This is how i truncate all tables inside a database (including table exceptions), it works for me.

    // set tables don't want to trucate here
    $excepts = ['migrations'];
    $tables = DB::connection()
    ->getPdo()
    ->query("SHOW FULL TABLES")
    ->fetchAll();
    $tableNames = [];

    $keys = array_keys($tables[0]);
    $keyName = $keys[0];
    $keyType = $keys[1];

    foreach ($tableNames as $name) {
        //if you don't want to truncate migrations
        if (in_array($name[$keyName], $excepts))
            continue;

        // truncate tables only
        if('BASE TABLE' !== $name[$keyType])
            continue;

        \DB::table($name)->truncate();
    }

回答5:

Based on previous answers, I filter table names directly into the SQL query. I'm agree it's a small optimization but that avoids unnecessary loop.

protected function truncateDatabase($excepts = []): void
{
    $excepts = array_merge(['migrations'], $excepts);
    \DB::statement('SET foreign_key_checks=0');
    $table_names = \DB::query()->select('TABLE_NAME')->from('information_schema.tables')
        ->where('TABLE_SCHEMA', \DB::getDatabaseName())
        ->whereNotIn('TABLE_NAME', $excepts)
        ->get()
        ->pluck('TABLE_NAME')
        ->toArray();
    foreach ($table_names as $table_name) {
        \DB::table($table_name)->truncate();
    }
    \DB::statement('SET foreign_key_checks=1');
}

回答6:

Use this:

$tables = DB::select('SHOW TABLES');
// it do truncate all tables in database
   foreach($tables as $table){
      if ($table == 'migrations') {
          continue;
      }
      DB::table($table->Tables_in_portal_test)->truncate();
}

Remember you import

use Illuminate\Support\Facades\DB;

PD: Tables_in_YOUR_DATABASE_NAME