mysql - 使用 ON DUPLICATE KEY UPDATE 的 Laravael4 db 原始查询会删除错误

Question

我真的在与 Laravel ON DUPLICATE KEY UPDATE 查询作斗争，我无法让它工作，所以查询基本上看起来像

foreach ($queries as $query) {
                $update_time = array('update_time' => date('Y-m-d H:i:s'));
                $query = array_merge($update_time, $query);
                $keysString = implode(", ", array_keys($query));                
                $indexes = "";
                $values  = "";
                $updates = "";
                foreach ($query as $i=>$v){
                    $values  .= ':'.$v.',';
                    $updates  .= $i.'="'.$v.'",';
                }
                //$holder = rtrim(str_repeat('?,', count($query)),',');
                $updates = rtrim($updates,',');

    DB::statement("INSERT INTO products ({$keysString}) VALUES ({rtrim($values,',')}) ON DUPLICATE KEY UPDATE {rtrim($updates,',')}")

}

但我明白了

SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

如何在 laravel4 中为原始查询制作准备好的语句？

Answer 1

默认情况下，Laravel 使用 ? 绑定(bind)其数据，而您则使用 :foo 绑定(bind)数据，这意味着这两种方法混合在一起，PDO 对此感到难过它。

PDO: Invalid parameter number: mixed named and positional parameters

这样的事情应该会让你朝着正确的方向前进:

foreach ($queries as $query) {

    // Add the update time without merging stuff
    $query['update_time'] = date('Y-m-d H:i:s');

    // How many bits of data do we have
    $bindingCount = count($query);

    // Same as before, just get the keys
    $keyString = implode(", ", array_keys($query));

    // Start off a bindings array with just the values
    $bindings = array_values($query);

    $updates = [];

    foreach ($query as $field => $value){
        $updates[] = "{$field} = ?";
        $bindings[] = $value;
    }

    $valueString = implode(',', array_fill(0, $bindingCount, '?'));

    $updateString = implode(',', $updates);

DB::statement("INSERT INTO products ({$keyString}) VALUES ({$valueString}) ON DUPLICATE KEY UPDATE {$updateString}", $bindings);

}