Laravel 8执行SQL报MariaDB 1064语法错误 同语句在DBeaver运行正常

问题描述 #

我在Laravel 8中遇到如下报错：

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '*) 

我的查询代码编写如下：

$totalCitasGenero = DB::table('citas')->selectRaw('idEspecialidad, genero,  count (*) as totalCitas')->join('personas', 'personas.id', '=', 'citas.idPersonaP')->groupBy('idEspecialidad','genero')->get();

涉及的两张数据表结构如下：

CREATE TABLE `personas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nombre` varchar(50) NOT NULL,
  `apellido` varchar(50) NOT NULL,
  `cedula` varchar(10) NOT NULL,
  `email` varchar(40) DEFAULT NULL,
  `telefono` varchar(13) DEFAULT NULL,
  `direccion` varchar(100) NOT NULL,
  `ciudadResi` varchar(50) NOT NULL,
  `fechaNacimiento` date NOT NULL,
  `genero` varchar(100) NOT NULL,
  `estado` binary(1) NOT NULL,
  `idTipoPersona` int(11) NOT NULL,
  `idPersona` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `personas_FK` (`idTipoPersona`),
  KEY `personas_FK_1` (`idPersona`),
  CONSTRAINT `personas_FK` FOREIGN KEY (`idTipoPersona`) REFERENCES `tipo_personas` (`id`),
  CONSTRAINT `personas_FK_1` FOREIGN KEY (`idPersona`) REFERENCES `personas` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `citas` (
  `idPersonaD` int(11) NOT NULL,
  `idPersonaP` int(11) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fecha` date NOT NULL,
  `hora` time NOT NULL,
  `idEspecialidad` int(11) NOT NULL,
  `estado` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `citas_FK` (`idPersonaD`),
  KEY `citas_FK_1` (`idPersonaP`),
  KEY `citas_FK_2` (`idEspecialidad`),
  CONSTRAINT `citas_FK` FOREIGN KEY (`idPersonaD`) REFERENCES `personas` (`id`),
  CONSTRAINT `citas_FK_1` FOREIGN KEY (`idPersonaP`) REFERENCES `personas` (`id`),
  CONSTRAINT `citas_FK_2` FOREIGN KEY (`idEspecialidad`) REFERENCES `especialidades` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=utf8mb4;

我在DBeaver中执行相同逻辑的SQL语句可正常运行，语句如下：

select idEspecialidad, genero,  count (*) as totalCitas 
from citas as c join personas
where c.idPersonaP = personas.id 
group by genero, idEspecialidad ;

期望得到的查询结果如下图所示：

我将代码中的->get()替换为->toSql()打印出最终生成的SQL语句如下，将该语句复制到DBeaver也可正常运行，仅在Laravel中执行会报错，无法定位问题原因，特此咨询该问题的根源及解决方案。

select idEspecialidad, genero,  count (*) as totalCitas from `citas` inner join `personas` on `personas`.`id` = `citas`.`idPersonaP` group by `idEspecialidad`, `genero`

问题根源 #

错误出在count (*)里的空格，Laravel底层用的PDO预处理机制在解析SQL时，会把count和(*)识别为两个独立的语法片段，不符合SQL语法规范。而DBeaver的SQL解析器会自动忽略函数名和括号之间的冗余空格，所以能正常执行。

解决方案 #

把selectRaw里的count (*)改成count(*)即可，修改后的代码如下：

$totalCitasGenero = DB::table('citas')
    ->selectRaw('idEspecialidad, genero, count(*) as totalCitas')
    ->join('personas', 'personas.id', '=', 'citas.idPersonaP')
    ->groupBy('idEspecialidad','genero')
    ->get();

如果修改后仍有报错，可以检查Laravel配置的数据库版本是否和DBeaver连接的版本一致，同时确认config/database.php里的strict模式配置匹配你的SQL_MODE要求。 #

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