wake-up-neo.com

Ermitteln Sie Datensatzzahlen für alle Tabellen in der MySQL-Datenbank

Gibt es eine Möglichkeit, die Anzahl der Zeilen in allen Tabellen einer MySQL-Datenbank abzurufen, ohne dass für jede Tabelle ein SELECT count() ausgeführt wird?

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

Hinweis aus den Dokumenten: Bei InnoDB-Tabellen ist die Zeilenanzahl nur eine grobe Schätzung, die bei der SQL-Optimierung verwendet wird. Sie müssen COUNT (*) für genaue Zählungen verwenden (was teurer ist).

355
Hates_

Sie können wahrscheinlich etwas mit Tables table zusammenstellen. Ich habe es noch nie gemacht, aber es sieht so aus, als hätte es eine Spalte für TABLE_ROWS und eine für TABLE NAME. 

Um Zeilen pro Tabelle zu erhalten, können Sie eine Abfrage wie folgt verwenden: 

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

Wie @Venkatramanan und andere fand ich INFORMATION_SCHEMA.TABLES unzuverlässig (bei Verwendung von InnoDB, MySQL 5.1.44), wobei ich jedes Mal, wenn ich es ausführte, auch bei stillgelegten Tabellen unterschiedliche Zeilenzahlen angibt. Hier ist eine relativ hackige (aber flexible/anpassungsfähige) Methode zum Generieren einer großen SQL-Anweisung, die Sie in eine neue Abfrage einfügen können, ohne Ruby-Gems und dergleichen zu installieren.

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**';

Es produziert Ausgabe wie folgt:

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

Kopieren und Einfügen mit Ausnahme der letzten UNION, um eine Nice-Ausgabe zu erhalten, z.

+------------------+-----------------+
| 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)
94
Nathan

Ich laufe einfach:

show table status;

Dies gibt Ihnen die Zeilenzahl für JEDE Tabelle und eine Reihe weiterer Informationen. Ich habe die oben gewählte Antwort verwendet, dies ist jedoch viel einfacher.

Ich bin nicht sicher, ob dies mit allen Versionen funktioniert, aber ich verwende 5.5 mit InnoDB-Engine.

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

Das ist alles was du brauchst.

10
Gustavo Castro

Diese gespeicherte Prozedur listet Tabellen auf, zählt Datensätze und erzeugt am Ende eine Gesamtzahl von Datensätzen.

So führen Sie es aus, nachdem Sie dieses Verfahren hinzugefügt haben:

CALL `COUNT_ALL_RECORDS_BY_TABLE` ();

-

Der Ablauf:

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
9
Jake Drew

Es gibt ein bisschen Hack/Workaround für dieses Schätzproblem.

Auto_Increment - aus irgendeinem Grund gibt dies eine viel genauere Zeilenanzahl für Ihre Datenbank zurück, wenn Sie die Tabelle automatisch inkrementieren.

Dies wurde festgestellt, wenn untersucht wurde, warum die Informationen der Showtabelle nicht mit den tatsächlichen Daten übereinstimmen.

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)

Sie können dann einfach mit PHP oder was auch immer das Maximum der 2 Datenspalten zurückgeben, um die "beste Schätzung" für die Zeilenanzahl zu erhalten.

d.h. 

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;

Auto Increment ist immer um +1 * (Tabellenanzahl) Zeilen deaktiviert, aber selbst bei 4.000 Tabellen und 3 Millionen Zeilen ist das zu 99,9% genau. Viel besser als die geschätzten Reihen.

Das Schöne daran ist, dass die in performance_schema zurückgegebenen Zeilenzahlen auch für Sie gelöscht werden, da die größte Anzahl von Nullen nicht funktioniert. Dies kann jedoch ein Problem sein, wenn Sie keine Tabellen mit automatischem Inkrement haben.

2
user3260912

Sie können es versuchen. Es funktioniert gut für mich.

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;
2
Nimesh07

Wenn Sie die Datenbank information_schema verwenden, können Sie diesen MySQL-Code verwenden (der Where-Teil bewirkt, dass die Abfrage keine Tabellen mit Nullwerten für Zeilen anzeigt):

SELECT TABLE_NAME, TABLE_ROWS
FROM `TABLES`
WHERE `TABLE_ROWS` >=0
2
Robin Manoli

Eine weitere Option: Für Nicht-InnoDB werden Daten von information_schema.TABLES (schneller) verwendet. Für InnoDB - Wählen Sie count (*) aus, um die genaue Anzahl zu ermitteln. Es ignoriert auch Ansichten.

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;

