mysqli_multi_query и ошибка commands out of sync you can't run this command now

grey109

Новичок
Привет всем!

Решил заменить в цикле несколько сотен запросов mysqli_query типа insert / update на один mysqli_multi_query. После которого сразу делаю "mysqli_query "select х from table..." и получаю ошибку на этой строке "commands out of sync you can't run this command now". Поискал в инете, толком причин такого поведения для моего случая не нашел.

Если вместо одного запрос делать сотню в цикле - ошибки не будет. Сейчас нашел решение в интернете, сделать
while(mysqli_next_result($link)){;} , но мне кажется это не правильно.

Подскажите, что я делаю не так и что упускаю?
 

Фанат

oncle terrible
Команда форума
Подскажите, что я делаю не так и что упускаю?
mysqli_multi_query

эта функция мало того что опасная, так еще и в большинстве случаев бессмысленная
так что вместо неё для запросов типа insert / update надо использовать несклько запросов mysqli_prepare/mysqli_bind_param/mysqli_execute

https://phpdelusions.net/mysqli_examples/insert
https://phpdelusions.net/mysqli_examples/update
 

Фанат

oncle terrible
Команда форума
а для скорости обрнуть их всех в одну транзакцию
это будет и быстрее, и просто логично - наверняка надо чтобы или все запросы выполнились, или ни одного
 

Valick

Новичок
транзакции не обязательно, можно собрать обычный многострочный инсерт
когда наполнял БД тестовыми данными, генерил запрос с десятками тысяч строк
кстати ради эксперемента делал и подготовленный запрос многострочного инсерта, больше сотни строк точно

P.S ну и на всякий случай есть загрузка из файла.
 

Фанат

oncle terrible
Команда форума
колупаться со сборкой обычного многострочного инсерта совсем не обязательно, достаточно добавить к коду две строчки, в начале begin а в конце commit.
особенно если кроме вставки есть апдейты и сё это в разные таблицы
 
Последнее редактирование:

Yoskaldyr

"Спамер"
Партнер клуба
@Фанат Все равно вряд-ли переубедишь любителей много строчных инсертов :)
Все еще живо мнение о скорости многострочных инсертов.
А по факту если сделать транзакцию, да еще и инсерты руками через один единственный преперед стейтмент будет быстрее в несколько раз. Но мифы они такие - живучие :))))
 

Фанат

oncle terrible
Команда форума
будет быстрее в несколько раз. Но мифы они такие - живучие :))))
ну про несколько раз я не очень согласен.
мне кажется разница будет минимальная с выигрышем в пользу мульти инсерта за счет меншего числа обращений а БД
Другое дело что если у тебя база стоит где-то далеко, то вставка тыщи строк одноразово будет твоей наименьшей проблемой.
А если база настроена рнормально, то обращения по сокету ты не заметишь.
 

Yoskaldyr

"Спамер"
Партнер клуба
ну про несколько раз я не очень согласен.
мне кажется разница будет минимальная с выигрышем в пользу мульти инсерта за счет меншего числа обращений а БД
Другое дело что если у тебя база стоит где-то далеко, то вставка тыщи строк одноразово будет твоей наименьшей проблемой.
А если база настроена нормально, то обращения по сокету ты не заметишь.
ой не факт, на мультиинсерте одним запросом парсинг строки запроса может занимать большую часть времени запроса.
когда 1 prepare и много exec и все в пределах одной транзакции, то иногда действительно быстрее (всего в пару тройку раз, но все же). Но все зависит от настроек базы (размеры буферов и т.д.). Т.к. база иногда с большими пачками данных не очень работает.
на 10000 строк уже чувствуешь без таймеров
если 10000 prepare + 10000 exec, то да, будет заметно медленнее.

Да и мультиинсерт собирать это еще то удовольствие, особенно когда по нормлаьному нельзя использовать с prepared statements
 

Valick

Новичок
PHP:
<?php
$mysqli = new mysqli('localhost', 'mysql', 'mysql', 'test'); 
$query = $mysqli->query('set names utf8');
$bind = [];
for ($j=0;$j<100;$j++) {
    for ($i = 0; $i < 10; $i++) {
        $bind[0] .= str_pad('', 2, 'ss');
        $bind[] = 'login' . rand(1111, 9999);
        $bind[] = 'password' . rand(1111, 9999);
    }
    $query = "INSERT INTO `test` ( `login`, `password` ) 
            VALUES " . implode(',', array_fill(1, (count($bind) - 1) / 2, '(?,?)'));
    foreach ($bind as $key => $value) {
        $bind[$key] = &$bind[$key];
    }
    $stmt = $mysqli->prepare($query);
    call_user_func_array([$stmt, 'bind_param'], $bind);
    $stmt->execute();
    $stmt->store_result();
    if ($stmt->affected_rows != (count($bind) - 1) / 2) {
        echo 'Ошибка добавления записей';
    }
    echo '<p>- добавлено: ' . $stmt->affected_rows . '</p>';
    unset($bind);
    $stmt->free_result();
}
$stmt->close();
А теперь просто измените во внутреннем цикле количество итераций с 10 на 10000
 
Последнее редактирование:

Фанат

oncle terrible
Команда форума
С желающими проверить
Вот и проверь :)

