MySQL: riepiloga tutti i conteggi delle righe della tabella in una singola query

Quando si esamina un database, è molto utile ottenere una panoramica di tutte le tabelle, compresi i loro conteggi delle righe:

TableName Count t1 1234 t2 37 ... ... 

La tabella TABLES MySQL nel database information_schema fornisce un campo table_rows:

 SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''; 

Ma table_rows è valido solo per alcuni motori di database, mentre per INNODB è NULL o non accurato.

Quindi è necessario comporre un metodo che faccia un SELECT SELECT (*) … per ogni tabella.

Attraverso molte ripetizioni di questa domanda su StackOverflow, ci sono numerose risposte che implicano un processo in due fasi. Una query per creare un set di risultati con righe contenenti le istruzioni del conteggio select individuale (*), seguita da una procedura di textediting per trasformarla nell’istruzione effettiva che può produrre l’output desiderato.

Non l’avevo visto trasformato in un unico passaggio, quindi di seguito ho postato la risposta. Non è scienza missilistica, ma è conveniente averlo spiegato.

Il primo codice di esempio qui è una procedura memorizzata che esegue l’intero processo in un unico passaggio, per quanto riguarda l’utente.

 BEGIN # zgwp_tables_rowcounts # TableName RowCount # Outputs a result set listing all tables and their row counts # for the current database SET SESSION group_concat_max_len = 1000000; SET @sql = NULL; SET @dbname = DATABASE(); SELECT GROUP_CONCAT( CONCAT ( 'SELECT ''',table_name,''' as TableName, COUNT(*) as RowCount FROM ', table_name, ' ' ) SEPARATOR 'UNION ' ) AS Qry FROM information_schema.`TABLES` AS t WHERE t.TABLE_SCHEMA = @dbname AND t.TABLE_TYPE = "BASE TABLE" ORDER BY t.TABLE_NAME ASC INTO @sql ; PREPARE stmt FROM @sql; EXECUTE stmt; END 

Gli appunti:

  • SELECT..INTO @sql crea la query necessaria e viene eseguito PREPARE … EXECUTE.

  • Imposta la variabile group_concat_max_len per consentire una stringa di risultati sufficientemente lunga da GROUP_CONCAT.

La procedura sopra riportata è utile per una rapida occhiata in un ambiente di amministrazione come Navicat o sulla riga di comando. Tuttavia, nonostante abbia restituito un set di risultati, per quanto ne sappia, non può essere referenziato in un’altra View o Query, presumibilmente perché MySQL non è in grado di determinare, prima di eseguirlo, quali set di risultati produce, lasciate che colonne abbiano .

Pertanto, è ancora utile poter produrre rapidamente, senza modifiche manuali, l’istruzione SELECT … UNION separata che può essere utilizzata come vista. Ciò è utile se vuoi unire i conteggi delle righe ad altre informazioni per tabella da un’altra tabella. Di seguito un’altra stored procedure:

 BEGIN # zgwp_tables_rowcounts_view_statement # Output: SelectStatement # Outputs a single row and column, containing a (possibly lengthy) # SELECT...UNION statement that, if used as a View, will output # TableName RowCount for all tables in the current database. SET SESSION group_concat_max_len = 1000000; SET @dbname = DATABASE(); SELECT GROUP_CONCAT( CONCAT ( 'SELECT ''',table_name,''' as TableName, COUNT(*) as RowCount FROM ', table_name, ' ', CHAR(10)) SEPARATOR 'UNION ' ) AS SelectStatement FROM information_schema.`TABLES` AS t WHERE t.TABLE_SCHEMA = @dbname AND t.TABLE_TYPE = "BASE TABLE" ORDER BY t.TABLE_NAME ASC ; END 

Gli appunti

  • Molto simile alla prima procedura nel concetto. Ho aggiunto un’interruzione di riga (CHAR (10)) a ciascuna istruzione sussidiaria “SELECT … UNION”, per comodità nella visualizzazione o nella modifica dell’istruzione.

  • È ansible crearlo come una funzione e restituire SelectStatement, se questo è più conveniente per il proprio ambiente.

Spero possa aiutare.