带有IN(?)的mySQL bind_param

Question

在我的所有查询上使用bind_param，我现在希望使用一个IN(?)，其中列表中的元素数量可以变化。

我在这里使用的SQLout函数基本上执行$sql_db->prepare、->bind_param、->execute()、->store_result()、->bind_result

// the code below does not work as the query only matches on element 'a':
$locations = ('a','b','c','d','e');

SQLout ("SELECT Name FROM Users WHERE Locations IN (?)",
    array('s', $locations), array(&$usrName));


// the code below does work as a brute-force method,
// but is not a viable solution as I can't anticipate the number of elements in $locations going forward:

SQLout ("SELECT Name FROM Users WHERE Locations IN (?,?,?,?,?)",
    array('sssss', $locations[0],$locations[1],$locations[2],$locations[3],$locations[4]), array(&$usrName));

有没有人想出一个更优雅的解决方案呢？

Answer 1

这是占位符失败的一个地方。去掉自动转义，它们几乎就是内部的字符串替换操作，这意味着如果您有WHERE Locations IN (?)，并传入1,2,3,4，您将得到与

WHERE Locations IN ('1,2,3,4')  // note, it's a string, not individual comma-separated integers

逻辑上等同于

WHERE Locations = '1,2,3,4' // again, just a string

而不是预期的

WHERE Locations = 1 OR Locations = 2 OR Locations = 3 OR Locations = 4

唯一实用的解决方案是构建您自己的逗号分隔占位符(?)列表，例如：

$placeholders = implode(',', array_fill(0, count($values), '?'));
$sql = "SELECT Name FROM Users WHERE Locations IN ($placeholders)";

然后像往常一样绑定你的参数。

Answer 2

正如Hazmat所说，您需要构建参数，然后通过在准备好的语句上调用call_user_func_array来传递它们，但比他的示例稍微更接近工作代码:)

//In the calling code
$queryString = "SELECT Name FROM Users WHERE Locations IN (";
$queryString .= getWhereIn($locations);
$queryString .= " )";

$parametersArray = array();

foreach($locations as $location){
    $parameter = array();
    $parameter[0] = 's'; //It's a string
    $parameter[1] = $location;

    $parametersArray[] = $parameter;
}



//This is a function in a class that wraps around the class mysqli_statement
function    bindParameterArray($parameterArray){

    $typesString = '';
    $parameterValuesArray = array();

    foreach($parameterArray as $parameterAndType){
        $typesString .= $parameterAndType[0];
        $parameterValuesArray[] = $parameterAndType[1];
    }

    $finalParamArray = array($typesString);
    $finalParamArray = array_merge($finalParamArray, $parametersArray);
    call_user_func_array(array($this->statement, "bind_param"), $finalParamArray);
}

function getWhereIn($inArray){
    $string = "";
    $separator = "";
    for($x=0 ; $x<count($inArray) ; $x++){
        $string .= $separator."?";
        $separator = ", ";
    }
    return  $string;
}

Answer 3

IN通常很慢，并且不是准备好的语句友好。更好的解决方案是构建一个包含in中的项的表，并使用连接来获得相同的效果。