纯数字字符串模糊搜索问题

XphteR 2016-02-21 08:49:05

加精

情况是这样的：有个表存放的是400电话号码数据，400电话号码字段是nvarchar类型，数据类似于4000377888这样的。
如何能以比较快的速度查询出包含某数字序列的号码呢？
比如：查询所有包含0377的号码。
方法1：SELECT * FROM tableName WHERE Number400 LIKE '%0377%'
存在的问题：无法使用索引，速度太慢；

方法2：SELECT * FROM tableName WHERE CONTAINS(Number400, '0377')
存在的问题：即使将非索引字表关闭，也依然查询不到任何数据，似乎MSSQL始终认为数字是干扰词；

谢谢

...全文

4128 25 打赏收藏转发到动态举报

写回复

用AI写文章

25 条回复

切换为时间正序

请发表友善的回复…

发表回复

mingqing6364 2016-03-19

打赏
举报

最后总结一下： 1.如果一个字段的不同取值数量很少（比如10个）那就没必要去给它加索引 2.UPDATE写的太复杂（各种函数表达式）会严重拖累性能 3.insert into tablename values （）,（）这种复合插入（最多接1000个）真心很快

mingqing6364 2016-03-19

打赏
举报

@XphteR SQL是结构化查询语言，结构化越彻底，查询速度越快，下面我会用SQL代码给出一个优化方案
先说下效果：
优化前出结果要5秒左右，优化后在2秒内

第一步当然是创建表了：

CREATE TABLE [dbo].[PN]

(

	[PhoneNum] nvarchar(10),

	[Num_Count_0] tinyint,

	[Num_Count_1] tinyint,

	[Num_Count_2] tinyint,

	[Num_Count_3] tinyint,

	[Num_Count_4] tinyint,

	[Num_Count_5] tinyint,

	[Num_Count_6] tinyint,

	[Num_Count_7] tinyint,

	[Num_Count_8] tinyint,

	[Num_Count_9] tinyint 

);

/*

受影响的行: 0

时间: 0.002s

*/

第二步，插入数据，PS：这一步的SQL我省略了一大段，要不然太长了，我是直接用EXCEL拖出来的^_^



DECLARE @i BIGINT = 4000377888

WHILE @i <= 4005177888

BEGIN

	INSERT INTO PN (PhoneNum) VALUES

(CAST(@i AS NVARCHAR(10))),

(CAST(@i+1 AS NVARCHAR(10))),

(CAST(@i+2 AS NVARCHAR(10))),

(CAST(@i+3 AS NVARCHAR(10))),

(CAST(@i+4 AS NVARCHAR(10))),

(CAST(@i+5 AS NVARCHAR(10))),

……

(CAST(@i+997 AS NVARCHAR(10))),

(CAST(@i+998 AS NVARCHAR(10))),

(CAST(@i+999 AS NVARCHAR(10)))

	SET @i = @i + 1000

END;

/*

受影响的行: 1000

时间: 13.308s

*/

第三步，UPDATE，这一步平时可以在插入的时候完成



UPDATE PN SET

Num_Count_0 = LEN(PhoneNum) - LEN(REPLACE(PhoneNum, '0', '')),

Num_Count_1 = LEN(PhoneNum) - LEN(REPLACE(PhoneNum, '1', '')),

Num_Count_2 = LEN(PhoneNum) - LEN(REPLACE(PhoneNum, '2', '')),

Num_Count_3 = LEN(PhoneNum) - LEN(REPLACE(PhoneNum, '3', '')),

Num_Count_4 = LEN(PhoneNum) - LEN(REPLACE(PhoneNum, '4', '')),

Num_Count_5 = LEN(PhoneNum) - LEN(REPLACE(PhoneNum, '5', '')),

Num_Count_6 = LEN(PhoneNum) - LEN(REPLACE(PhoneNum, '6', '')),

Num_Count_7 = LEN(PhoneNum) - LEN(REPLACE(PhoneNum, '7', '')),

Num_Count_8 = LEN(PhoneNum) - LEN(REPLACE(PhoneNum, '8', '')),

Num_Count_9 = LEN(PhoneNum) - LEN(REPLACE(PhoneNum, '9', ''))

/*

受影响的行: 4801000

时间: 71.020s

*/

最后，几种查询方案用时比较



PRINT @@version

/*

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) 

	Apr  2 2010 15:53:02 

	Copyright (c) Microsoft Corporation

	Express Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) (Hypervisor)



受影响的行: 0

时间: 0.007s

*/

SELECT PhoneNum FROM PN WHERE PhoneNum LIKE '%0377%'

/*

受影响的行: 0

时间: 5.277s

*/



