Golang用原生SQL API执行SQLite3查询遇near '%'语法错误如何解决？

Hey, that near "%": syntax error is a super common gotcha when working with SQLite in Go—let’s break down what’s probably going wrong and how to fix it fast.

1. 你大概率搞混了占位符和模糊查询的写法 #

SQLite only recognizes ? as a parameter placeholder, and you can’t mix % directly with the placeholder in your SQL statement. For example, if you wrote something like:

SELECT * FROM users WHERE name LIKE '%?%'

SQLite will parse %?% as a raw string. When it hits the ? right after %, it gets confused and throws that syntax error—it doesn’t recognize the ? as a parameter marker at all.

2. 正确的姿势：把%放在参数值里，SQL只留占位符 #

You need to append/prepend the % to your search term, then pass that combined value via the ? placeholder. This not only fixes the syntax error but also protects you from SQL injection.

Here’s a complete, runnable example:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
)

func main() {
    // Open database connection
    db, err := sql.Open("sqlite3", "./your-db-file.db")
    if err != nil {
        fmt.Printf("Failed to open DB: %v\n", err)
        return
    }
    defer db.Close()

    // Your search keyword
    searchKeyword := "doe"

    // Critical: Use only ? in SQL, add % to the parameter value
    query := "SELECT id, username FROM users WHERE username LIKE ?"
    rows, err := db.Query(query, "%"+searchKeyword+"%")
    if err != nil {
        fmt.Printf("Error executing query: %v\n", err)
        return
    }
    defer rows.Close()

    // Iterate over results
    var id int
    var username string
    for rows.Next() {
        if err := rows.Scan(&id, &username); err != nil {
            fmt.Printf("Failed to scan row: %v\n", err)
            return
        }
        fmt.Printf("ID: %d, Username: %s\n", id, username)
    }

    // Check for errors during iteration
    if err := rows.Err(); err != nil {
        fmt.Printf("Row iteration error: %v\n", err)
    }
}

3. Avoid these extra pitfalls #

• Never directly concatenate SQL strings (like "SELECT * FROM users WHERE username LIKE '%"+searchKeyword+"%'"). This isn’t just a syntax risk—it opens you up to deadly SQL injection attacks.
• Don’t use placeholders from other databases (like PostgreSQL’s $1 or MySQL’s :name). SQLite only supports ?.
• For multiple parameters, just use ? in order, e.g., SELECT * FROM users WHERE name LIKE ? AND age > ?, then pass parameters in the same order.

Quick troubleshooting step #

If you’re still stuck, print out your final query and parameters to spot mistakes:

fmt.Printf("Query: %s, Params: %v\n", query, []interface{}{"%"+searchKeyword+"%"})

This will show you if there are extra % signs or misplaced placeholders in your SQL.

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