MySQL SQL SELECT 查询不适用于电子邮件地址
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31030685/
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
SQL SELECT query not working with Email Addresses
提问by NMaduro
I am trying to query an e-mail address stored in my database for log-in. I am having issues with the query in PHP, when I attempted the query with SQL in PHPMyAdmin it returns an empty set. After doing some testing I determined the following for an email of [email protected]:
我正在尝试查询存储在我的数据库中的电子邮件地址以进行登录。我在 PHP 中遇到查询问题，当我在 PHPMyAdmin 中尝试使用 SQL 进行查询时，它返回一个空集。在做了一些测试之后，我为 [email protected] 的电子邮件确定了以下内容：
Works:
作品：
SELECT * FROM `Careers` WHERE `Email` LIKE '%something%' and
SELECT * FROM `Careers` WHERE `Email` LIKE '%gmail.com%' and
SELECT * FROM `Careers` WHERE `Email` LIKE '%@%'.
Doesn't work:
不起作用：
SELECT * FROM `Careers` WHERE `Email` LIKE '[email protected]' and
SELECT * FROM `Careers` WHERE `Email` LIKE '%[email protected]%' and
SELECT * FROM `Careers` WHERE `Email` LIKE '%@gmail.com%'
SELECT * FROM `Careers` WHERE `Email` LIKE '%something%gmail.com'
I'm completely lost as to how to correct this. The only think I can think of is it is an issue with the @ sign as when I add the @ sign the query seems to fail. Any help you could provide would be greatly appreciated!
我完全不知道如何纠正这个问题。我唯一能想到的是 @ 符号有问题，因为当我添加 @ 符号时，查询似乎失败了。您能提供的任何帮助将不胜感激！
回答by Rahul
Are you sure that it's not working. See a proof here that it works http://sqlfiddle.com/#!9/26b00/4. But you should change your queries a bit as shown below
你确定它不起作用。在此处查看它的工作证明 http://sqlfiddle.com/#!9/26b00/4。但是你应该改变你的查询，如下所示
SELECT * FROM table1 WHERE `Email` = '[email protected]' -- No need of LIKE operator
SELECT * FROM table1 WHERE `Email` = '[email protected]' -- No need of LIKE operator
SELECT * FROM table1 WHERE `Email` LIKE '%@gmail.com' -- search before string
SELECT * FROM table1 WHERE `Email` LIKE 'something_gmail.com' -- search a single char
EDIT:
编辑：
Per your latest comment your collation armscii8_general_ciis the issue here. For example create the table like
根据您的最新评论，您的整理armscii8_general_ci是这里的问题。例如创建表
CREATE TABLE Table1
(`email` varchar(19) collate armscii8_general_ci)
;
INSERT INTO Table1
(`email`)
VALUES
('[email protected]')
;
Do a select * ...returns below; as you can see the .as turned to ?kind of copyright symbol and that's why the wildcard with LIKEoperator not working.
在select * ...下面做一个回报；正如您所看到的.那样，as 变成了?一种版权符号，这就是带有LIKE运算符的通配符不起作用的原因。
something@gmail?com
Change your query to use _wilcard with LIKEoperator to match any single character and it will work fine. See http://sqlfiddle.com/#!9/ec46f/8
更改您的查询以使用_通配符和LIKE运算符来匹配任何单个字符，它会正常工作。见http://sqlfiddle.com/#!9/ec46f/8
SELECT * FROM Table1 WHERE `Email` LIKE 'something@gmail_com';
