php 使用 eloquent 截断 Laravel 中的所有表

truncate all tables in laravel using eloquent

提问by Mounir

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.

有没有一种方法可以使用 laravel 4 中的 eloquent 或 fluent 截断数据库中的所有表？我不想指定表名，我只想截断所有表。换句话说，清空所有表。

回答by Hao Luo

NOTE: doctrine/dbalPackage is Required for Performing this Operations

注意：doctrine/dbal执行此操作需要包

So Make Sure that is Installed composer require doctrine/dbal

所以请确保已安装 composer require doctrine/dbal

1. Get all the table names

1.获取所有表名

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

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

2. 循环遍历表名数组并使用 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

SQLSTATE[42000]：语法错误或访问冲突：1701 无法截断外键约束中引用的表

You Can disable foriegn Key Checks

您可以禁用外国密钥检查

Schema::disableForeignKeyConstraints();

and make sure to ReEnableit

并确保重新启用它

Schema::enableForeignKeyConstraints();

回答by ch271828n

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

这是我基于@Hao Luo 的回答。此外，它还具有以下优点：

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)

1. 你不需要安装任何额外的包（不需要学说）
2. 它很好地支持laravel 5
3. 它禁用外键约束（如果您在不关心订单的情况下截断并启用外键约束，则可能会出现错误）

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! :)

希望你喜欢！:)

回答by Ronald Perez

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

在 Laravel 5 中， migrate:fresh 将删除数据库中的所有表（即使表与 migrate 无关）

回答by Julien Moulin

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.

根据之前的答案，我将表名直接过滤到 SQL 查询中。我同意这是一个小的优化，但避免了不必要的循环。

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');
}

回答by Kevin Mendez

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;

使用 Illuminate\Support\Facades\DB;

PD: Tables_in_YOUR_DATABASE_NAME

PD：Tables_in_YOUR_DATABASE_NAME

回答by Mochamad Gufron Efendi

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();
    }