MYSQL 儲存 while

標籤：proc   create   表資料   div   朋友   count   行儲存   format   begin   

---恢複內容開始---

群裡一朋友，有一需求就是擷取資料庫所有表的總計(條數)
思路：動態傳入表名， count(1)

-- 1.執行這句。擷取所有表名Create table temp_tb (select t.table_name,@rownum:=@rownum+1  as numfrom information_schema.tables t,(select @rownum:=0 ) bwhere t.table_schema=‘test‘ and table_name not in (‘temp‘,‘temp_tb‘));-- 2.同理擷取表結構，把要統計的結果跟對應的表名放在這個表裡面Create table temp (select t.table_name,@rownum:=@rownum+1  as numfrom information_schema.tables t,(select @rownum:=0 ) bwhere t.table_schema=‘test‘ and table_name not in (‘temp‘,‘temp_tb‘));-- 3.刪除表資料保留表結構delete from temp;-- 4.建立儲存create PROCEDURE WhileLoopProc()BEGINselect @num :=1,@len :=count(1) from temp_tb;while @num<@len doselect @name :=table_name from temp_tb where num =@num;select @rownum := concat(‘select count(1)‘,‘as ‘,@name ,‘ into  @temp from ‘, @name);   insert into temp(table_name,num) values(@name,@temp); -- 把執行出來的結果儲存到結果表中set @num:=@num+1;prepare stmt from @rownum;   EXECUTE stmt;DEALLOCATE PREPARE stmt ; end while;end;-- 5.執行儲存 call WhileLoopProc;-- 6.查詢結果select * from temp;

 

完事！

MYSQL 儲存 while