英文:
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))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论