如何将字段的一部分与MySQL中字段的另一部分进行比较？

Question

这是试图在我的系统中识别一个潜在的重复用户，他将在其中创建两个条目: my.name.totoro@shibly.com和my.name.totoro@shibly.net

如何找到所有的匹配，其中唯一的区别是域名的最后一部分？

Answer 1

这并不是很有效率，但它做到了以下工作：

SELECT *
FROM mytable 
WHERE LEFT(email, LENGTH(email) - LOCATE('.', REVERSE(email))) IN 
(
   SELECT LEFT(email, LENGTH(email) - LOCATE('.', REVERSE(email))) AS common
   FROM mytable
   GROUP BY LEFT(email, LENGTH(email) - LOCATE('.', REVERSE(email)))
   HAVING COUNT(*) > 1
)

Demo here

Answer 2

http://sqlfiddle.com/#!9/2a6fa/2

SELECT 
  u.email,
  GROUP_CONCAT(c.email) clones
FROM users u
INNER JOIN users c
ON u.id <> c.id
  AND  SUBSTRING(u.email,1,LENGTH(u.email)-LENGTH(SUBSTRING_INDEX(u.email,'.',-1))) 
       = SUBSTRING(c.email,1,LENGTH(c.email)-LENGTH(SUBSTRING_INDEX(c.email,'.',-1))) 
GROUP BY u.id

此查询返回太多的记录，如.com = .net和.net = .com。

rec1 = my.name.totoro@shibly.com    my.name.totoro@shibly.net
rec2 = my.name.totoro@shibly.net    my.name.totoro@shibly.com

但可以通过添加以下内容来解决：

WHERE u.email = "my.name.totoro@shibly.com"

这将返回所有副本，为特定的电子邮件时，需要。