简单说一下各种写法的区别

 

本文出处:  

 

前不久发现还会有好多做开辟的小同伴,在写存款和储蓄进程的时候,在参谋已有的差别的写法时,往往很糊涂,
不清楚各类写法孰优孰劣,该选取这种写法,以及各样写法优弱点,本文以三个大致的询问存款和储蓄进程为例,轻巧说一下种种写法的区分,以及该用这种写法
正式DBA以及熟知数据库的同校请无视。

 

废话十分的少,上代码表达,先造三个测验表待用,轻松说多美滋(Dumex)下这些表的情事

恍如订单表,订单表有订单ID,客户ID,订单创制时间等,查询条件是常用的订单ID,客户ID,以及订单创造时间

create table SaleOrder
(
    id       int identity(1,1),
    OrderNumber  int         ,
    CustomerId   varchar(20)      ,
    OrderDate    datetime         ,
    Remark       varchar(200)
)
GO
declare @i int=0
while @i<100000
begin
    insert into SaleOrder values (@i,CONCAT('C',cast(RAND()*1000 as int)),GETDATE()-RAND()*100,NEWID())
    set @i=@i+1
end
create index idx_OrderNumber on SaleOrder(OrderNumber)
create index idx_CustomerId on SaleOrder(CustomerId)
create index idx_OrderDate on SaleOrder(OrderDate)

 生成的测验数据大约就是其同样子的

 图片 1

 

 

 

上面演示验证三种常见的写法以及每一个写法潜在的难题

 

 

首先种分布的写法:拼凑字符串,用EXEC的艺术实行那几个拼凑出来的字符串,不推荐

create proc pr_getOrederInfo_1
(
    @p_OrderNumber       int      ,
    @p_CustomerId        varchar(20) ,
    @p_OrderDateBegin    datetime   ,
    @p_OrderDateEnd      datetime
)
as
begin

    set nocount on;
    declare @strSql nvarchar(max);
    set @strSql= 'SELECT [id]
               ,[OrderNumber]
               ,[CustomerId]
               ,[OrderDate]
               ,[Remark]
            FROM [dbo].[SaleOrder] where 1=1 ';
    /*
        这种写法的特点在于将查询SQL拼凑成一个字符串,最后以EXEC的方式执行这个SQL字符串
    */

    if(@p_OrderNumber is not null)
        set @strSql = @strSql + ' and OrderNumber = ' + @p_OrderNumber
    if(@p_CustomerId is not null)
        set @strSql = @strSql + ' and CustomerId  = '+ ''''+ @p_CustomerId + ''''
    if(@p_OrderDateBegin is not null)
        set @strSql = @strSql + ' and OrderDate >= ' + '''' + cast(@p_OrderDateBegin as varchar(10)) + ''''
    if(@p_OrderDateEnd is not null)
        set @strSql = @strSql + ' and OrderDate <= ' + '''' + cast(@p_OrderDateEnd as varchar(10)) + ''''

    print @strSql
    exec(@strSql);

end

 

  假使我们查询CustomerId为88,在二零一五-10-1至二零一五-10-3最近内的订单新闻,如下,带入参数实施

exec pr_getOrederInfo_1
    @p_OrderNumber      = null      ,
    @p_CustomerId       = 'C88'     ,
    @p_OrderDateBegin   = '2016-10-1' ,
    @p_OrderDateEnd     = '2016-10-3'

 

  首先说明,这种艺术实践查询是一点一滴未有失水准的如下截图,结果也查出来了(当然结果也是没难点的)

图片 2

我们把执行的SQL打字与印刷出来,实践的SQL语句小编就是正是积存进程中拼凑出来的字符串,这么三个查询SQL字符串

SELECT [id]
    ,[OrderNumber]
    ,[CustomerId]
    ,[OrderDate]
    ,[Remark]
FROM [dbo].[SaleOrder] 
where 1=1  
    and CustomerId  = 'C88' 
    and OrderDate >= '2016-10-1' 
    and OrderDate <= '2016-10-3'

  

  那么这种存款和储蓄进度的有何样难点,可能直接一点说,这种方法有怎么着不佳的地点

    其一,绕但是转移符(以及注入难题)

       在拼凑字符串时,把装有的参数都当成字符串处理,当查问条件自个儿满含特殊字符的时候,比方 ‘ 符号,
       只怕别的供给转义的字符时,你拼凑的SQL就被打断了
       举个不伏贴的事例,举例字符串中 @p_CustomerId中隐含 ‘
