Obtenir un nombre d’enregistrements pour toutes les tables dans la firebase database MySQL

Est-il possible d’obtenir le nombre de lignes dans toutes les tables d’une firebase database MySQL sans exécuter un SELECT count() sur chaque table?

 SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{your_db}'; 

Note des documents cependant: Pour les tables InnoDB, le nombre de lignes n’est qu’une estimation approximative utilisée dans l’optimisation SQL. Vous devrez utiliser COUNT (*) pour les comptes exacts (ce qui est plus cher).

Vous pouvez probablement mettre quelque chose avec la table Tables . Je ne l’ai jamais fait, mais il semble qu’il y ait une colonne pour TABLE_ROWS et une pour TABLE NAME .

Pour obtenir des lignes par table, vous pouvez utiliser une requête comme celle-ci:

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

Comme @Venkatramanan et autres, j’ai trouvé INFORMATION_SCHEMA.TABLES non fiable (en utilisant InnoDB, MySQL 5.1.44), donnant des nombres de lignes différents chaque fois que je l’exécute, même sur des tables au repos. Voici une manière relativement géniale (mais flexible / adaptable) de générer une grosse requête SQL que vous pouvez coller dans une nouvelle requête, sans installer les gemmes Ruby.

 SELECT CONCAT( 'SELECT "', table_name, '" AS table_name, COUNT(*) AS exact_row_count FROM `', table_schema, '`.`', table_name, '` UNION ' ) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '**my_schema**'; 

Il produit une sortie comme celle-ci:

 SELECT "func" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.func UNION SELECT "general_log" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.general_log UNION SELECT "help_category" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_category UNION SELECT "help_keyword" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_keyword UNION SELECT "help_relation" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_relation UNION SELECT "help_topic" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.help_topic UNION SELECT "host" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.host UNION SELECT "ndb_binlog_index" AS table_name, COUNT(*) AS exact_row_count FROM my_schema.ndb_binlog_index UNION 

Copiez et collez sauf pour le dernier UNION pour obtenir de belles sorties comme,

 +------------------+-----------------+ | table_name | exact_row_count | +------------------+-----------------+ | func | 0 | | general_log | 0 | | help_category | 37 | | help_keyword | 450 | | help_relation | 990 | | help_topic | 504 | | host | 0 | | ndb_binlog_index | 0 | +------------------+-----------------+ 8 rows in set (0.01 sec) 

Je viens de courir:

 show table status; 

Cela vous donnera le nombre de lignes pour chaque table, plus un tas d’autres informations. J’avais l’habitude d’utiliser la réponse sélectionnée ci-dessus, mais c’est beaucoup plus facile.

Je ne suis pas sûr que cela fonctionne avec toutes les versions, mais j’utilise 5.5 avec le moteur InnoDB.

  SELECT TABLE_NAME,SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_db' GROUP BY TABLE_NAME; 

C’est tout ce dont vous avez besoin.

Cette procédure stockée répertorie les tables, compte les enregistrements et produit un nombre total d’enregistrements à la fin.

Pour l’exécuter après avoir ajouté cette procédure:

 CALL `COUNT_ALL_RECORDS_BY_TABLE` (); 

La procédure:

 DELIMITER $$ CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `COUNT_ALL_RECORDS_BY_TABLE`() BEGIN DECLARE done INT DEFAULT 0; DECLARE TNAME CHAR(255); DECLARE table_names CURSOR for SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE(); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN table_names; DROP TABLE IF EXISTS TCOUNTS; CREATE TEMPORARY TABLE TCOUNTS ( TABLE_NAME CHAR(255), RECORD_COUNT INT ) ENGINE = MEMORY; WHILE done = 0 DO FETCH NEXT FROM table_names INTO TNAME; IF done = 0 THEN SET @SQL_TXT = CONCAT("INSERT INTO TCOUNTS(SELECT '" , TNAME , "' AS TABLE_NAME, COUNT(*) AS RECORD_COUNT FROM ", TNAME, ")"); PREPARE stmt_name FROM @SQL_TXT; EXECUTE stmt_name; DEALLOCATE PREPARE stmt_name; END IF; END WHILE; CLOSE table_names; SELECT * FROM TCOUNTS; SELECT SUM(RECORD_COUNT) AS TOTAL_DATABASE_RECORD_CT FROM TCOUNTS; END 

