MySQL.html

<!doctype html>
<html class="no-js" lang="en">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>
    
  MySQL - 星晨入梦
  
  </title>
  
  
  <link href="atom.xml" rel="alternate" title="星晨入梦" type="application/atom+xml">
    <link rel="stylesheet" href="asset/css/foundation.min.css" />
    <link rel="stylesheet" href="asset/css/docs.css" />
    <script src="asset/js/vendor/modernizr.js"></script>
    <script src="asset/js/vendor/jquery.js"></script>
  <script src="asset/highlightjs/highlight.pack.js"></script>
  <link href="asset/highlightjs/styles/github.css" media="screen, projection" rel="stylesheet" type="text/css">
  <script>hljs.initHighlightingOnLoad();</script>
<script type="text/javascript">
  function before_search(){
    var searchVal = 'site:blog.cxstars.com ' + document.getElementById('search_input').value;
    document.getElementById('search_q').value = searchVal;
    return true;
  }
</script>
  </head>
  <body class="antialiased hide-extras">
    
    <div class="marketing off-canvas-wrap" data-offcanvas>
      <div class="inner-wrap">


<nav class="top-bar docs-bar hide-for-small" data-topbar>


  <section class="top-bar-section">
  <div class="row">
      <div style="position: relative;width:100%;"><div style="position: absolute; width:100%;">
        <ul id="main-menu" class="left">
        
        <li id=""><a target="_self" href="index.html">Home</a></li>
        
        <li id=""><a target="_self" href="archives.html">Archives</a></li>
        
        <li id=""><a target="_self" href="about.html">About</a></li>
        
        </ul>

        <ul class="right" id="search-wrap">
          <li>
<form target="_blank" onsubmit="return before_search();" action="http://google.com/search" method="get">
    <input type="hidden" id="search_q" name="q" value="" />
    <input tabindex="1" type="search" id="search_input"  placeholder="Search"/>
</form>
</li>
          </ul>
      </div></div>
  </div>
  </section>

</nav>

        <nav class="tab-bar show-for-small">
  <a href="javascript:void(0)" class="left-off-canvas-toggle menu-icon">
    <span> &nbsp; 星晨入梦</span>
  </a>
</nav>

<aside class="left-off-canvas-menu">
      <ul class="off-canvas-list">
        
        <li><a target="_self" href="index.html">Home</a></li>
        
        <li><a target="_self" href="archives.html">Archives</a></li>
        
        <li><a target="_self" href="about.html">About</a></li>
        

    <li><label>Categories</label></li>

        
            <li><a href="ml.html">ml</a></li>
        
            <li><a href="MySQL.html">MySQL</a></li>
        
            <li><a href="python.html">python</a></li>
        
            <li><a href="nginx.html">nginx</a></li>
        
            <li><a href="golang.html">golang</a></li>
        
            <li><a href="book.html">book</a></li>
        
            <li><a href="arch.html">arch</a></li>
        
            <li><a href="redis.html">redis</a></li>
        
            <li><a href="life.html">life</a></li>
        
            <li><a href="php.html">php</a></li>
         

      </ul>
    </aside>

<a class="exit-off-canvas" href="#"></a>


        <section id="main-content" role="main" class="scroll-container">
        
       

 <script type="text/javascript">
	$(function(){
		$('#menu_item_index').addClass('is_active');
	});
</script>
<div class="row">
	<div class="large-8 medium-8 columns">
		<div class="markdown-body home-categories">
		
			<div class="article">
                <a class="clearlink" href="14893113007289.html">
                
                  <h1>PHP PDO深入探索</h1>
                  <div class="a-content">
                      
                      <div class="a-content-text">
                        
                        	<h2 id="toc_0">引子</h2>

<blockquote>
<p>学而时习之.用了这么多年的PDO,就知道预处理和参数绑定,但是对于其具体实现并没有进行深入了解.今天好奇心大发了,习MySQL PDO吧. </p>
</blockquote>

<h2 id="toc_1">官释</h2>

<p>很多更成熟的数据库都支持预处理语句的概念。什么是预处理语句？可以把它看作是想要运行的 SQL 的一种编译过的模板，它可以使用变量参数进行定制。预处理语句可以带来两大好处：</p>

