在 Mysql 中验证电子邮件地址

Validate email addresses in Mysql

提问by Praveen Kumar Purushothaman

This query creates a mysql view that captures bad email address formats in one table. So if a row is inserted in that has rtrrg.comas a email it will be recorded in the view. My question is, how do I make the view track more than one table. A second table.

此查询创建一个 mysql 视图，该视图在一个表中捕获错误的电子邮件地址格式。因此，如果在其中插入一行rtrrg.com作为电子邮件，它将被记录在视图中。我的问题是，如何使视图跟踪多个表。第二张桌子。

The SQL

SQL

CREATE VIEW `invalid_emails` AS 
  select `table_with_email_column`.`email` AS `invalidemail` 
    from `table_with_email_column` 
   where ((locate(_latin1'', ltrim(rtrim(`table_with_email_column`.`email`))) <> 0) 
      or (left(ltrim(`table_with_email_column`.`email`), 1) = _latin1'@') 
      or (right(rtrim(`table_with_email_column`.`email`), 1) = _latin1'.') 
      or ((locate(_latin1'.', `table_with_email_column`.`email`,locate(_latin1'@', `table_with_email_column`.`email`)) -  locate(_latin1'@', `table_with_email_column`.`email`)) <= 1) 
      or ((length(ltrim(rtrim(`table_with_email_column`.`email`))) -  length(replace(ltrim(rtrim(`table_with_email_column`.`email`)), _latin1'@', _latin1''))) <> 1) 
      or (locate(_latin1'.', reverse(ltrim(rtrim(`table_with_email_column`.`email`)))) < 3) 
      or (locate(_latin1'.@', `table_with_email_column`.`email`) <> 0) 
      or (locate(_latin1'..', `table_with_email_column`.`email`) <> 0));

回答by Praveen Kumar Purushothaman

You can use a pure SELECTto validate Email Addresses:

您可以使用 pureSELECT来验证电子邮件地址：

SELECT * FROM `users` WHERE `email` NOT REGEXP '^[^@]+@[^@]+\.[^@]{2,}$';

And now for your question of tracking multiple tables, you can use comma seperated table names right?

现在对于跟踪多个表的问题，您可以使用逗号分隔的表名，对吗？

SELECT * FROM `users`, `customers`, `clients`
         WHERE `email` NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,63}$';

回答by Sachin Parse

For the proper email validation, you can use this regex as bellow:

对于正确的电子邮件验证，您可以使用此正则表达式如下：

SELECT
    *
FROM
    `school`
WHERE
    `email` NOT REGEXP '^[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9._-]@[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]\.[a-zA-Z]{2,63}$';

回答by Bud Damyanov

Simple SELECTstatement is sufficient, for example:

简单的SELECT语句就足够了，例如：

 SELECT * FROM user WHERE email NOT 
 REGEXP '^[a-zA-Z0-9][+a-zA-Z0-9._-]*@[a-zA-Z0-9][a-zA-Z0-9._-]*[a-zA-Z0-9]*\.[a-zA-Z]{2,4}$'

This query handles the Gmail addresses with +sign and addresses where the host is a single letter.

此查询处理带有+符号的 Gmail 地址和主机为单个字母的地址。

回答by hol

You can use a UNIONin the VIEWbut then you have to repeat all the WHEREstatement which gives you redundant code. So you would make a helper VIEWthat makes you a UNIONand then apply the WHEREclause.

您可以UNION在 the 中使用 aVIEW但随后您必须重复所有WHERE为您提供冗余代码的语句。因此，您将创建一个VIEW使您成为 a 的助手，UNION然后应用该WHERE子句。

Demo here: SQL Fiddle Demo.

演示在这里：SQL Fiddle Demo。

That would apply to your SQL somehow like this (untested);

这将以某种方式适用于您的 SQL（未经测试）；

CREATE VIEW `invalid_emails_helper` AS
  select `table_with_email_column`.`email` AS `invalidemail` 
    from `table_with_email_column` 
union
  select `table_with_email_column`.`email` 
    from `second_table_with_email_column` 

CREATE VIEW `invalid_emails` AS 
  select `invalidemail` as `email`
    from `invalid_emails_helper` as `table_with_email_column` 
   where ((locate(_latin1'', ltrim(rtrim(`table_with_email_column`.`email`))) <> 0) 
      or (left(ltrim(`table_with_email_column`.`email`), 1) = _latin1'@') 
      or (right(rtrim(`table_with_email_column`.`email`), 1) = _latin1'.') 
      or ((locate(_latin1'.', `table_with_email_column`.`email`,locate(_latin1'@', `table_with_email_column`.`email`)) -  locate(_latin1'@', `table_with_email_column`.`email`)) <= 1) 
      or ((length(ltrim(rtrim(`table_with_email_column`.`email`))) -  length(replace(ltrim(rtrim(`table_with_email_column`.`email`)), _latin1'@', _latin1''))) <> 1) 
      or (locate(_latin1'.', reverse(ltrim(rtrim(`table_with_email_column`.`email`)))) < 3) 
      or (locate(_latin1'.@', `table_with_email_column`.`email`) <> 0) 
      or (locate(_latin1'..', `table_with_email_column`.`email`) <> 0));

And yes, the query to check the e-mail address using a regexas can easily found everywhere in the internet simplifies it further.

是的，使用regex在互联网上随处可见的as检查电子邮件地址的查询进一步简化了它。

回答by Amrish Prajapati

SELECT
    *
FROM
    users
WHERE
    email NOT REGEXP ‘ ^[ a - zA - Z0 - 9 ][ a - zA - Z0 - 9._ -]*[ a - zA - Z0 - 9 ]@[ a - zA - Z0 - 9 ][ a - zA - Z0 - 9._ -]*[ a - zA - Z0 - 9 ]\.[ a - zA - Z ]{ 2,
    63 } $'