Aktualizace většího množství dat

CREATE TABLE new_prices_tmp  (id INT PRIMARY KEY, price DECIMAL);
INSERT INTO new_prices_tmp VALUES (1, 10), (5, 13), (15, 34);
UPDATE prices p JOIN new_prices_tmp t ON p.id = t.id SET p.price = t.price;
DROP TABLE new_prices_tmp;

/**
* Creates MySQL multi update and if necessary, split it into multiple queries according to the maximum length.
* @param  string Table name
* @param  array Columns names ['key_column' => 'value_column'] or ['key_column' => ['value_column_1', 'value_column_2']]
* @param  array Data collection, each row must have the same shape names.
* @param  callable Function for escape input data. In this implementation is used same function for all data types.
* @param  int Optional parameter. If specified, the resulting query will be divided into multiple queries with a maximum length.
* @return string | array Result update query.
*/
function buildUpdateArray($table, array $names, array $data, callable $fceEscape, $maxQueryLength = null) {
    if (count($data) == 0) {
        throw new Exception('Input array is empty.');
    }
    list($keyColumnName, $columnNames) = each($names);
    if (!is_array($columnNames)) {
        $columnNames = [$columnNames];
    }

    $update = sprintf('update `%s` t join', $table);
    $on = sprintf(' on t.`%s` = f.`%s`', $keyColumnName, $keyColumnName);

    $setStats = [];
    foreach ($columnNames as $columnName) {
        $setStats[] = sprintf('t.`%s` = f.`%s`', $columnName, $columnName);
    }
    $set = ' set '.implode(', ', $setStats);

    $updateSize = strlen($update) + strlen($on) + strlen($set);
    $subQueryParts = [];
    $joinInnerSelects = function($innerSelect) { return ' ('.implode(' union all ', $innerSelect).') f'; };

    $innerSelect = [];
    $subQuerySize = -6; // strlen(' (') + strlen(') f') - strlen(' union all ')
    foreach ($data as $key => $values) {
        $valueKeyPairs = [];
        foreach ($columnNames as $index => $columnName) {
            if ($index > 0 && count($values) <= $index) {
                throw new Exception('Missing values to build a query.');
            }
            $value = is_array($values) ? $values[$index] : $values;
            $valueKeyPairs[] = sprintf('%s `%s`', $fceEscape($value), $columnName);
        }
        $selectQuery = sprintf('(select %s `%s`, %s)', $fceEscape($key), $keyColumnName, implode(', ', $valueKeyPairs));
        $currentSize = strlen($selectQuery) + 11;
        if ($maxQueryLength && ($updateSize + $subQuerySize + $currentSize > $maxQueryLength)) {
            $subQueryParts[] = $joinInnerSelects($innerSelect);
            $innerSelect = [];
            $subQuerySize = -6;
        }
        if ($maxQueryLength && ($updateSize + $subQuerySize + $currentSize > $maxQueryLength)) {
            throw new Exception('Given max query size is too small.');
        }
        $innerSelect[] = $selectQuery;
        $subQuerySize += $currentSize;
    }
    if (count($innerSelect) > 0) {
        $subQueryParts[] = $joinInnerSelects($innerSelect);
    }

    if ($maxQueryLength) {
        $updates = [];
        foreach ($subQueryParts as $subQuery)  {
            $updates[] = $update.$subQuery.$on.$set;
        }
        return $updates;
    } else {
        $update .= $subQueryParts[0];
        $update .= $on;
        $update .= $set;
        return $update;
    }
}

$fceEscape = function($expr) { return '\''.addslashes($expr).'\''; /* doplnit spravnou funkci! */ };

echo buildUpdateArray('num', ['id' => 'num'], [21 => 118], $fceEscape), PHP_EOL;

update `num` t join ((select '21' `id`, '118' `num`)) f on t.id = f.id set t.num = f.num

$data = [
    15 => [118, 'czk', 300],
    13 => [230, 'eur', 200],
];
echo buildUpdateArray('offers', ['id' => ['price', 'currency', 'offers_image_id']], $data, $fceEscape), PHP_EOL;

update `offers` t join ((select '15' `id`, '118' `price`, 'czk' `currency`, '300' `offers_image_id`) union all (select '13' `id`, '230' `price`, 'eur' `currency`, '200' `offers_image_id`)) f on t.id = f.id set t.price = f.price, t.currency = f.currency, t.offers_image_id = f.offers_image_id

$data = array_combine(range(1, 50), range(50, 1, -1));
print_r(buildUpdateArray('test', ['id' => ['num']], $data, $fceEscape, 500));

Array
(
    [0] => update `test` t join ((select '1' `id`, '50' `num`) union all (select '2' `id`, '49' `num`) union all (select '3' `id`, '48' `num`) union all (select '4' `id`, '47' `num`) union all (select '5' `id`, '46' `num`) union all (select '6' `id`, '45' `num`) union all (select '7' `id`, '44' `num`) union all (select '8' `id`, '43' `num`) union all (select '9' `id`, '42' `num`) union all (select '10' `id`, '41' `num`) union all (select '11' `id`, '40' `num`)) f on t.id = f.id set t.num = f.num
    [1] => update `test` t join ((select '12' `id`, '39' `num`) union all (select '13' `id`, '38' `num`) union all (select '14' `id`, '37' `num`) union all (select '15' `id`, '36' `num`) union all (select '16' `id`, '35' `num`) union all (select '17' `id`, '34' `num`) union all (select '18' `id`, '33' `num`) union all (select '19' `id`, '32' `num`) union all (select '20' `id`, '31' `num`) union all (select '21' `id`, '30' `num`) union all (select '22' `id`, '29' `num`)) f on t.id = f.id set t.num = f.num
    [2] => update `test` t join ((select '23' `id`, '28' `num`) union all (select '24' `id`, '27' `num`) union all (select '25' `id`, '26' `num`) union all (select '26' `id`, '25' `num`) union all (select '27' `id`, '24' `num`) union all (select '28' `id`, '23' `num`) union all (select '29' `id`, '22' `num`) union all (select '30' `id`, '21' `num`) union all (select '31' `id`, '20' `num`) union all (select '32' `id`, '19' `num`) union all (select '33' `id`, '18' `num`)) f on t.id = f.id set t.num = f.num
    [3] => update `test` t join ((select '34' `id`, '17' `num`) union all (select '35' `id`, '16' `num`) union all (select '36' `id`, '15' `num`) union all (select '37' `id`, '14' `num`) union all (select '38' `id`, '13' `num`) union all (select '39' `id`, '12' `num`) union all (select '40' `id`, '11' `num`) union all (select '41' `id`, '10' `num`) union all (select '42' `id`, '9' `num`) union all (select '43' `id`, '8' `num`) union all (select '44' `id`, '7' `num`)) f on t.id = f.id set t.num = f.num
    [4] => update `test` t join ((select '45' `id`, '6' `num`) union all (select '46' `id`, '5' `num`) union all (select '47' `id`, '4' `num`) union all (select '48' `id`, '3' `num`) union all (select '49' `id`, '2' `num`) union all (select '50' `id`, '1' `num`)) f on t.id = f.id set t.num = f.num
)