mysql 查询重复字符串_如何在mysql中查询重复的字符串条目

如果你以相反的顺序存储(8)最右边的字符，那么

您的表格将包含以下字段：

id (int)| name (string) | phone (string) | phonerev (string)

----------------------------------------------------

1 | mike | 081239812345678 | 876543218932180

2 | jhon | 082222212345678 | 876543212222280

3 | rudy | 081237712345678 | 876543217732180

4 | lucy | 081237712345123 | 321543217732180

5 | lily | 081244412345678 | 876543214442180你可以这样做一个查询：

select right(phone,8) as myRight

from contact c1

inner join contract c2 on (left(c1.phonerev,8) = left(c2.phonerev,8)

and c1.id <> c2.id)

group by left(phonerev,8)确保在phonerev上设置索引

您可以通过执行以下操作来统一查询：

select c1.*

from contact c1

inner join contract c2 on (left(c1.phonerev,8) = left(c2.phonerev,8)

and c1.id <> c2.id)这将允许在电话号码上使用索引

如果您只是以相反的顺序存储最右边的8个字符，那么查询将变为：

select right(phone,8) as myRight

from contact c1

inner join contract c2 on (c1.phonerev,8 = c2.phonerev

and c1.id <> c2.id)

group by phonerev;哪个更快。