我正在尝试通过在围绕单个值使用大量 case 语句运行查询之前设置一些变量来清理 go 调用 mysql 查询的方式。我尝试运行的查询在控制台上运行良好,但在通过 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 }
现在,如果我取出 set 部分并将所有 @
变量替换为 ?
,然后多次传递 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 'SELECTn *nFROMn inventorynWHEREn user_id = @user_idnORDER BYn ' at line 3
感谢您的帮助。
对于那些感兴趣的人,我已经通过一些更新解决了我的问题。
连接时DSN上有设置吗?...&multiStatements=true&interpolateParams=true
Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
。我检查并转换了数据库并表到 utf8mb4_general_ci
,一切都按预期工作。
感谢那些提供解决方案的人,但这就是我们最终采取的路线。