MySQL中AND子句使用及地理位置查询SQL报错排查

问题背景 #

单独运行两段查询逻辑都正常，但用AND结合后无法执行：

• 校验matmemmatrix表中指定列值为1的查询
• 计算指定坐标25英里范围内地理位置的查询
  尝试子查询、无关联查询等写法时，要么在AS子句后报错，要么触发MariaDB错误：This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

核心需求是同时满足两个条件：

1. matmemmatrix表中由变量$a指定的列值为1
2. 地理位置距离指定坐标（$mylat、$mylong）25英里范围内

尝试的代码片段 #

第一段尝试代码 #

<?php
    $miles = '25'; //your search radius

    $resul = mysqli_query($con,"SELECT memlocid, LocLat, LocLong
    FROM matmemmatrix 
    inner JOIN matloc
    ON matloc.MLocID = matmemmatrix.memlocid
    WHERE `$a` = '1' AND
    

    ( 3959 * acos( cos( radians('$mylat') ) * 
    cos( radians( LocLat ) ) * 
    cos( radians( LocLong ) - 
    radians('$mylong') ) + 
    sin( radians('$mylat') ) * 
    sin( radians( LocLat ) ) ) ) 
    AS distance FROM matloc HAVING distance < '$miles' ORDER BY 
    distance ASC LIMIT 0, 5");
?>

子查询尝试代码 #

<?php
$miles = '25'; //your search radius

$resul = mysqli_query($con,"SELECT MLocID, LocLat, LocLong, ( 3959 * acos( cos( radians('$mylat') ) * 
cos( radians( LocLat ) ) * 
cos( radians( LocLong ) - 
radians('$mylong') ) + 
sin( radians('$mylat') ) * 
sin( radians( LocLat ) ) ) )
AS distance FROM matloc HAVING distance < '$miles' ORDER BY distance ASC LIMIT 0, 5
WHERE 
    `$a` IN (SELECT 
     memlocid
    FROM
     matmemmatrix
     WHERE `a`='1')");
?>

无关联查询尝试代码 #

<?php
$miles = '25'; //your search radius

$resul = mysqli_query($con,"SELECT MLocID, LocLat, LocLong, ( 3959 * acos( cos( radians('$mylat') ) * 
cos( radians( LocLat ) ) * 
cos( radians( LocLong ) - 
radians('$mylong') ) + 
sin( radians('$mylat') ) * 
sin( radians( LocLat ) ) ) )

AS distance FROM matloc HAVING distance < '$miles' ORDER BY distance ASC LIMIT 0, 5
inner JOIN matmemmatrix
ON matloc.MLocID = matmemmatrix.memlocid
WHERE `$a` = '1'");
?>

最新尝试代码（触发报错） #

<?php
$miles = '25'; //your search radius

$resul = mysqli_query($con, "SELECT MLocID, LocLat, LocLong
FROM matloc
inner JOIN matmemmatrix
ON matloc.MLocID = matmemmatrix.memlocid
WHERE `$a` = '1' IN 
    (SELECT ( 3959 * acos( cos( radians('$mylat') ) * 
cos( radians( LocLat ) ) * 
cos( radians( LocLong ) - 
radians('$mylong') ) + 
sin( radians('$mylat') ) * 
sin( radians( LocLat ) ) ) ) 
AS distance FROM matloc HAVING distance < '$miles' 
ORDER BY distance ASC LIMIT 0, 5)");
?>

报错信息 #

Fatal error: Uncaught mysqli_sql_exception: This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' in C:\xampp\htdocs\mydb\tempdistance.php:22 Stack trace: #0 C:\xampp\htdocs\mydb\tempdistance.php(22): mysqli_query(Object(mysqli), 'SELECT MLocID, ...') #1 {main} thrown in C:\xampp\htdocs\mydb\tempdistance.php on line 22

解决方案 #

问题根源是SQL语法顺序错误，以及子查询带LIMIT触发的MariaDB兼容性限制。正确写法是先关联表、校验列值，再计算距离并过滤范围：

<?php
$miles = '25'; // 搜索半径

// 修正后的SQL语句，遵循正确语法顺序
$query = "
SELECT 
    matloc.MLocID, 
    matloc.LocLat, 
    matloc.LocLong,
    (3959 * acos( 
        cos(radians('$mylat')) * 
        cos(radians(matloc.LocLat)) * 
        cos(radians(matloc.LocLong) - radians('$mylong')) + 
        sin(radians('$mylat')) * 
        sin(radians(matloc.LocLat)) 
    )) AS distance
FROM matloc
INNER JOIN matmemmatrix 
    ON matloc.MLocID = matmemmatrix.memlocid
WHERE `$a` = '1'
HAVING distance < '$miles'
ORDER BY distance ASC
LIMIT 0, 5
";

$resul = mysqli_query($con, $query);
?>

关键说明 #

1. 语法顺序修正：严格遵循SELECT→FROM→JOIN→WHERE→HAVING→ORDER BY→LIMIT的SQL语法顺序，之前的尝试多次颠倒顺序导致语法错误。
2. 避开子查询限制：直接在关联后的结果中计算距离，用HAVING过滤范围，无需使用带LIMIT的子查询，完美规避MariaDB的兼容性问题。
3. 字段归属明确：指定字段所属表（如matloc.LocLat），避免多表关联时的字段歧义。

重要提醒：当前代码存在SQL注入风险，建议使用预处理语句替代直接变量拼接，示例如下：

$query = "
SELECT 
    matloc.MLocID, 
    matloc.LocLat, 
    matloc.LocLong,
    (3959 * acos( 
        cos(radians(?)) * 
        cos(radians(matloc.LocLat)) * 
        cos(radians(matloc.LocLong) - radians(?)) + 
        sin(radians(?)) * 
        sin(radians(matloc.LocLat)) 
    )) AS distance
FROM matloc
INNER JOIN matmemmatrix 
    ON matloc.MLocID = matmemmatrix.memlocid
WHERE `$a` = ?
HAVING distance < ?
ORDER BY distance ASC
LIMIT 0, 5
";

$stmt = mysqli_prepare($con, $query);
mysqli_stmt_bind_param($stmt, "ssss", $mylat, $mylong, $mylat, $miles);
mysqli_stmt_execute($stmt);
$resul = mysqli_stmt_get_result($stmt);

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