mysql手机号码规律查询

已于 2022-03-04 09:27:14 修改
阅读量4k 收藏 2
点赞数
分类专栏： mysql 文章标签： mysql sql
于 2021-03-23 18:02:40 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_33745371/article/details/115129369
版权

tips：所有手机号均为随机创建的，为了做查询的效果展示，不涉及他人隐私。

mysql手机号码规律查询

数据库

数据库中的表只使用了一个字段：phone_number
在这里插入图片描述

全号规律

AAA

select phone_number from `phone_number` where 
(
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
)
OR
(
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
)
OR
(
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
)
OR
(
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
)
OR
(
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
)
OR
(
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
)
OR
(
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
)
OR
(
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

ABAB

select phone_number from `phone_number` where 
(
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,2,1)
and
SUBSTR(phone_number,1,1) = SUBSTR(phone_number,3,1)
AND
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
)
or
(
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
AND
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
)
or
(
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
AND
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
)
or
(
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
AND
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
)
or
(
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
)
or
(
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
AND
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
)
or
(
SUBSTR(phone_number,7,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
AND
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
)
or
(
SUBSTR(phone_number,8,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
AND
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

AABBCC

select phone_number from `phone_number` where 
(
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,1,1) = SUBSTR(phone_number,2,1)
AND
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
AND
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
)
or
(
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
AND
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
AND
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
)
or
(
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
AND
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
AND
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
)
or
(
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
AND
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
)
or
(
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,7,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
AND
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
AND
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
)
or
(
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,8,1) != SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
AND
SUBSTR(phone_number,10,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

ABCD(顺子，1234或4321)

select phone_number from `phone_number` where 
(
	(
	SUBSTR(phone_number,1,1) = (SUBSTR(phone_number,2,1)+1)
	and
	SUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)+1)
	and
	SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,1,1) = (SUBSTR(phone_number,2,1)-1)
	and
	SUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)-1)
	and
	SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)+1)
	and
	SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)+1)
	and
	SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)+1)
	)
	or
	(
	SUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)-1)
	and
	SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)-1)
	and
	SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)+1)
	and
	SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)+1)
	and
	SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)-1)
	and
	SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)-1)
	and
	SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)+1)
	and
	SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)+1)
	AND
	SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)-1)
	and
	SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)-1)
	AND
	SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)+1)
	and
	SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)+1)
	and
	SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)-1)
	and
	SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)-1)
	and
	SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)+1)
	and
	SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)+1)
	and
	SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)-1)
	and
	SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)-1)
	and
	SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)+1)
	and
	SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)+1)
	and
	SUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)-1)
	and
	SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)-1)
	and
	SUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)+1)
	and
	SUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)+1)
	AND
	SUBSTR(phone_number,10,1) = (SUBSTR(phone_number,11,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)-1)
	and
	SUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)-1)
	AND
	SUBSTR(phone_number,10,1) = (SUBSTR(phone_number,11,1)-1)
	)
)

在这里插入图片描述

AAABBB

select phone_number from `phone_number` where
(
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
)
OR
(
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
)
OR
(
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
)
OR
(
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
)
OR
(
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

6多8多9多(举例:11位的号码中存在超过6个)

tips:这里采用了一种比较巧妙的方式. 将存在6的号码替换为空,将11位减去替换后的长度就是6出现的个数.以此类推,将6的个数+8的个数+9的个数

select phone_number from `phone_number` where
(
	(LENGTH(phone_number) - LENGTH(REPLACE(phone_number,"6",""))) +
	(LENGTH(phone_number) - LENGTH(REPLACE(phone_number,"8",""))) +
	(LENGTH(phone_number) - LENGTH(REPLACE(phone_number,"9",""))) 
)>6

在这里插入图片描述

ABBCCDDA(更新时间:2021/04/21)

select * from `phone_number` where  
(																											
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,2,1)
and
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,1,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
)
or
(
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
)
or
(
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
)
or
(
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,11,1)
and
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,7,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
)

ABBCCDDA

中间规律

AAA

select phone_number from `phone_number` where
(
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
)
OR
(
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
)
OR
(
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
)
OR
(
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
)
OR
(
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
)
OR
(
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
)
OR
(
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
)

