php生成原生mysql批量更新语句

生成更新语句函数

* 主要的 *
function batchUpdate($data, $field, $table, $params = []) {
    if (!is_array($data) || !$field || !is_array($params)) {
        return false;
    }
    $updates = parseUpdate($data, $field);
    $where = parseParams($params);
    // 获取所有键名为$field列的值，值两边加上单引号，保存在$fields数组中
    // array_column()函数需要PHP5.5.0+，如果小于这个版本，可以自己实现，
    // 参考地址：http://php.net/manual/zh/function.array-column.php#118831
    $fields = array_column($data, $field);
    $fields = implode(',', array_map(function($value) {
        return "'".$value."'";
    }, $fields));
    $sql = sprintf("UPDATE `%s` SET %s WHERE `%s` IN (%s) %s", $table, $updates, $field, $fields, $where);
    return $sql;
}

function parseUpdate($data, $field){
    $sql = '';
    $keys = array_keys(current($data));
    foreach ($keys as $column) {

        $sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
        foreach ($data as $line) {
            $sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]);
        }
        $sql .= "END,";
    }
    return rtrim($sql, ',');
}

function parseParams($params){
    $where = [];
    foreach ($params as $key => $value) {
        $where[] = sprintf("`%s` = '%s'", $key, $value);
    }
    return $where ? ' AND ' . implode(' AND ', $where) : '';
}

示例

$data = [   # 更新数据，需要带上主键ID
	['id' => 1, 'name' => '张三', 'age' => 18],
	['id' => 2, 'name' => '李四', 'age' => 35],
	['id' => 3, 'name' => '王五', 'age' => 55],
];  
$table = 'prefix_table';  # 数据表名
$field = 'id';
$update_sql = batchUpdate($data, $field, $table, []);  # 生成语句
mysqli_query($links, $update_sql);					   # 执行语句