mysql批量更新

常规的批量更新方法

UPDATE t_user SET status=1 WHERE user_id>100;

所有user_id>100的记录的status值改成1，这句sql不出意外当然能执行成功。

但是我们有时候想让user_id=101的时候status改成101，user_id=102的时候status改成102……user_id=1001的时候status改成1001，这怎么搞？别急往下看……

“不”常规的批量更新方法

直接看sql

UPDATE t_user WHERE status=
(CASE 
    WHEN user_id=101 THEN 101
    WHEN user_id=102 THEN 102
    ……
    WHEN user_id=1001 THEN 1001
END)
WHERE user_id BETWWEN 100 AND 1001

好了应该都看懂了，但是最重要的一定要加上where 条件，不加where这条sql会去修改所有记录，能匹配到when里面的条件时就改成then后面的值，如果没有匹配到就改成null

既然这么常用，就封装一个吧

提供PHP示例
/**
 * 批量更新
 * @param string $table        表名,如‘user’
 * @param array  $rows         字段数据,如array(['id'=>1,'name'=>'liupei','age'=>18],['id'=>1,'name'=>'liupei2','age'=>19])
 * @param string $primaryKey   主键字段，更新条件，如'id'
 * @return int|string          返回影响行数，或者返回出错信息
 */
function updateBatch($table, $rows, $primaryKey)
{
    // dump($rows);
    if (empty($table)) {
        return 'table不允许为空';
    }
    if (empty($rows)) {
        return 'rows不允许为空';
    }
    if (empty($primaryKey)) {
        return 'primaryKey不允许为空';
    }
    $sql = "UPDATE $table SET ";
    $case = [];
    $ids = [];
    foreach ($rows as $row) {
        $ids[] = $row[$primaryKey];
        foreach ($row as $key=>$value) {
            if ($key === $primaryKey) {
                continue;
            }
            isset($case[$key]) || $case[$key] = "`{$key}`=(case ";
            if (is_string($value)) {
                $value = "'{$value}'";
            }
            $case[$key] .= " when {$primaryKey}=". $row[$primaryKey] . " then $value";
        }
    }
    foreach ($case as &$item) {
        $item .= ' end )';
        $sql .= $item . ',';
    }
    // 必须加上搜索条件，否则数据会出错
    $sql = rtrim($sql, ',') . " where `{$primaryKey}` in (". implode(',', $ids).')';
    // echo $sql;
    // 开始执行
    $rs = 0;
    try {
        // 数据库执行，这里是基于tp5的数据操作
        $rs = \think\Db::table($table)->execute($sql);
    } catch (exception $e) {
        return 'sql=' . $sql. ',error_message='.$e->getMessage();
    }
    return $rs;
}