<ul>
<li>查询仅需解析（或预处理）一次，但可以用相同或不同的参数执行多次。当查询准备好后，数据库将分析、编译和优化执行该查询的计划。对于复杂的查询，此过程要花费较长的时间，如果需要以不同参数多次重复相同的查询，那么该过程将大大降低应用程序的速度。通过使用预处理语句，可以避免重复分析/编译/优化周期。简言之，预处理语句占用更少的资源，因而运行得更快。</li>
<li>提供给预处理语句的参数不需要用引号括起来，驱动程序会自动处理。如果应用程序只使用预处理语句，可以确保不会发生SQL 注入。（然而，如果查询的其他部分是由未转义的输入来构建的，则仍存在 SQL 注入的风险）。
预处理语句如此有用，以至于它们唯一的特性是在驱动程序不支持的时PDO 将模拟处理。这样可以确保不管数据库是否具有这样的功能，都可以确保应用程序可以用相同的数据访问模式。</li>
</ul>

<p><em>PS:存储过程会稍有特殊,具体请看:<a href="http://php.net/manual/zh/pdo.prepared-statements.php">http://php.net/manual/zh/pdo.prepared-statements.php</a></em></p>

<h2 id="toc_2">上下而求索</h2>

<p>其实细心的会发现官释有句很特别的话[<strong>以至于它们唯一的特性是在驱动程序不支持的时PDO将模拟处理</strong>].<br/>
驱动程序,模拟处理?<br/>
直接上代码吧.</p>

<p>1.模拟处理</p>

<pre><code class="language-php">&lt;?php
$pdo = new PDO(&quot;mysql:host=127.0.0.1;dbname=words;charset=utf8&quot;,&quot;root&quot;,&quot;password&quot;);
$st = $pdo-&gt;prepare(&quot;SELECT `words`.* FROM `words` WHERE word = ? ORDER BY `id` DESC LIMIT 10 OFFSET 0&quot;);
$word = &#39;charm&#39;;
$st-&gt;bindParam(1,$word);
$st-&gt;execute();
$data = $st-&gt;fetchAll();
</code></pre>

<p>运行后使用<code>wireshark</code>进行抓包查看.一次完成的PHP运行MySQL PDO查询的TCP包就呈现在眼前:<br/>
<img src="http://bimg.cxstars.com/2017-03-12-14893045265092.jpg" alt=""/></p>

<p><mark>简单的回顾一下TCP握手协议</mark></p>

<h4 id="toc_3">TCP三路握手</h4>

<p><img src="http://bimg.cxstars.com/2017-03-12-14893050630847.jpg" alt=""/></p>

<h4 id="toc_4">TCP关闭确认</h4>

<p><img src="http://bimg.cxstars.com/2017-03-12-14893059174539.jpg" alt=""/></p>

<p>回归正题,MySQL的查询体现:<br/>
<img src="http://bimg.cxstars.com/2017-03-12-14893068792357.jpg" alt=""/></p>

<p>变化就是替换并添加上了单引号(转义,同<code>mysql_real_escape_string</code>).其他没有什么变化.</p>

<p>2.驱动处理<br/>
代码正如:</p>

<pre><code class="language-php">&lt;?php
$pdo = new PDO(&quot;mysql:host=127.0.0.1;dbname=words;charset=utf8&quot;,&quot;root&quot;,&quot;password&quot;);
$pdo-&gt;setAttribute(PDO::ATTR_EMULATE_PREPARES, false);//这行是关键
$st = $pdo-&gt;prepare(&quot;SELECT `words`.* FROM `words` WHERE word = ? ORDER BY `id` DESC LIMIT 10 OFFSET 0&quot;);
$word = &#39;charm&#39;;
$st-&gt;bindParam(1,$word);
$st-&gt;execute();
$data = $st-&gt;fetchAll();
</code></pre>

<p><img src="http://bimg.cxstars.com/2017-03-12-14893082386821.jpg" alt=""/></p>

<p>哇噢,这次跟上次大不一样.来看看具体做了什么.<br/>
先是进行了SQL预处理.<br/>
<img src="http://bimg.cxstars.com/2017-03-12-14893083215979.jpg" alt=""/></p>

<p>根据MySQL协议返回的预处理完成信息.<br/>
<img src="http://bimg.cxstars.com/2017-03-12-14893088477923.jpg" alt=""/></p>

