MySQL 对 INT 字段执行 LIKE 比较
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8422455/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Performing a LIKE comparison on an INT field
提问by Maxx
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:
我对 SQL 还很陌生，我试图获取与产品 ID 的用户输入相匹配的完整产品列表。就像是：
SELECT ProductID, ProductName FROM Products WHERE ProductID LIKE '%15%'
I would like it to list out all the matching products such as: 15, 150, 2154, etc
我希望它列出所有匹配的产品，例如：15、150、2154 等
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?
不幸的是，我遇到了问题，因为 productID 字段是一个 INT 而不是字符串。有没有一些相对简单的方法来解决这个问题？
回答by Pekka
You can CASTthe field to a string:
您可以CAST将字段转换为字符串：
... WHERE CAST(ProductID as CHAR) LIKE '%15%'
this is very bad for performance, as mySQL can't make use of any indexes it's created for the INT column. But then, LIKEis always slow, even when done on a varchar field: There's no way to have an index that speeds up a LIKE query.
这对性能非常不利，因为 mySQL 不能使用它为 INT 列创建的任何索引。但是LIKE，即使在 varchar 字段上完成，也总是很慢：没有办法拥有可以加速 LIKE 查询的索引。
It might be worth having a second varcharcolumn that mirrors the intcolumn's values and doing the LIKE on that one - you'd have to benchmark to find out whether it'll do any good.
可能值得拥有第二varchar列来反映int列的值并在该列上执行 LIKE - 您必须进行基准测试以确定它是否有任何好处。
回答by Jan
You can convertint to string using CONVERT(ProductID, CHAR(16)) AS ProductIDand then use LIKE. So in your case it would be
您可以使用将int转换为字符串CONVERT(ProductID, CHAR(16)) AS ProductID，然后使用LIKE. 所以在你的情况下它会是
SELECT ProductID, ProductName FROM Products
WHERE CONVERT(ProductID, CHAR(16)) LIKE '%15%'
You should remember that the query will make a full table scan without any support from indices. As a result it will be really expensive and time consuming.
您应该记住，该查询将在没有任何索引支持的情况下进行全表扫描。因此，这将是非常昂贵和耗时的。
