1

Ok I am doing a batch upsert in Laravel and it based on the matching field not being the primary key, however it is unique.

 $array_to_insert = [];

        foreach ($array as $account) {

            $record_array = [];

            $record_array['name'] = $account['name'];
            $record_array['coingecko_id'] = $account['id'];
            $record_array['identifier'] = strtoupper($account['symbol']);
            $record_array['current_price'] = $account['current_price'];
            $record_array['market_cap'] = $account['market_cap'];
            $record_array['market_cap_rank'] = $account['market_cap_rank'];
            $record_array['type'] = "cryptocurrency";

            array_push($array_to_insert,  $record_array);

        }

        $result = CryptoAccount::upsert($array_to_insert, ['identifier'], ['name','coingecko_id','identifier','current_price','market_cap','market_cap_rank']);

        var_dump($result);

It is looking throught about 350 results a time and the auto_increment value is going up by the same even though it is only updating most rows and not inserting them.

ORStudios
  • 3,157
  • 9
  • 41
  • 69
  • When you say `the auto_increment value is going up by the same`, do you mean it is inserting rather than updating? – Peppermintology May 10 '21 at 15:43
  • 1
    I don't think this is a Laravel thing. It will try to insert first, consuming the auto incremental value. Then after the insert fails and the value is already consumed, it tries to perform an update. Here's a little [demo](http://www.sqlfiddle.com/#!9/cc32a6/1) using `INSERT ... ON DUPLICATE KEY UPDATE`. – El_Vanja May 10 '21 at 15:45
  • @El_Vanja ahh ok that makes sense thanks, shame it can't keep the value. Uniflux sorry, no I mean that even when the row is updated it is still incrementing the auto increment value so that when a new row is instead the gap between the pk values is quite large. – ORStudios May 10 '21 at 15:54
  • Seems like this issue is covered [here](https://stackoverflow.com/questions/23516958/on-duplicate-key-auto-increment-issue-mysql) – ORStudios May 10 '21 at 15:57
  • Worst case, if you really wanna use this, is that you could find out what the current auto_increment is (maybe order by id desc, then limit 1, and add 1), then after your upsert, just run `DB::statement("ALTER TABLE table SET AUTO_INCREMENT = $newValue")`. $newValue obviously needs to take into account how many were added during the insert part. – JustCarty May 10 '21 at 16:20

0 Answers0