README

插入重复键更新是进行大量插入的一种快速方法。

这是一个旨在与Laravel的Eloquent ORM一起使用的特质。

代码示例

use Illuminate\Database\Eloquent\Model;
use Yadakhov\InsertOnDuplicateKey;

/**
 * Class User.
 */
class User extends Model
{
    // The function is implemented as a trait.
    use InsertOnDuplicateKey;
}

多值插入。

    $users = [
        ['id' => 1, 'email' => 'user1@email.com', 'name' => 'User One'],
        ['id' => 2, 'email' => 'user2@email.com', 'name' => 'User Two'],
        ['id' => 3, 'email' => 'user3@email.com', 'name' => 'User Three'],
    ];

重要：键的顺序很重要。每个数组应该相同。原因在于代码使用了 array_values()。

不要这样做

    $users = [
        ['id' => 1, 'email' => 'user1@email.com', 'name' => 'User One'],
        ['email' => 'user2@email.com', 'id' => 2, 'name' => 'User Two'],
        ['email' => 'user3@email.com', 'name' => 'User Three', 'id' => 3],
    ];

INSERT ON DUPLICATE KEY UPDATE

    User::insertOnDuplicateKey($users);

    -- produces this query
    INSERT INTO `users`(`id`,`email`,`name`) VALUES
    (1,'user1@email.com','User One'), (2,'user3@email.com','User Two'), (3,'user3email.com','User Three')
    ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `email` = VALUES(`email`), `name` = VALUES(`name`)

    User::insertOnDuplicateKey($users, ['email']);

    -- produces this query
    INSERT INTO `users`(`id`,`email`,`name`) VALUES
    (1,'user1@email.com','User One'), (2,'user3@email.com','User Two'), (3,'user3email.com','User Three')
    ON DUPLICATE KEY UPDATE `email` = VALUES(`email`)

如果我们希望对数字列进行求和，例如：

    $users = [
        ['id' => 1, 'name' => 'User One', 'heritage' => 1000],
        ['id' => 2, 'name' => 'User Two', 'heritage' => 2000],
        ['id' => 3, 'name' => 'User Three', 'heritage' => 1500],
    ];

    User::insertOnDuplicateKey($users, ['heritage' => DB::raw('`heritage` + VALUES(`heritage`)')]);

    -- produces this query
    INSERT INTO `users`(`id`,`email`,`name`) VALUES
    (1,'user1@email.com','User One'), (2,'user3@email.com','User Two'), (3,'user3email.com','User Three')
    ON DUPLICATE KEY UPDATE `heritage` = `heritage` + VALUES(`heritage`)

INSERT IGNORE

    User::insertIgnore($users);

    -- produces this query
    INSERT IGNORE INTO `users`(`id`,`email`,`name`) VALUES
    (1,'user1@email.com','User One'), (2,'user3@email.com','User Two'), (3,'user3email.com','User Three');

REPLACE INTO

    User::replace($users);

    -- produces this query
    REPLACE INTO `users`(`id`,`email`,`name`) VALUES
    (1,'user1@email.com','User One'), (2,'user3@email.com','User Two'), (3,'user3email.com','User Three');

created_at 和 updated_at 字段。

created_at 和 updated_at 将不会自动更新。要更新，可以在插入数组中传递字段。

['id' => 1, 'email' => 'user1@email.com', 'name' => 'User One', 'created_at' => Carbon::now(), 'updated_at' => Carbon::now()]

运行单元测试

./vendor/bin/phpunit

这会在Postgresql上工作吗？

不会。On Duplicate Key Update 只在MySQL上可用。Postgresql 9.4 有一个类似的功能，称为 UPSERT。实现UPSERT留作读者练习。

这和updateOrCreate()一样吗？

它们很相似但不完全一样。updateOrCreate() 将只会对一行数据进行操作，这不允许批量插入。InsertOnDuplicateKey将对多行数据起作用。

yadakhov / insert-on-duplicate-key

维护者

详细信息