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;