Laravel - 播种大型 SQL 文件

Laravel - seeding large SQL file

提问by olleh

A memory exhaustion happens when I run my DB seed script in production.

当我在生产中运行我的数据库种子脚本时会发生内存耗尽。

Below is my seed script.

下面是我的种子脚本。

class MembershipTableSeeder extends Seeder 
{
    public function run()
    {
        DB::table('members')->delete();

        foreach (range(1, 99) as $days){
            Members::create(array('membership_code' => 'test'.$days));
        }

        DB::unprepared(file_get_contents(app_path()."/database/seeds/members.sql"));
    }
}

So what I did was add a no-limit on my seed script.

所以我所做的是在我的种子脚本上添加一个无限制。

ini_set('memory_limit', '-1');

The problem now is that when I run the script it logs the output into the terminal the content of the SQL script (which is very, very big).

现在的问题是，当我运行脚本时，它会将 SQL 脚本的内容（非常非常大）记录到终端中。

Is there a good way of running a SQL dump inside my DB seeds that doesn't consume much memory? What I did now was run it manually:

有没有一种在我的数据库种子中运行 SQL 转储不消耗太多内存的好方法？我现在所做的是手动运行它：

mysql -uuser -p db < script.sql

回答by winkbrace

For others who prefer a more Laravel-ish solution, this is how I handled it:

对于更喜欢 Laravel-ish 解决方案的其他人，我是这样处理的：

/**
 * This class is responsible for running the data dump sql.
 * It is recommended to update this class instead of creating new ones for new database content dumps.
 */
class DatabaseDumpSeeder extends Seeder
{
    /**
     * Run the database seeds.
     * @throws \Exception
     */
    public function run()
    {
        // Note: these dump files must be generated with DELETE (or TRUNCATE) + INSERT statements
        $sql = file_get_contents(__DIR__ . '/dumps/dump-20150709.sql');

        if (! str_contains($sql, ['DELETE', 'TRUNCATE'])) {
            throw new Exception('Invalid sql file. This will not empty the tables first.');
        }

        // split the statements, so DB::statement can execute them.
        $statements = array_filter(array_map('trim', explode(';', $sql)));

        foreach ($statements as $stmt) {
            DB::statement($stmt);
        }
    }
}

回答by Vit Kos

The problem happens because when using Db::unprepared it also logs the query to the laravel.log file, making in background much more actions then you think, from this side you have memory exhaust. If you are not running the safe mode I would stick to executing the console command like this:

出现问题是因为在使用 Db::unprepared 时，它还会将查询记录到 laravel.log 文件，在后台执行比您想象的更多的操作，从这方面您会耗尽内存。如果您没有运行安全模式，我会坚持执行这样的控制台命令：

exec("mysql -u ".\Config::get('database.mysql.user')." -p".\Config::get('database.mysql.password')." ".\Config::get('database.mysql.database')." < script.sql")

回答by WHY

Create Seeder File "PostalCodeTableSeeder.php" in Project_directory/database/seeds

在 Project_directory/database/seeds 中创建 Seeder 文件“PostalCodeTableSeeder.php”

use Illuminate\Database\Seeder;

class PostalCodeTableSeeder extends Seeder {
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        // =============================================================
        // file Path -> Project/app/configs/database.php
        // get the database name, database username, database password
        // =============================================================
        $db     = \Config::get('database.connections.mysql.database');
        $user   = \Config::get('database.connections.mysql.username');
        $pass   = \Config::get('database.connections.mysql.password');

        // $this->command->info($db);
        // $this->command->info($user);
        // $this->command->info($pass);

        // running command line import in php code
        exec("mysql -u " . $user . " -p" . $pass . " " . $db . " < postal_codes.sql");
        // postal_codes.sql is inside root folder
    }
}

Also add the class name into Project_directory/database/seed/DatabaseSeeder.php like code below

还将类名添加到 Project_directory/database/seed/DatabaseSeeder.php 中，如以下代码

use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $this->call(PostalCodeTableSeeder::class);
        // $this->call(UsersTableSeeder::class);
    }
}