字符串和整数的严格匹配 [英] Strict matching of strings and integers
问题描述
我正在为客户的网站编写一种灵活的搜索机制.我利用并集子句查询数据库中许多不同的字段，以搜索用户输入的字符串值.除一个问题外，此方法工作正常.
I am writing a flexible search mechanism for a customer's website. I am utilizing union clauses to query a number of different fields in the database in search of a string value entered by the user. This works fine except for one issue.
将文本的字符串与当前设置为零的整数进行比较时，匹配项始终返回true.换句话说，根据MySQL，"email@example.com"等于0.
When comparing a string of a text to an integer that is currently set to zero, the match always returns true. In other words, according to MySQL, "email@example.com" is equal to 0.
我尝试利用CAST和CONVERT函数将其转换为标准的字符串与字符串比较，但是我似乎无法正确理解语法.我的尝试要么重复上述问题，要么在某些行应该匹配时完全不返回任何行.我还担心这样做会影响性能，因为我合并了很多工会.
I have tried utilizing the CAST and CONVERT function to turn this into a standard string to string comparison, but I can't seem to get the syntax right. My attempts either repeat the above issue or return no rows at all when some should match. I am also concerned that doing this would have an effect on performance since I am combining lots of unions.
我真正需要的是对输入的字符串和数据库中的值(无论是整数还是字符串)进行严格的比较.
What I really need is a strict comparison between an entered string and the value in the database, be it an integer or string.
这是一个例子.
CREATE TABLE `test_table` (
`id` INT NOT NULL AUTO_INCREMENT ,
`email` VARCHAR(255) NOT NULL ,
`phone` BIGINT(19) NOT NULL DEFAULT '0' ,
PRIMARY KEY (`id`) )
ENGINE = MyISAM;
INSERT INTO `test_table` (`id`, `email`, `phone`) VALUES (1, 'email@example.com', 0);
SELECT * FROM test_table WHERE phone = 'email@example.com';
执行此操作，将返回已插入的一行.我的问题是不应该！
Execute this and the one row that has been inserted will return. My issue is that it shouldn't!
推荐答案
此查询应失败:
SELECT * FROM test_table WHERE cast(phone as char) = 'email@example.com';
最初问题的原因是，在比较字符串和数字时，它将字符串转换为数字(因此您可以编写where phone = '123').您需要使用该字段的显式强制转换来进行字符串到字符串的比较，以防止这种默认转换.
The cause of the original problem is that when comparing strings and numbers, it converts the string to a number (so you can write where phone = '123'). You need to use an explicit cast of the field to make it a string-to-string comparison, to prevent this default conversion.
不幸的是，像这样进行转换很可能会阻止它使用索引.即使该字段已经是char，强制转换显然也阻止了对其建立索引.
Unfortunately, casting like this is likely to prevent it from using indexes. Even if the field is already char, the cast apparently prevents it from indexing.
您也可以在输入验证期间解决它:如果phone是整数，则不允许用户在搜索字段中提供非整数值.
You could also solve it during input validation: if phone is an integer, don't allow the user to provide a non-integer value in the search field.
这篇关于字符串和整数的严格匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
