Go postgresql LIKE 查询

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

Go postgresql LIKE query

问题

我正在使用Go和PostgreSQL（pq驱动程序）进行工作，我有以下查询：

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

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

  1. pq: syntax error at or near "%"

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

以下是完整的源代码：

  1. func FindByName(name *string) ([]*Product, error) {
  2.     db, err := db.StablishConnection()
  3.     if err != nil {
  4.         log.Fatal(err)
  5.         panic(err)
  6.     }
  7.     defer db.Close()
  9.     query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
  10.         FROM products AS p
  11.         WHERE LOWER(p.name) LIKE %$1% ORDER BY p.rate DESC`
  13.     product_rows, err := db.Query(query, name)
  15.     if err != nil {
  16.         return nil, err
  17.     }
  19.     if product_rows == nil {
  20.         return nil, errors.New("No Products Named " + *name)
  21.     }
  23.     products := []*Product{}
  24.     for product_rows.Next() {
  25.         product := new(Product)
  26.         err = product_rows.Scan(&product.Id,
  27.             &product.Name,
  28.             &product.Description,
  29.             &product.Price,
  30.             &product.Image,
  31.             &product.Rate)
  32.         if err != nil {
  33.             panic(err)
  34.         }
  35.         products = append(products, product)
  36.     }
  37.     return products, nil
  38. }

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

英文:

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

  1. SELECT p.id, p.name, p.description, p.price, p.image, p.rate
  2. FROM products AS p
  3. 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:

  1. 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

  1. func FindByName(name *string) ([]*Product, error) {
  2.     db, err := db.StablishConnection()
  3.     if err != nil {
  4.             log.Fatal(err)
  5.             panic(err)
  6.     }
  7.     defer db.Close()
  9.     query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
  10.         FROM products AS p
  11.         WHERE LOWER(p.name) LIKE %$1% ORDER BY p.rate DESC`
  13.     product_rows, err := db.Query(query, name)
  15.     if err != nil {
  16.             return nil, err
  17.     }
  19.     if product_rows == nil {
  20.             return nil, errors.New("No Products Named " + *name)
  21.     }
  23.     products := []*Product{}
  24.     for product_rows.Next() {
  25.             product := new(Product)
  26.             err = product_rows.Scan(&product.Id,
  27.                     &product.Name,
  28.                     &product.Description,
  29.                     &product.Price,
  30.                     &product.Image,
  31.                     &product.Rate)
  32.             if err != nil {
  33.                     panic(err)
  34.             }
  35.             products = append(products, product)
  36.     }
  37.     return products, nil
  38. }

答案1

得分: 47

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

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

You need to put the like pattern in single quotes:

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

答案2

得分: 0

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

  1. query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
  2.     FROM products AS p
  3.     WHERE LOWER(p.name) LIKE CONCAT('%%',$1::text,'%%') ORDER BY p.rate DESC`
  5. product_rows, err := db.Query(query, name)
  7. if err != nil {
  8.         return nil, err
  9. }
英文:

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

  1. query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
  2.     FROM products AS p
  3.     WHERE LOWER(p.name) LIKE CONCAT('%%',$1::text,'%%') ORDER BY p.rate DESC`
  5. product_rows, err := db.Query(query, name)
  7. if err != nil {
  8.         return nil, err
  9. }

答案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

  1. 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

  1. 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(查询, '%'+名称+ '%')

英文: 
  1. query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
  2.     FROM products AS p
  3.     WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC`
  5. product_rows, err := db.Query(query, '%' + name+ '%'))

答案5

得分: -2

这对我来说有效。

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

This works for me

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

答案6

得分: -3

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

  1. query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
  2.     FROM products AS p
  3.     WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC`
  5. 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 '%'

  1. query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
  2.     FROM products AS p
  3.     WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC`
  5. product_rows, err := db.Query(query, fmt.Sprintf("%%%s%%", name))

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

发表评论

匿名网友

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

确定