Wenn Ihre Datenbank viele große InnoDB-Tabellen enthält, kann das Zählen aller Zeilen mehr Zeit in Anspruch nehmen.

1
filimonov

Dies ist, was ich mache, um die tatsächliche Zählung zu erhalten (keine Verwendung des Schemas) 

Es ist langsamer aber genauer.

Es ist ein zweistufiger Prozess

  1. Liste der Tabellen für Ihre Datenbank abrufen. Sie können es mit bekommen 

    mysql -uroot -p mydb -e "show tables"

  2. Erstellen Sie die Array-Tabelle in diesem Bash-Skript und ordnen Sie sie der Array-Variablen zu (getrennt durch ein Leerzeichen wie im folgenden Code)

    array=( table1 table2 table3 )

for i in "${array[@]}"
do
    echo $i
    mysql -uroot mydb -e "select count(*) from $i"
done

  3. Starte es:

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

Die folgende Abfrage erzeugt eine a(nother) - Abfrage, die den Wert von count (*) für jede Tabelle und jedes Schema in information_schema.tables auflistet. Das gesamte Ergebnis der hier gezeigten Abfrage - alle Zeilen zusammengenommen - umfasst eine gültige SQL-Anweisung, die mit einem Semikolon endet - keine "Union". Die baumelnde Union wird durch die Verwendung einer Union in der folgenden Abfrage vermieden.

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);';
1
user1575139

Das Plakat wollte die Zeilenanzahl ohne zu zählen, aber es wurde nicht angegeben, welche Tabellen-Engine verwendet werden soll. Mit InnoDB kenne ich nur einen Weg, nämlich zu zählen.

So pflücke ich meine Kartoffeln:

# 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"
}

Ich mache keine Behauptungen darüber, außer dass dies ein wirklich hässlicher, aber effektiver Weg ist, um zu ermitteln, wie viele Zeilen in jeder Tabelle in der Datenbank vorhanden sind, unabhängig von der Tabellen-Engine und ohne die Berechtigung zum Installieren gespeicherter Prozeduren und ohne Installation Ruby oder PHP. Ja, es ist rostig. Ja, es zählt. count (*) ist genau.

0
apotek

Wenn Sie die genauen Zahlen wünschen, verwenden Sie das folgende Ruby-Skript. Sie benötigen Ruby und RubyGems.

Installiere folgende Gems:

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

Datei: 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

Gehen Sie zurück zur Befehlszeile:

$> Ruby count_table_records.rb

Ausgabe:

Table users has 7328974 rows.
0
Michael Voigt

Basierend auf der obigen Antwort von @ Nathan, aber ohne "die letzte Vereinigung entfernen zu müssen" und mit der Option, die Ausgabe zu sortieren, verwende ich die folgende SQL. Es generiert eine weitere SQL-Anweisung, die dann einfach ausgeführt wird:

select CONCAT( 'select * from (\n', group_concat( single_select SEPARATOR ' UNION\n'), '\n ) Q order by Q.exact_row_count desc') as sql_query
from (
    SELECT CONCAT(
        'SELECT "', 
        table_name, 
        '" AS table_name, COUNT(1) AS exact_row_count
        FROM `', 
        table_schema,
        '`.`',
        table_name, 
        '`'
    ) as single_select
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE table_schema = 'YOUR_SCHEMA_NAME'
      and table_type = 'BASE TABLE'
) Q

Sie benötigen einen ausreichend großen Wert für die Servervariable group_concat_max_len, aber ab MariaDb 10.2.4 sollte der Standardwert 1 MB sein.

0
Adam

Einfacher Weg:

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

Ergebnis Beispiel:

+----------------+-----------------+
| TABLE_NAME     | SUM(TABLE_ROWS) |
+----------------+-----------------+
| calls          |            7533 |
| courses        |             179 |
| course_modules |             298 |
| departments    |              58 |
| faculties      |             236 |
| modules        |             169 |
| searches       |           25423 |
| sections       |             532 |
| universities   |              57 |
| users          |           10293 |
+----------------+-----------------+
0
Eduardo Cuomo

So zähle ich TABLES und ALL RECORDS mit 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 . "<br>";      
    $jmltbl++;
    $jml_record += $jml_record;
}

echo "--------------------------------<br>$jmltbl Tables, $jml_record > records.";
0
koswara1482