使用mysql 自带的语句构建 多个更新条件 批量更新

使用mysql 自带的语句构建 多个更新条件 批量更新

    /**
     * 批量修改
     * @param string $table_name 表名
     * @param array $data        提交的数据 array
     * @param string $field      修改的条件字段 支持多条件修改 可用逗号分隔,或者数组
     * @return bool|int          返回修改的条数
     */
    public static function batch_update($table_name = '', $data = [], $field = '')
    {
        if (!$table_name || !$data || !$field) {
            return false;
        } else {
            $sql = 'UPDATE ' . $table_name;
        }
        $con = [];
        $con_sql = [];
        $fields = [];
        if (!is_array($field)) {
            $field = explode(',', $field);
        }
        foreach ($data as $key => $value) {
            $x = 0;
            foreach ($value as $k => $v) {
                $con_sql[$x] = isset($con_sql[$x]) ? $con_sql[$x] : '';
                if (!in_array($k, $field) && !isset($con[$x]) && $x == 0) {
                    $con[$x] = " SET `" . $k . "` = (CASE ";
                    $con_sql[$x] = '';
                } elseif (!in_array($k, $field) && !isset($con[$x]) && $x > 0) {
                    $con[$x] = " `" . $k . "` = (CASE ";
                }
                if (!in_array($k, $field)) {
                    $con_sql[$x] .= " WHEN ";
                    foreach ($field as $i => $val) {
                        $temp = $value[$val];
                        if ($i > 0) {
                            $con_sql[$x] .= " AND `" . $val . "` = " . $temp;
                        } else {
                            $con_sql[$x] .= " `" . $val . "` = " . $temp;
                        }
                    }
                    $con_sql[$x] .= " THEN '" . $v . "'";
                    $x++;
                }
            }
            foreach ($field as $fk => $fv) {
                $fields[$fv] = isset($fields[$fv]) ? $fields[$fv] : [];
                $temp = $value[$fv];
                if (!in_array($temp, $fields[$fv])) {
                    $fields[$fv][] = $temp;
                }
            }
        }
        $num = count($con) - 1;
        foreach ($con as $key => $value) {
            foreach ($con_sql as $k => $v) {
                if ($k == $key && $key < $num) {
                    $sql .= $value . $v . ' END),';
                } elseif ($k == $key && $key == $num) {
                    $sql .= $value . $v . ' END)';
                }
            }
        }
        $sql .= " WHERE ";
        $i = 0;
        foreach ($fields as $fsk => $fsv) {
            if ($i > 0) {
                $sql .= " AND " . $fsk . " IN (" . implode(',', $fsv) . ")";
            } else {
                $sql .= $fsk . " IN (" . implode(',', $fsv) . ")";
            }
            $i++;
        }
        $res = Db::name($table_name)->execute($sql);
        return $res;
    }

最后返回的$sql就是组合后的批量修改的sql语句，上文中用 thinkphp 5 提交sql，并返回处理的条数，可以根据自己情况改变改代码

$res = Db::name($table_name)->execute($sql);
return $res;