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;
    }