比如我有某个字段 uid
他的值可能是 88
99,88,100
99,88,100,110,120,130,140
等我现在要查询 该字段不包含 88 的值
语句为 where uid not like '%88%'请问有没有比这个 like 效率高点的语句字段里面都是 整数 每个整数用,分割
他的值可能是 88
99,88,100
99,88,100,110,120,130,140
等我现在要查询 该字段不包含 88 的值
语句为 where uid not like '%88%'请问有没有比这个 like 效率高点的语句字段里面都是 整数 每个整数用,分割
where charindex(',88,',uid)=0
select * from tb
where charindex(',88,',','+uid+',')=0
where charindex(',88,',uid)=0效率真的比 like 高吗?
但是 where uid not like '%88%' 是不正确的写法。
反例:uid='12,188,222'
where ','+uid+',' not like '%,88,%'SQL codeselect * from tb
where uid not like '%,88,%'
and uid not like '88,%'
and uid not like '%,88'select * from tb
where charindex(',88,',','+uid+',')=0效率那个高?
1.由于'%%',没有索引优势
2.NOT关键字会降低效率
SQL codeselect * from tb
where ','+uid+',' not like '%,88,%'
SQL codeselect * from tb
where uid not like '%,88,%'
and uid not like '88,%'
and uid not like '%,88'
后者绝对不会比前者快
SQL codeselect * from tb
where ','+uid+',' not like '%,88,%'
SQL codeselect * from tb
where uid not like '%,88,%'
and uid not like '88,%'
and uid not like '%,88'
后者绝对不会比前者快
测试一下patindex
select * from tb
where patindex('%,88,%',','+uid+',')=0
[/code]
http://blog.csdn.net/Haiwer/archive/2008/08/25/2826881.aspxA、 不要对索引字段进行运算,而要想办法做变换
B、 不要对索引字段进行格式转换
C、 不要对索引字段使用函数
那么比较7楼的3种方法:
第一种显然不可取,在uid上进行了运算,并有not,'%%'这些符号,肯定用不到索引。
第二种,存在'%,88,%'这种条件。网上说这种形式会显式阻止使用索引……
另:SQL codeselect * from tb
where uid not like '%,88,%'
and uid not like '88,%'
and uid not like '%,88'
是不对的
应该是
SQL codeselect * from tb
where uid not like '%,88,%'
and uid not like '88,%'
and uid not like '%,88'
and uid not like '88'
charindex(',88,',','+uid+',')=0这个很牛