CodeIgniter3项目MySQL查询迁移至PostgreSQL的高效方案咨询

问题背景 #

我们有一个基于CodeIgniter 3的PHP项目，原数据库为AWS RDS上的MySQL，计划迁移至PostgreSQL。核心问题如下：

• 原项目的Active Record查询使用单引号包裹外层查询字符串，内部字符串用转义双引号（如concat("O",o.id)）
• PostgreSQL要求外层用双引号包裹查询字符串，内部字符串用单引号（如concat('O',o.id)）
• 同时存在函数差异：IFNULL需替换为coalesce，group_concat需替换为string_agg
• 项目中有数百个类似查询，手动修改工作量极大；自行编写脚本时，因查询中嵌入'.$cond.'这类PHP变量，导致替换效果不佳。已在database.php中配置PostgreSQL驱动。

原MySQL查询示例 #

$query = $this->db->select('o.*,concat("O",o.id) as id, c.name as customer,w.name as warehouse,a.name as account,a.type as channel,
         IFNULL(oi.qty,0) as qty, IFNULL(oi.picked_qty,0) as picked_qty, IFNULL(oi.boxes,0) as boxes,IFNULL(oi.packed_qty,0) as packed_qty,
         IFNULL(oi.shipped_qty,0) as shipped_qty', FALSE)
                ->join('customers c', 'c.id=o.customer_id')
                ->join('warehouses w', 'w.id=o.warehouse_id')
                ->join('accounts a', 'a.id=o.account_id')
                ->join('(select order_id, sum(qty) as qty,  sum(boxes) as boxes,sum(picked_qty) as picked_qty, sum(packed_qty) as packed_qty,
         sum(shipped_qty) as shipped_qty,  group_concat(IFNULL(order_items.sku,"null") separator "|") as skus,
           group_concat(order_items.in_stock separator "|") as in_stock, group_concat(distinct(order_items.upc) separator "|") as upcs,
           group_concat(distinct(order_items.ean) separator "|") as eans , group_concat(brand_name) brands,  group_concat(product_category) categories 
            from order_items  left join upc on order_items.sku = upc.sku group by order_items.order_id) oi', 'oi.order_id=o.id', 'left')
                ->from('orders o');

正确的PostgreSQL查询示例 #

$query = $this->db->select("o.*,concat('O',o.id) as id, c.name as customer,w.name as warehouse,a.name as account,a.type as channel,
         coalesce(oi.qty,0) as qty, coalesce(oi.picked_qty,0) as picked_qty, coalesce(oi.boxes,0) as boxes,coalesce(oi.packed_qty,0) as packed_qty,
         coalesce(oi.shipped_qty,0) as shipped_qty", FALSE)
                ->join("customers c", "c.id=o.customer_id")
                ->join("warehouses w", "w.id=o.warehouse_id")
                ->join("accounts a", "a.id=o.account_id")
                ->join("(select order_id, sum(qty) as qty,  sum(boxes) as boxes,sum(picked_qty) as picked_qty, sum(packed_qty) as packed_qty,
         sum(shipped_qty) as shipped_qty,  string_agg(coalesce(order_items.sku,'null'), '|') as skus,
         string_agg(order_items.in_stock, '|') as in_stock, string_agg(distinct(order_items.upc), '|') as upcs,
         string_agg(distinct order_items.ean, '|') as eans , string_agg(brand_name, '') brands,  string_agg(product_category, '') categories 
            from order_items  left join upc on order_items.sku = upc.sku group by order_items.order_id) oi", 'oi.order_id=o.id', 'left')
                ->from("orders o");

快速迁移方案 #

1. 扩展CodeIgniter PostgreSQL驱动 #

CodeIgniter 3允许扩展数据库驱动类，在驱动层自动处理函数替换和引号转换：

• 在application/core目录下创建MY_Postgre_driver.php，继承原生驱动并重写相关方法：

class MY_Postgre_driver extends CI_DB_postgre_driver {
    public function select($select = '', $escape = NULL) {
        // 替换IFNULL为coalesce
        $select = str_replace('IFNULL(', 'coalesce(', $select);
        // 替换group_concat为string_agg，处理separator语法
        $select = preg_replace('/group_concat\((.*?) separator "(.*?)"\)/', 'string_agg($1, \'$2\')', $select);
        $select = preg_replace('/group_concat\((.*?)\)/', 'string_agg($1, \'\')', $select);
        // 转换内部转义双引号为单引号
        $select = str_replace('"', "'", $select);
        return parent::select($select, $escape);
    }

    public function join($table, $cond, $type = '', $escape = NULL) {
        // 转换join语句的外层单引号为双引号
        $table = str_replace("'", '"', $table);
        $cond = str_replace("'", '"', $cond);
        return parent::join($table, $cond, $type, $escape);
    }
}

• 该方法无需修改业务代码，驱动层自动完成适配，适合大规模项目。

2. IDE全局正则替换 #

利用IDE（如PHPStorm、VS Code）的全局正则替换功能，分批次处理：

• 引号转换：
  • 查找：->select\('(.*?)"(.*?)"(.*?)', FALSE\)
  • 替换为：->select("$1'$2'$3", FALSE)
  • 对join/from等方法同理，将外层单引号替换为双引号，内部"替换为'
• 函数替换：
  • 全局替换IFNULL(为coalesce(
  • 查找：group_concat\((.*?) separator "(.*?)"\)
  • 替换为：string_agg($1, '$2')
• 注意：替换前务必备份代码，优先在测试环境验证，避开包含'.$/.'的PHP变量片段。

3. 批量处理脚本 #

编写PHP脚本遍历项目文件，针对性处理查询语句：

$dir = '/path/to/your/application/models'; // 替换为实际目录
$files = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($dir));

foreach ($files as $file) {
    if ($file->isFile() && $file->getExtension() == 'php') {
        $content = file_get_contents($file->getPathname());
        
        // 处理select语句
        $content = preg_replace_callback('/->select\(\'(.*?)\', FALSE\)/s', function($matches) {
            $query = $matches[1];
            $query = str_replace('"', "'", $query);
            $query = str_replace('IFNULL(', 'coalesce(', $query);
            $query = preg_replace('/group_concat\((.*?) separator "(.*?)"\)/', 'string_agg($1, \'$2\')', $query);
            $query = preg_replace('/group_concat\((.*?)\)/', 'string_agg($1, \'\')', $query);
            return '->select("'.$query.'", FALSE)';
        }, $content);
        
        // 处理join语句
        $content = preg_replace('/->join\(\'(.*?)\', \'(.*?)\'\)/', '->join("$1", "$2")', $content);
        
        file_put_contents($file->getPathname(), $content);
    }
}

• 脚本需先在测试环境运行，全程备份文件，避免不可逆错误。

4. 封装查询适配层 #

创建适配类封装原生数据库方法，在调用前完成转换：

class DB_Adapter {
    private $db;
    public function __construct() {
        $this->db =& get_instance()->db;
    }

    public function select($select = '', $escape = NULL) {
        $select = str_replace('IFNULL(', 'coalesce(', $select);
        $select = str_replace('"', "'", $select);
        $select = preg_replace('/group_concat\((.*?) separator "(.*?)"\)/', 'string_agg($1, \'$2\')', $select);
        return $this->db->select($select, $escape);
    }

    // 封装join、query等其他方法，逻辑类似
}

• 在项目中替换$this->db为$this->db_adapter，或重写CI控制器的db属性指向适配类。

内容的提问来源于stack exchange，提问作者Vishal Dubey