CodeIgniter为FROM子句加括号致MySQL 8.0语法错误求助

问题背景 #

服务器升级到MySQL 8.0后，原本正常运行的CodeIgniter查询突然报SQL语法错误，这类问题出现在大量现有代码中，原代码由离职同事编写，需要找到不用大量重写代码的解决办法。

引发问题的PHP代码 #

$this->db->select('tblIndicators.*,GROUP_CONCAT(tblVersionInfo.fkDatasetID) as fldDatasetIDs');
$this->db->join('tblIndicatorsDataset', 'tblIndicatorsDataset.fkIndicatorID = tblIndicators.pkIndicatorID');
$this->db->join('tblIndicatorsDatasetVersion', 'tblIndicatorsDatasetVersion.pkIndicatorsDatasetVersionID = tblIndicatorsDataset.fkIndicatorsDatasetVersionID', 'left');
$this->db->join('tblVersionInfo', 'tblIndicatorsDatasetVersion.fkDatasetVersionID = tblVersionInfo.pkVersionID', 'left');
$this->db->order_by('fldCategoryCode');
$this->db->order_by('fldOrder');
$this->db->where(array('fkIndicatorsVersionID'=>$indicatorsVersionID, 'fldDisabled'=>0));
$this->db->group_by('tblIndicators.fldIndicatorCode');

return $this->db->get($tblIndicators)->result(); 

补充：$tblIndicators的定义如下：

$tblIndicators = "(SELECT DISTINCT(tblIndicators.pkIndicatorID) as id, tblIndicators.* FROM tblIndicatorsDataset"
                    . " JOIN tblIndicators ON tblIndicatorsDataset.fkIndicatorID = tblIndicators.pkIndicatorID"
                    . " WHERE fkIndicatorsVersionID = $appVersionID) as tblIndicators";

错误信息 #

Query error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
JOIN `tblIndicatorsDataset` ON `tblIndicatorsDataset`.`fkIndicatorID` = `tblIn' at line 2 - Invalid query: SELECT `tblIndicators`.*, GROUP_CONCAT(tblVersionInfo.fkDatasetID) as fldDatasetIDs
FROM ((SELECT DISTINCT(tblIndicators.pkIndicatorID) as id, tblIndicators.* FROM tblIndicatorsDataset JOIN tblIndicators ON tblIndicatorsDataset.fkIndicatorID = tblIndicators.pkIndicatorID WHERE fkIndicatorsVersionID = 32) as tblIndicators)
JOIN `tblIndicatorsDataset` ON `tblIndicatorsDataset`.`fkIndicatorID` = `tblIndicators`.`pkIndicatorID`
LEFT JOIN `tblIndicatorsDatasetVersion` ON `tblIndicatorsDatasetVersion`.`pkIndicatorsDatasetVersionID` = `tblIndicatorsDataset`.`fkIndicatorsDatasetVersionID`
LEFT JOIN `tblVersionInfo` ON `tblIndicatorsDatasetVersion`.`fkDatasetVersionID` = `tblVersionInfo`.`pkVersionID`
WHERE `fkIndicatorsVersionID` = '32'
AND `fldDisabled` = 0
GROUP BY `tblIndicators`.`fldIndicatorCode`
ORDER BY `fldCategoryCode`, `fldOrder`

手动验证的可行修复 #

手动移除FROM子句外层的多余括号，并关闭ONLY_FULL_GROUP_BY后，查询可正常运行：

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

SELECT `tblIndicators`.*, GROUP_CONCAT(tblVersionInfo.fkDatasetID) as fldDatasetIDs
FROM (SELECT DISTINCT(tblIndicators.pkIndicatorID) as id, tblIndicators.* FROM tblIndicatorsDataset JOIN tblIndicators ON tblIndicatorsDataset.fkIndicatorID = tblIndicators.pkIndicatorID WHERE fkIndicatorsVersionID = 32) as tblIndicators
JOIN `tblIndicatorsDataset` ON `tblIndicatorsDataset`.`fkIndicatorID` = `tblIndicators`.`pkIndicatorID`
LEFT JOIN `tblIndicatorsDatasetVersion` ON `tblIndicatorsDatasetVersion`.`pkIndicatorsDatasetVersionID` = `tblIndicatorsDataset`.`fkIndicatorsDatasetVersionID`
LEFT JOIN `tblVersionInfo` ON `tblIndicatorsDatasetVersion`.`fkDatasetVersionID` = `tblVersionInfo`.`pkVersionID`
WHERE `fkIndicatorsVersionID` = '32'
AND `fldDisabled` = 0
GROUP BY `tblIndicators`.`fldIndicatorCode`
ORDER BY `fldCategoryCode`, `fldOrder`

问题根源 #

1. CodeIgniter的get()方法会自动给传入的表名/子查询包裹一层括号，但$tblIndicators本身就是带括号的子查询别名，叠加后出现((子查询) as tblIndicators)的双重括号结构，MySQL 8.0对这种语法兼容性变差，直接报错。
2. MySQL 8.0默认开启ONLY_FULL_GROUP_BY模式，原查询的GROUP BY字段和SELECT中的非聚合字段不匹配，触发语法校验错误。

批量解决方案（无需大量重写代码） #

方案1：修改CodeIgniter数据库驱动的编译逻辑 #

找到对应数据库驱动文件（比如system/database/drivers/mysqli/mysqli_driver.php），定位到_compile_select()方法中处理FROM子句的部分，添加判断：如果传入的表名以(开头且以) as 结尾，就直接使用该内容，不再额外包裹括号。

方案2：全局关闭ONLY_FULL_GROUP_BY #

在CodeIgniter的数据库配置文件application/config/database.php中，直接设置全局SQL_MODE，替代手动执行SET语句：

$db['default']['stricton'] = FALSE;
$db['default']['sql_mode'] = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

方案3：封装自定义的查询方法 #

在自定义的Model基类或数据库扩展类中，封装一个跳过括号包裹的查询方法：

public function get_no_wrap($table = '', $limit = NULL, $offset = NULL)
{
    if ($table !== '')
    {
        // 第二个参数FALSE表示不对表名/子查询进行包裹处理
        $this->from($table, FALSE);
    }
    $this->limit($limit, $offset);
    $query = $this->db->query($this->get_compiled_select());
    return $query->result();
}

之后把原有代码中的$this->db->get($tblIndicators)->result()替换为$this->get_no_wrap($tblIndicators)即可，修改量远比重写查询小。

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