一、使用migrate命令输出sql文件
php artisan migrate 可以在运行时添加标记–pretend以将查询输出到终端:
php artisan migrate --pretend
这看起来像这样:
Migration table created successfully.
CreateUsersTable: create table "users" ("id" integer not null primary key autoincrement, "name" varchar not null, "email" varchar not null, "password" varchar not null, "remember_token" varchar null, "created_at" datetime not null, "updated_at" datetime not null)
CreateUsersTable: create unique index users_email_unique on "users" ("email")
CreatePasswordResetsTable: create table "password_resets" ("email" varchar not null, "token" varchar not null, "created_at" datetime not null)
CreatePasswordResetsTable: create index password_resets_email_index on "password_resets" ("email")
CreatePasswordResetsTable: create index password_resets_token_index on "password_resets" ("token")
要将其保存到文件中,只需重定向不带ansi的输出:
该命令仅包含尚未迁移的迁移。
php artisan migrate --pretend --no-ansi > migrate.sql
入迁移命令
示例代码
$migrator = app('migrator');
$db = $migrator->resolveConnection(null);
$migrations = $migrator->getMigrationFiles('database/migrations');
$queries = [];
foreach($migrations as $migration) {
$migration_name = $migration;
$migration = $migrator->resolve($migration);
$queries[] = [
'name' => $migration_name,
'queries' => array_column($db->pretend(function() use ($migration) { $migration->up(); }), 'query'),
];
}
dd($queries);
示例输出
array:2 [
0 => array:2 [
"name" => "2014_10_12_000000_create_users_table"
"queries" => array:2 [
0 => "create table "users" ("id" integer not null primary key autoincrement, "name" varchar not null, "email" varchar not null, "password" varchar not null, "remember_token" varchar null, "created_at" datetime not null, "updated_at" datetime not null)"
1 => "create unique index users_email_unique on "users" ("email")"
]
]
1 => array:2 [
"name" => "2014_10_12_100000_create_password_resets_table"
"queries" => array:3 [
0 => "create table "password_resets" ("email" varchar not null, "token" varchar not null, "created_at" datetime not null)"
1 => "create index password_resets_email_index on "password_resets" ("email")"
2 => "create index password_resets_token_index on "password_resets" ("token")"
]
]
]
laravel 8.0 新增 迁移文件压缩
在应用迭代的过程中,随着时间的推移,可能会累积越来越多的迁移文件,这可能会导致迁移目录下包含上百个迁移文件而变得臃肿,如果需要的话,你可以将这些迁移文件压缩到单个 SQL 文件中。要使用这个功能,只需要执行 Artisan 命令 schema:dump 即可:
php artisan schema:dump
// 转储当前数据库表结构并清理所有已存在的迁移文件...
php artisan schema:dump --prune
二、sql语句和sql文件来创建执行数据库迁移
public function up()
{
$file = storage_path('install/sql/strongshop.v1.0.sql');
DB::unprepared($file);
}