<p>进行参数映射查询<br/>
<img src="http://bimg.cxstars.com/2017-03-12-14893089225887.jpg" alt=""/></p>

<p>返回数据<br/>
<img src="http://bimg.cxstars.com/2017-03-12-14893093001183.jpg" alt=""/></p>

<p>关闭预处理<br/>
<img src="http://bimg.cxstars.com/2017-03-12-14893093293735.jpg" alt=""/></p>

<p>跟PHP本地预处理有什么区别呢?<br/>
<em>一次处理,终身(此次连接)受益,MySQL语句预处理,比如词法分析等,想象一下,在插入中使用,并且用二进制协议进行数据传输,更加安全效率</em></p>

<h2 id="toc_5">左右至MySQL求索</h2>

<p>其实也就是转换到MySQL中进行.</p>

<pre><code class="language-mysql">#prepare statement
mysql&gt; PREPARE select1 FROM &#39;SELECT `words`.id FROM `words` WHERE word = ? ORDER BY `id` DESC LIMIT 10 OFFSET 0&#39;;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

#bind variable
mysql&gt; set @w1=&quot;charm&quot;;
Query OK, 0 rows affected (0.00 sec)

#execute
mysql&gt; EXECUTE select1 USING @w1;
+----+
| id |
+----+
|  5 |
+----+
1 row in set (0.00 sec)
</code></pre>

<h4 id="toc_6">wiki的定义</h4>

<blockquote>
<p>As compared to executing SQL statements directly, prepared statements offer two main advantages:[1]</p>
</blockquote>

<ul>
<li>The overhead of compiling and optimizing the statement is incurred only once, although the statement is executed multiple times. Not all optimization can be performed at the time the prepared statement is compiled, for two reasons: the best plan may depend on the specific values of the parameters, and the best plan may change as tables and indexes change over time.[2]</li>
<li>Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.</li>
</ul>

<p>链接<a href="https://en.wikipedia.org/wiki/Prepared_statement">https://en.wikipedia.org/wiki/Prepared_statement</a></p>

                        
                      </div>
                  </div>
                </a>
                <div class="read-more clearfix">
                  <div class="more-left left">
                  
                    <span class="date">2017/3/12 17:35 下午</span>
                    <span>posted in&nbsp;</span> 
          				  
          					    <span class="posted-in"><a href='MySQL.html'>MySQL</a></span>
          				   
                  </div>
                  <div class="more-right right">
                  <span class="comments">
                      

                      
                        &nbsp;<a  class="ds-thread-count" data-thread-key="14893113007289.html" data-count-type="comments" href="14893113007289.html#ds-thread"></a>
                       
                  </span>
                  </div>
                </div>
              </div><!-- article -->
        
              


			<div class="row">
			  <div class="large-6 columns">
			  <p class="text-left" style="padding-top:25px;">
			   
			  </p>
			  </div>
			  <div class="large-6 columns">
			<p class="text-right" style="padding-top:25px;">
			
			</p>
			  </div>
			</div>
		</div>
	</div><!-- large 8 -->

 <div class="large-4 medium-4 columns">
  <div class="hide-for-small">
    <div id="sidebar" class="sidebar">
          <div id="site-info" class="site-info">
            
                <div class="site-a-logo"><img src="asset/img/cx_logo.png" /></div>
            
                <h1>星晨入梦</h1>
                <div class="site-des">life&code</div>
                <div class="social">









