您当前的位置:首页 > 电脑百科 > 数据库 > 百科

以一个简单的查询存储过程为例,简单说一下sql的几种写法

时间:2021-12-23 09:32:03  来源:  作者:linux上的码农

最近发现还有不少做开发的小伙伴,在写存储过程的时候,在参考已有的不同的写法时,往往很迷茫,
不知道各种写法孰优孰劣,该选用哪种写法,以及各种写法的优缺点,本文以一个简单的查询存储过程为例,简单说一下各种写法的区别,以及该用那种写法
专业DBA以及熟悉数据库的同学请无视。

废话不多说,上代码说明,先造一个测试表待用,简单说明一下这个表的情况

类似订单表,订单表有订单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)

生成的测试数据大概就是这个样子的

以一个简单的查询存储过程为例,简单说一下sql的几种写法

 

下面演示说明几种常见的写法以及每种写法潜在的问题

更多linux内核视频教程文本资料免费获取后台私信【内核】。

以一个简单的查询存储过程为例,简单说一下sql的几种写法

 


以一个简单的查询存储过程为例,简单说一下sql的几种写法

 

第一种常见的写法:拼凑字符串,用EXEC的方式执行这个拼凑出来的字符串,不推荐

以一个简单的查询存储过程为例,简单说一下sql的几种写法

 

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,在2016-10-1至2016-10-3这段时间内的订单信息,如下,带入参数执行

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

  首先说明,这种方式执行查询是完全没有问题的如下截图,结果也查出来了(当然结果也是没问题的)

以一个简单的查询存储过程为例,简单说一下sql的几种写法

 

我们把执行的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资源(当然也需要一些内存资源)

第二种常见的写法:对所有查询条件用OR的方式加在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

首先看这种方式的执行结果,带入同样的参数,跟上面的结果一样,查询(结果)本身是没有任何问题的

以一个简单的查询存储过程为例,简单说一下sql的几种写法

 

  这种写法写起来避免了拼凑字符串的处理,看起来很简洁,写起来也很快,稀里哗啦一个存储过程就写好了,
  发布到生产环境之后就相当于埋了一颗雷,随时引爆。
  因为一条低效而又频繁执行的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    

以一个简单的查询存储过程为例,简单说一下sql的几种写法

 

      纠错:上面的一句话,使用参数做编译的时候,是知道参数的值的(只有使用本地变量的时候才不知道具体的参数值,直接使用参数确实是知道的),
         编译也是根据具体的参数值来生成执行计划的,但是为什么即使知道具体的参数值的情况下,依然生成一个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

      如下

以一个简单的查询存储过程为例,简单说一下sql的几种写法

 

      如果我直接带入CustomerId=‘C88’,再来看执行计划,结果跟上面一样,但是执行计划是完全不一样的,这就是所谓的抑制到索引的使用。

以一个简单的查询存储过程为例,简单说一下sql的几种写法

 

   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

首先我们用同样的参数来执行一下查询,当然没问题,结果跟上面是一样的

以一个简单的查询存储过程为例,简单说一下sql的几种写法

 

所谓的参数化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的同学可能会有点迷茫,有很多种写法,究竟要用哪种写法这些写法之间有什么区别。
  本文通过一个简单的示例,说了常见的几种写法之间的区别,每种方式存在的问题,以及孰优孰劣,请小伙伴们明辨。
  数据库大神请无视,谢谢。

要用哪种写法这些写法之间有什么区别。
  本文通过一个简单的示例,说了常见的几种写法之间的区别,每种方式存在的问题,以及孰优孰劣,请小伙伴们明辨。
  数据库大神请无视,谢谢。



