SQL ::抽象类型铸件列
使用sql :: Abstract我需要将IP列键入text，以便能够使用喜欢。
我只找到了一种实现目标的“骇人听闻”的方法：
where( { ip => { '::TEXT LIKE' => $myParameter } } )
生成
WHERE ( "ip" ::TEXT LIKE ? )
问题是：实现这一目标的技巧较少或官方方法吗？
问题不是：
- 表演会很差吗？
- 我应该使用
文本列而不是ip列吗？ - 使用CIDR是更好的选择吗
Using SQL::Abstract I need to type cast an IP column to TEXT in order to be able to search using LIKE.
I only found a "hacky" way to achieve it with:
where( { ip => { '::TEXT LIKE' => $myParameter } } )
Which generates
WHERE ( "ip" ::TEXT LIKE ? )
Question is: Is there a less hacky or official way to achieve this?
Questions are not:
- Will the performance be poor?
- Should I use a
TEXTcolumn instead of anIPcolumn? - Are searches using CIDR a better alternative
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
问题是在
mojo :: pg中添加QUOTE_CHAR的“ ”到SQL :: Abstract Object。当我将其设置为空时字符串，这将按预期工作：
但是，要完成，我必须使用，
因为
:: text将为IP提供附加的/32。The issue was in
Mojo::Pgwhich adds aquote_charof"to the SQL::Abstract object.When I set this to the empty string, this will work as expected:
But, to be complete, I had to use
because
::TEXTwill give the IP with an appended/32.我认为您在问题中混合了很多东西。您听起来像是
sql :: Abstract问题，当您的实际问题与基础SQL本身有关。首先，我个人会避免使用
sql :: Abstract在大多数情况下（有时创建复杂的查询非常慢，您无法确定其输出），并且在这样的情况下肯定您想要一些非标准的地方。因此，继续前进，我不确定您的意思是
ip类型，从您的postgres标签中以及提及cidr我怀疑您的意思是您的意思是inet 类型？如果是这样，则相当于喜欢是使用子网掩码，这基本上是使用inet而不是文本/varchar字段的全部原因。对于192.168。像文本一样对待它们（例如，您要搜索
192.16％，生产192.168。*结果，但与192.16。使用子网掩码完成），然后您首先使用文本类型，或者正如您所说的那样，可以直接使用like直接使用：使用子网掩码的性能罚款，但是当然，这是否可能是问题取决于您的数据。
注意
cidr工作类似于inet，它不会为您提供之类的。I think you mix a lot of things in your question. You make it sound like it is an
SQL::Abstractissue, when your real issue is with the underlying SQL itself.First of all, I would personally avoid using
SQL::Abstractin most cases (it is sometimes VERY slow to create complex queries and you can't be sure of its output) and definitely in cases like this where you want something non-standard.So, moving on, I am not sure what you mean by
IPtype, from your postgres tag and the mention ofCIDRI suspect you mean theinettype? If so, the equivalent ofLIKEis to use subnet masks, which is basically the whole reason to useinetinstead of a text/varchar field. For192.168.*for example you would do something like below using the subnet slash notation:If you don't want to treat IPs as actual IPs and take advantage of things like above, but instead you want to treat them like text (e.g. you want to search
192.16%with that producing192.168.*results but along with192.16.*etc which is not done with subnet masks), then you either use a text type in the first place, or as you said convert on the fly to useLIKEdirectly:There is a performance penalty over using the subnet masks, but whether that may be an issue depends on your data of course.
Note
cidrworks similar toinet, it won't help you with aLIKE.