Меню
Что нового?

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

Yoskaldyr

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

Фанат

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

Yoskaldyr

"Спамер"
Партнер клуба
Фанат написал(а):
ну про несколько раз я не очень согласен.
мне кажется разница будет минимальная с выигрышем в пользу мульти инсерта за счет меншего числа обращений а БД
Другое дело что если у тебя база стоит где-то далеко, то вставка тыщи строк одноразово будет твоей наименьшей проблемой.
А если база настроена нормально, то обращения по сокету ты не заметишь.
Нажмите для раскрытия...
ой не факт, на мультиинсерте одним запросом парсинг строки запроса может занимать большую часть времени запроса.
когда 1 prepare и много exec и все в пределах одной транзакции, то иногда действительно быстрее (всего в пару тройку раз, но все же). Но все зависит от настроек базы (размеры буферов и т.д.). Т.к. база иногда с большими пачками данных не очень работает.
WMix написал(а):
на 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
Команда форума
Valick написал(а):
С желающими проверить
Нажмите для раскрытия...
Вот и проверь :)

Да, и на будущее.
Рекомендую следить за новшествами в языке, хотя бы на горизонте 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

WMix

герр M:)ller
Партнер клуба
Yoskaldyr написал(а):
если 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

fixxxer

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

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

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

Valick

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

Yoskaldyr

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