Go postgresql LIKE 查询

huangapple go评论97阅读模式
英文:

Go postgresql LIKE query

问题

我正在使用Go和PostgreSQL(pq驱动程序)进行工作,我有以下查询:

SELECT p.id, p.name, p.description, p.price, p.image, p.rate
FROM products AS p
WHERE LOWER(p.name) LIKE %$1% ORDER BY p.rate DESC

如果我直接在PostgreSQL中执行此查询,它可以工作,但在Golang中会显示以下错误:

pq: syntax error at or near "%"

你该如何修复它?我尝试使用"%",但没有起作用。谢谢。

以下是完整的源代码:

func FindByName(name *string) ([]*Product, error) {
    db, err := db.StablishConnection()
    if err != nil {
        log.Fatal(err)
        panic(err)
    }
    defer db.Close()

    query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
        FROM products AS p
        WHERE LOWER(p.name) LIKE %$1% ORDER BY p.rate DESC`

    product_rows, err := db.Query(query, name)

    if err != nil {
        return nil, err
    }

    if product_rows == nil {
        return nil, errors.New("No Products Named " + *name)
    }

    products := []*Product{}
    for product_rows.Next() {
        product := new(Product)
        err = product_rows.Scan(&product.Id,
            &product.Name,
            &product.Description,
            &product.Price,
            &product.Image,
            &product.Rate)
        if err != nil {
            panic(err)
        }
        products = append(products, product)
    }
    return products, nil
}

请注意,我只会翻译代码部分,不会回答关于翻译的问题。

英文:

I'm working with Go and PostgreSQL (pq driver), I have the following query

SELECT p.id, p.name, p.description, p.price, p.image, p.rate
FROM products AS p
WHERE LOWER(p.name) LIKE %$1% ORDER BY p.rate DESC

If I exec this query directly in PostgreSQL it work but in Golang says:

pq: syntax error at or near "%"

How can I fix it? I tried with "%" but didn't works.
thanks.

here is the complete source code

func FindByName(name *string) ([]*Product, error) {
    db, err := db.StablishConnection()
    if err != nil {
            log.Fatal(err)
            panic(err)
    }
    defer db.Close()

    query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
        FROM products AS p
        WHERE LOWER(p.name) LIKE %$1% ORDER BY p.rate DESC`

    product_rows, err := db.Query(query, name)

    if err != nil {
            return nil, err
    }

    if product_rows == nil {
            return nil, errors.New("No Products Named " + *name)
    }

    products := []*Product{}
    for product_rows.Next() {
            product := new(Product)
            err = product_rows.Scan(&product.Id,
                    &product.Name,
                    &product.Description,
                    &product.Price,
                    &product.Image,
                    &product.Rate)
            if err != nil {
                    panic(err)
            }
            products = append(products, product)
    }
    return products, nil
}

答案1

得分: 47

你需要将like模式放在单引号中:

SELECT p.id, p.name, p.description, p.price, p.image, p.rate
FROM products AS p
WHERE LOWER(p.name) LIKE '%'|| $1 || '%'
ORDER BY p.rate DESC;
英文:

You need to put the like pattern in single quotes:

SELECT p.id, p.name, p.description, p.price, p.image, p.rate
FROM products AS p
WHERE LOWER(p.name) LIKE '%' || $1 || '%'
ORDER BY p.rate DESC;

答案2

得分: 0

我猜这是做这个的最正确的方式:

query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
    FROM products AS p
    WHERE LOWER(p.name) LIKE CONCAT('%%',$1::text,'%%') ORDER BY p.rate DESC`

product_rows, err := db.Query(query, name)

if err != nil {
        return nil, err
}
英文:

I guess this is the most correct way of doing this:

query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
    FROM products AS p
    WHERE LOWER(p.name) LIKE CONCAT('%%',$1::text,'%%') ORDER BY p.rate DESC`

product_rows, err := db.Query(query, name)

if err != nil {
        return nil, err
}

答案3

得分: -1

不要在准备查询时使用引号。只需使用引号和百分号提供值即可解决问题。经过尝试和测试。

解决方案:
查询 := SELECT p.id, p.name, p.description, p.price, p.image, p.rate FROM products AS p WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC

product_rows, err := db.Query(query, "%"+name+"%")

我从这个讨论串中找到了解决方案。

英文:

Dont put qoutes when you are preparing your query. Just provide the value with qoutes and % sign. This will solve the problem. tried and tested.

Solution:
query := SELECT p.id, p.name, p.description, p.price, p.image, p.rate
FROM products AS p
WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC

product_rows, err := db.Query(query, "'%" + name + "%'")

I got my soltion from this thread

答案4

得分: -2

查询 := SELECT p.id, p.name, p.description, p.price, p.image, p.rate FROM products AS p WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC

产品行, 错误 := db.Query(查询, '%'+名称+ '%')

英文:
query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
    FROM products AS p
    WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC`

product_rows, err := db.Query(query, '%' + name+ '%'))

答案5

得分: -2

这对我来说有效。

query := "SELECT ProductId,Name,Description,Price,SKU FROM Products WHERE Name LIKE ?"
rows, err := r.db.QueryContext(ctx, query, "%"+name+"%")
英文:

This works for me

query := "SELECT ProductId,Name,Description,Price,SKU FROM Products WHERE Name LIKE ?"
rows, err := r.db.QueryContext(ctx, query, "%"+name+"%")

答案6

得分: -3

根据这个问题,你的查询语句不能包含'%'符号,但是"name"参数必须用'%'引起来。

query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
    FROM products AS p
    WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC`

product_rows, err := db.Query(query, fmt.Sprintf("%%%s%%", name))
英文:

According to this issue your query must not contain '%' sign, but "name" parameter must be quoted by '%'

query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
    FROM products AS p
    WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC`

product_rows, err := db.Query(query, fmt.Sprintf("%%%s%%", name))

huangapple
  • 本文由 发表于 2014年8月9日 09:41:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/25214459.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定