Mysql 删除所有表,需要考虑外键约束

2019-06-19 20:02栏目:技术创新

下面存储过程,使用游标遍历information_新浦京娱乐场官网,schema库里的tables表来实现:

CREATE PROCEDURE `drop_all_tables`()
BEGIN
    DECLARE _done INT DEFAULT FALSE;
    DECLARE _tableName VARCHAR(255);
    DECLARE _cursor CURSOR FOR
        SELECT table_name 
        FROM information_schema.TABLES
        WHERE table_schema = SCHEMA();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

    SET FOREIGN_KEY_CHECKS = 0;

    OPEN _cursor;

    REPEAT FETCH _cursor INTO _tableName;

    IF NOT _done THEN
        SET @stmt_sql = CONCAT('DROP TABLE ', _tableName);
        PREPARE stmt1 FROM @stmt_sql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
    END IF;

    UNTIL _done END REPEAT;

    CLOSE _cursor;
    SET FOREIGN_KEY_CHECKS = 1;
END

使用这个存储过程可以方便的删除所有数据表

版权声明:本文由新浦京娱乐站_新浦京娱乐场官网_新浦京娱乐发布于技术创新,转载请注明出处:Mysql 删除所有表,需要考虑外键约束