<a target="_blank" class="github" target="_blank" href="https://github.com/star1989" title="GitHub">GitHub</a>

  <a target="_blank" class="rss" href="atom.xml" title="RSS">RSS</a>
                
              	 </div>
          	</div>

             

              <div id="site-categories" class="side-item ">
                <div class="side-header">
                  <h2>Categories</h2>
                </div>
                <div class="side-content">

      	<p class="cat-list">
        
            <a href="ml.html"><strong>ml</strong></a>
        
            <a href="MySQL.html"><strong>MySQL</strong></a>
        
            <a href="python.html"><strong>python</strong></a>
        
            <a href="nginx.html"><strong>nginx</strong></a>
        
            <a href="golang.html"><strong>golang</strong></a>
        
            <a href="book.html"><strong>book</strong></a>
        
            <a href="arch.html"><strong>arch</strong></a>
        
            <a href="redis.html"><strong>redis</strong></a>
        
            <a href="life.html"><strong>life</strong></a>
        
            <a href="php.html"><strong>php</strong></a>
         
        </p>


                </div>
              </div>

              <div id="site-categories" class="side-item">
                <div class="side-header">
                  <h2>Recent Posts</h2>
                </div>
                <div class="side-content">
                <ul class="posts-list">
	      
		      
			      <li class="post">
			        <a href="14900171104128.html">TensorFlow安装</a>
			      </li>
		     
		  
		      
			      <li class="post">
			        <a href="14899316488389.html">一篇上了头条的文章感想</a>
			      </li>
		     
		  
		      
			      <li class="post">
			        <a href="14897253436771.html">How to Chose A Restful Api Http Status Code</a>
			      </li>
		     
		  
		      
			      <li class="post">
			        <a href="14894790545658.html">设计模式原则</a>
			      </li>
		     
		  
		      
			      <li class="post">
			        <a href="14893113007289.html">PHP PDO深入探索</a>
			      </li>
		     
		  
		      
		  
		      
		  
		      
		  
		      
		  
		      
		  
		      
		  
		      
		  
		      
		  
		      
		  
		      
		  
		      
		  
		      
		  
		      
		  
		      
		  
		      
		   
		  		</ul>
                </div>
              </div>
        </div><!-- sidebar -->
      </div><!-- hide for small -->
</div><!-- large 4 -->

</div><!-- row -->

 <div class="page-bottom clearfix">
  <div class="row">
   <p class="copyright">Copyright &copy; 2015
Powered by <a target="_blank" href="http://www.mweb.im">MWeb</a>,&nbsp; 
Theme used <a target="_blank" href="http://github.com">GitHub CSS</a>.</p>
  </div>
</div>

        </section>
      </div>
    </div>

  


<script type="text/javascript">
var duoshuoQuery = {short_name:'chenxin1989'};
  (function() {
    var ds = document.createElement('script');
    ds.type = 'text/javascript';ds.async = true;
    ds.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') + '//static.duoshuo.com/embed.js';
    ds.charset = 'UTF-8';
    (document.getElementsByTagName('head')[0] 
     || document.getElementsByTagName('body')[0]).appendChild(ds);
  })();
  </script>
    

    <script src="asset/js/foundation.min.js"></script>
    <script>
      $(document).foundation();
      function fixSidebarHeight(){
        var w1 = $('.markdown-body').height();
          var w2 = $('#sidebar').height();
          if (w1 > w2) { $('#sidebar').height(w1); };
      }
      $(function(){
        fixSidebarHeight();
      })
      $(window).load(function(){
          fixSidebarHeight();
      });
     
    </script>

    <script src="asset/chart/all-min.js"></script><script type="text/javascript">$(function(){    var mwebii=0;    var mwebChartEleId = 'mweb-chart-ele-';    $('pre>code').each(function(){        mwebii++;        var eleiid = mwebChartEleId+mwebii;        if($(this).hasClass('language-sequence')){            var ele = $(this).addClass('nohighlight').parent();            $('<div id="'+eleiid+'"></div>').insertAfter(ele);            ele.hide();            var diagram = Diagram.parse($(this).text());            diagram.drawSVG(eleiid,{theme: 'simple'});        }else if($(this).hasClass('language-flow')){            var ele = $(this).addClass('nohighlight').parent();            $('<div id="'+eleiid+'"></div>').insertAfter(ele);            ele.hide();            var diagram = flowchart.parse($(this).text());            diagram.drawSVG(eleiid);        }    });});</script>
<script type="text/javascript" src="https://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML"></script><script type="text/x-mathjax-config">MathJax.Hub.Config({TeX: { equationNumbers: { autoNumber: "AMS" } }});</script>
<script>
var _hmt = _hmt || [];
(function() {
  var hm = document.createElement("script");
  hm.src = "https://hm.baidu.com/hm.js?b65a52970ddc989d3f85c97f61de0c39";
  var s = document.getElementsByTagName("script")[0]; 
  s.parentNode.insertBefore(hm, s);
})();
</script>

  </body>
</html>