SELECT SUM(1) FROM PN WHERE PhoneNum LIKE '%0377%'

/*

受影响的行: 0

时间: 4.607s

*/



SELECT PhoneNum FROM PN WHERE Num_Count_0 >= 1 AND Num_Count_3 >= 1 AND Num_Count_7 >= 2 AND PhoneNum LIKE '%0377%'

/*

受影响的行: 0

时间: 1.429s

*/



SELECT SUM(1) FROM PN WHERE Num_Count_0 >= 1 AND Num_Count_3 >= 1 AND Num_Count_7 >= 2 AND PhoneNum LIKE '%0377%'

/*

受影响的行: 0

时间: 0.740s

*/

最后的最后贴出我SQL SERVER服务器的配置，虚拟机，分配了4个CPU核心过去

com8716187161 2016-03-14

打赏
举报

select 1 as id into #tt where '4000377888' LIKE '4000377%' select 1 as id into #tt2 where '4000037788' LIKE '400_0377%' select 1 as id into #tt3 where '4000003778' www.60400.com LIKE '400__0377%' select *From #tt union all select *From #tt2 union all select *From #tt3

我爱娃哈哈 2016-03-03

打赏
举报

引用 17 楼 hemowolf 的回复:

1、把表增加两个字段，一段保存 0377 那部分，一段保存 888 那部分，作为冗余字段，加快查询用的，由于是数字，可以考虑使用 int 型，可以少占点空间 2、在这两个字段上建索引查询可以变 like 为数值范围比如查询 0377，可以是： select * from youtable where p1 = 377 -- 400 0377 XXX or (p2 between 700 and 799 and p1 mod 100 = 37) -- 400 X037 7XX or (p2 between 770 and 779 and p1 mod 10 = 3) -- 400 XXX3 77X or (p2 = 337 and p1 mod 10 = 0) -- 400 XXX0 377 between 放前面，取模放后面，应该对效率有提升

好方法

giftsf 2016-03-03

打赏
举报

select 1 as id into #tt where '4000377888' LIKE '4000377%' select 1 as id into #tt2 where '4000037788' LIKE '400_0377%' select 1 as id into #tt3 where '4000003778' LIKE '400__0377%' select *From #tt union all select *From #tt2 union all select *From #tt3 索引就用上了

qzyf1992 2016-03-02

打赏
举报

先 SELECT * FROM tableName WHERE Number400 LIKE '0377%' 没有前置的%索引会使用索引查找，把找到的结果集放入临时表然后使用SELECT * FROM tableName WHERE Number400 LIKE '%0377%' 查询临时表

小灰狼 2016-03-01

打赏
举报

1、把表增加两个字段，一段保存 0377 那部分，一段保存 888 那部分，作为冗余字段，加快查询用的，由于是数字，可以考虑使用 int 型，可以少占点空间 2、在这两个字段上建索引查询可以变 like 为数值范围比如查询 0377，可以是： select * from youtable where p1 = 377 -- 400 0377 XXX or (p2 between 700 and 799 and p1 mod 100 = 37) -- 400 X037 7XX or (p2 between 770 and 779 and p1 mod 10 = 3) -- 400 XXX3 77X or (p2 = 337 and p1 mod 10 = 0) -- 400 XXX0 377 between 放前面，取模放后面，应该对效率有提升

小灰狼 2016-03-01

打赏
举报

引用 1 楼 fengqingtao2008 的回复:

路过帮顶下，如果是纯数字的话，先把字段值 Cast转换成数字类型，在做进一步的模糊筛选！

这样要做一次全表扫描，还要把每一行的相关字段作转换，速度快不起来，反而会更慢

-0000- 2016-03-01

打赏
举报

400后面每个数字存一列，那不是更快。。。

XphteR 2016-02-29

打赏
举报

引用 7 楼 xiaoxiangqing 的回复:

最好分表来保存，不然速度快不了

测试了3天，480多万数据，不管如何做分区表，速度还是很慢。

XphteR 2016-02-26

打赏
举报

引用 8 楼 SugarToffee 的回复:

你截取字符串吧毕竟=比like快
 SELECT * FROM tableName WHERE substring（ Number400,4,4）= 0377

现在是想实现类似LIKE匹配查询的功能，所以没办法用substring函数。

XphteR 2016-02-26

打赏
举报

引用 12 楼 shoppo0505 的回复:

[quote=引用 10 楼 XphteR 的回复:]
[quote=引用 6 楼 shoppo0505 的回复:]
插入index应该比like效率高.
你试一下select Number from....那个快,在建立index,然后include其他字段看看.

你现在的问题感觉是之后select字段时候阻塞了,和你提到的两个函数关系不大

