问题描述
我对SQL还是很陌生，我试图获取与productID的用户输入匹配的产品的完整列表.像这样:
I'm fairly new to SQL and I was trying to get a full list of products that match a user input of a productID. Something like:
SELECT ProductID, ProductName FROM Products WHERE ProductID LIKE '%15%'
我希望它列出所有匹配的产品，例如:15、150、2154等
I would like it to list out all the matching products such as:15, 150, 2154, etc
不幸的是，由于productID字段是INT而不是字符串，因此我遇到了问题.有什么相对简单的方法可以解决这个问题吗?
Unfortunately I'm running into problems because the productID field is an INT and not a string. Is there some relatively simple way around this?
推荐答案
您可以 CAST 字段为字符串:
You can CAST the field to a string:
... WHERE CAST(ProductID as CHAR) LIKE '%15%'
这对性能非常不利，因为mySQL无法利用它为INT列创建的任何索引.但是，即使在varchar字段上进行操作，LIKE总是很慢:无法通过索引来加快LIKE查询的速度.
this is very bad for performance, as mySQL can't make use of any indexes it's created for the INT column. But then, LIKE is always slow, even when done on a varchar field: There's no way to have an index that speeds up a LIKE query.
可能值得拥有第二个varchar列来镜像int列的值并对其进行LIKE-您必须进行基准测试，以查明它是否会对您有好处.
It might be worth having a second varchar column that mirrors the int column's values and doing the LIKE on that one - you'd have to benchmark to find out whether it'll do any good.
这篇关于在INT字段上执行LIKE比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！