在这里插入图片描述

ABAB

select phone_number from `phone_number` where
(
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,2,1)
and
SUBSTR(phone_number,1,1) = SUBSTR(phone_number,3,1)
AND
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
)
or
(
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
AND
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
)
or
(
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
AND
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
)
or
(
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
AND
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
)
or
(
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
)
or
(
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
AND
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
)
or
(
SUBSTR(phone_number,7,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
AND
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
)

在这里插入图片描述

AABBCC

select phone_number from `phone_number` where
(
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,1,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,1,1) = SUBSTR(phone_number,2,1)
AND
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
AND
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
)
or
(
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,2,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
AND
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
AND
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
)
or
(
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,3,1) != SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
AND
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
AND
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
)
or
(
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,4,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
AND
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
)
or
(
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,5,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,7,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
AND
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
AND
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
)

在这里插入图片描述

ABCD(顺子，1234或4321)

select phone_number from `phone_number` where
(
	(
	SUBSTR(phone_number,1,1) = (SUBSTR(phone_number,2,1)+1)
	and
	SUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)+1)
	and
	SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,1,1) = (SUBSTR(phone_number,2,1)-1)
	and
	SUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)-1)
	and
	SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)+1)
	and
	SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)+1)
	and
	SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)+1)
	)
	or
	(
	SUBSTR(phone_number,2,1) = (SUBSTR(phone_number,3,1)-1)
	and
	SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)-1)
	and
	SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)+1)
	and
	SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)+1)
	and
	SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,3,1) = (SUBSTR(phone_number,4,1)-1)
	and
	SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)-1)
	and
	SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)+1)
	and
	SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)+1)
	AND
	SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,4,1) = (SUBSTR(phone_number,5,1)-1)
	and
	SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)-1)
	AND
	SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)+1)
	and
	SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)+1)
	and
	SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,5,1) = (SUBSTR(phone_number,6,1)-1)
	and
	SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)-1)
	and
	SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)+1)
	and
	SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)+1)
	and
	SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,6,1) = (SUBSTR(phone_number,7,1)-1)
	and
	SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)-1)
	and
	SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)-1)
	)
)
OR
(
	(
	SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)+1)
	and
	SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)+1)
	and
	SUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,7,1) = (SUBSTR(phone_number,8,1)-1)
	and
	SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)-1)
	and
	SUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)-1)
	)
)

在这里插入图片描述

AAABBB

select phone_number from `phone_number` where
(
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,3,1)
and
SUBSTR(phone_number,2,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
)
OR
(
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,4,1)
and
SUBSTR(phone_number,3,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,8,1)
)
OR
(
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,5,1)
and
SUBSTR(phone_number,4,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,9,1)
)
OR
(
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,6,1)
and
SUBSTR(phone_number,5,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
)

在这里插入图片描述

尾号规律

AAA

select phone_number from `phone_number` where
(
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

ABAB

select phone_number from `phone_number` where
(
SUBSTR(phone_number,8,1) != SUBSTR(phone_number,9,1)
and
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,10,1)
AND
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

AABBCC

select phone_number from `phone_number` where
(
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,6,1) != SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,8,1) != SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
AND
SUBSTR(phone_number,8,1) = SUBSTR(phone_number,9,1)
AND
SUBSTR(phone_number,10,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

ABCD(顺子，1234或4321)

select phone_number from `phone_number` where
(
	(
	SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)+1)
	and
	SUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)+1)
	AND
	SUBSTR(phone_number,10,1) = (SUBSTR(phone_number,11,1)+1)
	)
	OR
	(
	SUBSTR(phone_number,8,1) = (SUBSTR(phone_number,9,1)-1)
	and
	SUBSTR(phone_number,9,1) = (SUBSTR(phone_number,10,1)-1)
	AND
	SUBSTR(phone_number,10,1) = (SUBSTR(phone_number,11,1)-1)
	)
)

在这里插入图片描述

AAABBB

