如何解决Lumen中出现的SQLSTATE[42000]: 1064语法错误？

我遇到一个奇怪的问题，执行的SQL语句如下：

UPDATE TABLE1 SET VALUE2 ='value2' WHERE VALUE1 = 'value1'; 
INSERT INTO TABLE1(VALUE1, VALUE2)  
SELECT 'value1' AS VALUE1, 'value2' AS VALUE2
FROM TABLE1 x
WHERE x.VALUE1 = 'value1' 
HAVING COUNT(*) = 0; 

尝试用同一条SQL语句完成更新和插入操作，该语句在MySQL Workbench中运行正常，但在Lumen控制器中却抛出错误：

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

原因分析 #

Lumen（基于Laravel）的数据库组件默认禁止执行多语句SQL（即单条执行命令中包含多个用分号分隔的SQL语句），这是出于防范SQL注入的安全设计。MySQL Workbench允许执行多语句，但Lumen会把整个带分号的内容当作单条语句解析，自然会触发语法错误。

解决方案 #

方案1：拆分为两条独立语句执行 #

将更新和插入操作分开，分别调用Lumen的数据库执行方法：

// 执行更新操作
DB::statement("UPDATE TABLE1 SET VALUE2 ='value2' WHERE VALUE1 = 'value1';");

// 执行插入操作
DB::statement("INSERT INTO TABLE1(VALUE1, VALUE2)  
SELECT 'value1' AS VALUE1, 'value2' AS VALUE2
FROM TABLE1 x
WHERE x.VALUE1 = 'value1' 
HAVING COUNT(*) = 0;");

方案2：改用INSERT ... ON DUPLICATE KEY UPDATE（更高效推荐） #

先给TABLE1的VALUE1字段添加唯一索引，然后用这条单语句替代原有的两条逻辑，既能更新已存在的记录，又能插入不存在的记录，完全符合Lumen的执行要求：

INSERT INTO TABLE1(VALUE1, VALUE2) 
VALUES ('value1', 'value2')
ON DUPLICATE KEY UPDATE VALUE2 = 'value2';

对应的Lumen代码：

DB::statement("INSERT INTO TABLE1(VALUE1, VALUE2) 
VALUES ('value1', 'value2')
ON DUPLICATE KEY UPDATE VALUE2 = 'value2';");

注：原SQL中用HAVING COUNT(*) = 0判断记录是否存在的逻辑，本质是统计匹配VALUE1='value1'的行数，ON DUPLICATE KEY UPDATE依赖唯一索引自动判断记录是否存在，逻辑更简洁且性能更优。

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