MySQL如何对SQL做prepare预处理(解决IN查询SQL预处理仅能查询出一条记录的问题) | 您所在的位置:网站首页 › plan与prepare › MySQL如何对SQL做prepare预处理(解决IN查询SQL预处理仅能查询出一条记录的问题) |
一、前言/概述
本周同事问了我一个问题,SQL中如何使用占位符?做预处理,为什么他用了prepare却没有生效; 1、prepare简介多次执行一条 SQL 语句时,如果每次都处理该 SQL 语句,生成执行计划,必然会浪费一定的时间。 SQL预处理(Prepare),是一种特殊的 SQL 处理方式;预处理不会直接执行 SQL 语句,而是先将 SQL 语句编译,生成执行计划,然后通过 Execute 命令携带 SQL 参数执行 SQL 语句。 Prepare 的使用十分广泛,绝大多数 ORM 框架都有 API 支持;Prepare 既可以提升 SQL 执行性能,还能防止 SQL 注入引发的安全问题;Prepare 虽然在每个数据库中的语法差异很大,但是一般情况下我们都不会手写 SQL,而是使用 ORM 框架来做; 2、prepare的由来?从mysql服务器执行sql的过程来看,SQL执行过程包括以下阶段 词法分析->语法分析->语义分析->执行计划优化->执行。 词法分析->语法分析这两个阶段我们称之为硬解析。 词法分析识别sql中每个词;语法分析解析SQL语句是否符合sql语法,并得到一棵语法树(Lex)。对于只是参数不同,其他均相同的sql,它们执行时间不同但硬解析的时间是相同的。 而同一SQL随着查询数据的变化,多次查询执行时间可能不同,但硬解析的时间是不变的。所以对于sql执行时间较短,sql硬解析的时间占总执行时间的比率越高。 Prepare的出现就是为了优化硬解析的问题。 虽然Prepare在execute阶段可以节省硬解析的时间。但如果sql只执行一次,且以prepare的方式执行,那么sql执行需两次与服务器交互(Prepare和execute), 而以普通(非prepare)方式,只需要一次交互。这样使用prepare会带来额外的网络开销,得不偿失。 如果同一sql需要执行多次,比如:以prepare方式执行10次,那么只需要一次硬解析。这时候 额外的网络开销就显得微乎其微了;因此prepare更适用于频繁执行的SQL中。 二、prepare做IN查询MySQL 预处理是一组 SQL 操作的集合,它没有固定的语法格式,但多数情况下会按照如下 3 个步骤使用: 使用PREPARE指令预定义 SQL 语句模板;使用SET指令定义 SQL 参数;使用EXECUTE指令携带参数执行 SQL 模板。 1、预处理IN查询时失效了!SQL: prepare myFun from 'select * from user where id IN (?)'; set @str='1,2'; execute myFun using @str;上述SQL会做三件事: 创建预处理SQL(myFun),要使用变量的地方 用?表示;设置变量@str;执行预处理SQL(myFun),USING 后面跟参数,如果存在多个参数(即多个占位符?)使用英文逗号,分隔;SQL执行结果: 使用如下SQL替换: prepare myFun from 'select * from user where FIND_IN_SET(id,?)'; set @str='1,2'; execute myFun using @str;SQL执行结果(符合预期): mybatis支持in的动态sql查询,其入参可以是一个Collection,底层将Collection的内容构建成使用英文逗号,分隔的字符串;也可以是英文逗号,分隔的字符串。 而原生的jdbc中sql不支持IN直接针对单个占位符?传入逗号拼接的字符串,例如in (‘001’, ‘002’),需要开发者根据传入参数的个数来个构建占位符?的个数; 例如传入一个数组或List,{‘001’, ‘002’};需要构造的in为:in(?, ?); 然后再通过PreparedStatement#setString()方法动态构建SQL语句,。 |
CopyRight 2018-2019 实验室设备网 版权所有 |