使用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;