在 Rails 3 / ActiveRecord 中构建 LIKE 查询时转义 %% 的正确方法
我想将 url 字段与 url 前缀（可能包含百分号）进行匹配，例如 .where("url LIKE ?", "#{some_url}%")。 最 Rails 的方式是什么？
I want to match a url field against a url prefix (which may contain percent signs), e.g. .where("url LIKE ?", "#{some_url}%").
What's the most Rails way?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
从 Rails 版本 4.2.x 开始，有一个名为
sanitize_sql_like的活动记录方法。因此，您可以在模型中执行如下搜索范围：并调用该范围：
生成的转义 sql 字符串为：
在此处阅读更多内容： http://edgeapi.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html
From Rails version 4.2.x there is an active record method called
sanitize_sql_like. So, you can do in your model a search scope like:and call the scope like:
The resulting escaped sql string is:
Read more here: http://edgeapi.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html
如果我理解正确的话，您担心“%”出现在
some_url中，这是正确的；您还应该担心嵌入的下划线（“_”），它们是“.”的类似版本。在正则表达式中。我认为没有任何特定于 Rails 的方法可以做到这一点，因此您只剩下 gsub：这里也不需要字符串插值。您需要将反斜杠加倍以从数据库的字符串解析器中转义它们的含义，以便 LIKE 解析器将看到简单的“\%”并知道忽略转义的百分号。
您应该检查日志以确保两个反斜杠通过。我在检查 irb 中的内容时得到了令人困惑的结果，使用五个（！）得到了正确的输出，但我看不出其中的意义；如果有人确实明白其中五个的意义，那么解释性评论将不胜感激。
更新：杰森·金（Jason King）善意地为逃脱角色的噩梦提供了简化。这可以让你指定一个临时转义字符，这样你就可以做一些事情像这样：
我还切换到了 gsub 的块形式，以使其不那么令人讨厌。
这是标准 SQL92 语法，因此适用于任何支持该语法的数据库，包括 PostgreSQL、MySQL 和 SQLite。
将一种语言嵌入另一种语言总是有点噩梦般的混乱，对此您无能为力。总会有一些丑陋的小事，你只能微笑着忍受。
If I understand correctly, you're worried about "%" appearing inside
some_urland rightly so; you should also be worried about embedded underscores ("_") too, they're the LIKE version of "." in a regex. I don't think there is any Rails-specific way of doing this so you're left withgsub:There's no need for string interpolation here either. You need to double the backslashes to escape their meaning from the database's string parser so that the LIKE parser will see simple "\%" and know to ignore the escaped percent sign.
You should check your logs to make sure the two backslashes get through. I'm getting confusing results from checking things in
irb, using five (!) gets the right output but I don't see the sense in it; if anyone does see the sense in five of them, an explanatory comment would be appreciated.UPDATE: Jason King has kindly offered a simplification for the nightmare of escaped escape characters. This lets you specify a temporary escape character so you can do things like this:
I've also switched to the block form of
gsubto make it a bit less nasty.This is standard SQL92 syntax, so will work in any DB that supports that, including PostgreSQL, MySQL and SQLite.
Embedding one language inside another is always a bit of a nightmarish kludge and there's not that much you can do about it. There will always be ugly little bits that you just have to grin and bear.
https://gist.github.com/3656283
使用此代码，
Item.where(Item .arel_table[:name].matches("%sample!%code%"))正确转义“sample”和“code”之间的
%，并匹配“AAAsample%codeBBB”，但是做至少不适用于 MySQL、PostgreSQL 和 SQLite3 上的“AAAsampleBBBcodeCCC”。https://gist.github.com/3656283
With this code,
Item.where(Item.arel_table[:name].matches("%sample!%code%"))correctly escapes
%between "sample" and "code", and matches "AAAsample%codeBBB" but does not for "AAAsampleBBBcodeCCC" on MySQL, PostgreSQL and SQLite3 at least.