Golang Gorm在Where子句中转义字符不起作用。

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

Golang Gorm Escaping characters in Where clause not working

问题

我有以下工作正常的查询：

  1. if err := rs.db.
  2. 	Preload("Artist").
  3. 	Preload("Genre").
  4. 	Where("MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`) AGAINST (? IN BOOLEAN MODE)", text).
  5. 	Find(&rs.Records).
  6. 	Error; err != nil {
  7. 	utils.Logger.Error("error while preloading artists and genres for record", zap.Error(err))
  8. 	return errors.New(err.Error())
  9. }

它按预期填充了&rs.Records。

但是我需要使查询更加灵活，所以我尝试添加一些通配符，因此原始查询应该如下所示：

  1. SELECT * FROM records WHERE MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`)
  2. AGAINST ('* TEXT YOU WANT TO SEARCH *' IN BOOLEAN MODE)

请注意使用了*。这个转换为我的 Gorm 查询应该是这样的：

  1. if err := rs.db.
  2. 	Preload("Artist").
  3. 	Preload("Genre").
  4. 	Where("MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`) AGAINST (*?* IN BOOLEAN MODE)", text).
  5. 	Find(&rs.Records).
  6. 	Error; err != nil {
  7. 	utils.Logger.Error("error while preloading artists and genres for record", zap.Error(err))
  8. 	return errors.New(err.Error())
  9. }

它失败并显示以下错误：

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 '? IN BOOLEAN MODE)' at line 1

显然是因为我添加了*，所以我尝试对其进行转义：

  1. if err := rs.db.
  2. 	Preload("Artist").
  3. 	Preload("Genre").
  4. 	Where("MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`) AGAINST (\\*?\\* IN BOOLEAN MODE)", text).
  5. 	Find(&rs.Records).
  6. 	Error; err != nil {
  7. 	utils.Logger.Error("error while preloading artists and genres for record", zap.Error(err))
  8. 	return errors.New(err.Error())
  9. }

然而，我得到了相同的错误。我还尝试在其上添加转义的'：

  1. if err := rs.db.
  2. 	Preload("Artist").
  3. 	Preload("Genre").
  4. 	Where("MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`) AGAINST (\\'\\*?\\*\\' IN BOOLEAN MODE)", text).
  5. 	Find(&rs.Records).
  6. 	Error; err != nil {
  7. 	utils.Logger.Error("error while preloading artists and genres for record", zap.Error(err))
  8. 	return errors.New(err.Error())
  9. }

但结果是一样的。

你有什么想法，我做错了什么？

谢谢。

英文:

I've got the following query which works just fine:

  1. if err := rs.db.
  2. 		Preload("Artist").
  3. 		Preload("Genre").
  4. 		Where("MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`) AGAINST (? IN BOOLEAN MODE)", text).
  5. 		Find(&rs.Records).
  6. 		Error; err != nil {
  7. 		utils.Logger.Error("error while preloading artists and genres for record", zap.Error(err))
  8. 		return errors.New(err.Error())
  9. 	}

It populates &rs.Records as expected.

But I need my query to be more flexible, so I tried to add some wildcards to it, so the raw query should look like the following:

  1. SELECT * FROM records WHERE MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`)
  2. AGAINST ('* TEXT YOU WANT TO SEARCH *' IN BOOLEAN MODE)

Notice the use of *. This translated to my Gorm query should be something like:

  1. if err := rs.db.
  2. 		Preload("Artist").
  3. 		Preload("Genre").
  4. 		Where("MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`) AGAINST (*?* IN BOOLEAN MODE)", text).
  5. 		Find(&rs.Records).
  6. 		Error; err != nil {
  7. 		utils.Logger.Error("error while preloading artists and genres for record", zap.Error(err))
  8. 		return errors.New(err.Error())
  9. 	}

It fails with the following error:

> 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 '? IN BOOLEAN MODE)' at line 1

so clearly it's because I added *, so I tried to escape it:

  1. if err := rs.db.
  2. 		Preload("Artist").
  3. 		Preload("Genre").
  4. 		Where("MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`) AGAINST (\\*?\\* IN BOOLEAN MODE)", text).
  5. 		Find(&rs.Records).
  6. 		Error; err != nil {
  7. 		utils.Logger.Error("error while preloading artists and genres for record", zap.Error(err))
  8. 		return errors.New(err.Error())
  9. 	}

yet I'm getting the same error. I also tried adding escaped ' to it:

  1. if err := rs.db.
  2. 		Preload("Artist").
  3. 		Preload("Genre").
  4. 		Where("MATCH(`title`, `label`, `description`, `catalog_number`,`barcode`) AGAINST (\\'\\*?\\*\\' IN BOOLEAN MODE)", text).
  5. 		Find(&rs.Records).
  6. 		Error; err != nil {
  7. 		utils.Logger.Error("error while preloading artists and genres for record", zap.Error(err))
  8. 		return errors.New(err.Error())
  9. 	}

But the result is the same.

Any ideas what am I doing wrong?

Thanks.

答案1

得分: 1

AGAINST()函数的参数必须是一个带引号的字符串、一个字符串参数或一个常量字符串表达式。

以下是无效的，因为*字符需要在一个字符串值内：

  1. AGAINST (*?* IN BOOLEAN MODE)

以下也是无效的，因为参数占位符不能在引号内：

  1. AGAINST ('*?*' IN BOOLEAN MODE)

因此，你有两个选择，要么将表达式创建为字符串字面量和参数的连接：

  1. AGAINST (CONCAT('*', ?, '*') IN BOOLEAN MODE)

要么在你的应用程序中将*字符连接到模式中，并将其作为参数值传递。

  1. AGAINST (? IN BOOLEAN MODE)
英文:

The argument to AGAINST() must be a quoted string, or a string parameter, or a constant string expression.

The following is not valid, because the * characters need to be inside a string value:

  1. AGAINST (*?* IN BOOLEAN MODE)

The following is also not valid, because a parameter placeholder must not be inside the quotes:

  1. AGAINST ('*?*' IN BOOLEAN MODE)

So your options are either create the expression as a concatenation of a string literal and parameter:

  1. AGAINST (CONCAT('*', ?, '*') IN BOOLEAN MODE)

Or else concatenate the * characters into your pattern in your application, and pass that as the parameter value.

  1. AGAINST (? IN BOOLEAN MODE)

huangapple
  • 本文由 发表于 2023年6月13日 23:43:34
  • 转载请务必保留本文链接：https://go.coder-hub.com/76466608.html
  • go
  • go-gorm
  • mysql
  • mysql-8.0
匿名

发表评论

匿名网友

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

确定