符号,直接就把您拼SQL的节凑给打乱了
       拼凑的SQL就产生了那么些样子了,语法就不通过,更别提实践

          SELECT [id]
              ,[OrderNumber]
              ,[CustomerId]
              ,[OrderDate]
              ,[Remark]
          FROM [dbo].[SaleOrder] 
          where 1=1  and CustomerId  = 'C'88' 

       一方面需求处理转移符,另一方面须求要严防SQL注入

   其二,参数不一致就亟须另行编写翻译
      
 这种拼凑SQL的措施,假若老是查询的参数区别,拼凑出来的SQL字符串也不等同,
        假如熟练SQL
Server的同窗料定精通,只要你奉行的SQL文本不雷同,
        比如
        第三次是实践查询 *** where CustomerId = ‘C88’ ,
                   第一回是实行查询 *** where CustomerId = ‘C99’
,因为四次进行的SQL文本分裂
      
 每便实践从前一定必要对其进展编写翻译,编写翻译的话就必要CPU,内部存款和储蓄器能源
      
 假如存在大量的SQL编写翻译,无疑要开销更加多的CPU财富(当然也亟需有的内部存款和储蓄器能源)

 

 

其次种常见的写法:对具备查询条件用OENCORE的办法加在where条件中,那么些不引入

create proc pr_getOrederInfo_2
(
    @p_OrderNumber      int      ,
    @p_CustomerId       varchar(20) ,
    @p_OrderDateBegin   datetime   ,
    @p_OrderDateEnd     datetime
)
as
begin

    set nocount on;

    declare @strSql nvarchar(max);

    SELECT [id]
            ,[OrderNumber]
            ,[CustomerId]
            ,[OrderDate]
            ,[Remark]
    FROM [dbo].[SaleOrder] 
    where 1=1
        and (@p_OrderNumber is null  or OrderNumber  = @p_OrderNumber)
        and (@p_CustomerId  is null  or CustomerId   = @p_CustomerId)
        /*
        这是另外一种类似的奇葩的写法,下面会重点关注
        and  OrderNumber  = ISNULL( @p_OrderNumber,OrderNumber)
        and  CustomerId   = ISNULL( @p_CustomerId,CustomerId)
        */
        and (@p_OrderDateBegin is null or OrderDate  >= @p_OrderDateBegin)
        and (@p_OrderDateEnd is null   or OrderDate  <= @p_OrderDateEnd)

end

先是看这种方法的实行结果,带入一样的参数,跟上边的结果同样,查询(结果)本人是向来不别的难点的

图片 3

  这种写法写起来防止了拼凑字符串的拍卖,看起来很简单,写起来也极快,稀里哗啦贰个积攒进度就写好了,
  发布到生产条件之后就一定于埋了一颗雷,随时引爆。
  因为一条低效而又往往施行的SQL,拖垮一台服务器也是普通
  然而呢,难点丰富多,也非常特别不推荐,乃至比第一种办法更不好。

  深入分析一下这种管理方式的逻辑:
  这种管理方式,因为不分明询问的时候到底有未有传播参数,也就数说不能显明某壹个询问条件是还是不是见效,
简单说一下各种写法的区别。简单说一下各种写法的区别。  于是就动用类似 and (@p_简单说一下各种写法的区别。OrderNumber is null or OrderNumber =
@p_OrderNumber)这种格局,来拍卖参数,
  那样的话
  如果@p_OrderNumber为null,or的前者(@p_OrderNumber is
null)创建,后面一个不创造,查询条件不奏效
  如果@p_OrderNumber为非null,or的后者(OrderNumber =
@p_OrderNumber)创设而前面贰个不树立,查询条件生效
简单说一下各种写法的区别。  总来说之来讲,不管参数是不是为空,都足以有效地拼凑到查询条件中去。
  防止了拼SQL字符串,既达成让参数非空的时候生效,有产生参数为空的时候不奏效,看起来不错,是实在吗?

  那么这种存款和储蓄进度的有怎么着难题?

