精华内容
下载资源
问答
  • from table where username like '%陈哈哈%' and hobby like '%牛逼'这是一条我们在MySQL中常用到的模糊查询方法,通过通配符%来进行匹配,其实,这只是冰山一角,在MySQL中,支持模糊匹配的方法有很多,且各有各的...

    文章来源:MySQL模糊查询用法大全(正则、通配符、内置函数等)
    原文作者:陈哈哈
    来源平台:CSDN

    SELECT * from table where username like '%陈哈哈%' and hobby like '%牛逼'

    这是一条我们在MySQL中常用到的模糊查询方法,通过通配符%来进行匹配,其实,这只是冰山一角,在MySQL中,支持模糊匹配的方法有很多,且各有各的优点。好了,今天让我带大家一起掀起MySQL的小裙子,看一看模糊查询下面还藏着多少鲜为人知的好东西。

    一、MySQL通配符模糊查询(%,_)

    1-1. 通配符的分类

    1. "%" 百分号通配符: 表示任何字符出现任意次数 (可以是0次)。
    2. "_" 下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符。当然,也可以like "陈____",数量不限。
    3. like操作符:LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较;但如果like后面没出现通配符,则在SQL执行优化时将 like 默认为 “=”执行
    意: 如果在使用like操作符时,后面没有使用通用匹配符(%或_),那么效果是和“=”一致的。在SQL执行优化时查询优化器将 like 默认为 “=”执行,SELECT * FROM movies WHERE movie_name like '唐伯虎';只能匹配movie_name=“唐伯虎”的结果,而不能匹配像“唐伯虎点秋香”或“唐伯虎点香烟”这样的结果.

    1-2. 通配符的使用

    1) % 通配符:

    -- 模糊匹配含有“网”字的数据

    SELECT * from app_info where appName like '%%';

    -- 模糊匹配以“网”字结尾的数据

    SELECT * from app_info where appName like '%';

    1b9ff74dee57ec07689e98fb038bf79e.png

    -- 模糊匹配以“网”字开头的数据

    SELECT * from app_info where appName like '%';

    7938be3ba661d05e6392d92af23a8c4e.png

    -- 精准匹配,appName like '网' 等同于:appName = '网'

    SELECT * from app_info where appName = '网';
    -- 等同于
    SELECT * from app_info where appName like '网';

    -- 模糊匹配含有“xxx网xxx车xxx”的数据,如:"途途网约车司机端、网络约车平台"

    SELECT * from app_info where appName like '%%%';

    2) _ 通配符:

    -- 查询以“网”为结尾的,长度为三个字的数据,如:"链家网",

    SELECT * from app_info where appName like '__网';

    1d5c10b00041072a81e64ef5f4f29562.png

    -- 模糊匹配含有“xx网x车xxx”的数据,如:"携程网约车客户端"

    SELECT * from app_info where appName like '__网_车%';

    89068e9fd27fb8106a1dfb5cc1ff0265.png

    注意事项:

    注意大小写,在使用模糊匹配时,也就是匹配文本时,MySQL默认配置是不区分大小写的。当你使用别人的MySQL数据库时,要注意是否区分大小写,是否区分大小写取决于用户对MySQL的配置方式.如果是区分大小写,那么像Test12这样记录是不能被"test__"这样的匹配条件匹配的。
    注意尾部空格,"%test"是不能匹配"test "这样的记录的。
    注意NULL,%通配符可以匹配任意字符,但是不能匹配NULL,也就是说SELECT * FROM blog where title_name like '%';是匹配不到title_name为NULL的的记录。

    1-3. 技巧与建议:

    正如所见,MySQL的通配符很有用。但这种功能是有代价的:通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长,消耗更多的内存等资源。这里给出一些使用通配符要记住的技巧。

    • 不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。
    • 在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处。因为MySQL在where后面的执行顺序是从左往右执行的,如果把通配符置于搜索模式的开始处(最左侧),搜索起来是最慢的(因为要对全库进行扫描)。
    • 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据。

    有细心地朋友会发现,如果数据中有“%”、“_”等符号,那岂不是和通配符冲突了?

    SELECT * from app_info where appName LIKE '%%%';
     
    SELECT * from app_info where appName LIKE '%_%';

    确实如此,上面面两条SQL语句查询的都是全表数据,而不是带有"%"和"_"的指定数据。这里需要加 ESCAPE 关键字进行转义。

    如下,ESCAPE 后面跟着一个字符,里面写着什么,MySQL就把那个符号当做转义符,一般我就写成"/";然后就像 C语言中转义字符一样 例如 ‘n’,’t’, 把这个字符写在你需要转义的那个%号前就可以了;

    SELECT * from app_info where appName LIKE '%/_%' ESCAPE '/'; 

    351bd85f600d6768f2bd721c0056a855.png

    但是这种情况有没有更高端点的解决办法呢?能让检查你代码的同事或领导对你刮目相看那种~~

    当然,下面我们就来看看MySQL的第二类模糊匹配方式 --- 内置函数查询

    二、MySQL内置函数检索(locate,position,instr)

    话接上文,通过内置函数locate,position,instr进行匹配,相当于Java中的str.contains()方法,返回的是匹配内容在字符串中的位置,效率和可用性上都优于通配符匹配。

    SELECT * from app_info where INSTR(`appName`, '%') > 0;
     
    SELECT * from app_info where LOCATE('%', `appName`) > 0;
     
    SELECT * from app_info where POSITION( '%' IN `appName`) > 0;

    如上,三种内置函数默认都是:> 0,所以下列 > 0 可加可不加,加上可读性更好。

    OK,下面一起来看看这三种内置函数的使用方法吧。

    先明确一下,MySQL中的角标从左往右是从1开始的,不像java最左边第一位角标是0,因此在MySQL中角标为0时说明不存在。

    2-1. LOCATE()函数

    语法: LOCATE(substr,str)

    返回 substr 在 str 中第一次出现的位置。如果 substr 在 str 中不存在,返回值为 0,如果substr 在 str 中存在,返回值为:substr 在 str中第一次出现的位置。

    注意:LOCATE(substr,str)与 POSITION(substr IN str)是同义词,功能相同。

    语法: LOCATE(substr, str, [pos])

    从位置pos开始的字符串str中第一次出现子字符串substr的位置。 如果substr不在str中,则返回0。 如果substr或str为NULL,则返回NULL。

    SELECT locate('a', 'banana');       -- 2
    SELECT locate('a', 'banana', 3);    -- 4
    SELECT locate('z', 'banana');       -- 0
    SELECT locate(10, 'banana');        -- 0
    SELECT locate(NULL , 'banana');     -- null
    SELECT locate('a' , NULL );         -- null

    实例:

    -- 用LOCATE关键字进行模糊匹配,等同于:"like '%网%'"

    SELECT * from app_info where LOCATE('网', `appName`) > 0;

    -- 用LOCATE关键字进行模糊匹配, 从第二个字符开始匹配"网",则"网易云游戏、网来商家"等数据就被过滤了

    SELECT * from app_info where LOCATE('网', `appName`, 2) > 0;

    2-2. POSITION()方法

    语法:POSITION(substr IN substr)

    这个方法可以理解为locate(substr,str)方法的别名,因为它和locate(substr,str)方法的作用是一样的。

    实例:

    -- 用POSITION关键字进行模糊匹配,等同于:"like '%网%'"

    SELECT * from app_info where POSITION( '网' IN `appName`);

    2-3. INSTR()方法

    语法: INSTR(str,substr)

    返回字符串str中第一次出现子字符串substr的位置。INSTR()与LOCATE()的双参数形式相同,只是参数的顺序相反。

    实例:

    -- 用INSTR关键字进行模糊匹配,功能跟like一样 ,等同于:"like '%网%'"

    SELECT * from app_info where INSTR(`appName`, '网');

    -- instr函数作用,一般用于检索某字符在某字符串中的位置,等同于:"like '%网%'"

    SELECT * from app_info where INSTR(`appName`, '网') > 0;

    三、MySQL基于regexp、rlike的正则匹配查询

    MySQL中的regexp和rlike关键字属于同义词,功能相同。本文以regexp为准。

    REGEXP 不支持通配符"%、_",支持正则匹配规则,是一种更细力度且优雅的匹配方式,一起来看看吧

    -- 这里给出regexp包含的参数类型

    7099759af3b5f15fde3a5f8d2ec08918.png

    -- REGEXP '网' 等同于 like '%网%'

    SELECT * from app_info where appName REGEXP '网';
    -- 等同于
    SELECT * from app_info where appName like '%%';

    3-1. regexp中的 OR : |

    功能:可以搜索多个字符串之一,相当于 or

    -- 支持 "|" ‘或’符号,匹配包含“中国”或“互联网”或“大学”的数据,支持叠加多个

    SELECT * from app_info where appName REGEXP '中国|互联网|大学';

    7f2e5e5c2fd96608438d138f235f2794.png

    -- 匹配同时命中“中国”、“网”的数据可以用".+"连接,代表中国xxxx网,中间允许有任意个字符,顺序不能反。

    SELECT * from app_info where appName REGEXP '中国.+';

    5122bde6103f79a8eed56b2171bbb62c.png

    3-2. REGEXP中的正则匹配 : []

    功能:匹配[]符号中几个字符之一,支持解析正则表达式

    -- 匹配包含英文字符的数据,默认不区分大小写情况下

    SELECT * from app_info where appName REGEXP '[a-z]';

    c980e1dae7be4a36c9e6e406f500a064.png

    -- 跟like一样,取反集加 "not REGEXP" 即可,下面不再赘述

    SELECT * from app_info where appName not REGEXP '[a-z]';

    df45111033095e1c6676967c3547a4e2.png

    -- 匹配包含大写英文字符的数据,默认忽略大小写,需要加上"BINARY"关键字。如where appName REGEXP BINARY 'Hello'

    -- 关于大小写的区分:MySQL中正则表达式匹配(从版本3.23.4后)不区分大小写 。
    SELECT * from app_info where appName REGEXP BINARY '[A-Z]';

    9dd790603a4c1e5a338c6a36d902ae3f.png

    -- 匹配包含数字的数据

    SELECT * from app_info where appName REGEXP '[0-9]';

    -- 匹配包含数字或英文的数据,

    SELECT * from app_info where appName REGEXP '[a-z0-9]';

    a-z、0-9都认定为一个单位,不要加多余符号,前两天就发现了一个特殊情况,很有意思的bug,跟他家分享一下
    -- 之前写查询语句时多加了"|"符号,以为是"或",没有在意,但万万没想到,查出数量竟不同

    SELECT * from app_info where appName REGEXP '[567]'; -- 87条
     
    SELECT * from app_info where appName REGEXP '[5|6|7]'; -- 88条

    8bec12a1e94523ec441bbf3b0af14af7.png

    一头雾水,赶快看看差得是哪一条

    -- 原来"|"符号也参与到了匹配中,认定为一个单位。巧的是有一个数据为:“无线调音台 | Wireless Mixer” 这个正好匹配上。卧槽了个DJ

    SELECT * from app_info where appName REGEXP '[5|6|7]' and pid not in (SELECT pid from app_info where appName REGEXP '[567]');

    f1bcd14ba5c5e96655cd8e6b6038fcd6.png

    -- 查询以5、6、7其中一个为开头的数据

    SELECT * from app_info where appName REGEXP '^[5|6|7]';

    b17e3d190d2f448060117badfb86751b.png
    温馨提示:MySQL中,UTF-8的中文=3个字节;GBK的中文=2个字节

    -- 查询appName字节长度为10,任意内容的数据

    SELECT * from app_info where appName REGEXP '^.{10}$';

    d3422bc1ba138d98418fc17401666815.png

    - 查询appName字节长度为10,且都为大写英文的数据,加上BINARY即可

    SELECT * from app_info where appName REGEXP BINARY '^[A-Z]{10}$';

    a48abb550530a710da7f44161270483f.png

    -- 查询version_name字节长度为6,且都为数字或"." 的数据

    SELECT * from app_info where version_name REGEXP '^[0-9.]{6}$';

    faf21762697e5f4e06bcb74960aaf88d.png

    -- 查询version_name字节长度为6,且都为数字或"." 的数据;要求首位为1,末位为7

    SELECT * from app_info where version_name REGEXP '^1[0-9.]{4}7$' ;

    b3a2aa5f006aaf28622cf6e2272d235b.png

    -- 查询version_name字节长度为6位以上,且都为数字或"." 的数据;要求首位为1,末位为7

    SELECT * from app_info where version_name REGEXP '^1[0-9.]{4,}7$' ;

    f7b0a4afebb9e2cfa4bcc59d80900a21.png

    -- 查询version_name字节长度为 6 - 8 位,且都为数字或"." 的数据;要求首位为1,末位为7

    SELECT * from app_info where version_name REGEXP '^1[0-9.]{4,6}7$' ;

    0bee1129c09524d5a07a8be0375bfec5.png

    -- 首位字符不是中文的

    SELECT * from app_info where appName REGEXP '^[ -~]';

    b8374352b2ed4711527fed4b958080ce.png

    -- 首位字符是中文的

    SELECT * from app_info where appName REGEXP '^[^ -~]';

    c041e6c7142fea255e2929537a01a537.png

    -- 查询不包含中文的数据

    SELECT * from app_info where appName REGEXP '^([a-z]|[0-9]|[A-Z])+$';

    dda42e0503d9ae9dc2ba6d5d8472b772.png

    -- 以5或F开头的,且包含英文的数据

    SELECT * from app_info where appName REGEXP BINARY '^[5F][a-zA-Z].';

    445e881a4a7f416e3b2108ea357177f7.png

    特殊符号的匹配,例如.,需要加(注意是两个斜杠),但是如果在[]中可以不加:

    -- 匹配name中含有.
    select * from app_info where appName regexp '.';
    -- 匹配name中含有.
    select * from app_info where appName regexp '[.]';

    56bf6a3582449c750aa999dbc4a41a32.png

    3-3. 字符类匹配(posix)

    mysql中有一些特殊含义的符号,可以代表不同类型的匹配:

    -- 匹配name中含有数字的

    select * from app_info where appName regexp '[[:digit:]]';

    其他的这种字符类还有:

    4c171a79b62e6dd11341d135e1fcaf6f.png

    这种字符类需要主要的外层要加一层[]。

    3-4. [:<:]和[:>:]

    上面的字符类中有两个比较特殊的,这两个是关于位置的,[:<:]匹配词的开始,[:>:]匹配词的结束,它们和 ^、$ 不同。

    后者是匹配整个整体的开头和结束,而前者是匹配一个单词的开始和结束。

    -- 只能匹配整体以a开头的,例如abcd

    select * from app_info where appName regexp '^a';

    -- 能匹配整体以a开头的,也能匹配中间的单词以a开头,如:dance after。

    select * from app_info where appName regexp '[[:<:]]a';

    aa39cc446ffd4aca9ec0df303926ce1d.png

    [[:<:]] 、 [[:>:]] 分别匹配一个单词开头和结尾的空的字符串,这个单词开头和结尾都不是包含在alnum中的字符也不能是下划线。

    select "a word a" REGEXP "[[:<:]]word[[:>:]]"; -- 1表示匹配 
     
    select "a xword a" REGEXP "[[:<:]]word[[:>:]]"; -- 0表示不匹配 
     
    select "weeknights" REGEXP "^(wee|week)(knights|nights)$"; -- 1表示匹配 

    四、总结

    好啦,本篇文章就到这里了,能看到这里的都是有缘人,希望本文能帮助到你对MySQL的理解更进一步。请不要吝啬手中的赞呦。谢谢支持!

    展开全文
  • 在 Spring Boot 中使用 Dataway 配置数据查询有参接口 Dataway介绍 Dataway 是基于 DataQL 服务聚合能力,为应用提供的一个接口配置工具。使得使用者无需开发任何代码就配置一个满足需求的接口。 整个接口配置、测试...

    使用 Dataway 配置数据查询 与前端参数整合

    Dataway介绍
    Dataway 是基于 DataQL 服务聚合能力,为应用提供的一个接口配置工具。使得使用者无需开发任何代码就配置一个满足需求的接口。 整个接口配置、测试、冒烟、发布。一站式都通过 Dataway 提供的 UI 界面完成。UI 会以 Jar 包方式提供并集成到应用中并和应用共享同一个 http 端口,应用无需单独为 Dataway 开辟新的管理端口。

    这种内嵌集成方式模式的优点是,可以使得大部分老项目都可以在无侵入的情况下直接应用 Dataway。进而改进老项目的迭代效率,大大减少企业项目研发成本。

    Dataway 工具化的提供 DataQL 配置能力。这种研发模式的变革使得,相当多的需求开发场景只需要配置即可完成交付。 从而避免了从数据存取到前端接口之间的一系列开发任务,例如:Mapper、BO、VO、DO、DAO、Service、Controller 统统不在需要。

    Dataway 是 Hasor 生态中的一员,因此在 Spring 中使用 Dataway 首先要做的就是打通两个生态。根据官方文档中推荐的方式我们将 Hasor 和 Spring Boot 整合起来。这里是原文:https://www.hasor.net/web/extends/spring/for_boot.html

    不设置请求头默认的请求 contentType
    默认值: “application/x-www-form-urlencoded”。服务器是接收不到参数的
    并且需要使用JSON.stringify转化成String类型

    // sql模式(mysql)
    -- a new Query.
    select a.* from work as a  inner join user_work as b on a.work_id=b.work_id  where user_id=#{user_id}
    //datasql
    var dataSet = @@sql(work_id) 
    <%
        select * from work   where work_id=#{work_id};
    %>
    return dataSet(${work_id});
    
    //前段请求参数
     let user_id={user_id:1}
        $.ajax({
    		type : "post",
    		contentType:"application/json; charset=UTF-8",
    		dataType: 'json',
    		url : "../../interface/sel_work",
    		data :JSON.stringify(user_id) ,
    		success : function(data) {					 						
    	}});
    
    展开全文
  • 注意视图中列的顺序与select语句中显示列的数据是一一对应的,视图里放的不是数据,是sql查询语句(一)创建视图使用场景:在频繁使用某一sql语句时如何使用视图:直接在from子句中使用视图名称代替表名称优点:提高...

    一、视图

    创建视图步骤:

    create view 视图名称(<视图列名1>,<视图列名2>)

    as <select 查询语句>

    注意视图中列的顺序与select语句中显示列的数据是一一对应的,视图里放的不是数据,是sql查询语句

    (一)创建视图

    使用场景:在频繁使用某一sql语句时

    如何使用视图:直接在from子句中使用视图名称代替表名称

    优点:提高效率;视图数据会随着原表的变化更新;不需要保存数据,节省空间

    使用注意事项:

    1、避免在使用视图的基础上再使用视图

    2、不能往视图里插入数据

    练习题1:

    创建视图:

    c9d864cf5a1e5e65ca30b202fb857dbd.png

    直接使用视图

    a1bd00ec8539c6a7a6ac2e846f2f4ab5.png

    二、子查询

    (一)什么是子查询:子查询可以作为一个临时表,在语句运行结束后临时表消失;一次性的视图,在from子句中直接写定义视图的sql查询语句,当sql里有子查询时会先运行子查询

    例如:

    select性别,人数

    from(

    select 性别,count(*) as 人数

    from student

    group by 性别

    ) as 按性别汇总;

    from子句中为sql查询语句,“as 按性别汇总”就是子查询的名称

    随堂练习:

    3afa061d0e3c088faa9af0cd91c96f5d.png

    (二)如何使用子查询

    子查询也可以用在where子句里面,与in /any/all一起使用,在in /any/all后面放入子查询

    随堂练习:

    在练习中注意如果直接筛选课程和成绩,显示的学号为随机生成,因为在实际运行过程中,第二步算最低成绩时,只有课程号与成绩两列数据保留。

    eb323c18456edf43fa65bd17e768c7fe.png

    any、all的使用:

    1、必须与比较运算符(大小)一起使用

    2、any 与some使用方法相同

    示例:

    select 列名1

    from 表名1

    where列名1>any(子查询);

    表示:查询结果只需大于子查询中的任意一个数字即可。

    随堂练习:

    any子查询:

    6e67c61941195d96460a97747e368cfc.png

    all子查询:

    62fa8b38cd621c281c1b0f8af9e74a22.png

    (三)使用子查询的场景:偶尔使用某个查询语句用子查询,频繁查询使用视图

    (四)使用子查询注意的事项:

    (1)all后面跟的是集合范围不是单独的数字,因此不能表示成a>3*all(b),而是a/3>all(b)

    (2)避免使用多层嵌套子查询

    (3)select from 子查询 as 子查询名称,其中“as 子查询名称”可以省略

    (五)sql运行顺序:子查询——from、where、group by、having——select 子句——order by、limit

    三、标量子查询

    注意:在where子句中不能使用汇总函数

    标量子查询:只能返回一行一列的查询结果,因为标量子查询结果是一个单一的值,因此可以与比较运算符一起使用。

    随堂练习:

    e54f03315ce595ed31cdefdf8ed20835.png

    任何使用单一值都可以用标量子查询。

    因为标量子查询返回的是单一的值,所以可以和比较运算符、in、any、all、between使用,一定要注意标量子查询不会返回多行结果

    四、关联子查询

    随堂练习:

    b62c8f0e192a9074776fcd3cfaef33c5.png

    select 学号,课程号,成绩

    from score as S1

    where 成绩> ( select avg(成绩) from score as S2

    where S1.课程号= S2.课程号

    group by 课程号);

    (一)注意:关联条件一定要写在子查询里面,S2仅在子查询里有效。所以S2能在子查询里看到S1,S1在子查询外看不到S2。

    (二)使用子查询的场景:在每个组里进行比较,关联子查询起作用是因为有一个关联条件。

    五、如何使用sql解决业务问题

    随堂练习:

    3261c3149f7aec6341b1f67a1a569b4b.png

    如何查找错误:可以注意检查问题,例如先检验子查询的问题再查询是否为子查询外面的问题。

    六、各种函数

    函数的3个功能:具备功能、有输入端(参数)、有输出端(返回值)

    常用的函数:

    汇总——count/sum/avg/max/min(求和与平均值只能对数值类型计算)

    算术函数

    字符串函数

    日期函数

    七、课后练习

    3c903fca1a59f5683b92d4acbb4e4005.png

    c69f97c0792ba9aeed53cfbed8e9d49b.png

    79436c9b87c1cdde2c24073a6d51d704.png

    299ca6c5dd7954189ce79cc9d0ce8398.png

    51efcf786a6b351f318ad92692fca9b9.png

    此题求出结果与网站结果不一,练习结果按照四舍五入后数据显示,网站显示比例不按四舍五入取值。

    3a2337c5e59cd2c926cd05ded4a24628.png

    b5e21547e1b940199b4724a286a88772.png

    0747c4313776c0cc4e1276ea110a373c.png

    911aa8f6dac4c9ec4a59c3c98e3a5f98.png

    4085d8b98d581fade910ce8ae9acfd3a.png

    本题经检查,应注意 x中的名称不等于y中的名称。

    修改为:

    select name,continent from world as x where population/3 >=

    all(select population from world as y

    where x.continent=y.continent and x.name <> y.name

    group by continent);

    本节学习心得:复杂查询相比较与简单查询困难很多,想快速掌握感觉是不太可能的,还是要多做题多理解,读懂题目意思,用老师的方法一步步分解题目逐步解决。

    展开全文
  • 说来惭愧,工作差不多4年了,直到前些日子被DBA找上门让我优化一个CPU占用很高的复杂SQL语句时,我才突然意识到了参数查询的重要性。 相信有很多开发者和我一样对于参数查询认识比较模糊,没有引起足够的重视 ...

    sql参数化参数化

    说来惭愧,工作差不多4年了,直到前些日子被DBA找上门让我优化一个CPU占用很高的复杂SQL语句时,我才突然意识到了参数化查询的重要性。

    相信有很多开发者和我一样对于参数化查询认识比较模糊,没有引起足够的重视

    错误认识1.不需要防止sql注入的地方无需参数化
      参数化查询就是为了防止SQL注入用的,其它还有什么用途不知道、也不关心,原则上是能不用参数就不用参数,为啥?多麻烦,我只是做公司内部系统不用担心SQL注入风险,使用参数化查询不是给自己找麻烦,简简单单拼SQL,万事OK

    错误认识2.参数化查询时是否指定参数类型、参数长度没什么区别
      以前也一直都觉的加与不加参数长度 应该没有什么区别,仅是写法上的不同而已,而且觉得加参数类型和长度写法太麻烦,最近才明白其实两者不一样的,为了提高sql执行速度,请为 SqlParameter参数加上SqlDbType和size属性,在参数化查询代码编写过程中很多开发者忽略了指定查询参数的类型,这将导致托管代码 在执行过程中不能自动识别参数类型,进而对该字段内容进行全表扫描以确定参数类型并进行转换,消耗了不必要的查询性能所致。根据MSDN解释:如果未在 size参数中显式设置Size,则从dbType参数的值推断出该大小。如果你认为上面的推断出该大小是指从SqlDbType类型推断,那你就错了, 它实际上是从你传过来的参数的值来推断的,比如传递过来的值是"username",则size值为8,"username1",则size值为9。那 么,不同的size值会引发什么样的结果呢?且经测试发现,size的值不同时,会导致数据库的执行计划不会重用,这样就会每次执行sql的时候重新生成 新的执行计划,而浪费数据库执行时间。

    下面来看具体测试

    首先清空查询计划

    DBCC FREEPROCCACHE

    传值username,不指定参数长度,生成查询计划

    复制代码
    复制代码
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserName=@UserName";
        //传值 username,不指定参数长度
        //查询计划为(@UserName varchar(8))select * from Users where UserName=@UserName
        comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = "username" });
        comm.ExecuteNonQuery();
    }
    复制代码
    复制代码

     

    传值username1,不指定参数长度,生成查询计划

    复制代码
    复制代码
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserName=@UserName";
        //传值 username1,不指定参数长度
        //查询计划为(@UserName varchar(9))select * from Users where UserName=@UserName
        comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = "username1" });
        comm.ExecuteNonQuery();
    }
    复制代码
    复制代码

    传值username,指定参数长度为50,生成查询计划

    复制代码
    复制代码
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserName=@UserName";
        //传值 username,指定参数长度为50
        //查询计划为(@UserName varchar(50))select * from Users where UserName=@UserName
        comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,50) { Value = "username" });
        comm.ExecuteNonQuery();
    }
    复制代码
    复制代码

     

    传值username1,指定参数长度为50,生成查询计划

    复制代码
    复制代码
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserName=@UserName";
        //传值 username1,指定参数长度为50
        //查询计划为(@UserName varchar(50))select * from Users where UserName=@UserName
        comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,50) { Value = "username1" });
        comm.ExecuteNonQuery();
    }
    复制代码
    复制代码

     

    使用下面语句查看执行的查询计划

    SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects 
    WHERE sql LIKE '%Users%'  and sql not like '%syscacheobjects%'

    结果如下图所示

     可以看到指定了参数长度的查询可以复用查询计划,而不指定参数长度的查询会根据具体传值而改变查询计划,从而造成性能的损失。

    这里的指定参数长度仅指可变长数据类型,主要指varchar,nvarchar,char,nchar等,对于 int,bigint,decimal,datetime等定长的值类型来说,无需指定(即便指定了也没有用),详见下面测试,UserID为int类 型,无论长度指定为2、20、-1查询计划都完全一样为(@UserIDint)select*from Users where UserID=@UserID

    复制代码
    复制代码
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserID=@UserID";
        //传值 2,参数长度2
        //执行计划(@UserID int)select * from Users where UserID=@UserID
        comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 2) { Value = 2 });
        comm.ExecuteNonQuery();
    }
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserID=@UserID";
        //传值 2,参数长度20
        //执行计划(@UserID int)select * from Users where UserID=@UserID
        comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 20) { Value = 2 });
        comm.ExecuteNonQuery();
    }
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserID=@UserID";
        //传值 2,参数长度-1
        //执行计划(@UserID int)select * from Users where UserID=@UserID
        comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, -1) { Value = 2 });
        comm.ExecuteNonQuery();
    }
    复制代码
    复制代码

    这里提一下,若要传值varchar(max)或nvarchar(max)类型怎么传,其实只要设定长度为-1即可

    复制代码
    复制代码
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandText = "select * from Users where UserName=@UserName";
        //类型为varchar(max)时,指定参数长度为-1
        //查询计划为 (@UserName varchar(max) )select * from Users where UserName=@UserName
        comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,-1) { Value = "username1" });
        comm.ExecuteNonQuery();
    }
    复制代码
    复制代码

    当然了若是不使用参数化查询,直接拼接SQL,那样就更没有查询计划复用一说了,除非你每次拼的SQL都完全一样

    总结,参数化查询意义及注意点

    1.可以防止SQL注入

    2.可以提高查询性能(主要是可以复用查询计划),这点在数据量较大时尤为重要

    3.参数化查询参数类型为可变长度时(varchar,nvarchar,char等)请指定参数类型及长度,若为值类型(int,bigint,decimal,datetime等)则仅指定参数类型即可

    4.传值为varchar(max)或者nvarchar(max)时,参数长度指定为-1即可

    5.看到有些童鞋对于存储过程是否要指定参数长度有些疑惑,这里补充下,若调用的是存储过程时,参数无需指定长度,如果指定了也会忽略,以存储过程 中定义的长度为准,不会因为没有指定参数长度而导致重新编译,不过还是建议大家即便时调用存储过程时也加上长度,保持良好的变成习惯

    转载于:https://www.cnblogs.com/jinanxiaolaohu/p/8084373.html

    展开全文
  • 地下水水位自动监测系统可对地下水的水位、水温、水质等参数进行长期监测并自动存储监测数据,可对地下水的变化规律进行动态分析。 地下水位自动监测系统依托既有的GPRS无线网络进行建设,具有投资成本低、建设速度...
  • 存储过程优点

    2016-04-09 20:42:38
    第二:存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。 第三:存储过程运行比较稳定,不会有太多的错误。只要一次成功,以后都会按这个程序运行。 第四:存储过程主要...
  • MySQL复制的优点: 主库故障,可以快速切换至从库提供服务; 在从库执行查询操作,降低主库的访问压力; 在从库执行备份,避免备份期间对主库影响; MySQL复制原理 1、MySQL主库在事务提交时会把数据变更作为...
  • sklearn文档查询

    2018-08-17 21:38:15
    以CountVectorizer类为例,总结一下怎么查询sklearn文档 Google ...优点参数有着详细的说明 缺点:没有详细的用例,不知道该如何使用 查看用例代码 结合API,理解各个函数用法 ...
  • 参数绑定优点: (1)安全性  防止用户恶意输入条件和恶意调用存储过程 (2)提高性能  底层采用JDBC的PreparedStatement预定义sql功能,后期查询直接从缓存中获取执行 一,参数绑定两种方式 ...
  • 标准查询解析器的主要优点是,它支持一种健壮且相当直观的语法,允许您创建各种结构化查询。最大的缺点是,与设计为尽可能少地抛出错误的DisMax的查询解析器相比,它非常不容忍语法错误。 标准查询分析器参数 除了...
  • 相对于Statement,PreparedStatement的优点是什么? a、PreparedStatement有助于防止SQL注入,因为它会自动对特殊字符转义。 b、PreparedStatement可以用来进行动态查询。 c、PreparedStatement执行更快。尤其当你...
  • Java提供了 Statement、PreparedStatement 和 CallableStatement三种方式来执行查询语句,其中 Statement 用于通用查询, PreparedStatement 用于执行参数查询,而 CallableStatement则是用于存储过程。...
  • 背景&痛点通过ES进行查询,如果需要新增查询条件,则每次都需要进行...解决方案思路一:配置参数通过配置参数的方式来配置参数映射、查询方式等,代码读取配置文件,根据配置文件构建查询语句。优点:可配置化,...
  • 背景&痛点 通过ES进行查询,如果需要新增查询条件,则每次都需要进行硬编码,然后实现对应的查询功能。...通过配置参数的方式来配置参数映射、查询方式等,代码读取配置文件,根据配置文件构建查询语句。 优点...
  • 自动撤销管理允许DBA指定撤销信息在提交之后需要保留的时间,以防止 在长时间的查询过程中出现“snapshot too old”的错误。可以通过设置UNDO_RETENTION参数来实现这一过程。这一参数的缺省值为900秒(即15分钟),但...
  • 点播平台的优点

    2015-06-17 11:39:54
    带的查询参数进行缓存。当token过期时候,请求token变化后,请求cdn的时候,cdn会去源站验证一下(返回状态码304),如果可用,不会去源站访问资源,而是去命中cdn的缓存。(对以后的拖拽有显著的帮助) 注意:对于...
  • ibatis优点与缺点

    千次阅读 2011-09-27 13:21:39
     MyBatis是支持普通SQL查询,存储过程和高级映射的优秀持久层框架。MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及结果集的检索。MyBatis使用简单的XML或注解用于配置和原始映射,将接口和Java的
  • PreparedStatement有助于防止SQL注入,因为它会自动对特殊字符转义。 PreparedStatement可以用来进行动态查询。 PreparedStatement执行更快。... 如果参数太多了,字符串拼接看起来会不美观并且容易出错。 ...
  • man exports 查询权限 参数名称 参数用途 rw read-write,表示可读写权限 ro read-only,只读 sync 请求或写入数据时,数据同步写入到NFS Server的硬盘后才返回。优点:数据安全不会丢;缺点:性能比不...
  • 目录实现动态代理优点规范mybatis.xml加载映射文件接口:UsersMapper.classmapper文件:UsersMapper.xml测试类:MapperTest根据map查询接口mapper测试类传递多参数接口mapper测试类接口mapper测试类 实现动态代理 ...
  •  存储过程通过明确地将查询的可变部分转换为参数来增进执行计划的可重用性。这使执行计划在查询以可变部分的相同或不同值重新提交时可以被重用。存储过程包含一组复杂的SQL语句,使生成存储过程的执行计划的代价...
  • JPA基础查询--开篇

    2017-01-18 14:43:22
    前言正式开始系统地学习一下Hibernate/JPA框架,持续...参数绑定法的优点防止SQL注入攻击: 通常,我们在编写一个条件查询时,常用的代码设计如下:String searchString = "itemName"; (某个String类型的变量) Query
  • 在上一篇文章中,我们学习了如何在Grails应用程序中使用Hibernate本机SQL查询。 我们还可以使用Groovy SQL执行自定义SQL 。 我们必须在代码中创建groovy.sql.Sql的新实例以执行SQL代码。 最简单的方法是将javax.sql....
  • 文章目录一、慢查询1、什么是慢查询2、如何查看慢查询时间3、开启慢查询二、索引基本知识1、索引的优点2、索引的用处3、索引的分类4、面试技术名词5、索引采用的数据结构6、索引匹配方式三、哈希索引1、概念2、哈希...
  • 在进行查询时,索引列不能是表达式的一部分,也不能是函数的参数,否则无法使用索引。 例如下面的查询不能使用 actor_id 列的索引: SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5; 2、多列索引 ...
  • Hive 企业级 参数调优专篇 .

    千次阅读 多人点赞 2020-05-18 13:53:00
    目录:Hive 企业级调优点整理1、Fetch 抓取2、本地模式1)开启本地模式,并执行查询语句2)关闭本地模式,并执行查询语句3、表的优化4、MapJoin5、 Group By数据倾斜文章:6、 Count(Distinct) 去重统计7、笛卡尔积8、...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 644
精华内容 257
关键字:

参数查询优点