SQL实现批量修改

根据SQL的casewhen 拼接SQL语句实现批量修改

    /**
     * @param array $data 包括条件在内的要更新的数据
     * @param array|string $field 更新条件 单个更新条条件
     * @param $table 表明
     * @param array $params SQL的 where 条件
     * @return bool
     */
    public function batchUpdate(array $data, array|string $field, $table, array $params = []): bool
    {
        if (!is_array($data) || !$field || !is_array($params)) {
            return false;
        }
        $updates = $this->parseUpdate($data, $field);
        $where = $this->parseParams($params);

        if(is_array($field))
        {
            $inSql = "1 = 1 ";
            $whereCondition = $this->getWhereCondition($data, $field);
            foreach ($whereCondition as $key => $condition)
            {
                if(!empty($inSql))
                {
                    $inSql .= " AND ";
                }
                $inSql .= sprintf(" `%s` In (%s) ", $key, implode(',', $condition));
            }
            if(empty($where))
            {
                $sql = sprintf("UPDATE %s SET %s where %s", $table, $updates, $inSql);
            }
            else
            {
                // 获取所有键名为$field列的值，值两边加上单引号，保存在$fields数组中
                $sql = sprintf("UPDATE %s SET %s WHERE %s %s", $table, $updates, $inSql, $where);
            }
        }
        else
        {
            // 获取所有键名为$field列的值，值两边加上单引号，保存在$fields数组中
            $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 Database::$Db->query($sql);
    }

    public function getWhereCondition(array $data, array $condition): array
    {
        $return = [];
        foreach ($data as $item)
        {
            foreach ($condition as $index)
            {
                if(isset($item[$index]))
                {
                    $return[$index][] = $item[$index];
                }
            }
        }
        return $return;
    }

    public function getUpdateCaseWhere(array $data, array|string $condition): string
    {
        if(!is_array($condition))
        {
            $condition = [$condition];
        }
        $where = "";
        foreach ($condition as $item)
        {
            if(isset($data[$item]))
            {
                if(!empty($where))
                {
                    $where .= " AND ";
                }
                $where .= sprintf(" `%s` = '%s' \n", $item, $data[$item]);
            }
        }
        if(empty($where))
        {
            return "";
        }
        return $where;
    }

    /**
     * 将二维数组转换成CASE WHEN THEN的批量更新条件
     * @param $data array 二维数组
     * @param $field array|string 列名
     * @return string sql语句
     */
    public function parseUpdate(array $data, array|string $field): string
    {
        $sql = '';
        $keys = array_keys(current($data));

        foreach ($keys as $column) {
            if ((is_array($field) && in_array($column, $field)) || $column == $field) {
                continue;
            }

            $sql .= sprintf("`%s` = CASE \n", $column);
            foreach ($data as $line) {
                $caseWhere = $this->getUpdateCaseWhere($line, $field);
                $caseValue = $line[$column];

                if (in_array(strtoupper($caseValue), ["UNIX_TIMESTAMP()", "DATE_ADD", "DATE_FORMAT", "NOW()", "NULL"])) {
                    $sql .= sprintf("WHEN %s THEN %s \n", $caseWhere, $caseValue);
                } else {
                    $sql .= sprintf("WHEN %s THEN '%s' \n", $caseWhere, $caseValue);
                }
            }

            $sql .= "END,";
        }

        return rtrim($sql, ',');
    }

    /**
     * 解析where条件
     * @param $params
     * @return array|string
     */
    public function parseParams($params)
    {
        $where = [];
        foreach ($params as $key => $value) {
            $where[] = sprintf("`%s` = '%s'", $key, $value);
        }
        return $where ? ' AND ' . implode(' AND ', $where) : '';
    }