数据库播种-在一个查询中插入多个记录 [英] Database seeding - insert multiple records in one query

问题描述

我有一个Laravel项目，我想将900多个城市插入数据库中作为种子.

例如，我可以这样:

$state_id = State::whereName('state name')->pluck('id');

$city = new City();
$city->name = 'my city name';
$city->state_id = $state_id;
$city->save();

在我的城市模型中，我将另存为:

public function save(array $options = array()) {
    $this->url = $this->createUrl($this->name);
    parent::save($options);
}

因此它还会创建城市的网址.

我可以放置900倍的此类代码块，但是有一个问题-它会在单独的查询中运行，因此将这些数据插入数据库需要30秒钟以上.

例如，我可以这样:

DB::table('cities')->insert(
    [
        [
            'name' => 'City name',
            'url' => Slug::create('City name'),
            'created_at' => $now,
            'updated_at' => $now,
            'state_id' => $state_id
        ],
        [
            'name' => 'City name 2',
            'url' => Slug::create('City name 2'),
            'created_at' => $now,
            'updated_at' => $now,
            'state_id' => $state_id
        ],
    ]);

通过这种方式，我可以在一个SQL查询中插入许多记录，但是我认为这不是一个很好的解决方案-我需要手动设置所有数据库字段，但是只需要3-4秒即可将所有数据插入数据库. /p>

问题是-是否可以创建模型并使用某种魔术方法返回准备就绪的PHP数组以在多重插入中使用它(我读到Eloquent不能用于在一个查询中插入多个记录)?

我认为更好的代码是这样的:

$state_id = State::whereName('state name')->pluck('id');

$city = new City();
$city->name = 'my city name';
$city->state_id = $state_id;
$city1 = $city->saveFake(); // magic method that returns complete array

$city = new City();
$city->name = 'my city name';
$city->state_id = $state_id;
$city2 = $city->saveFake(); // magic method that returns complete array

DB::table('cities')->insert(
    [
        $city1,
        $city2,
    ]);

解决方案

$cities = [];
$cities[] = new City(...);
$cities[] = new City(...);
$table = with(new City)->getTable();
$data = array_map(function($city) {
    return $city->getAttributes();
}, $cities);
DB::table($table)->insert($data);

getAttributes返回插入到数据库中的原始"基础属性.

请记住，这将绕过诸如保存/创建之类的口才事件.

还请记住，如果$data变大，则可能会遇到内存问题.在这种情况下，请使用array_chunk()之类的方法将其拆分.

I have a Laravel project where I would like to insert above 900 cities into database as database seeding.

For example I could do it this way:

$state_id = State::whereName('state name')->pluck('id');

$city = new City();
$city->name = 'my city name';
$city->state_id = $state_id;
$city->save();

and in my City model I have defined save as:

public function save(array $options = array()) {
    $this->url = $this->createUrl($this->name);
    parent::save($options);
}

so it creates also url for the city.

I can put 900 times such block of codes but there is one problem - it will run in separate queries, so it will take above 30 seconds to insert those data to database.

I can do it for example this way:

DB::table('cities')->insert(
    [
        [
            'name' => 'City name',
            'url' => Slug::create('City name'),
            'created_at' => $now,
            'updated_at' => $now,
            'state_id' => $state_id
        ],
        [
            'name' => 'City name 2',
            'url' => Slug::create('City name 2'),
            'created_at' => $now,
            'updated_at' => $now,
            'state_id' => $state_id
        ],
    ]);

and this way I can insert many records in one SQL query but in my opinion it's not very nice solution - I need to manually set all database fields but it takes only 3-4 seconds to insert all the data into database.

The question is - is it possible to create models and using some magic method return ready PHP array to use it in multpile inserts ( I read that Eloquent cannot be used to insert multiple records in one query) ?

I think much better would be code something like this:

$state_id = State::whereName('state name')->pluck('id');

$city = new City();
$city->name = 'my city name';
$city->state_id = $state_id;
$city1 = $city->saveFake(); // magic method that returns complete array

$city = new City();
$city->name = 'my city name';
$city->state_id = $state_id;
$city2 = $city->saveFake(); // magic method that returns complete array

DB::table('cities')->insert(
    [
        $city1,
        $city2,
    ]);

解决方案

$cities = [];
$cities[] = new City(...);
$cities[] = new City(...);
$table = with(new City)->getTable();
$data = array_map(function($city) {
    return $city->getAttributes();
}, $cities);
DB::table($table)->insert($data);

getAttributes returns the "raw" underlying attributes that are inserted into the database.

Keep in mind that this will bypass Eloquent events like saving/creating.

Also keep in mind that if $data becomes to big, you might run into memory issues. Use something like array_chunk() to split it up if that's the case.

这篇关于数据库播种-在一个查询中插入多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！