mysql数据USE pobo_mhall2; DROP TEMPORARY TABLE IF EXISTS tmp_table_rows; CREATE TEMPORARY TABLE tmp_table_rows ( table_name VARCHAR(128), row_count BIGINT ); DROP PROCEDURE IF EXISTS proc_get_all_rows; DELIMITER // CREATE PROCEDURE proc_get_all_rows() BEGIN DECLARE v_tbl VARCHAR(128); DECLARE v_done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA pobo_mhall2 AND TABLE_TYPE BASE TABLE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; OPEN cur; loop_fetch: LOOP FETCH cur INTO v_tbl; IF v_done 1 THEN LEAVE loop_fetch; END IF; SET sql CONCAT(INSERT INTO tmp_table_rows(table_name,row_count) SELECT ,v_tbl,,COUNT(*) FROM ,v_tbl,); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP loop_fetch; CLOSE cur; END // DELIMITER ; CALL proc_get_all_rows(); -- 查看全部结果可复制导出 SELECT * FROM tmp_table_rows ORDER BY row_count DESC;达梦数据库USE POBO_MHALL2; DROP TEMPORARY TABLE IF EXISTS tmp_table_rowcnt; CREATE TEMPORARY TABLE tmp_table_rowcnt( table_name VARCHAR(128), row_count BIGINT ); DROP PROCEDURE IF EXISTS sp_fill_rowcnt; DELIMITER // CREATE PROCEDURE sp_fill_rowcnt() BEGIN DECLARE t_name VARCHAR(128); DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMAPOBO_MHALL2 AND TABLE_TYPEBASE TABLE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1; OPEN cur; READ_LOOP:LOOP FETCH cur INTO t_name; IF done 1 THEN LEAVE READ_LOOP; END IF; SET sql CONCAT(INSERT INTO tmp_table_rowcnt(table_name,row_count) SELECT ,t_name,,COUNT(*) FROM ,t_name,); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP READ_LOOP; CLOSE cur; END // DELIMITER ; CALL sp_fill_rowcnt(); -- 查询所有表按行数倒序可直接导出excel SELECT * FROM tmp_table_rowcnt ORDER BY row_count DESC;人大金仓DROP TABLE IF EXISTS tmp_pobo_rowcnt; CREATE TEMP TABLE tmp_pobo_rowcnt ( table_name text, row_cnt bigint ); DO $$ DECLARE rec record; v_cnt bigint; BEGIN FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema pobo_mhall2 AND table_type BASE TABLE ORDER BY table_name LOOP EXECUTE format(SELECT COUNT(*) FROM %I.%I,pobo_mhall2,rec.table_name) INTO v_cnt; INSERT INTO tmp_pobo_rowcnt(table_name,row_cnt) VALUES(rec.table_name,v_cnt); END LOOP; END $$; -- 和上面代码同一个窗口不要新开标签页 SELECT * FROM tmp_pobo_rowcnt ORDER BY row_cnt DESC;海量数据DROP TABLE IF EXISTS tmp_pobo_rowcnt; CREATE TEMP TABLE tmp_pobo_rowcnt ( table_name text, row_cnt bigint ); DO $$ DECLARE rec record; v_cnt bigint; BEGIN FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema pobo_mhall2 AND table_type BASE TABLE ORDER BY table_name LOOP EXECUTE format(SELECT COUNT(*) FROM %I.%I,pobo_mhall2,rec.table_name) INTO v_cnt; INSERT INTO tmp_pobo_rowcnt(table_name,row_cnt) VALUES(rec.table_name,v_cnt); END LOOP; END $$; -- 和上面脚本一起执行不要单独跑 SELECT * FROM tmp_pobo_rowcnt ORDER BY row_cnt DESC;
mysql数据USE pobo_mhall2; DROP TEMPORARY TABLE IF EXISTS tmp_table_rows; CREATE TEMPORARY TABLE tmp_table_rows ( table_name VARCHAR(128), row_count BIGINT ); DROP PROCEDURE IF EXISTS proc_get_all_rows; DELIMITER // CREATE PROCEDURE proc_get_all_rows() BEGIN DECLARE v_tbl VARCHAR(128); DECLARE v_done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA pobo_mhall2 AND TABLE_TYPE BASE TABLE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done 1; OPEN cur; loop_fetch: LOOP FETCH cur INTO v_tbl; IF v_done 1 THEN LEAVE loop_fetch; END IF; SET sql CONCAT(INSERT INTO tmp_table_rows(table_name,row_count) SELECT ,v_tbl,,COUNT(*) FROM ,v_tbl,); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP loop_fetch; CLOSE cur; END // DELIMITER ; CALL proc_get_all_rows(); -- 查看全部结果可复制导出 SELECT * FROM tmp_table_rows ORDER BY row_count DESC;达梦数据库USE POBO_MHALL2; DROP TEMPORARY TABLE IF EXISTS tmp_table_rowcnt; CREATE TEMPORARY TABLE tmp_table_rowcnt( table_name VARCHAR(128), row_count BIGINT ); DROP PROCEDURE IF EXISTS sp_fill_rowcnt; DELIMITER // CREATE PROCEDURE sp_fill_rowcnt() BEGIN DECLARE t_name VARCHAR(128); DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMAPOBO_MHALL2 AND TABLE_TYPEBASE TABLE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done 1; OPEN cur; READ_LOOP:LOOP FETCH cur INTO t_name; IF done 1 THEN LEAVE READ_LOOP; END IF; SET sql CONCAT(INSERT INTO tmp_table_rowcnt(table_name,row_count) SELECT ,t_name,,COUNT(*) FROM ,t_name,); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP READ_LOOP; CLOSE cur; END // DELIMITER ; CALL sp_fill_rowcnt(); -- 查询所有表按行数倒序可直接导出excel SELECT * FROM tmp_table_rowcnt ORDER BY row_count DESC;人大金仓DROP TABLE IF EXISTS tmp_pobo_rowcnt; CREATE TEMP TABLE tmp_pobo_rowcnt ( table_name text, row_cnt bigint ); DO $$ DECLARE rec record; v_cnt bigint; BEGIN FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema pobo_mhall2 AND table_type BASE TABLE ORDER BY table_name LOOP EXECUTE format(SELECT COUNT(*) FROM %I.%I,pobo_mhall2,rec.table_name) INTO v_cnt; INSERT INTO tmp_pobo_rowcnt(table_name,row_cnt) VALUES(rec.table_name,v_cnt); END LOOP; END $$; -- 和上面代码同一个窗口不要新开标签页 SELECT * FROM tmp_pobo_rowcnt ORDER BY row_cnt DESC;海量数据DROP TABLE IF EXISTS tmp_pobo_rowcnt; CREATE TEMP TABLE tmp_pobo_rowcnt ( table_name text, row_cnt bigint ); DO $$ DECLARE rec record; v_cnt bigint; BEGIN FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema pobo_mhall2 AND table_type BASE TABLE ORDER BY table_name LOOP EXECUTE format(SELECT COUNT(*) FROM %I.%I,pobo_mhall2,rec.table_name) INTO v_cnt; INSERT INTO tmp_pobo_rowcnt(table_name,row_cnt) VALUES(rec.table_name,v_cnt); END LOOP; END $$; -- 和上面脚本一起执行不要单独跑 SELECT * FROM tmp_pobo_rowcnt ORDER BY row_cnt DESC;
从空模型到随机斜率的完整实操指南)
