MySQL存储过程导致“命令不同步" [英] MySQL stored procedure caused `Commands out of sync`

问题描述

调用过程在MySQL终端中可以正常使用，但是在PHP中，导致Commands out of sync; you can't run this command nowCommands out of sync; you can't run this command now

Call procedure works all right in MySQL terminal, but in PHP, caused Commands out of sync; you can't run this command nowCommands out of sync; you can't run this command now

我的程序是

delimiter $$
create procedure getMostSimilar (IN vU_ID INT, IN voffset INT, IN vsize INT)
BEGIN
set @offset = voffset;
set @size = vsize;
set @uid = vU_ID;
prepare SimilarStmt from
"SELECT U_ID, getSimilarity(U_ID, ?) AS similar FROM Answer WHERE U_ID != ? GROUP BY U_ID ORDER BY similar DESC LIMIT ?, ?";
execute SimilarStmt using @uid, @uid, @offset, @size; 
deallocate prepare SimilarStmt;
END
$$

其中getSimilarity是一个函数.

where getSimilarity is a function.

在PHP中:

function getMostSimilar($U_ID, $offset, $size){
    $query = sprintf("CALL getMostSimilar(%s, %s, %s)",
                $U_ID, $offset, $size);
    $result = mysql_query($query);
    print mysql_error();
    if (!$result){
        return $query;
    }
    $ans = array();
    $len = 0;
    while($row = mysql_fetch_assoc($result)){
        $ans[$len] = $row;
        $len++;
    }
    return $ans;
}

我现在该怎么办?谢谢！

What should I do now? Thanks!

推荐答案

C.5.2.14.命令不同步如果您 使命令不同步；你不能 现在在客户端中运行此命令 代码，您正在调用客户端函数 顺序错误.

C.5.2.14. Commands out of sync If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.

例如，如果您 正在使用mysql_use_result()并尝试 在执行新查询之前 名为mysql_free_result().它可以 如果您尝试执行两个，也会发生 不返回数据的查询 调用mysql_use_result()或 两者之间为mysql_store_result().

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

http://dev.mysql. com/doc/refman/5.0/en/commands-out-of-sync.html

编辑

如果您在过程中使用诸如

I think you need to rewrite the getMostSimilar stored procedure, instead of using prepare and execute (which I thinks is fooling mysql) if you use the parameters in the procedure like in this example I think your error will be fixed.

HTH

这篇关于MySQL存储过程导致“命令不同步"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！