如何使用REGEXP MySQL函数查找超过2个点的电子邮件？ [英] How to find email with more than 2 dots using REGEXP MySQL function?

问题描述

我有一个大量的垃圾邮件帐户的用户数据库，如 [e-mail] 或 [e-mail] ，我想用一个查询删除它们（而不是做一个php脚本，解析所有的数据库）。

I have a big user database with a lot of spammy email account, like [e-mail] or [e-mail] and I want to remove them with one queries (instead of doing a php script that parses all the database).

我看到MySQL有一个REGEXP功能，但很难使用dot。我试过很多东西，如：

I see MySQL have a REGEXP function, but it's hard to use it with "dot". I tried lots of things like :

• SELECT * FROM users WHERE email REGEXP'（[[...] ]）{2，}'


• SELECT * FROM users WHERE email REGEXP'（\\。）{2，} '（。*）[[...]] {2}（。*） ）'

• SELECT * FROM users WHERE email REGEXP '([[...]]){2,}'
• SELECT * FROM users WHERE email REGEXP '(\\.){2,}'
• SELECT * FROM users WHERE email REGEXP '(.*)[[...]]{2}(.*)'

每个失败。

任何想法？

推荐答案

这个怎么样？

SELECT * FROM users WHERE email REGEXP '[.].*[.].*@';

在@之前搜索两个（或更多）个点（这仍然允许多点域名如.co.uk等）。

this searches for two (or more) dots before @ (which would still allow multi-dot domain names like .co.uk etc).

但是，您应该考虑一些用户在其用户名中可能真的有多个点的可能性，这仍然是合法的。

However, you should consider the possibility that some users may really have multiple dots in their username as well, and that will still be legit.

这篇关于如何使用REGEXP MySQL函数查找超过2个点的电子邮件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！