yadakhov / insert-on-duplicate-key

MySQL Insert On Duplicate Key Update Eloquent Trait

Insert Duplicate Key Update is a quick way to do mass insert.

It's a trait meant to be used with Laravel's Eloquent ORM.

Code Example

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

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

Multi values insert.

    $users = [
        ['id' => 1, 'email' => '[email protected]', 'name' => 'User One'],
        ['id' => 2, 'email' => '[email protected]', 'name' => 'User Two'],
        ['id' => 3, 'email' => '[email protected]', 'name' => 'User Three'],
    ];

Important: the order of the keys are important. It should be the same for every arrays. The reason is the code uses array_values().

Do not do this:

    $users = [
        ['id' => 1, 'email' => '[email protected]', 'name' => 'User One'],
        ['email' => '[email protected]', 'id' => 2, 'name' => 'User Two'],
        ['email' => '[email protected]', 'name' => 'User Three', 'id' => 3],
    ];

INSERT ON DUPLICATE KEY UPDATE

    User::insertOnDuplicateKey($users);

    -- produces this query
    INSERT INTO `users`(`id`,`email`,`name`) VALUES
    (1,'[email protected]','User One'), (2,'[email protected]','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,'[email protected]','User One'), (2,'[email protected]','User Two'), (3,'user3email.com','User Three')
    ON DUPLICATE KEY UPDATE `email` = VALUES(`email`)

If users have a numeric column we would like, for example, to sum:

    $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,'[email protected]','User One'), (2,'[email protected]','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,'[email protected]','User One'), (2,'[email protected]','User Two'), (3,'user3email.com','User Three');

REPLACE INTO

    User::replace($users);

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

created_at and updated_at fields.

created_at and updated_at will not be updated automatically. To update you can pass the fields in the insert array.

['id' => 1, 'email' => '[email protected]', 'name' => 'User One', 'created_at' => Carbon::now(), 'updated_at' => Carbon::now()]

Run unit tests

./vendor/bin/phpunit

Will this work on Postgresql?

No. On Duplicate Key Update is only available on MySQL. Postgresql 9.4 has a similar feature called UPSERT. Implementing UPSERT is left as an exercise for the reader.

Isn't this the same as updateOrCreate()?

It is similar but not the same. The updateOrCreate() will only work for one row at a time which doesn't allow bulk insert. InsertOnDuplicateKey will work on many rows.

insert-on-duplicate-key's Issues

insertOnDuplicateKey $updateColumns parameter doesn't allow strings

The insertOnDuplicateKey function works great, except when trying to pass strings in the $updateColumns parameter, as the resulting SQL doesn't enclose the value in quotes to treat them as strings:

SponsoredCallWallet::insertOnDuplicateKey( [ 'msisdn' => $msisdn->toStrict(), 'client_id' => ServiceRequest::client()->id, 'sponsoredcall_campaign_id' => $campaign->id, 'balance' => $duration, 'created_at' => Carbon::now()->format( 'Y-m-d H:i:s' ), 'updated_at' => Carbon::now()->format( 'Y-m-d H:i:s' ), ], [ 'updated_at' => Carbon::now()->format( 'Y-m-d H:i:s' ), 'balance' => 'balance + ' . $duration, ] );

Is there something I'm doing wrong?

Incremental values on UPDATE

Hi there,

What would be really amazing would be to allow incremental values on update, something like this INSERT:

INSERT INTO table 
  (a, counter_elem) 
VALUES 
  (1, 1),
  (2, 1)
ON DUPLICATE KEY UPDATE counter_elem = counter_elem + 1;

In this example, with counter_elem incrementing by one on each duplicate. In my project, I have an aggregation table that aggregates the values of a transaction table. If your function were able to indicate that a value being passed for update was potentially incrementable, that would be amazing !

Do not have any handling for timestamps

First, it is a very useful function for laravel. Just save my life.
But, when i using this function, i found that there are no handling for timestamps.
For example: created_at,updated_at
So, if we have the improved version. i would say this is one of crucial part for further development.

Punctuated strings

Hi @yadakhov!
Thanks for your great work!

I'm trying to store some strings to a database but, in some cases, I need a punctuated version of the same string to be inserted to a table.

An example could be a string like 'cascos para niños' and 'cascos para ninos'. I need both of them to be inserted in a table but, currently, they are considered to be equal preventing them to be inserted twice as different values.

Is there a way I could store them both?

Thanks!

Possible SQL injection

Source: https://github.com/yadakhov/insert-on-duplicate-key/blob/master/src/InsertOnDuplicateKey.php#L207

Example: insertOnDuplicateKey(..., [ 'col' => 'value' ])

This just sticks 'value' straight into the SQL without sanitisation:
ON DUPLICATE KEY UPDATE col = value

@yadakhov

does not support sqlite for insert ignore

SQLite uses the syntax

INSERT OR IGNORE instead of INSERT IGNORE

Support REPLACE INTO

Since we have INSERT IGNORE and INSERT.. ODKU and we might as well have the trifecta and support REPLACE

http://dev.mysql.com/doc/refman/5.7/en/replace.html

version 1.x

Please, tag version 1.x
composer does not want to download the new version in tags 0.x.x

Only update certain columns instead of all.

There are some cases where you wouldn't want to update all columns.

Low performance with not too big insert datas

Here's a test to fetch 500 rows which has 20 columns from a table, then insert them back without any changes:

$datas = App\SomeModel->limit(500)->get()->toArray();
App\SomeModel::insertOnDuplicateKey($datas);

The laravel-debugbar's timeline shows that inserting data spend ~250ms to excute while the insert sql just excute for ~30ms

It's seems that the ~200ms overheads comes from excuting Connection->affectingStatement(): https://github.com/illuminate/database/blob/0e126944cf0fb3da8fa456fefc34dcf65e80181d/Connection.php#L469 and it's just doing PDO value binding then to excute them.

So does this overheads produced by PDO or the Eloquent itself?

How to fill time field data in table?

The table contains 'create_time' and 'update_time' fields, how to automatically fill the data of these two fields?

Support table prefixed.

Noticed a few people forking this repo and adding a table prefix.

I'll incorporate this feature to the repo to save people from forking.

Slow for more than a certain number of records

Thanks for a great library - does exactly what I need!

I have an interesting issue: when I insertIgnore, insertReplace or insertOnDuplicateKey for more than approximately 7200 records with 9 columns, then it suddenly starts timing out.

Less than that number of rows or columns completes very quickly (<5 seconds). Above that number suddenly starts timing out - and increasing the PHP max execution time still had no effect when set to 10 minutes (still timing out); then I figured something else must be wrong.

I solved it in this way:

$videoLogArrayChunks = array_chunk($videoLogArray, 3000);
foreach ($videoLogArrayChunks as $chunk) {
   VideoLog::insertIgnore($chunk);
}

Not sure if it's an issue or something else that I did incorrectly.

Return Affected Rows

Would it be possible to somehow grab the number of rows affected? At the moment all the methods return a bool whereas I'd like to know if an INSERT was actually done or ignored. It would be easy to use Laravel's affectingStatement() method, but that would change the API of your code.

Would it be possible to make this breaking change and bump the version number?

Or possibly save the number of affected rows and add a method to grab that value.

I should be able to do the work if you like.

Support INSERT IGNORE

Since we have INSERT ON DUPLICATE KEY we might as well support INSERT IGNORE.

http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update

INSERT IGNORE will simply ignore the rows where the primary key already exists.

Support unique keys

The INSERT ... ON DUPLICATE KEY syntax also works with unique keys (instead of primary keys) but your code doesn't because of the primary key check (checkPrimaryKeyExists).

Anyway to get this to work with the query builder instead of a model?

I'd like to be able to use this with DB::table('my-table')->insertOnDuplicateKey($data);, is that possible? I'm not sure how to make the DB::class use a trait.

Use the connection that was set on the model instance

Copied from: #7

@yadakhov Thank you for creating this plugin.

Too bad the non default connection is not read from the instance of the model.

\App\Model::on('otherconnection')->getModel()->insertOnDuplicateKey([[....]]); // will still use the default connection

when rewriting the function to a non static, the instance settings are available.

public function insertOnDuplicateKey(array $data, array $updateColumns = null)
    {
        if (empty($data)) {
            return false;
        }

        // Case where $data is not an array of arrays.
        if (!isset($data[0])) {
            $data = [$data];
        }

        $sql = self::buildInsertOnDuplicateSql($data, $updateColumns);

        $data = self::inLineArray($data);

        return $this->getConnection()->affectingStatement($sql, $data);
    }

SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders

Hey @yadakhov I guess I pushed too hard insert-on-duplicate-key, cause apparently I hit the limit of MySQL. 😅
I'm just posting this here mainly for maybe somebody else who could get carried away with fast bulk insert/updates like me.

I tried to insert ~5,800 rows of 32 columns at once and I got the above error. It seems it's due to the place holders limit of MySQL.

Don't know if you want to, but basically I believe it's possible to fix this limiting the length of the inline $data array generating the SQL statement. You could split the array and iterate through it in slots of less than ~65,000 placeholders, or maybe simply raise an Exception for "too much data". It seems in fact Eloquent (v5.5 in my case) somehow fails to raise an appropriate Exception and keeps trying forever.

BTW I fixed the issue limiting my data to ~2,000 rows and it works great, super fast! 🚀

Order of data key => value pairs

I think the documentation should mention that the order of key value pairs is important. If it's different for one record, insert/update will yield unexpected results. For example:

    $users = [
        ['id' => 1, 'email' => '[email protected]', 'name' => 'User One'],
        ['email' => '[email protected]', 'id' => 2, 'name' => 'User Two'],
        ['email' => '[email protected]', 'name' => 'User Three', 'id' => 3],
    ];

Each array has the same keys but in different order. After array_values runs, the keys turn into integer indexes, which disregard which value was attached to which key. I think this should be either noted in the docs or dealt with in the code (now I run ksort on each array).

use Model->connection instead of Illuminate\Support\Facades\DB;

Maybe it would be better to use Model class property $connection instead of Facade.

For example I am using Eloquent with Slim framework and instead of using Illuminate\Support\Facades\DB I have Illuminate\Database\Capsule\Manager as DB (http://stackoverflow.com/a/29500701) but if this trait tries to use Support\Facades\DB i'm in a trouble.

this all is not necessary as Model itself is aware of its connection ($connection) so it would be better to use it instead of having direct dependency to Illuminate\Support\Facades package

how to work with uuid

While mass insert i need to generate uuid for each row. as in my database i doesn't have autoincrement id

More complex update logic

Hi, sometimes I need to perform some "complex" logic instead of a simple update in a ON DUPLICATE KEY query, for example

ON DUPLICATE KEY UPDATE
`counter` = `counter` + VALUES(`counter`)

How can I achieve that?

Something like this will be a good solution imho:

Model::insertOnDuplicateKey($data, [
    'counter' => 'sum',
    'last_name' => 'replace,
]); @

preg_replace(): Parameter mismatch, pattern is a string while replacement is an array

Hello! Thanks for this package. I wait it a long time)
If I pass associative array to insertIgnore method

            App\Models\Page::insertIgnore([
                'page1' => [
                    'search_request_id' => 1,
                    'url' => 'url1',
                    'position' => 0,
                    'hash' => 'hash1',
                ],
                'page2' => [
                    'search_request_id' => 1,
                    'url' => 'url2',
                    'position' => 0,
                    'hash' => 'hash3',
                ],
            ]);

or such array

            App\Models\Page::insertIgnore([
                4 => [
                    'search_request_id' => 1,
                    'url' => 'url1',
                    'position' => 0,
                    'hash' => 'hash1',
                ],
                5 => [
                    'search_request_id' => 1,
                    'url' => 'url2',
                    'position' => 0,
                    'hash' => 'hash3',
                ],
            ]);

it throws me exception pointed in the issue title. I don't know about associative array: if it bug or not. But when I do such loop

                    foreach ($pages->chunk(100) as $chunk) {
                        Page::insertIgnore($chunk->toArray());
                    }

on second iteration $chunk->toArray() pass next array to insertIgnore

    [
        101 => [...],
        102 => [...],
    ]

If I use standart Eloquent insert() instead of insertIgnore() exception is not thrown. I think it is a bug in insertIgnore() and other methods.

Multiple Scheme?

This trait doesn't support multiple scheme, i change it but i can't open pull req. ,

I change

$sql = 'REPLACE INTO `' . static::getTablePrefix()....

To :

protected static function buildReplaceSql(array $data,$scheme=null)
     {

    $first = static::getFirstRow($data);
        if($scheme == null)
             $scheme = env('DB_DATABASE');

  $sql  = "REPLACE INTO `".$scheme.'`.`' . static::getTablePrefix()...

so i forked HERE

Extending Your Class

Hello. I had the need to work with json in mysql and in the case of the insert I needed to use json_merge. I removed the array validation from the $ updateColumns variable and changed the buildInsertOnDuplicateSql method to:

protected static function buildInsertOnDuplicateSql(array $data, $updateColumns = null)
{
$first = static::getFirstRow($data);

$sql = 'INSERT INTO `' . static::getTablePrefix() . static::getTableName() . '`(' . static::getColumnList($first) . ') VALUES' . PHP_EOL;

$sql .= static::buildQuestionMarks($data) . PHP_EOL;

$sql .= 'ON DUPLICATE KEY UPDATE ';

if (empty($updateColumns)) {
$sql .= static::buildValuesList($first);
} else if(is_array($updateColumns)) {
$sql .= static::buildValuesList(array_combine($updateColumns, $updateColumns));
} else if(is_string($updateColumns)) {
$sql .= $updateColumns;
}

return $sql;
}

Thanks for code. I love you hahahah

Creating wrong record

Hi,
I noticed that in some particular cases the insertOnDuplicateKey method creates a wrong record in the table. This is an example of input with the table schema, at the very bottom.
Simply inserting the data, starting from an empty table,

AwnRecord::insertOnDuplicateKey($records);

results in the insertion of only 50 records (out of 92 as input) one of which is totally wrong and even invalid, i.e. the is_valid_solarRad field, defined as boolean, is equal to 127 and tstamp, a datetime field, equals 0000-00-00 00:00:00 (which I believe it's not a valid datetime value)

What's wrong with the code/data?
How can the database even allow for creating an invalid type field?
If of any relevance, I noticed the not inserted records are mostly empty, although null is a valid value for those columns (and relevant data in my case).