使用PostgreSQL CONCAT函数出现500错误及查询优化问询

首先，咱们先搞定500错误的问题——这类错误通常是因为具体的报错被隐藏了，所以第一步得先拿到具体的错误信息，才能精准定位：

一、排查500错误的步骤 #

1. 开启CodeIgniter调试模式
   打开项目根目录的index.php，把ENVIRONMENT的值改成development：

   define('ENVIRONMENT', isset($_SERVER['CI_ENV']) ? $_SERVER['CI_ENV'] : 'development');
   

   或者在application/config/config.php里调整日志级别：

   $config['log_threshold'] = 2;
   

   这样页面会直接显示错误详情，或者你可以去application/logs目录下查看日志文件，里面会记录具体的报错（比如SQL语法错误、数据库连接问题、权限问题等）。

2. 验证执行的SQL语句
   在你的Model方法里，执行查询后打印实际执行的SQL，看看和你在phppgAdmin里跑的是不是完全一致：

   public function select_by_location(){
       $sql = "select tb1.*, (select name_code from master_code tb2 where tb2.code like CONCAT('%', tb1.code, '%') LIMIT 1) as master_code_name from city tb1 where tb1.location_id like 'blablabla%'";
       $query = $this->db->query($sql);
       // 打印SQL并终止执行，复制到phppgAdmin里测试
       echo $this->db->last_query();
       die();
       return $query->result_array();
   }
   

   有时候CodeIgniter会自动转义某些字符，导致SQL和你预期的不一样，这一步能帮你确认问题。

3. 排查潜在的SQL兼容问题
   虽然PostgreSQL支持CONCAT函数，但你可以试试换成PostgreSQL原生的字符串拼接符||，看看是不是CodeIgniter对CONCAT的解析有问题：

   select tb1.*, (select name_code from master_code tb2 where tb2.code like '%' || tb1.code || '%' LIMIT 1) as master_code_name from city tb1 where tb1.location_id like 'blablabla%'
   

二、更高效的查询实现方式 #

你原来的关联子查询虽然加了LIMIT 1，但本质上还是每行都执行一次子查询，当city表数据量较大时，效率还是会受影响。推荐用PostgreSQL的DISTINCT ON特性结合LEFT JOIN来改写，只需要一次查询就能完成关联：

优化后的SQL语句 #

SELECT DISTINCT ON (tb1.id) 
       tb1.*, 
       tb2.name_code AS master_code_name
FROM city tb1
LEFT JOIN master_code tb2 
       ON tb2.code LIKE '%' || tb1.code || '%'
WHERE tb1.location_id LIKE 'blablabla%'
ORDER BY tb1.id, tb2.id; -- 这里按你需要的规则排序，确保取到你想要的第一个匹配项

DISTINCT ON (tb1.id)会保留每个城市ID对应的第一行数据，完美替代子查询里的LIMIT 1，而且查询效率会高很多。

CodeIgniter Active Record写法（更安全易维护） #

如果你想用CodeIgniter的Active Record来写，避免原生SQL的转义问题，可以这样写：

public function select_by_location() {
    // 直接写DISTINCT ON，第二个参数false表示不转义SELECT内容
    $this->db->select('DISTINCT ON (tb1.id) tb1.*, tb2.name_code AS master_code_name', false);
    $this->db->from('city tb1');
    // JOIN条件用||拼接，第三个参数false表示不转义
    $this->db->join('master_code tb2', "tb2.code LIKE '%' || tb1.code || '%'", 'left');
    // LIKE条件不转义%，第三个参数false
    $this->db->where('tb1.location_id LIKE', 'blablabla%', false);
    $this->db->order_by('tb1.id, tb2.id');
    
    $query = $this->db->get();
    return $query->result_array();
}

索引优化（进一步提速） #

如果master_code.code字段经常需要做%xxx%这类模糊查询，建议给它创建GIN trigram索引，PostgreSQL的这个索引能大幅提升模糊匹配的效率：

CREATE INDEX idx_master_code_code_trgm ON master_code USING GIN (code gin_trgm_ops);

创建后，LIKE '%xxx%'的查询会从全表扫描变成索引扫描，速度会有质的提升。

内容的提问来源于stack exchange，提问作者Ugy Astro