不太明白，能否详细说一下，多谢。

[/quote]

你先分别执行下，
1. SELECT Number FROM tableName WHERE Number400 LIKE '%0377%'
2. SELECT Number FROM tableName WHERE CONTAINS(Number400, '0377')
看哪个快，如果一样的话说明导致速度变化的原因是执行select时候的阻塞。

如果上述成立，那你可以对列 Number400建立一个index，里面include进去你想要最后select出来的内容就行[/quote]
试过了，用全文索引查询不出来任何数据



SELECT Number400 FROM tableName WHERE CONTAINS(Number400, '0377')

shoppo0505 2016-02-26

打赏
举报

引用 10 楼 XphteR 的回复:

[quote=引用 6 楼 shoppo0505 的回复:] 插入index应该比like效率高. 你试一下select Number from....那个快,在建立index,然后include其他字段看看. 你现在的问题感觉是之后select字段时候阻塞了,和你提到的两个函数关系不大

不太明白，能否详细说一下，多谢。

[/quote] 你先分别执行下， 1. SELECT Number FROM tableName WHERE Number400 LIKE '%0377%' 2. SELECT Number FROM tableName WHERE CONTAINS(Number400, '0377') 看哪个快，如果一样的话说明导致速度变化的原因是执行select时候的阻塞。如果上述成立，那你可以对列 Number400建立一个index，里面include进去你想要最后select出来的内容就行

顾西昂 2016-02-26

打赏
举报

要不你把要搜的字段全写出来这样毕竟优化了

XphteR 2016-02-26

打赏
举报

引用 6 楼 shoppo0505 的回复:

插入index应该比like效率高. 你试一下select Number from....那个快,在建立index,然后include其他字段看看. 你现在的问题感觉是之后select字段时候阻塞了,和你提到的两个函数关系不大

不太明白，能否详细说一下，多谢。

顾西昂 2016-02-23

打赏
举报

你截取字符串吧毕竟=比like快


 SELECT * FROM tableName WHERE substring（ Number400,4,4）= 0377

xiaoxiangqing 2016-02-23

打赏
举报

最好分表来保存，不然速度快不了

shoppo0505 2016-02-22

打赏
举报

插入index应该比like效率高. 你试一下select Number from....那个快,在建立index,然后include其他字段看看. 你现在的问题感觉是之后select字段时候阻塞了,和你提到的两个函数关系不大

XphteR 2016-02-22

打赏
举报

引用 4 楼 ch21st 的回复:

你有没有试试charindex


SELECT * FROM tableName WHERE CHARINDEX( '0377', Number400)>0

试过了，速度没有直接LIKE快

XphteR 2016-02-22

打赏
举报

引用 1 楼 fengqingtao2008 的回复:

路过帮顶下，如果是纯数字的话，先把字段值 Cast转换成数字类型，在做进一步的模糊筛选！

谢谢帮顶。这个字段是可以存储为整数类型的，数字类型怎么做模糊筛选呢？

本篇文章给大家带来的内容是关于MySQL中字符串和数字的比较(附示例)，有一定的参考价值，有需要的朋友可以参考一下，希望对你有所帮助。在项目中，我们经常会用到模糊搜索，但如果错误的将字符串类型和数字类型做比较，有时搜索出来的结果就并不是我们预期的。举例如下：mybatis中的xml语句如下：AND (name like concat("%",#{criteria},"%") OR id = #{c...

本文给出了在C#下利用正则表达式实现字符串搜索功能的方法,通过对.NET框架下的正则表达式的研究及实例分析,总结了正则表达式的元字符、规则、选项等. 关键字：正则表达式、元字符、字符串、匹配 1、正则表达式简介正则表达式提供了功能强大、灵活而又高效的方法来处理文本.正则表达式的全面模式匹配表示法可以快速地分析大量的文本以找到特定的字符模式;提? ⒈嗉⑻婊换蛏境谋

1:以字段开头模糊查询的like的方法查询 select * from t where t.user_code like 'A%' 2:以字段开头模糊查询的left(字段,长度)的方法查询 select * from t where left(t.user_code, 1)='A'; 3:字符间隔查询段对应的值以 1,4,6 的格式查询使用find_in_set select * ...

假设表名为t1,列名为d1,则sql如下 select d1 from t1 where regexp_like(d1,’^[0-9a-zA-Z]+$’) +：表示匹配前面的字表达式1次或多次(*号表示至少匹配0次) ^：匹配输入字符串开始的位置，对应like中的like ’xxx%’，即没有前模糊查询(^在[]中括号表示否的意思) $：匹配输入字符串结束的位置，对应like中的li...

应用实例

27,582

社区成员

68,544

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章