Si vous utilisez la firebase database information_schema, vous pouvez utiliser ce code mysql (la partie où la requête n’affiche pas les tables ayant une valeur nulle pour les lignes):

 SELECT TABLE_NAME, TABLE_ROWS FROM `TABLES` WHERE `TABLE_ROWS` >=0 

Vous pouvez essayer ceci. Cela fonctionne très bien pour moi.

 SELECT IFNULL(table_schema,'Total') "Database",TableCount FROM (SELECT COUNT(1) TableCount,table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') GROUP BY table_schema WITH ROLLUP) A; 

Il y a un peu de hack / contournement à ce problème d’estimation.

Auto_Increment – pour une raison quelconque, cela renvoie un nombre de lignes beaucoup plus précis pour votre firebase database si l’incrémentation automatique est configurée sur les tables.

Cela a été trouvé lors de l’exploration des raisons pour lesquelles les informations de la table d’affichage ne correspondaient pas aux données réelles.

 SELECT table_schema 'Database', SUM(data_length + index_length) AS 'DBSize', SUM(TABLE_ROWS) AS DBRows, SUM(AUTO_INCREMENT) AS DBAutoIncCount FROM information_schema.tables GROUP BY table_schema; +--------------------+-----------+---------+----------------+ | Database | DBSize | DBRows | DBAutoIncCount | +--------------------+-----------+---------+----------------+ | Core | 35241984 | 76057 | 8341 | | information_schema | 163840 | NULL | NULL | | jspServ | 49152 | 11 | 856 | | mysql | 7069265 | 30023 | 1 | | net_snmp | 47415296 | 95123 | 324 | | performance_schema | 0 | 1395326 | NULL | | sys | 16384 | 6 | NULL | | WebCal | 655360 | 2809 | NULL | | WxObs | 494256128 | 530533 | 3066752 | +--------------------+-----------+---------+----------------+ 9 rows in set (0.40 sec) 

Vous pouvez alors facilement utiliser PHP ou autre pour retourner le maximum des 2 colonnes de données pour obtenir la “meilleure estimation” du nombre de lignes.

c’est à dire

 SELECT table_schema 'Database', SUM(data_length + index_length) AS 'DBSize', GREATEST(SUM(TABLE_ROWS), SUM(AUTO_INCREMENT)) AS DBRows FROM information_schema.tables GROUP BY table_schema; 

L’incrémentation automatique sera toujours de +1 * (nombre de tables), mais même avec 4000 tables et 3 millions de lignes, c’est exact à 99,9%. Beaucoup mieux que les lignes estimées.

La beauté de ceci est que les nombres de lignes retournés dans performance_schema sont également effacés, car la valeur maximale ne fonctionne pas sur les valeurs NULL. Cela peut être un problème si vous n’avez pas de tables avec incrément automatique, cependant.

La requête suivante génère une requête (nother) qui obtiendra la valeur de count (*) pour chaque table, à partir de chaque schéma, répertorié dans information_schema.tables. Le résultat entier de la requête montrée ici – toutes les lignes sockets ensemble – comprend une instruction SQL valide se terminant par un point-virgule – pas de «union» en suspens. L’union pendante est évitée par l’utilisation d’une union dans la requête ci-dessous.

 select concat('select "', table_schema, '.', table_name, '" as `schema.table`, count(*) from ', table_schema, '.', table_name, ' union ') as 'Query Row' from information_schema.tables union select '(select null, null limit 0);'; 

C’est ce que je fais pour obtenir le nombre réel (pas utiliser le schéma)

C’est plus lent mais plus précis.

C’est un processus en deux étapes à

  1. Obtenez la liste des tables pour votre firebase database. Vous pouvez l’obtenir en utilisant

     mysql -uroot -p mydb -e "show tables" 
  2. Créez et atsortingbuez la liste des tables à la variable tableau dans ce script bash (séparé par un seul espace comme dans le code ci-dessous)

     array=( table1 table2 table3 ) for i in "${array[@]}" do echo $i mysql -uroot mydb -e "select count(*) from $i" done 
  3. Exécuter:

     chmod +x script.sh; ./script.sh 

Si vous voulez les chiffres exacts, utilisez le script Ruby suivant. Vous avez besoin de Ruby et RubyGems.

Installez les gems suivants:

 $> gem install dbi $> gem install dbd-mysql 

Fichier: count_table_records.rb

 require 'rubygems' require 'dbi' db_handler = DBI.connect('DBI:Mysql:database_name:localhost', 'username', 'password') # Collect all Tables sql_1 = db_handler.prepare('SHOW tables;') sql_1.execute tables = sql_1.map { |row| row[0]} sql_1.finish tables.each do |table_name| sql_2 = db_handler.prepare("SELECT count(*) FROM #{table_name};") sql_2.execute sql_2.each do |row| puts "Table #{table_name} has #{row[0]} rows." end sql_2.finish end db_handler.disconnect 

Revenez à la ligne de commande:

 $> ruby count_table_records.rb 

Sortie:

 Table users has 7328974 rows. 

Voici comment je compte TABLES et ALL RECORDS en utilisant PHP:

 $dtb = mysql_query("SHOW TABLES") or die (mysql_error()); $jmltbl = 0; $jml_record = 0; $jml_record = 0; while ($row = mysql_fetch_array($dtb)) { $sql1 = mysql_query("SELECT * FROM " . $row[0]); $jml_record = mysql_num_rows($sql1); echo "Table: " . $row[0] . ": " . $jml_record record . "
"; $jmltbl++; $jml_record += $jml_record; } echo "--------------------------------
$jmltbl Tables, $jml_record > records.";

L’affiche voulait compter les lignes sans compter, mais ne spécifiait pas quel moteur de table. Avec InnoDB, je ne connais qu’un seul moyen, à savoir compter.

Voici comment je cueille mes pommes de terre:

 # Put this function in your bash and call with: # rowpicker DBUSER DBPASS DBNAME [TABLEPATTERN] function rowpicker() { UN=$1 PW=$2 DB=$3 if [ ! -z "$4" ]; then PAT="LIKE '$4'" tot=-2 else PAT="" tot=-1 fi for t in `mysql -u "$UN" -p"$PW" "$DB" -e "SHOW TABLES $PAT"`;do if [ $tot -lt 0 ]; then echo "Skipping $t"; let "tot += 1"; else c=`mysql -u "$UN" -p"$PW" "$DB" -e "SELECT count(*) FROM $t"`; c=`echo $c | cut -d " " -f 2`; echo "$t: $c"; let "tot += c"; fi; done; echo "total rows: $tot" } 

Je ne fais aucune assertion à ce sujet, à part que c’est un moyen vraiment moche mais efficace d’obtenir le nombre de lignes existant dans chaque table de la firebase database, sans avoir à installer les procédures stockées et sans avoir à installer ruby ou php. Oui, c’est rouillé. Oui ça compte. count (*) est exact.

Une autre option: pour non InnoDB, il utilise les données de information_schema.TABLES (car il est plus rapide), pour InnoDB – sélectionnez count (*) pour obtenir le compte exact. En outre, il ignore les vues.

 SET @table_schema = DATABASE(); -- or SET @table_schema = 'my_db_name'; SET GROUP_CONCAT_MAX_LEN=131072; SET @selects = NULL; SELECT GROUP_CONCAT( 'SELECT "', table_name,'" as TABLE_NAME, COUNT(*) as TABLE_ROWS FROM `', table_name, '`' SEPARATOR '\nUNION\n') INTO @selects FROM information_schema.TABLES WHERE TABLE_SCHEMA = @table_schema AND ENGINE = 'InnoDB' AND TABLE_TYPE = "BASE TABLE"; SELECT CONCAT_WS('\nUNION\n', CONCAT('SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND ENGINE <> "InnoDB" AND TABLE_TYPE = "BASE TABLE"'), @selects) INTO @selects; PREPARE stmt FROM @selects; EXECUTE stmt USING @table_schema; DEALLOCATE PREPARE stmt; 

Si votre firebase database contient beaucoup de grandes tables InnoDB, le comptage de toutes les lignes peut prendre plus de temps.

Si vous connaissez le nombre de tables et leurs noms, et en supposant qu’elles possèdent chacune des clés primaires, vous pouvez utiliser une jointure croisée avec COUNT(distinct [column]) pour obtenir les lignes provenant de chaque table:

 SELECT COUNT(distinct t1.id) + COUNT(distinct t2.id) + COUNT(distinct t3.id) AS totalRows FROM firstTable t1, secondTable t2, thirdTable t3; 

Voici un exemple de SQL Fiddle .