MySQL圆形区域查询结果过滤：如何仅保留state为usable的记录？

Let's break down what's going on here and get your query working correctly in your Go server.

First, Fix the SQL Query Logic #

Your original query uses HAVING d.state="faulty" which isn't the right approach here—HAVING is meant for filtering aggregated results (like after GROUP BY), not regular columns. Since you want only "usable" fountains, we can adjust the query in a cleaner, more efficient way:

Option 1: Filter Early in the Subquery (Better Performance)

Adding the state = 'usable' condition directly in the inner query reduces the number of rows processed early on, which boosts performance:

SELECT id, ST_X(location), ST_Y(location), state 
FROM ( 
    SELECT id, location, state, r, 
           units * DEGREES( ACOS( COS(RADIANS(latpoint)) * COS(RADIANS(ST_X(location))) * COS(RADIANS(longpoint) - RADIANS(ST_Y(location))) + SIN(RADIANS(latpoint)) * SIN(RADIANS(ST_X(location))))) AS distance 
    FROM fountains 
    JOIN ( SELECT 13.1 AS latpoint, 2 AS longpoint, 1000 AS r, 111.045 AS units ) AS p ON (1=1) 
    WHERE MbrContains(
        ST_GeomFromText( CONCAT('LINESTRING(', latpoint-(r/units),' ', longpoint-(r /(units* COS(RADIANS(latpoint)))), ',', latpoint+(r/units) ,' ', longpoint+(r /(units * COS(RADIANS(latpoint)))), ')')), 
        location
    )
    AND state = 'usable' -- Add filter here to cut down on rows early
) AS d 
WHERE d.distance <= d.r 
ORDER BY d.distance ASC;

Option 2: Filter in the Outer Query

If you prefer to keep the inner query focused solely on distance calculations, you can add the condition to the outer WHERE clause instead:

SELECT id, ST_X(location), ST_Y(location), state 
FROM ( 
    SELECT id, location, state, r, 
           units * DEGREES( ACOS( COS(RADIANS(latpoint)) * COS(RADIANS(ST_X(location))) * COS(RADIANS(longpoint) - RADIANS(ST_Y(location))) + SIN(RADIANS(latpoint)) * SIN(RADIANS(ST_X(location))))) AS distance 
    FROM fountains 
    JOIN ( SELECT 13.1 AS latpoint, 2 AS longpoint, 1000 AS r, 111.045 AS units ) AS p ON (1=1) 
    WHERE MbrContains(
        ST_GeomFromText( CONCAT('LINESTRING(', latpoint-(r/units),' ', longpoint-(r /(units* COS(RADIANS(latpoint)))), ',', latpoint+(r/units) ,' ', longpoint+(r /(units * COS(RADIANS(latpoint)))), ')')), 
        location
    )
) AS d 
WHERE d.distance <= d.r 
AND d.state = 'usable' -- Filter usable states here
ORDER BY d.distance ASC;

Why It Might Fail in Go (and How to Fix It) #

Most issues in Go stem from two common mistakes:

1. Incorrect SQL string formatting: Direct string concatenation can lead to quote escaping errors or invalid syntax.
2. Missing parameter binding: Hardcoding values like 'usable' risks SQL injection, and improper parameter passing can make your condition fail silently.

Here's a safe, correct way to execute this query in Go using the standard database/sql package with proper parameter binding (using Option 1 as an example):

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

type Fountain struct {
    ID    int
    Lat   float64
    Lng   float64
    State string
}

func main() {
    // Initialize database connection (replace with your credentials)
    db, err := sql.Open("mysql", "your_user:your_password@tcp(localhost:3306)/your_db")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Dynamic parameters you can adjust as needed
    latpoint := 13.1
    longpoint := 2.0
    radius := 1000.0
    units := 111.045
    targetState := "usable"

    // Use ? placeholders for all dynamic values to avoid injection and syntax issues
    query := `
        SELECT id, ST_X(location), ST_Y(location), state 
        FROM ( 
            SELECT id, location, state, r, 
                   units * DEGREES( ACOS( COS(RADIANS(latpoint)) * COS(RADIANS(ST_X(location))) * COS(RADIANS(longpoint) - RADIANS(ST_Y(location))) + SIN(RADIANS(latpoint)) * SIN(RADIANS(ST_X(location))))) AS distance 
            FROM fountains 
            JOIN ( SELECT ? AS latpoint, ? AS longpoint, ? AS r, ? AS units ) AS p ON (1=1) 
            WHERE MbrContains(
                ST_GeomFromText( CONCAT('LINESTRING(', latpoint-(r/units),' ', longpoint-(r /(units* COS(RADIANS(latpoint)))), ',', latpoint+(r/units) ,' ', longpoint+(r /(units * COS(RADIANS(latpoint)))), ')')), 
                location
            )
            AND state = ?
        ) AS d 
        WHERE d.distance <= d.r 
        ORDER BY d.distance ASC;
    `

    // Execute query with parameters
    rows, err := db.Query(query, latpoint, longpoint, radius, units, targetState)
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    // Parse results into a struct slice
    var fountains []Fountain
    for rows.Next() {
        var f Fountain
        if err := rows.Scan(&f.ID, &f.Lat, &f.Lng, &f.State); err != nil {
            panic(err)
        }
        fountains = append(fountains, f)
    }

    // Check for row iteration errors
    if err := rows.Err(); err != nil {
        panic(err)
    }

    // Print results
    for _, fountain := range fountains {
        fmt.Printf("ID: %d | Lat: %.1f | Lng: %.1f | State: %s\n", fountain.ID, fountain.Lat, fountain.Lng, fountain.State)
    }
}

Key takeaways for Go:

• Always use ? placeholders for dynamic values to let the driver handle escaping and avoid SQL injection.
• Write multi-line SQL strings for readability—it makes syntax errors easier to spot.
• Use defer to clean up database connections and row iterators to prevent resource leaks.

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