Laravel 10 迁移日志增强需求：补充执行时间与SQL语句记录

需求1：记录迁移执行时间（为migrations表添加created_at字段） #

默认migrations表仅记录迁移文件名和批次，无执行时间信息。可通过以下步骤添加created_at字段：

1. 创建新迁移文件：

php artisan make:migration add_created_at_to_migrations_table

2. 编写迁移逻辑：

<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('migrations', function (Blueprint $table) {
            // 添加created_at字段，默认值为当前时间，兼容旧迁移记录
            $table->timestamp('created_at')->useCurrent()->nullable();
        });
        
        // 可选：为旧迁移记录批量填充created_at值
        \Illuminate\Support\Facades\DB::table('migrations')
            ->whereNull('created_at')
            ->update(['created_at' => now()]);
    }

    public function down(): void
    {
        Schema::table('migrations', function (Blueprint $table) {
            $table->dropColumn('created_at');
        });
    }
};

3. 执行迁移：

php artisan migrate

需求2：记录实际SQL语句及受影响行数 #

--pretend参数无法保留占位符的实际值，因此需要手动捕获执行的SQL和受影响行数，建议创建专门的日志表存储信息：

步骤1：创建迁移日志表 #

生成创建migration_logs表的迁移：

php artisan make:migration create_migration_logs_table

编写迁移逻辑：

<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('migration_logs', function (Blueprint $table) {
            $table->id();
            // 关联migrations表的记录ID
            $table->foreignId('migration_id')->constrained()->onDelete('cascade');
            // 存储完整可执行的SQL语句
            $table->text('sql_statement');
            // 记录操作影响的数据行数
            $table->integer('affected_rows')->default(0);
            $table->timestamps();
        });
    }

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

执行该迁移：

php artisan migrate

步骤2：在自定义迁移中添加日志记录逻辑 #

在需要记录SQL的迁移类中，手动捕获操作的SQL和受影响行数，写入日志表。示例如下：

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use App\Models\Post;

return new class extends Migration
{
    public function up(): void
    {
        // 1. 定义查询并捕获基础信息
        $query = Post::where('id', '<', 1000);
        $rawSql = $query->toSql();
        $bindings = $query->getBindings();
        
        // 替换占位符为实际值，生成完整SQL
        $fullSql = str_replace('?', '%s', $rawSql);
        $fullSql = vsprintf($fullSql, array_map(function ($binding) {
            return is_string($binding) ? "'" . addslashes($binding) . "'" : $binding;
        }, $bindings));
        
        // 执行操作并获取受影响行数
        $affectedRows = $query->delete();

        // 2. 获取当前迁移记录的ID
        $currentMigration = DB::table('migrations')
            ->where('migration', class_basename($this))
            ->latest()
            ->first();

        // 3. 写入日志表
        DB::table('migration_logs')->insert([
            'migration_id' => $currentMigration->id,
            'sql_statement' => $fullSql,
            'affected_rows' => $affectedRows,
            'created_at' => now(),
            'updated_at' => now(),
        ]);
    }

    public function down(): void
    {
        // 回滚操作时可复用相同逻辑记录日志
        $query = Post::onlyTrashed()->where('id', '<', 1000);
        $rawSql = $query->toSql();
        $bindings = $query->getBindings();
        
        $fullSql = str_replace('?', '%s', $rawSql);
        $fullSql = vsprintf($fullSql, array_map(function ($binding) {
            return is_string($binding) ? "'" . addslashes($binding) . "'" : $binding;
        }, $bindings));
        
        $affectedRows = $query->restore();

        $currentMigration = DB::table('migrations')
            ->where('migration', class_basename($this))
            ->latest()
            ->first();

        DB::table('migration_logs')->insert([
            'migration_id' => $currentMigration->id,
            'sql_statement' => $fullSql,
            'affected_rows' => $affectedRows,
            'created_at' => now(),
            'updated_at' => now(),
        ]);
    }
};

批量操作的简化日志记录 #

如果迁移中有多个数据库操作，可封装辅助函数简化代码：

private function logMigrationQuery($query, $migrationId)
{
    $rawSql = $query->toSql();
    $bindings = $query->getBindings();
    
    $fullSql = str_replace('?', '%s', $rawSql);
    $fullSql = vsprintf($fullSql, array_map(function ($binding) {
        return is_string($binding) ? "'" . addslashes($binding) . "'" : $binding;
    }, $bindings));
    
    // 根据操作类型调整获取受影响行数的方式
    $affectedRows = method_exists($query, 'delete') ? $query->delete() : $query->update([]);

    DB::table('migration_logs')->insert([
        'migration_id' => $migrationId,
        'sql_statement' => $fullSql,
        'affected_rows' => $affectedRows,
        'created_at' => now(),
        'updated_at' => now(),
    ]);
}

在迁移的up方法中调用：

public function up(): void
{
    $currentMigration = DB::table('migrations')
        ->where('migration', class_basename($this))
        ->latest()
        ->first();
    $migrationId = $currentMigration->id;

    // 操作1：删除指定Post
    $this->logMigrationQuery(Post::where('id', '<', 1000), $migrationId);

    // 操作2：更新指定User状态
    $this->logMigrationQuery(User::where('status', 'inactive')->update(['status' => 'archived']), $migrationId);
}

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