Laravel中SQLSTATE[HY093]参数错误但SQL直接执行正常问题

错误根源 #

你在whereRaw的SQL语句中，把参数占位符?包裹在了单引号内部（'POLYGON((? ?, ...))'）。Laravel的查询构建器不会解析单引号内的占位符，导致你传入的10个参数没有对应的占位符匹配，触发SQLSTATE[HY093]: Invalid parameter number错误。而你看到的错误提示中的SQL是Laravel为调试自动替换参数后的结果，所以直接执行能正常返回数据。

方案一：预构建多边形坐标字符串 #

先拼接好完整的多边形坐标串，再作为单个参数传入，代码简洁且安全：

// 拼接多边形的坐标点，闭合多边形
$polygonCoords = implode(', ', [
    "{$southWest['lat']} {$southWest['lng']}",
    "{$southWest['lat']} {$northEast['lng']}",
    "{$northEast['lat']} {$northEast['lng']}",
    "{$northEast['lat']} {$southWest['lng']}",
    "{$southWest['lat']} {$southWest['lng']}"
]);

$events = Event::select(
    'id',
    'name',
    'description',
    DB::raw('ST_X(location) as latitude'),
    DB::raw('ST_Y(location) as longitude'),
    'event_date'
)
->whereRaw(
    "ST_Contains(ST_GeomFromText('POLYGON((?))'), location)",
    [$polygonCoords]
)
->get();

方案二：使用CONCAT拼接SQL字符串 #

通过CONCAT函数将静态字符串和参数拼接，确保占位符不在单引号内，让Laravel正确解析每个参数：

$events = Event::select(
    'id',
    'name',
    'description',
    DB::raw('ST_X(location) as latitude'),
    DB::raw('ST_Y(location) as longitude'),
    'event_date'
)
->whereRaw(
    "ST_Contains(
        ST_GeomFromText(CONCAT('POLYGON((', ?, ' ', ?, ', ', ?, ' ', ?, ', ', ?, ' ', ?, ', ', ?, ' ', ?, ', ', ?, ' ', ?, '))')),
        location
    )",
    [
        $southWest['lat'],
        $southWest['lng'],
        $southWest['lat'],
        $northEast['lng'],
        $northEast['lat'],
        $northEast['lng'],
        $northEast['lat'],
        $southWest['lng'],
        $southWest['lat'],
        $southWest['lng']
    ]
)
->get();

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