求助:SQL删除空表如何批量处理

凡尘婉星

来自: 凡尘婉星 2018-02-01 23:06:10

×
加入小组后即可参加投票
  • [已注销]

    [已注销] 2018-02-01 23:29:30

    写代码,show tables取所有表,然后一个个select count(*) from xx,为0的就drop table xx.

  • redhulk

    redhulk 2018-02-02 02:19:38

    stackoverflow 上面的查到的,不过 根据 information_schema 表取到的数量不准确。可以用第二种改过的 ,不过这种需求感觉最好还是用 python之类的脚本做。 (不保证脚本的正确性)

    SET FOREIGN_KEY_CHECKS = 0;
    SET @tables = NULL;
    SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
    FROM information_schema.tables
    WHERE table_schema = 'database_name' and table_rows = 0;;.

    SET @tables = CONCAT('DROP TABLE ', @tables);
    PREPARE stmt FROM @tables;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET FOREIGN_KEY_CHECKS = 1;

    第二种:
    DELIMITER |
    DROP PROCEDURE IF EXISTS `drop_empty_table` |
    CREATE PROCEDURE `drop_empty_table`(IN db_name nvarchar(255))
    BEGIN
    DECLARE _next TEXT DEFAULT NULL;
    DECLARE _nextlen INT DEFAULT NULL;
    DECLARE _value TEXT DEFAULT NULL;
    DECLARE _list TEXT DEFAULT NULL;

    SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO _list
    FROM information_schema.tables
    WHERE table_schema = db_name;
    iterator:
    LOOP

    IF LENGTH(TRIM(_list)) = 0 OR _list IS NULL THEN
    LEAVE iterator;
    END IF;

    SET _next = SUBSTRING_INDEX(_list,',',1);
    SET _nextlen = LENGTH(_next);
    SET _value = TRIM(_next);
    set @_stmt = CONCAT('SELECT count(*) INTO @row_cnt', ' FROM ', _value);
    PREPARE stmt1 FROM @_stmt;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    IF @row_cnt = 0 THEN
    SET @del_stmt = CONCAT('DROP TABLE ', _value);
    PREPARE stmt2 FROM @del_stmt;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
    END IF;
    SET _list = INSERT(_list,1,_nextlen + 1,'');
    END LOOP;
    END|
    DELIMITER ;

  • 凡尘婉星

    凡尘婉星 2018-02-02 08:47:47

    写代码,show tables取所有表,然后一个个select count(*) from xx,为0的就drop table xx. 写代码,show tables取所有表,然后一个个select count(*) from xx,为0的就drop table xx. [已注销]

    谢谢谢谢,满满哒都是感动。

    来自 豆瓣App
  • 凡尘婉星

    凡尘婉星 2018-02-02 08:48:15

    stackoverflow 上面的查到的,不过 根据 information_schema 表取到的数量不准确。可以用第二种 stackoverflow 上面的查到的,不过 根据 information_schema 表取到的数量不准确。可以用第二种改过的 ,不过这种需求感觉最好还是用 python之类的脚本做。 (不保证脚本的正确性) SET FOREIGN_KEY_CHECKS = 0; SET @tables = NULL; SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables FROM information_schema.tables WHERE table_schema = 'database_name' and table_rows = 0;;. SET @tables = CONCAT('DROP TABLE ', @tables); PREPARE stmt FROM @tables; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS = 1; 第二种: DELIMITER | DROP PROCEDURE IF EXISTS `drop_empty_table` | CREATE PROCEDURE `drop_empty_table`(IN db_name nvarchar(255)) BEGIN DECLARE _next TEXT DEFAULT NULL; DECLARE _nextlen INT DEFAULT NULL; DECLARE _value TEXT DEFAULT NULL; DECLARE _list TEXT DEFAULT NULL; SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO _list FROM information_schema.tables WHERE table_schema = db_name; iterator: LOOP IF LENGTH(TRIM(_list)) = 0 OR _list IS NULL THEN LEAVE iterator; END IF; SET _next = SUBSTRING_INDEX(_list,',',1); SET _nextlen = LENGTH(_next); SET _value = TRIM(_next); set @_stmt = CONCAT('SELECT count(*) INTO @row_cnt', ' FROM ', _value); PREPARE stmt1 FROM @_stmt; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; IF @row_cnt = 0 THEN SET @del_stmt = CONCAT('DROP TABLE ', _value); PREPARE stmt2 FROM @del_stmt; EXECUTE stmt2; DEALLOCATE PREPARE stmt2; END IF; SET _list = INSERT(_list,1,_nextlen + 1,''); END LOOP; END| DELIMITER ; ... redhulk

    咦?大神你说的这个地方是哪里啊?

    来自 豆瓣App

你的回应

回应请先 , 或 注册

25123 人聚集在这个小组
↑回顶部