我正在try 清理Go调用MySQL查询的方式,方法是在运行查询之前设置一些变量,并在单个值周围使用大量的Case语句.我试图运行的查询在控制台上运行得很好,但在运行GO时,由于语法问题,我在SELECT
上失败了.这样的事情有可能发生吗?
func (d *DB) SelectByUserId(uid string, srt string, pg, lim int) ([]Inventory, error) {
query := `
SET @user_id := ?,
@orderBy := ?;
SELECT
*
FROM
inventory
WHERE
user_id = @user_id
ORDER BY
(CASE WHEN @orderBy = 'type,asc' THEN type END),
(CASE WHEN @orderBy = 'type,desc' THEN type END) DESC,
(CASE WHEN @orderBy = 'visible,asc' THEN visible END),
(CASE WHEN @orderBy = 'visible,desc' THEN visible END) DESC,
(CASE WHEN @orderBy = 'create_date,asc' THEN create_date END),
(CASE WHEN @orderBy = 'create_date,desc' THEN create_date END) DESC,
(CASE WHEN @orderBy = 'update_date,asc' THEN update_date END),
(CASE WHEN @orderBy = 'update_date,desc' THEN update_date END) DESC
LIMIT ?,?;
`
rows, err := d.Query(
query,
uid,
srt,
pg*lim,
lim,
)
if err != nil {
return nil, err
}
defer rows.Close()
result := make([]Inventory, 0)
for rows.Next() {
var inv Inventory
if err := rows.Scan(
&inv.Id,
&inv.UserId,
&inv.Type,
&inv.Name,
&inv.Description,
&inv.Visible,
&inv.CreateDate,
&inv.UpdateDate); err != nil {
return result, err
}
result = append(result, inv)
}
if err = rows.Err(); err != nil {
return result, err
}
return result, nil
}
现在,如果我拿出设置的棋子,用?
替换所有的@
个变量,然后多次传递srt
个变量,如下所示.但我真的不希望有这样的查询呼叫:
rows, err := d.Query(
query,
uid,
srt,
srt,
srt,
srt,
srt,
srt,
srt,
srt,
pg*lim,
lim)
报告的错误没有多大帮助:
Error 1064: 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 'SELECT\n *\nFROM\n inventory\nWHERE\n user_id = @user_id\nORDER BY\n ' at line 3
谢谢你的帮助.