select phone_number from `phone_number` where
(
SUBSTR(phone_number,6,1) = SUBSTR(phone_number,7,1)
and
SUBSTR(phone_number,7,1) = SUBSTR(phone_number,8,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,10,1)
and
SUBSTR(phone_number,9,1) = SUBSTR(phone_number,11,1)
)

在这里插入图片描述

qiweilong123456
关注
专栏目录
在mysql怎样查询地址和电话_Mysql数据查询
weixin_39969257的博客
01-19 1096
Mysql查询数据多次过滤条件：from、where、group by、having、distinct、order by、limit => 层层筛选后的结果查：select [distinct] 字段1 [[as] 别名1],...,字段n [[as] 别名n] from [数据库名.]表名 [条件];注：一条查询语句，可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选，disti...
mysql 字段命名is__数据库表字段命名规则
weixin_31869579的博客
02-27 6102
一、数据库命名规范采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成，命名简洁明确，多个单词用下划线'_'分隔，一个项目一个数据库，多个项目慎用同一个数据库二、数据库表命名规范2.1数据表命名规范(1)采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成，命名简洁明确，多个单词用下划线'_'分隔(2)全部小写命名，禁止出现大写(3)禁止...
手机号码归属地查询mysql数据库
01-22
手机号码归属地查询mysql数据库 包含省份 城市 地区编码等
mysql查询某字段包含手机号
liyuan_yoao的博客
10-19 1338
mysql查询某字段包含手机号
手机号码归属地查询sql
06-26
手机号码归属地查询sql，数据库导入，便捷高效，需要的同学自行下载 手机号码归属地查询sql，数据库导入，便捷高效，需要的同学自行下载 手机号码归属地查询sql，数据库导入，便捷高效，需要的同学自行下载 手机号码归属地查询sql，数据库导入，便捷高效，需要的同学自行下载 手机号码归属地查询sql，数据库导入，便捷高效，需要的同学自行下载
mysql数据库高级 索引_MySQL高级知识（六）——索引优化
weixin_39704971的博客
02-18 143
前言：索引优化的目的主要是让索引不失效，本篇通过相关案例对索引优化进行讲解。0.准备创建经典的tb_emp表。DROP TABLE IF EXISTS `tb_emp`;CREATE TABLE `tb_emp` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL,`age` int(11) NOT NULL,gend...
手机号码归属地和手机号码类型查询
08-25
在压缩包文件"手机号码查询"中，很可能包含了实现这两个功能所需的数据和代码。可能包括以下几个部分： 1. 数据库文件：可能是一个SQLite、MySQL或其他形式的数据库，存储了手机号码的归属地和类型信息。数据库结构...
手机号码归属地本地库文件
09-02
可以进行各种探索性数据分析，比如研究不同地区手机号码的分布规律，运营商市场份额的变化，甚至预测未来的通信趋势。 至于如何使用这些文件，对于`phone_location.csv`，可以使用Excel、Python的pandas库或R语言...
MySQL 查询题型总结
woaixuexi6666的博客
10-11 1611
无
手机号码归属地(分类多txt版).rar
04-08
此外，如果这些TXT文件的命名遵循一定的规则，比如包含地区名称，那么在处理时可以更加方便。我们可以通过文件名快速识别每个文件的数据来源，然后根据需求对单个文件或多个文件进行操作。 在实际应用中，这些手机...
mysql提取电话号码_mysql中多个前缀号码的提取方法
weixin_35841706的博客
01-27 328
比如电话号码为：1234567(号码可能为8位或4位或7位)前缀有：852，3，3852，852133假如用户输入：31234567 ，在mysql中如何判断前缀是3？或者用户输入：8521234567 。mysql> create table areacode( -> acode varchar(10) primary key -> ) -> ...
MYSQL function使用正则表达式验证是否是手机号码
weixin_34101784的博客
08-26 1529
DELIMITER $$ DROP FUNCTION IF EXISTS `ISMOBILE`$$ CREATE FUNCTION `ISMOBILE`(mobileNum VARCHAR(32)) RETURNS INT(32) BEGIN DECLARE result INT(1) DEFAULT 0; SELECT pmsg REGEXP...
手机号码靓号匹配
水货大帝的专栏
10-27 2586
手机号码靓号匹配在做一个项目时需要对储存的所有号码进行靓号推荐，需要根据前台用户选择的号码模式将符合条件的号码返回前台进行显示，供用户进行选择。 发现MySQL数据库的正则表达式支持度弱，只有部分简单的正则表达式可以支持，比如匹配以特定字符开始、结尾或包含某特定字符的情况。只能是通过java编程的方式进行匹配
mysql取电话号码的后四位字符
weixin_30687811的博客
10-16 264
SELECT id, RIGHT (phone,4) from user where id= 'xxxxxx' 转载于:https://www.cnblogs.com/dengyg200891/p/5966167.html
MySQL教程（11）查询数据
.... 永远年轻，永远热泪盈眶
04-06 5519
MySQL 查询数据 MySQL 数据库使用SQL SELECT语句来查询数据。 你可以通过 mysql> 命令提示窗口中在数据库中查询数据，或者通过PHP脚本来查询数据。 语法 以下为在MySQL数据库中查询数据通用的 SELECT 语法： SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT ...
mysql如何查询不是身份证号（15位和18位都有）的记录
u010674395的专栏
06-02 6480
mysql是支持正则表达式的。（REGEXP） 下面mysql语句即可查询出card不为身份证号的记录 （18位纯数字，17位纯数字+X,15位纯数字） select * from table where card not REGEXP '[0-9]{18}|[0-9]{17}X|[0-9]{15}'
手机号合法性检查-MySQL自定义函数实现
zhangting1113的博客
04-14 736
- 正则校验 头部去除"+86"和"86"以后，字符串为11位数字组成，以"13"，"14"，"15"，"16"，"17"，"18"，"19"开头。-- 头部去除"+86"和"86"以后字符串以"13"，"14"，"15"，"16"，"17"，"18"，"19"开头。-- 头部去除"+86"和"86"以后长度满足11位。-- 返回值（varchar）：1：通过，0：不通过。-- 字符串仅包含[0-9+]-- 入参：手机号码（varchar）-- 去掉头部 86。
mysql 查询字段加密 - 对手机号、身份证号 查询结果 用****加密代替
cichu111的博客
09-29 1531
CONCAT_WS函数 CONCAT_WS( '****', SUBSTRING(phone,1,3),SUBSTRING(phone, 8, 4)) demo: select consignee, CONCAT_WS( '****', SUBSTRING(phone,1,3),SUBSTRING(phone, 8, 4)) from t_order where out_order_code = '1627912858-001';
一个奇怪的MySQL查询错误
walle的博客
05-28 861
SELECT CAST(phone_number AS UNSIGNED) digit,acc_moblie_number FROM t_account WHERE acc_moblie_number = 13450419286
手机号码归属地查询mysql数据库 2022 csdn
最新发布
09-06
手机号码归属地查询可以利用MySQL数据库实现。首先，我们需要创建一个包含相应字段的表，包括手机号码列和归属地列。接着，将包含所有手机号码及其相应归属地信息的数据导入到该表中。 在查询过程中，我们可以使用MySQL的SELECT语句，并结合WHERE子句来过滤需要查询的手机号码。例如，可以使用类似以下的查询语句来查询某个手机号码的归属地： SELECT 归属地 FROM 手机归属地表 WHERE 手机号码 = '2022'; 其中，手机号码为用户输入的待查询的号码，归属地为查询结果返回的字段。 使用MySQL数据库进行手机号码归属地查询的好处是数据存储和管理都比较方便。我们可以根据需要添加和删除数据，也可以通过索引来优化查询速度。同时，MySQL数据库支持复杂的查询语句和聚合操作，可以满足不同需求的查询要求。 除了MySQL数据库外，还有其他数据库管理系统也可以实现手机号码归属地查询，如Oracle、SQL Server等。它们都具备相似的功能和操作方式，只是语法和细节上有所不同。 总之，使用MySQL数据库进行手机号码归属地查询，可以通过创建包含手机号码和归属地字段的表，然后使用SELECT语句进行查询操作。这样可以方便地存储和管理数据，并且可以根据具体需求进行灵活的查询和操作。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

qiweilong123456

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值