1

  系统集成 SpringMVC 的测试框架,为了在测试结束后,清空测试数据库的所有表单,需要写一段 SQL 脚本实现。这个任务到了我这里,尴尬的是,对 MySql 的理解仅在于 CRUD 的我,要写操作数据库的 SQL ,简直是莫大的难事,作为程序员,不会写不是问题,不会查就是罪过了。
  然而,在查百度的时候,内心真的有一万只草泥马奔腾而过,没想到很多资料都是无用的。先不说一大堆博客都是复制粘贴而来,更糟糕的是,博客里有写代码根本运行不起来的,这些测试 Demo 还有什么意思,结果查了半天都没找到可用的,也要吐槽百度的搜索引擎,基于商业的排序的算法根本没办法和谷歌的搜索引擎相比,神伤。最后为了解决问题,只能自学 MySql 的一些基本语法,再把这个脚本写出来。

  学习所有的 SQL 语法肯定是不现实的,所以,为了实现目标,只能将这个任务切割开来。

  • 删除表 SQL

  • 获取数据库下的所有表 SQL

  • 遍历结果集 SQL

  • 遍历过程中,拼接删除表的 SQL ,并执行该 SQL

SQL:删除表

直接查 MySQL 的 语法:

DROP ‘table’  /*删除数据库下对应名称的表*/

抱着侥幸的心理,希望有可以删除数据库下的所有表的语法,这样就不需要进行下面的步骤了,然并卵,幻想破灭。

SQL:获取数据库下的所有表

select table_name from information_schema.tables ;  /*获取数据库下的所有表单,可添加筛选条件*/

解析 : information_schema表

  information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。

  • information_schema.schemata 表:数据库的信息

  • information_schema.tables 表:数据库中表的信息

  • information_schema.columns 表:表的列的信息

  • information_schema.statistics 表:表的索引信息

  只要想找关于数据库的信息,这张表都可以提供相关的信息。

SQL:遍历结果集

  涉及到遍历,第一时间就想到了 for / while 这样的关键字,MySql当然提供了相关的语法,但是这次的语句就不再是简单的查询语句,而必须通过函数来实现。

MySql 提供了三种方式:WHILE 、 LOOP 、 REPEAT:

以 REPEAT UNTIL 循环为例,MySQL 给出的方法中需要明确三个参数:

[ label: ]  REPEAT   /*label:循环名称,可有可无*/

 statements          /*statements:循环语句内的执行语句*/

 UNTIL expression    /*expression : 循环条件*/

 END  REPEAT  [ label ]  ;

有了前面三点做铺垫,若要实现目标,只要再循环体里拼接出删除表的语句,并执行,也就是以下的重难点。

SQL:拼接语句,执行语句

难点一:循环体内如何遍历结果集

百度关键词:MySql 循环体内如何遍历结果集

百度结果,使用游标,一番查阅后,对游标的理解是:

  • 游标充当了指针的作用

  • 游标可对查询数据库所返回的结果集进行遍历,以便进行相应的操作

  • 尽管游标能遍历结果中的所有行,但他一次只指向一行

难点二:循环的判断条件要怎么写

百度关键词:mysql 判断游标结束

百度结果:使用以下的 SQL:

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

原理不太懂,但是查了一下 02000 的意思:
发生下述异常之一:

  • SELECT INTO 语句或 INSERT 语句的子查询的结果为空表。

  • 在搜索的 UPDATE 或 DELETE 语句内标识的行数为零。

  • 在 FETCH 语句中引用的游标位置处于结果表最后一行之后。

该SQL的语意是:当游标到了结果集最后一行的时候,设置done=1。

难点三:如何执行拼接好的语句

百度关键词:mysql 如何执行拼接的sql

百度结果:使用 prepare ,语法如下:

PREPARE statement_name FROM preparable_SQL_statement; /*定义*/
EXECUTE statement_name [USING @var_name [, @var_name] ...]; /*执行预处理语句*/

使用的方法和JDBC差不多,所以难度也不算太大,接下来,就要开始实现这个功能:
代码:遍历过程中,拼接删除表的 SQL ,并执行该 SQL

  DECLARE done INT DEFAULT 0;  
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
 
  OPEN sur;
  REPEAT  /*循环体:使用游标遍历结果集*/
    FETCH sur INTO tableName; /* 将游标获取值设置到 tableName 变量中 */
    IF NOT done THEN 
        SET cmd=concat('DROP TABLE ',DB_NAME,'.',tableName);  
        SET @E=cmd; 
        PREPARE stmt FROM @E;  
          EXECUTE stmt;        /*执行删除命令*/
         DEALLOCATE PREPARE stmt;   /*清空删除命令*/
    END IF;
  UNTIL done END REPEAT;  /*更新done值,并判断是否跳出循环*/ 
  CLOSE sur;

结合以上四点,最终的SQL脚本是:

DROP  PROCEDURE IF  EXISTS procedure_drop_table;

CREATE PROCEDURE procedure_drop_table()
BEGIN
  DECLARE DB_NAME varchar(50) DEFAULT "testcase";  -- 测试数据库的名称
  DECLARE done INT DEFAULT 0;
  DECLARE tableName varchar(50);  -- 测试库表明
  DECLARE cmd varchar(50);        -- 执行命令
  DECLARE sur CURSOR              -- 游标
  FOR 
  SELECT table_name FROM information_schema.TABLES WHERE table_schema=DB_NAME; 
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
  OPEN sur;
  REPEAT
    FETCH sur INTO tableName;
    IF NOT done THEN 
       set cmd=concat('DROP TABLE ',DB_NAME,'.',tableName);   -- 拼接删除命令 
        SET @E=cmd; 
        PREPARE stmt FROM @E; 
          EXECUTE stmt;  
         DEALLOCATE PREPARE stmt;  
    END IF;
  UNTIL done END REPEAT;
  CLOSE sur;
END

call procedure_drop_table();

Seven_Nee
614 声望53 粉丝