Tags:sql   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
1增1.1【插入单行】insert [into] <表名> (列名) values (列值)例:insert into Strdents (姓名,性别,出生日期) values (&#39;开心朋朋&#39;,&#39;男&#39;,&#39;1980/6/15&#3...【详细内容】
2021-12-27  Tags: sql  点击:(1)  评论:(0)  加入收藏
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  Tags: sql  点击:(6)  评论:(0)  加入收藏
前言JDBC访问Postgresql的jsonb类型字段当然可以使用Postgresql jdbc驱动中提供的PGobject,但是这样在需要兼容多种数据库的系统开发中显得不那么通用,需要特殊处理。本文介绍...【详细内容】
2021-12-23  Tags: sql  点击:(12)  评论:(0)  加入收藏
最近发现还有不少做开发的小伙伴,在写存储过程的时候,在参考已有的不同的写法时,往往很迷茫, 不知道各种写法孰优孰劣,该选用哪种写法,以及各种写法的优缺点,本文以一个简单的查询...【详细内容】
2021-12-23  Tags: sql  点击:(9)  评论:(0)  加入收藏
场景描述:由于生产环境的表比较复杂,字段很多。这里我们做下简化,只为说明今天要聊的问题。有两张表 tab1,tab2: tab1 数据如下: tab2 数据如下: 然后给你看下,我用来统计 name=&#3...【详细内容】
2021-12-20  Tags: sql  点击:(5)  评论:(0)  加入收藏
概述我们知道SQL Server是微软公司推出的重要的数据库产品,通常情况下只支持部署在windows平台上。不过令人感到兴奋的是,从SQL Server 2017开始支持 linux系统。此 SQL Serve...【详细内容】
2021-12-17  Tags: sql  点击:(13)  评论:(0)  加入收藏
读取SQLite数据库,就是读取一个路径\\192.168.100.**\position\db.sqlite下的文件<startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0"/...【详细内容】
2021-12-16  Tags: sql  点击:(21)  评论:(0)  加入收藏
前言知识无底,学海无涯,知识点虽然简单,但是比较多,所以将MySQL的基础写出来,方便自己以后查找,还有就是分享给大家。一、SQL简述1.SQL的概述Structure Query Language(结构化查...【详细内容】
2021-12-16  Tags: sql  点击:(13)  评论:(0)  加入收藏
一、为什么要搭建主从架构呢1.数据安全,可以进行数据的备份。2.读写分离,大部分的业务系统来说都是读数据多,写数据少,当访问压力过大时,可以把读请求给到从服务器。从而缓解数据...【详细内容】
2021-12-15  Tags: sql  点击:(10)  评论:(0)  加入收藏
前言作为一名测试工程师,工作中在对测试结果进行数据比对的时候,或多或少要和数据库打交道的,要和数据库打交道,那么一些常用的 SQL 查询语法必须要掌握。最近有部分做测试小伙...【详细内容】
2021-12-14  Tags: sql  点击:(15)  评论:(0)  加入收藏
▌简易百科推荐
1增1.1【插入单行】insert [into] <表名> (列名) values (列值)例:insert into Strdents (姓名,性别,出生日期) values (&#39;开心朋朋&#39;,&#39;男&#39;,&#39;1980/6/15&#3...【详细内容】
2021-12-27  快乐火车9d3    Tags:SQL   点击:(1)  评论:(0)  加入收藏
最近发现还有不少做开发的小伙伴,在写存储过程的时候,在参考已有的不同的写法时,往往很迷茫, 不知道各种写法孰优孰劣,该选用哪种写法,以及各种写法的优缺点,本文以一个简单的查询...【详细内容】
2021-12-23  linux上的码农    Tags:sql   点击:(9)  评论:(0)  加入收藏
《开源精选》是我们分享Github、Gitee等开源社区中优质项目的栏目,包括技术、学习、实用与各种有趣的内容。本期推荐的HasorDB 是一个全功能数据库访问工具,提供对象映射、丰...【详细内容】
2021-12-22  GitHub精选    Tags:HasorDB   点击:(5)  评论:(0)  加入收藏
作者丨Rafal Grzegorczyk译者丨陈骏策划丨孙淑娟【51CTO.com原创稿件】您是否还在手动对数据库执行各种脚本?您是否还在浪费时间去验证数据库脚本的正确性?您是否还需要将...【详细内容】
2021-12-22    51CTO  Tags:Liquibase   点击:(3)  评论:(0)  加入收藏
场景描述:由于生产环境的表比较复杂,字段很多。这里我们做下简化,只为说明今天要聊的问题。有两张表 tab1,tab2: tab1 数据如下: tab2 数据如下: 然后给你看下,我用来统计 name=&#3...【详细内容】
2021-12-20  Bald    Tags:SQL   点击:(5)  评论:(0)  加入收藏
前言知识无底,学海无涯,知识点虽然简单,但是比较多,所以将MySQL的基础写出来,方便自己以后查找,还有就是分享给大家。一、SQL简述1.SQL的概述Structure Query Language(结构化查...【详细内容】
2021-12-16  谣言止于独立思考    Tags:SQL基础   点击:(13)  评论:(0)  加入收藏
前言作为一名测试工程师,工作中在对测试结果进行数据比对的时候,或多或少要和数据库打交道的,要和数据库打交道,那么一些常用的 SQL 查询语法必须要掌握。最近有部分做测试小伙...【详细内容】
2021-12-14  柠檬班软件测试    Tags:SQL   点击:(15)  评论:(0)  加入收藏
话说C是面向内存的编程语言。数据要能存得进去,取得出来,且要考虑效率。不管是顺序存储还是链式存储,其寻址方式总是很重要。顺序存储是连续存储。同质结构的数组通过其索引表...【详细内容】
2021-12-08  小智雅汇    Tags:数据存储   点击:(17)  评论:(0)  加入收藏
概述DBConvert Studio 是一款强大的跨数据库迁移和同步软件,可在不同数据库格式之间转换数据库结构和数据。它将成熟、稳定、久经考验的 DBConvert 和 DBSync 核心与改进的现...【详细内容】
2021-11-17  雪竹聊运维    Tags:数据库   点击:(26)  评论:(0)  加入收藏
一、前言 大家好,我是小诚,《从0到1-全面深刻理解MySQL系列》已经来到第四章,这一章节的主要从一条SQL执行的开始,由浅入深的解析SQL语句由客户端到服务器的完整执行流程,最...【详细内容】
2021-11-09  woaker    Tags:SQL   点击:(35)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条