Да, и на будущее.
Рекомендую следить за новшествами в языке, хотя бы на горизонте 3-5 лет и использовать их в своем коде
Ну и в целом старайся писать более простой и логичный код. Самому же потом его читать :)

PHP:
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'mysql', 'mysql', 'test');
$mysqli->set_charset('utf8mb4');

$bind = [];
for ($i=0; $i<10;$i++) {
    $bind[] = 'login' . rand(1111,9999);
    $bind[] = 'password' . rand(1111,9999);
}
$query = "INSERT INTO `test` ( `login`, `password` ) VALUES ". str_repeat("(?,?),", count($bind)/2 - 1) .'(?,?)';
$stmt = $mysqli->prepare( $query );
$stmt->bind_param(str_repeat('s', count($bind)), ...$bind);
$stmt->execute();
echo '<p>- добавлено: '. count($bind)/2 . '</p>';
 
Последнее редактирование:

Фанат

oncle terrible
Команда форума
Ну и сравни с простым читабельным кодом, в котором котором ты просто выполняешь запрос как обычно
PHP:
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'mysql', 'mysql', 'test');
$mysqli->set_charset('utf8mb4');

$query = "INSERT INTO `test` ( `login`, `password` ) VALUES (?,?)";
$stmt = $mysqli->prepare( $query );
$mysqli->begin_transaction();
for ($i=0; $i<10;$i++) {
    $stmt->bind_param(str_repeat('ss', ...['login'.rand(1111,9999),'password'.rand(1111,9999)]);
    $stmt->execute();
}
$mysqli->commit();
echo '<p>- добавлено: '. count($bind)/2 . '</p>';
 

WMix

герр M:)ller
Партнер клуба
если 10000 prepare + 10000 exec, то да, будет заметно медленнее.
код говно, я о скорости

PHP:
$count = 10000;

$bind = [];
for ($i=0; $i<$count;$i++) {
    $bind[] = [
        'nick'  => 'user-'.$i,
        'pass' => md5(rand(1111,9999))
    ];
}

$mysqli = new mysqli('mysql', 'root', 'root', 'test');
$query = $mysqli->query('set names utf8');
$query = $mysqli->query('TRUNCATE `test`');

// ----------------------------------------------------------------
$time_pre = microtime(true);
$query = "INSERT INTO `test` ( `nick`, `pass` ) VALUES (?,?)";
$stmt = $mysqli->prepare( $query );
           
foreach($bind as $key => $value) {
    $stmt->bind_param("ss", $value['nick'], $value['pass']);
    $stmt->execute();
}
$stmt->close();
echo "построчно: ".(microtime(true) - $time_pre) ."\n";
// ----------------------------------------------------------------
$query = $mysqli->query('TRUNCATE `test`');
// ----------------------------------------------------------------

$time_pre = microtime(true);
$count = 0;
$query = "INSERT INTO `test` ( `nick`, `pass` ) VALUES ";

$params = [];
$types = '';
foreach($bind as $key => $value) {
    $count++;
    $params[] = &$bind[$key]['nick'];
    $params[] = &$bind[$key]['pass'];
    $types .= 'ss';
    $query .= "(?,?),";
    if($count%1000 === 0){
        $query = substr($query,0,-1);
        //echo $query;
        array_unshift($params, $types);
        $stmt = $mysqli->prepare( $query );
        call_user_func_array([$stmt, 'bind_param'], $params);
       
        $query = "INSERT INTO `test` ( `nick`, `pass` ) VALUES ";
        $params = [];
        $types = '';
        $stmt->execute();
        $stmt->close();
    }
}
echo "группами: ".(microtime(true) - $time_pre)."\n";
Код:
построчно: 6.4563529491425
группами: 0.31162309646606
с транзакцией, да лучше
Код:
построчно: 1.0694270133972
группами: 0.30048394203186
 

fixxxer

К.О.
Партнер клуба
И часто ты по сто тыщ инсертов делаешь?

Обычно это одноразовые операции импорта, которые запускаются из какой-нибудь консольной команды или очереди раз в месяц от силы.
Одна там секунда или двадцать - вообще никого не волнует. Читабельность кода намного важнее.

Если же сотни тыщ инсертов делаются каждую минуту, то тут возникает вопрос "нафига", поскольку с большой вероятностью таким данным вообще нечего делать в РСУБД (логи или что-то подобное).
 

Valick

Новичок
Обычно это одноразовые операции импорта, которые запускаются из какой-нибудь консольной команды или очереди раз в месяц от силы.
Одна там секунда или двадцать - вообще никого не волнует. Читабельность кода намного важнее
глупости
 

Yoskaldyr

"Спамер"
Партнер клуба
@WMix Все это хорошо для теста, а когда появляются реальные данные и реальные объемы (вставка в существующие данные), да еще и под нагрузкой - все становится интереснее.
Например, уникальные индексы с возможностью правильной обработки при невозможности вставки и т.п. Также всегда на стороне мускуля надо проверять текущее значение размера входящего буфера под тело запросов. Оно надо?
Но как правильно заметил @fixxxer терять удобство нормальной разработки, городить полностью неподдерживаемый код (увеличив время разработки и поддержки в несколько раз) при потери в скорости в худшем случае всего в 3.5 раза - это полное извращение.
 
Сверху