MYSQL Storage While

---restore content starts---

A friend in the group, there is a need to get a total of all tables in the database (number of bars)
Idea: Dynamic incoming table name, COUNT (1)

--1. Implement this sentence. Get all table namesCreate TableTEMP_TB (SelectT.table_name,@rownum:=@rownum+1   asNum fromInformation_schema.tables T, (Select @rownum:=0) bwhereT.table_schema='Test'  andtable_name not inch('Temp','TEMP_TB'));--2. Get the table structure in the same vein, and put the results of the statistics and the corresponding table names in the table.Create Table Temp(SelectT.table_name,@rownum:=@rownum+1   asNum fromInformation_schema.tables T, (Select @rownum:=0) bwhereT.table_schema='Test'  andtable_name not inch('Temp','TEMP_TB'));--3. Delete table Data retention table structureDelete  from Temp;--4. Create StorageCreate PROCEDUREWhileloopproc ()BEGINSelect @num:=1,@len:=Count(1) fromTEMP_TB; while @num<@len DoSelect @name:=table_name fromTemp_tbwhereNum=@num;Select @rownum:=Concat'Select COUNT (1)',' as',@name,'Into @temp from',@name); Insert  into Temp(Table_name,num)Values(@name,@temp);--Save the executed results to the results tableSet @num:=@num+1;Preparestmt from @rownum; EXECUTEstmt;deallocate PREPAREstmt;End  while;End;--5. Execute the storageCall Whileloopproc;--6. Query ResultsSelect *  from Temp;

Done!

MYSQL Storage While