Можно ли одновременно выполнить множество query за раз?

Question

[Ninja Mate]

У меня несколько тысяч запросов на обновление бд. Хочу ускорить работу скрипта и обновлять по 100 записей за раз.

function new_product_cat($SKU){

    $mysqli = new mysqli("127.0.0.1", "test", "000", "000", 3306);
    if ($mysqli->connect_errno) {
        echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error . "</br>";
    }
    echo $mysqli->host_info . "\n";

    $plugins_url = plugins_url();
    $name = "$plugins_url"."/sinco_mate/" . "categories_by_sku.csv";

    $file = fopen( $name, 'r');

    $SKUCategoriesSubcat = array();
    while (($line = fgetcsv($file, 4096)) != false) {
        //$line is an array of the csv elements
        if (is_numeric($line[0])){

            $SKUCategoriesSubcat[$line[0]]= array (
                'category' => $line[4],
                'subcat' => $line[3]
            );

        }
    }
    fclose($file);

    set_time_limit(0);

    $query = "";
    $count = 0;
    foreach ($SKU as $sku){

        if( array_key_exists( $sku, $SKUCategoriesSubcat ) ){

            $category = '';
            $subcat = '';
            $category = $mysqli->real_escape_string( $SKUCategoriesSubcat[$sku]['category'] );
            $subcat = $mysqli->real_escape_string( $SKUCategoriesSubcat[$sku]['subcat'] );

            $query = $query. "; UPDATE `extract` SET `category` = '$category', `subcat` = '$subcat' WHERE `sku`='$sku'";

            $count++;
            if( $count >99 ){

                $count = 0;

                $success= $mysqli->query($query);

                $query = '';
            }

        }

    }

    mysqli_close($mysqli);

    return null;
}

Проблема в том, что если забираю значение query с дебагера, а потом вставляю все в sql phpmyadmin, то все работает, а через php $success = false и без ошибок.

affected_rows = -1
client_info = "mysqlnd 5.0.11-dev - 20120503 - $Id: 76b08b24596e12d4553bd41fc93cccd5bac2fe7a $"
client_version = 50011
errno = 0
field_count = 0
host_info = "127.0.0.1 via TCP/IP"
error_list = {array} [0]
error = ""
server_info = "5.6.35"
info = null
insert_id = 0
protocol_version = 10
sqlstate = "00000"
stat = "Uptime: 2749 Threads: 2 Questions: 7516 Slow queries: 0 Opens: 85 Flush tables: 1 Open tables: 78 Queries per second avg: 2.734"
server_version = 50635
connect_errno = 0
connect_error = null
warning_count = 0
thread_id = 431

Query

; UPDATE `extract` SET `category` = 'PIANO', `subcat` = 'PIANO' WHERE `sku`='930001002560'; UPDATE `extract` SET `category` = 'AURAL TRAINING', `subcat` = 'EDUCATIONAL' WHERE `sku`='930001002621'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001002769'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001002774'; UPDATE `extract` SET `category` = 'MISCELLANEOUS INSTRUMENTS', `subcat` = 'MISCELLANEOUS INSTRUM' WHERE `sku`='930001002776'; UPDATE `extract` SET `category` = 'PIANO', `subcat` = 'PIANO' WHERE `sku`='930001002778'; UPDATE `extract` SET `category` = 'SAXOPHONE METHOD', `subcat` = 'SAXOPHONE' WHERE `sku`='930001002790'; UPDATE `extract` SET `category` = 'TEXT', `subcat` = 'EDUCATIONAL' WHERE `sku`='930001002801'; UPDATE `extract` SET `category` = 'VOCAL METHOD', `subcat` = 'VOCAL ALBUMS' WHERE `sku`='930001002815'; UPDATE `extract` SET `category` = 'RECORDER METHOD', `subcat` = 'RECORDER' WHERE `sku`='930001003089'; UPDATE `extract` SET `category` = 'MANUSCRIPT', `subcat` = 'MANUSCRIPT' WHERE `sku`='930001003114'; UPDATE `extract` SET `category` = 'DRUM METHOD', `subcat` = 'DRUMS' WHERE `sku`='930001003163'; UPDATE `extract` SET `category` = 'GUITAR', `subcat` = 'GUITAR' WHERE `sku`='930001003456'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003489'; UPDATE `extract` SET `category` = 'PIANO', `subcat` = 'PIANO' WHERE `sku`='930001003503'; UPDATE `extract` SET `category` = 'GUITAR TAB', `subcat` = 'GUITAR' WHERE `sku`='930001003522'; UPDATE `extract` SET `category` = 'REFERENCE', `subcat` = 'GUITAR' WHERE `sku`='930001003524'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003537'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003538'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003539'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003540'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003541'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003574'; UPDATE `extract` SET `category` = 'CHARTS/CHORDS', `subcat` = 'KEYBOARD/ORGAN' WHERE `sku`='930001003577'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003593'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003596'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003598'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003599'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003602'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003603'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003604'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003606'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003607'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003608'; UPDATE `extract` SET `category` = 'THEORY', `subcat` = 'EDUCATIONAL' WHERE `sku`='930001003609'; UPDATE `extract` SET `category` = 'THEORY', `subcat` = 'EDUCATIONAL' WHERE `sku`='930001003610'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003611'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003612'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003613'; UPDATE `extract` SET `category` = 'PIANO METHOD', `subcat` = 'PIANO' WHERE `sku`='930001003614'; UPDATE `extract` SET `category` = 'PIANO', `subcat` = 'PIANO' WHERE `sku`='930001003616'; UPDATE `extract` SET `category` = 'ELECTRONIC KEYBOARD', `subcat` = 'KEYBOARD/ORGAN' WHERE `sku`='930001003621';

Answer 1

[Stalker_RED]

Да полно рецептов.
Например:

UPDATE mytable SET title = CASE
WHEN id = 1 THEN 'Great Expectations';
WHEN id = 2 THEN 'War and Peace';
ELSE title
END;

The ELSE title is very important, otherwise you will overwrite the rest of the table with NULL.

Или INSERT ... ON DUPLICATE KEY UPDATE

По производительности - хз, лучше протестировать на ваших таблицах.

Comments

[Ninja Mate]

Хороший вариант, но мне нужно не одно квери сделать, а много разных. пробовал так

$mysqli->multi_query( $query );

не работает

[Shaks]

Ninja Mate php.net/manual/ru/mysqli.multi-query.php

[Shaks]

читайте мануал внимательно. там все написано. Разделяете строки ; и фигачите

[Ninja Mate]

Shaks: напишите ответ, отмечу

$result = mysqli_multi_query($mysqli, $query);

                if ($result) {
                    do {
                        // grab the result of the next query
                        if (($result = mysqli_store_result($mysqli)) === false && mysqli_error($mysqli) != '') {
                            echo "Query failed: " . mysqli_error($mysqli);
                        }
                    } while (mysqli_more_results($mysqli) && mysqli_next_result($mysqli)); // while there are more results
                } else {
                    echo "First query failed..." . mysqli_error($mysqli);
                }

[Ninja Mate]

Shaks: спасибо, то что я искал

[Ninja Mate]

Stalker_RED все заработало, благодаря mysqli_multi_query. скорость выполнения примерно с 40 минут до 5 минут сократилась.

Answer 2

[Mikhail Vasilyev]

А еще можно попробовать сделать CREATE temporary table, insert в неё одним запросом, потом - update с join-ом оригинальной таблицы и временной.