Laravel 8 如何为多个数据库批量执行迁移？

现有数据库配置 #

在config/database.php中已配置两个数据库连接：

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => '',
    'username' => env('DB_USERNAME',''),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => ''
],
'databaselist'=>[
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => 'databaselist',
    'username' => env('DB_USERNAME',''),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => ''
],

应用逻辑与需求 #

用户登录时，通过动态赋值mysql.database切换当前使用的数据库。现在需要为所有数据库执行迁移，计划遍历目标数据库列表，为mysql.database传入不同数据库名，批量执行迁移操作。

当前迁移代码 #

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateTestTable extends Migration
{
    public $connection ='mysql';
    
    public function up()
    {
        Schema::create('test_table', function (Blueprint $table) {
           $table->id();
           $table->string('name');
        });
    }

   
    public function down()
    {
        Schema::dropIfExists('test_table');
    }
}

实现方案 #

1. 创建批量迁移Artisan命令 #

生成新的Artisan命令：

php artisan make:command MigrateAllDatabases

替换命令文件内容为：

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Config;
use Illuminate\Database\Migrations\Migrator;

class MigrateAllDatabases extends Command
{
    protected $signature = 'migrate:all';
    protected $description = '为所有数据库执行迁移';

    public function handle(Migrator $migrator)
    {
        // 从databaselist连接获取所有数据库列表
        $databases = DB::connection('databaselist')->select('SHOW DATABASES');
        
        // 过滤系统数据库与databaselist本身
        $excludeDatabases = ['information_schema', 'mysql', 'performance_schema', 'sys', 'databaselist'];
        $targetDatabases = collect($databases)->pluck('Database')->filter(function ($db) use ($excludeDatabases) {
            return !in_array($db, $excludeDatabases);
        })->toArray();

        foreach ($targetDatabases as $database) {
            $this->info("开始迁移数据库: {$database}");
            
            // 动态修改mysql连接的数据库名
            Config::set('database.connections.mysql.database', $database);
            
            // 重置并重新连接mysql
            DB::purge('mysql');
            DB::connection('mysql')->reconnect();
            
            // 执行迁移
            $migrator->setConnection('mysql');
            $migrator->run(database_path('migrations'), ['--force' => true]);
            
            $this->info("数据库 {$database} 迁移完成");
        }

        $this->info('所有数据库迁移执行完毕');
    }
}

2. 优化迁移类（可选） #

为确保迁移使用动态切换后的连接，可在迁移类中明确指定连接：

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateTestTable extends Migration
{
    public $connection = 'mysql';
    
    public function up()
    {
        Schema::connection($this->connection)->create('test_table', function (Blueprint $table) {
           $table->id();
           $table->string('name');
        });
    }

    public function down()
    {
        Schema::connection($this->connection)->dropIfExists('test_table');
    }
}

3. 执行批量迁移 #

运行命令即可完成所有数据库的迁移：

php artisan migrate:all

注意事项 #

• 确保databaselist连接的数据库账号拥有所有目标数据库的操作权限
• 若需要批量回滚，可参照上述逻辑编写migrate:rollback:all命令，调用$migrator->rollback()方法
• 生产环境执行时，--force参数会跳过确认提示，如需二次确认可移除该参数并添加交互逻辑

内容的提问来源于stack exchange，提问作者Anonymous