THINKPHP5.3 支援PGSQL插入與更新

前提：公司需要将mysql更換為pgsql。更換過程中除了官網提供的配置之外，發現無法對表進行批量插入與更新。

原因：tp自帶的insert，update方法轉換為sql，如果帶有關鍵字state, user, group。就無法進行相關sql操作。

Navicate執行報錯如圖 syntax error at or near "user"：

修複方法：需要将圖中sql中關鍵字(添加雙引号)進行替換修改為 INSERT INTO test_table ("user","group",create_time) VALUES  ('a','b','2021-04-19 12:12:12')

修改thinkphp源碼 thinkphp/library/think/db/Builder.php 中insert方法：改為

public function insert(array $data, $options = [], $replace = false)
    {
        // 分析并處理資料
        $data = $this->parseData($data, $options);
        if (empty($data)) {
            return 0;
        }
        $fields = array_keys($data);
        //相容pgsql替換關鍵字加雙引号
        foreach($fields as $k => $v){
            if($v == "user"){
                $fields[$k] = "\"user\"";
            }
            if($v == "state"){
                $fields[$k] = "\"state\"";
            }
            if($v == "group"){
                $fields[$k] = "\"group\"";
            }
        }
        $values = array_values($data);

        $sql = str_replace(
            ['%INSERT%', '%TABLE%', '%FIELD%', '%DATA%', '%COMMENT%'],
            [
                $replace ? 'REPLACE' : 'INSERT',
                $this->parseTable($options['table'], $options),
                implode(' , ', $fields),
                implode(' , ', $values),
                $this->parseComment($options['comment']),
            ], $this->insertSql);

        return $sql;
    }
           

修改thinkphp源碼 thinkphp/library/think/db/Builder.php 中update方法：改為

public function update($data, $options)
    {
        $table = $this->parseTable($options['table'], $options);
        $data  = $this->parseData($data, $options);
        if (empty($data)) {
            return '';
        }
        foreach ($data as $key => $val) {
            if($key == "user" || ($key == "state") || $key == "group"){
                $set[] = '"' . $key . '"' . '=' . $val;
            }else{
                $set[] = $key . '=' . $val;
            }
        }

        $sql = str_replace(
            ['%TABLE%', '%SET%', '%JOIN%', '%WHERE%', '%ORDER%', '%LIMIT%', '%LOCK%', '%COMMENT%'],
            [
                $this->parseTable($options['table'], $options),
                implode(',', $set),
                $this->parseJoin($options['join'], $options),
                $this->parseWhere($options['where'], $options),
                $this->parseOrder($options['order'], $options),
                $this->parseLimit($options['limit']),
                $this->parseLock($options['lock']),
                $this->parseComment($options['comment']),
            ], $this->updateSql);

        return $sql;
    }
           

修改thinkphp源碼 thinkphp/library/think/db/Builder.php中insertAll方法：

public function insertAll($dataSet, $options)
    {
        // 擷取合法的字段
        if ('*' == $options['field']) {
            $fields = array_keys($this->query->getFieldsType($options));
        } else {
            $fields = $options['field'];
        }

        foreach ($dataSet as &$data) {
            foreach ($data as $key => $val) {
                if (!in_array($key, $fields, true)) {
                    if ($options['strict']) {
                        throw new Exception('fields not exists:[' . $key . ']');
                    }
                    unset($data[$key]);
                } elseif (is_scalar($val)) {
                    $data[$key] = $this->parseValue($val, $key);
                } else {
                    // 過濾掉非标量資料
                    unset($data[$key]);
                }
            }
            $value    = array_values($data);
            $values[] = '( ' . implode(',', $value);
        }
        $fields = array_map([$this, 'parseKey'], array_keys(reset($dataSet)));
        $sql    = str_replace(
            ['%TABLE%', '%FIELD%', '%DATA%', '%COMMENT%'],
            [
                $this->parseTable($options['table'], $options),
                implode(' , ', $fields),
                implode(' ), ', $values),
                $this->parseComment($options['comment']),
            ], $this->insertAllSql) . ")";

        //更換批量插入sql語句
        $position = strpos($sql, ")");
        $sql = substr($sql,0, $position + 1) . " VALUES " . substr($sql,$position + 1);
        return $sql;
    }