简单说一下各种写法的区别。    1,会抑制索引的动静

      如图,带入参数值试行存款和储蓄进度,先忽略其它七个查询字段,只传入@p_CustomerId参数,
      相关查询列上(CustomerId)有目录,不过此地走的是CustomerId列上的Index
Scan而非预期的Index Seek

      图片 4

 

      为啥说可能会抑制到目录的时候?上边提到过,SQL在举办在此之前是索要编写翻译的,
      因为在编写翻译的时候并不知道查询条件是不是传入了值,有异常的大或者为null,有望是贰个实际的值

     
 纠错:上边的一句话,使用参数做编写翻译的时候,是掌握参数的值的(唯有选用本地变量的时候才不知情具体的参数值,直接利用参数确实是理解的),
        
 编写翻译也是依照实际的参数值来变化推行布署的,可是怎么不怕知器具体的参数值的情状下,依旧生成一个Index
Scan的格局,而不是意在的Index Seek?
        
 即正是储存进程在编写翻译的时候知道了参数的值,为啥照旧用不到目录?
          还要从and (@p_CustomerId  is null  or CustomerId   =
@p_CustomerId)这种写法入手剖析。

         即就是CustomerId列上有索引,
           如果@p_CustomerId
 参数非空,走索引Seek完全没不平时。
                      如果@p_CustomerId  为null,此时and
(@p_CustomerId  is null  or CustomerId   =
@p_CustomerId)这几个法规恒创制,如若再走索引Seek会并发哪些结果?
                      语义上产生了是查找CustomerId
 为null的值,假如采用Index Seek的艺术实行,那样的话逻辑上业已错误了。
                  
 由此出现这种写法,为了安全起见,优化器只好选用三个索引的扫视(即就是字段上有索引的情形下)

        
能够认为是这种写法在语义帮助不断相关索引的Seek,而索引的Scan是管理这种写法的一种安全的方式

         The optimiser can tell that and it plays safe. It
creates plans that will always work.
         That’s (one of the reasons) why in the first example it
was an index scan, not an index seek.

        参照这里,能够大致地领略成这种写法,语义上补助不住索引的Seek,最多协理到index
scan

简单说一下各种写法的区别。      至于(@p_CustomerId  is null or CustomerId  = @p_CustomerId
 )这种写法遇到本地变量的时候,
      为何抑制到到目录的施用,小编在此之前也是尚未弄精通的,批评中10楼Uest
给出了疏解,这里十三分谢谢Uest

      如下

      图片 5

      假如本人一向带入CustomerId=‘C88’,再来看执行布置,结果跟下面同样,不过施行安排是完全区别等的,这正是所谓的防止到目录的行使。

      图片 6

   

   2,特别极度沉重的逻辑错误

        /*
            这是另外一种类似的奇葩的写法,需要重点关注,真的就能满足“不管参数是否为空都满足”
            and  OrderNumber = ISNULL( @p_OrderNumber,OrderNumber)
            and  CustomerId  = ISNULL( @p_CustomerId,CustomerId)
            */

    对于如下这种写法:OrderNumber = ISNULL(
@p_OrderNumber,OrderNumber),
    一部分人不胜重申,以为这种方法大致、清晰,笔者也是醉了,有非常的大希望产生十二分严重的逻辑错误
    假如参数为null,就调换来这种语义 where
1=1 and OrderNumber = OrderNumber
    目标是查询参数为null,查询条件不奏效,让这一个查询条件恒成立,恒制造吗,不必然,有个别景况下就能有生死攸关的语义错误 

    博主开采那么些标题也是因为某个实际系统中的bug,折腾了长久才察觉这么些严重的逻辑错误 

    对于这种写法,
    不管是首先点说的平抑索引的标题,数据量大的时候是可怜沉痛的,上述写法会形成全表(索引)扫描,有目录也用不上,至于全表(索引)扫描的缺欠就背着了
    依然第二点说的诱致的逻辑错误,都以老大沉重的
    所以这种艺术是最最不引入的。

 

 

其两种常见的写法:参数化SQL,推荐

