如何在Go中的sqlx查询中使用通配符？
问题描述：
I'm using sqlx with mysql database and want to query author table for names which start with certain letters. Here is the query:
sqlx.DB.Select(&authors, "SELECT * FROM author WHERE first_name LIKE ?% OR last_name LIKE ?%", letter,letter)
but I get
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '% OR last_name LIKE ?%'
I've looked in the docs for examples of LIKE queries but could not find any. So wondering how can I fix this?
答
You have % in there, wouldn't those be ?, ? or :p1, :p2, $1, $2 (whatever the MySQL parameter placeholder is)
I would do like this:
sqlx.DB.Select(&authors, "SELECT * FROM author WHERE first_name LIKE ? OR last_name LIKE ?",
letter+"%",letter+"%")
The wildcard % should be part of the parameter string.
答
You can use SQL string concatenation:
SELECT * FROM author
WHERE first_name LIKE ? || '%' OR last_name LIKE ? || '%'
答
Another way to do it is using CONCAT:
sqlx.DB.Select(&authors, "SELECT * FROM author WHERE first_name LIKE CONCAT(?, '%') OR last_name LIKE CONCAT(?, '%')", letter, letter)
Hope it helps!