mysql实战训练_MySQL实战练习

在测试Django的数据库同步时，经常会出现需要Drop掉MySql数据库表情况。单独开发了一个MySQL删除脚本。

其中涉及到动态语法与MySQL的数据字典概念。

CREATE DEFINER = 'Product'@'localhost' PROCEDURE `ClearTables`(

IN Confirm BOOLEAN

)

DETERMINISTIC

MODIFIES SQL DATA

SQL SECURITY DEFINER

COMMENT ''

BEGIN

declare done int;

declare vcTableName varchar(64);

declare dropSyntax varchar(200);

declare vcForeKey varchar(64);

DECLARE fk_cursor CURSOR FOR

SELECT table_name, CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE K

where referenced_table_name is not null and table_schema = ;

DECLARE tb_cursor CURSOR FOR

SELECT

`T`.`table_name`

FROM

`information_schema`.`TABLES` `T`

WHERE

`table_schema` = ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

exitProc:Begin

if NOT Confirm then

leave exitProc;

end if;

set done = 0;

open fk_cursor;

fk_loop:loop

FETCH fk_cursor into vcTableName, vcForeKey;

if done = 1 then

leave fk_loop;

end if;

set @dropSyntax = CONCAT("alter table ", vcTableName, " DROP FOREIGN KEY ", vcForeKey);

PREPARE stmt_fk FROM @dropSyntax;

EXECUTE stmt_fk ;

DEALLOCATE PREPARE stmt_fk;

/*execute immediate :dropSyntax;*/

end loop fk_loop;

close fk_cursor;

set done = 0;

open tb_cursor;

tb_loop:loop

FETCH tb_cursor into vcTableName;

if done = 1 then

leave tb_loop;

end if;

set @dropSyntax = CONCAT("drop table ", vcTableName);

PREPARE stmt_tb FROM @dropSyntax;

EXECUTE stmt_tb ;

DEALLOCATE PREPARE stmt_tb;

/*execute immediate :dropSyntax;*/

end loop tb_loop;

close tb_cursor;

End;

END;