insert on dup key update BULK

mysqlBuilder.php

<?php namespace [YOUR NAMESPACE HERE];

use Illuminate\Database\Eloquent\Model;
use Log;
use DB;

class MysqlBuilder {

    protected $table;
    protected $pdoPlaceholderLimit;
    protected $columnString;
    protected $updateString;

    public function __construct($table, $pdoPlaceholderLimit = '60000')
    {
        $this->table = $table;
        $this->pdoPlaceholderLimit = $pdoPlaceholderLimit;
    }

    public function chunk(array $valueCollection)
    {
        $valueList = head($valueCollection);

        return array_chunk($valueCollection, $this->getMaxChunkSize($valueList), true);
    }

    /**
     * Must call this before calling insertOrUpdate
     * @param array $columns
     */
    public function setColumns(array $columns)
    {
        $this->columnString = $this->buildColumnString($columns);
        $this->updateString = $this->buildUpdateString($columns);
    }

    /**
     * Execute the INSERT ON DUPLICATE KEY UPDATE mysql statement
     * @param array $valueChunk
     * @return int
     */
    public function insertOrUpdate(array $valueChunk)
    {
        $valuePlaceholders = $this->buildValuePlaceholderString($valueChunk);
        $bindings = array_flatten($valueChunk);

        $query = "INSERT INTO {$this->table} ({$this->columnString}) VALUES $valuePlaceholders ON DUPLICATE KEY UPDATE {$this->updateString}";

        return DB::affectingStatement($query, $bindings);
    }

    /**
     * @return string
     */
    protected function buildColumnString(array $columns)
    {
        return '`' . implode('`,`', $columns) . '`';
    }

    /**
     * @return string
     */
    protected function buildUpdateString(array $columns)
    {
        $updates = '';
        foreach($columns as $column){
            $updates .= "`$column`=VALUES(`$column`),";
        }
        return rtrim($updates, ',');
    }

    /**
     * @param array $valueCollection  a chunk of values to build placeholders for
     * @return string
     */
    protected function buildValuePlaceholderString(array $valueCollection)
    {
        $placeholder = '';
        foreach ($valueCollection as $attributes) {
            $placeholder .= '(' . str_repeat("TRIM(?),", count($attributes));
            $placeholder = rtrim($placeholder, ',');
            $placeholder .= '),';
        }

        return rtrim($placeholder, ',');
    }

    protected function getMaxChunkSize(array $valueList)
    {
        // round the number down by casting to an int
        return (int) ($this->pdoPlaceholderLimit / count($valueList));
    }
}

<?php

namespace App\Traits;

use DB;
use Log;
use BadMethodCallException;
use Illuminate\Database\Eloquent\Model;

trait QueryBuilder
{
    protected static $columnString;
    protected static $updateString;

    /**
     * @var Model
     */
    protected static $model;

    /**
     * Execute the INSERT ON DUPLICATE KEY UPDATE mysql statement
     *
     * <code>
     * Model::insertOrUpdate(
     *     [
     *         'field1'     => 1,
     *         'field2'     => 'value',
     *         'created_at' => 'SQL:NOW()',
     *     ],
     *     [
     *         'field2'     => 'value',
     *         'updated_at' => 'SQL:NOW()',
     *     ]
     * );
     * </code>
     *
     * @param array $insert INSERT key => value pairs
     * @param array $update UPDATE key => value pairs
     * @return int Affected rows
     */
    public static function insertOrUpdate(array $insert, array $update)
    {
        if (empty($insert) || empty($update)) {
            return 0;
        }

        self::initModel();

        $insertKeys = [];
        $updateKeys = [];
        $bindings   = [];

        foreach ($insert as $k => $v) {
            if (strpos($v, 'SQL:') === 0) {
                $insertKeys[] = "`{$k}` = " . trim(str_replace('SQL:', '', $v));
            } else {
                $insertKeys[] = "`{$k}` = :i_{$k}";
                $bindings[":i_{$k}"] = $v;
            }
        }

        foreach ($update as $k => $v) {
            if (strpos($v, 'SQL:') === 0) {
                $updateKeys[] = "`{$k}` = " . trim(str_replace('SQL:', '', $v));
            } else {
                $updateKeys[] = "`{$k}` = :u_{$k}";
                $bindings[":u_{$k}"] = $v;
            }
        }

        $sql = strtr(
            /** @lang text */
            'INSERT INTO `:table` SET :insert ON DUPLICATE KEY UPDATE :update',
            [
                ':table'  => self::$model->getTable(),
                ':insert' => implode(', ', $insertKeys),
                ':update' => implode(', ', $updateKeys)
            ]
        );

        Log::debug(DB::raw($sql), $bindings);

        return DB::affectingStatement(DB::raw($sql), $bindings);
    }

    protected static function initModel()
    {
        if (!self::$model) {
            $modelClass = get_called_class();

            /** @var Model $model */
            $model = new $modelClass;

            if (!$model instanceof Model) {
                throw new BadMethodCallException(
                    sprintf(
                        'It is unacceptable to use %s for objects which do not implement %s',
                        __METHOD__,
                        'Illuminate\Database\Eloquent\Model'
                    )
                );
            }

            self::$model = $model;
        }
    }
}