分析一个动态创建表的mysql存储过程

一直以来不用存储过程主要的原因是存储过程的可移植性问题。我在公司经历过的所有项目,到最后几乎都有了数据库移植的需求。客户嫌软件产品太贵,则打包一 个基于免费的mysql的版本给他;客户有钱,对可靠性要求很高,则打包个基于oracle的版本给他。就我工作中接触到的这两种数据库而言,她们的存储 过程语法是大体相似,但不尽相同的。所以,如果将业务逻辑放到存储过程中,就意味着移植需要额外的工作,维护时需要同时维护两套存储过程代码。这样子代价 比较高,所以之前的做法是宁可将业务逻辑在代码中维护,并以一致的标准sql来操作数据库。

      但是,正所谓凡事存在既有她的道理。抛开可移植性问题(移植问题在一些场合里面可能并不是最重要的),将需要多条sql才能完成的操作,放到存储过程中,编译存储在数据库中,执行起来往往是比代码方式高效的。

     明白存储过程的使用场景后,一起来分析mysql5开始引入的存储过程。根据外界传递进来的表名来判断某个表是否存在,不存在的话则动态创建表,这在在mysql下,使用按时间分表的方式来处理大数据量的问题,还是相当有用的。学习下,以备不时之需额。(via 毕竟红尘)

    /*

     首行执行DELIMITER 语句,该语句是mysql发明的,应该属于专用的语句。该语句故名思议,是
改变语句的结束符号。这么说可能听得有点模糊。可以想想我们在mysql客户端上执行sql语句的情景:
我们在终端上敲入长长的一条语句时,按回车健换行,mysql并不马上执行语句,而一般都是在我们敲入
分号(;)的时候,才开始执行语句。所以这个DELIMITER所指定的符号就是告诉mysql,在什么时候语句已经结束了,可以执行该语句了。

       那为什么在存储过程这边要改变这个DELIMITER所指定的符号呢? 因为,我们平常在写程序时候,一般语句也是使用分号来结束的,这是种很自然的姿态。为了把这种自然的姿态保持下来,我们在存储过程的开始将结束暂时修改为 $$(当然其它的符号也是可以的),在程序中用完 ; 号,看看与首句呼应的尾句:

    DELIMITER ;

将 结束符 改回来。

    说到这儿应该就知道,下面程序中使用 $$ 号时会发生什么了吧?

    */

     DELIMITER $$     

        /**

            将sql_mode清空是什么原因?
          下面的语句在mysql5.1版本上是必须要做的。否则存储过程就创建不了。
      顾名思义,如果是sql_mode之下,当然就搞不了存储过程。清掉之后即可ok。

         **/

      SET sql_mode = '' $$

      DROP PROCEDURE IF EXISTS ensureTable $$

      CREATE PROCEDURE ensureTable(i_table_name)

      BEGIN

          /**

     根据用户的输入参数,动态地拼装一条建表的sql。

    @指定的变量是用户变量,也就是在可以在同一个session里面几个共享的变量,比如你设定一个
   @name变量,那么几个存储过程中都可以引用到这个变量。相对存储过程内部申明的变量是只能在存储过程内部使用的了。
    那这里为什么要用带@的用户变量而不用内部变量呢?
这得看看下面的PREPARE语句了。

        **/

         SET @csql=concat("create table if not exists ",i_table_name , " (name varchat(20))");

            

             /**

                  prepare语句是 mysql5引入的新功能,动态执行一条语句。测试中发现,它所执行的变量只能是用户变量(带@的)。下一篇转一个详细描述prepare语句的文章。要深究可自便额。

           **/

            PREPARE create_stmt from @csql;

             EXECUTE create_stmt;

             END $$

   DELIMITER ;

添加新评论