create proc pr_getOrederInfo_3
(
    @p_OrderNumber       int      ,
    @p_CustomerId        varchar(20) ,
    @p_OrderDateBegin    datetime   ,
    @p_OrderDateEnd      datetime
)
as
begin

       set nocount on;

      DECLARE @Parm         NVARCHAR(MAX) = N'',
              @sqlcommand   NVARCHAR(MAX) = N''

        SET @sqlcommand = 'SELECT [id]
                                  ,[OrderNumber]
                                  ,[CustomerId]
                                  ,[OrderDate]
                                  ,[Remark]
                            FROM [dbo].[SaleOrder] 
                            where 1=1 '

        IF(@p_OrderNumber IS NOT NULL)
            SET @sqlcommand = CONCAT(@sqlcommand,' AND OrderNumber= @p_OrderNumber')

        IF(@p_CustomerId IS NOT NULL)
            SET @sqlcommand = CONCAT(@sqlcommand,' AND CustomerId= @p_CustomerId')

        IF(@p_OrderDateBegin IS NOT NULL)
            SET @sqlcommand = CONCAT(@sqlcommand,' AND OrderDate>=@p_OrderDateBegin ')

        IF(@p_OrderDateEnd IS NOT NULL)
            SET @sqlcommand = CONCAT(@sqlcommand,' AND OrderDate<=@p_OrderDateEnd ')

        SET @Parm= '@p_OrderNumber        int,
                    @p_CustomerId        varchar(20),
                    @p_OrderDateBegin    datetime,
                    @p_OrderDateEnd        datetime '


        PRINT @sqlcommand
        EXEC sp_executesql @sqlcommand,@Parm,
                            @p_OrderNumber       =    @p_OrderNumber,
                            @p_CustomerId        =    @p_CustomerId,
                            @p_OrderDateBegin    =    @p_OrderDateBegin,
                            @p_OrderDateEnd      =    @p_OrderDateEnd 

end

 

第一大家用同一的参数来实施一下询问,当然没难点,结果跟上边是均等的。

  图片 7

所谓的参数化SQL,就是用变量当做占位符,通过 EXEC
sp_executesql试行的时候将参数字传送递进去SQL中,在急需填写数值或数额的地点,使用参数
(Parameter) 来给值,
那样的话,

首先,不仅可以幸免第一种写法中的SQL注入难点(包涵转移符的管理),
   因为参数是运作时传递踏向SQL的,并不是编写翻译时传递步入的,传递的参数是怎么样就根据什么样施行,参数自身不参加编写翻译
其次,保证实践安顿的选定,因为使用占位符来拼凑SQL的,SQL参数的值差异并招致最终实行的SQL文本区别
   同下面,参数自己不到场工编织译,如若查询条件一样(SQL语句就同样),而参数不均等,并不会潜移暗化要编写翻译的SQL文本音讯
其三,还应该有正是制止了第三种意况(and (@p_CustomerId is null or
CustomerId = @p_CustomerId)
   或者 and OrderNumber = ISNULL( @p_OrderNumber,OrderNumber))
  
 这种写法,查询条件有正是有,没有正是从未,不会丢给SQL查询引擎叁个模棱七个的结果,
    防止了对索引的防止行为,是一种相比好的管理查询条件的议程。

瑕玷,1,对于这种方式,也是有少数倒霉的地点,正是拼接的字符串管理进程中,
     
调节和测量检验实际的SQL语句的时候,参数是直接拼凑在SQL文本中的,不能够平昔试行,要手动将占位参数替换成具体的参数值

      2,恐怕存在parameter sniff难题,可是对于parameter
sniff难点,不是不是认参数化SQL的最首要,当然化解parameter
sniff难点的主意照旧有个别,

       参考:

 

总结:

  以上总结了二种在开辟中相比常见的积攒进度的写法,每一种存储进度的写法大概在不一致的商铺都用利用,
  是或不是有人挑个最简易最高效(第二种)写法,写完不是成就了,而是埋雷了。
  不是太熟知SQL
Server的同校或然会有一点迷茫,有很八种写法,究竟要用哪一类写法那几个写法之间有啥样界别。
  本文通过一个简易的演示,说了普及的两种写法之间的区分,每一个格局存在的难点,以及孰优孰劣,请小同伴们明辨。
  数据库大神请无视,多谢。

 

You may also like...

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图