Uzyskaj liczbę rekordów dla wszystkich tabel w bazie danych MySQL


347

Czy istnieje sposób na uzyskanie liczby wierszy we wszystkich tabelach w bazie danych MySQL bez uruchamiania SELECT count()w każdej tabeli?


1
Rozszerzona odpowiedź, która jest również dokładna dla InnoDB: stackoverflow.com/questions/24707814/...
gwideman

1
WYBIERZ licznik (nazwa_tabeli) OD INFORMACJI_SCHEMA.TABELE GDZIE TABELA_SCHEMA = „YOUR_DB” poda liczbę tabel w bazie danych
shasi kanth

Odpowiedzi:


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

Uwaga z dokumentów: w przypadku tabel InnoDB liczba wierszy jest jedynie przybliżonym oszacowaniem stosowanym w optymalizacji SQL. Musisz użyć COUNT (*) do dokładnych obliczeń (co jest droższe).


267
lub, jeśli chcesz dla każdej tabeli: WYBIERZ nazwę_tabeli, TABELA_WARUNKÓW OD INFORMACJI_SCHEMA.TABLE, GDZIE TABELA_SCHEMA = „{twoja_db}”;
TheSoftwareJedi

5
Czy jest jakiś inny sposób na uzyskanie table_row i table_name? Ponieważ chcę dokładnego wyniku, a nie przybliżonego oszacowania. Dziękuję Ci.
krunal shah

3
@krunalshah, To jedno z ograniczeń InnoDB. Aby uzyskać więcej informacji, zobacz dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html , sekcja Ograniczenia dotyczące tabel InnoDB. Zawsze możesz użyć SELECT COUNT (*) FROM t, który jest jednak znacznie wolniejszy
Oznaczanie

2
Jaitsu, nie, nie jest. count (*) (lub bardziej realistycznie count (id)) jest tym, czego mysql używa do liczenia swoich wierszy, prawda? W każdym razie właśnie go przetestowałem i otrzymałem większą liczbę dla wywołania count (), cokolwiek to jest warte.
codygman

2
WYBIERZ TABLE_NAME, SUMA (TABLE_ROWS) N FROM INFORMACJE_SCHEMA.TABLES GDZIE TABLE_SCHEMA = „{twoja_db}” grupa według TABLE_NAME;
PiyusG,

175

Prawdopodobnie możesz coś połączyć ze stołem Tabele . Nigdy tego nie zrobiłem, ale wygląda na to, że ma kolumnę dla TABLE_ROWS i jedną dla TABLE NAME .

Aby uzyskać wiersze na tabelę, możesz użyć następującego zapytania:

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

4
Czy jest jakiś inny sposób na uzyskanie table_row i table_name? Ponieważ chcę dokładnego wyniku, a nie przybliżonego oszacowania. Dziękuję Ci.
krunal shah

1
jak wspomniał Kuranl, zwraca to tylko szacunek i prawdopodobnie zwróci inne wyniki po kilkukrotnym uruchomieniu
Kris

Tabele z co najmniej ~ 250 rekordami wydają się zgłaszać inną liczbę wierszy przy każdym uruchomieniu tego zapytania.
Arthur

Ups ... żałuję, że nie widziałem wcześniej słowa „Szacowany”… jak wczoraj! Czy nie należy odrzucać odpowiedzi? Ponieważ OP nie poprosił o „oszacowanie” i wydaje się głupie myśleć, że może chcieć oszacowania. „oszacowanie” Czy uratowałbyś takich maroonów jak ja przed pominięciem „oszacowania”?
Kreeverp,

111

Podobnie jak @Venkatramanan i inni, stwierdziłem, że INFORMACJE_SCHEMA.TABLES są niewiarygodne (przy użyciu InnoDB, MySQL 5.1.44), podając różne liczby wierszy za każdym razem, gdy uruchamiam go nawet na wyciszonych tabelach. Oto stosunkowo pospolity (ale elastyczny / elastyczny) sposób generowania dużej instrukcji SQL, którą można wkleić do nowego zapytania, bez instalowania klejnotów Ruby i innych rzeczy.

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

Wytwarza dane wyjściowe w następujący sposób:

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 

Skopiuj i wklej z wyjątkiem ostatniej UNII, aby uzyskać ładne wyniki, takie jak,

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

2
Dzięki, miałem nadzieję, że nie będę musiał instalować żadnych wtyczek / klejnotów, aby uzyskać dokładne liczby.
bradvido

Wykonanie tej operacji trwa zbyt długo w przypadku dużej liczby tabel w bazie danych.
ollamh

1
dodaj „wybierz * z („ na początku i ”) jako kolejność wyjściową przez exact_row_count desc” na końcu wygenerowanego zapytania po usunięciu ostatniego UNIONU, aby uzyskać zamówienie według liczby tabel desc
Raghavendra

Aby wykluczyć widoki: WHERE table_schema = ' my_schema ' i TABLE_TYPE LIKE '% TABLE%'
Watson

39

Po prostu biegam:

show table status;

To da ci liczbę wierszy dla KAŻDEJ tabeli oraz kilka innych informacji. Użyłem wcześniej wybranej odpowiedzi, ale jest to o wiele łatwiejsze.

Nie jestem pewien, czy to działa ze wszystkimi wersjami, ale używam 5.5 z silnikiem InnoDB.


5
Niestety, jeśli używasz InnoDB, to podejście ma te same niedokładności, co inne metody opisane powyżej. Na przykład mam tabelę InnoDB, która ma około 65 000 wierszy, ale te metody tutaj zgłaszają, że ma ona od 350 000 do ponad 780 000.
PeterToTheThird

W przypadku DB z kilkoma wierszami jest dość dokładny (lub wystarczająco dokładny na moje potrzeby). Dało mi 1086 wierszy dla tabeli, która COUNT (*) zgłosiła 904 wiersze.
Magne,

Zdecydowanie najlepsza odpowiedź. Korzystam z InnoDB, ale potrzebuję tylko szybkiego polecenia, aby poznać rząd wielkości.
Nemo,

Poważnie, szkoda, że ​​to nie zostało zaakceptowane. Nie używam InnoDB i daje mi dokładną odpowiedź.
Kanion Kolob,

1
Liczba wierszy nie jest dokładna, ale „Auto_increment” może dać ci dokładną liczbę, jeśli nie usunąłeś żadnych wierszy z takich tabel.
Peter T.

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

To wszystko czego potrzebujesz.


1
tworzy szacunkowe wiersze tabeli - porównaj z "mysql_num_rows ($ tableresult)"
Kreeverp

1
to właściwie najlepsza odpowiedź! Również prostsze do wykonania z mysql cli:mysql> SELECT TABLE_NAME,SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'ngramsdb' GROUP BY TABLE_NAME;
loretoparisi

11

Ta procedura składowana wyświetla tabele, zlicza rekordy i generuje całkowitą liczbę rekordów na końcu.

Aby uruchomić go po dodaniu tej procedury:

CALL `COUNT_ALL_RECORDS_BY_TABLE` ();

-

Procedura:

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

8

Prosta droga:

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

Przykład wyniku:

+----------------+-----------------+
| 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 |
+----------------+-----------------+

4

Jest trochę hack / obejście tego problemu z oszacowaniem.

Auto_Increment - z jakiegoś powodu zwraca znacznie dokładniejszą liczbę wierszy dla bazy danych, jeśli masz ustawione automatyczne zwiększanie tabel.

Stwierdzono to podczas badania, dlaczego informacje o tabeli pokazu nie zgadzają się z rzeczywistymi danymi.

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)

Następnie możesz łatwo użyć PHP lub cokolwiek innego, aby zwrócić maksimum 2 kolumny danych, aby dać „najlepszą ocenę” dla liczby wierszy.

to znaczy

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;

Automatyczny przyrost zawsze będzie wynosił +1 * (liczba tabel) wierszy, ale nawet przy 4000 tabel i 3 milionach wierszy, to 99,9% dokładności. Znacznie lepiej niż szacowane wiersze.

Piękno tego polega na tym, że liczby wierszy zwrócone w scenie performance_schema również są dla ciebie kasowane, ponieważ największy nie działa na wartości zerowe. Może to być problem, jeśli nie masz tabel z automatycznym zwiększaniem.


3

Możesz tego spróbować. Dla mnie działa dobrze.

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

Jeśli korzystasz z bazy danych schemat_informacyjny, możesz użyć tego kodu mysql (gdzie część powoduje, że zapytanie nie pokazuje tabel, które mają wartość zerową dla wierszy):

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

1

Poniższe zapytanie tworzy (inne) zapytanie, które otrzyma wartość count (*) dla każdej tabeli, z każdego schematu wymienionego w information_schema.tables. Cały wynik pokazanego tutaj zapytania - wszystkie wiersze razem wzięte - zawiera poprawną instrukcję SQL zakończoną średnikiem - bez wiszącego „związku”. Zwisającego związku można uniknąć, używając związku w poniższym zapytaniu.

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

To właśnie robię, aby uzyskać rzeczywistą liczbę (nie używając schematu)

Jest wolniejszy, ale dokładniejszy.

Jest to dwuetapowy proces w

  1. Uzyskaj listę tabel dla swojej bazy danych. Możesz to uzyskać za pomocą

    mysql -uroot -p mydb -e "show tables"
  2. Utwórz i przypisz listę tabel do zmiennej tablicowej w tym skrypcie bash (oddzielone pojedynczym odstępem, tak jak w poniższym kodzie)

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

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

1

Jeszcze jedna opcja: w przypadku InnoDB wykorzystuje dane z schematu_informacji.TABLES (ponieważ jest szybszy), w przypadku InnoDB - wybierz liczbę (*), aby uzyskać dokładną liczbę. Ignoruje także widoki.

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;

Jeśli baza danych zawiera wiele dużych tabel InnoDB, liczenie wszystkich wierszy może zająć więcej czasu.


0

Oto jak liczę TABELE i WSZYSTKIE REKORDY używając 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.";

Dlaczego nie użyjesz count (*), jeśli zignorujesz dane?
Svetoslav Marinov

0

Plakat chciał liczby wierszy bez liczenia, ale nie określił silnika tabeli. Dzięki InnoDB znam tylko jeden sposób, który się liczy.

Oto jak zbieram ziemniaki:

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

Nie twierdzę o tym, poza tym, że jest to naprawdę brzydki, ale skuteczny sposób, aby dowiedzieć się, ile wierszy istnieje w każdej tabeli w bazie danych bez względu na silnik tabeli i bez konieczności posiadania pozwolenia na instalowanie procedur przechowywanych i bez konieczności instalowania rubin lub php. Tak, jest zardzewiały. Tak, to się liczy. liczba (*) jest dokładna.


0

Na podstawie powyższej odpowiedzi @ Nathana, ale bez potrzeby „usuwania ostatecznej unii” i opcji sortowania danych wyjściowych, używam następującego kodu SQL. Generuje kolejną instrukcję SQL, która następnie uruchamia się:

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 

Potrzebujesz wystarczająco dużej wartości group_concat_max_lenzmiennej serwera, ale od MariaDb 10.2.4 powinna ona domyślnie wynosić 1M.


0

Poniższy kod generuje wybrane zapytanie dla wszystkich opowieści. Wystarczy usunąć ostatni „UNION ALL” wybierz wszystkie wyniki i wklej nowe okno zapytania, aby uruchomić.

SELECT 
concat('select ''', table_name ,''' as TableName, COUNT(*) as RowCount from ' , table_name , ' UNION ALL ')  as TR FROM
information_schema.tables where 
table_schema = 'Database Name'

-1

Jeśli chcesz dokładnych liczb, użyj następującego skryptu ruby. Potrzebujesz Ruby i RubyGems.

Zainstaluj następujące klejnoty:

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

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

Wróć do wiersza poleceń:

$> ruby count_table_records.rb

Wynik:

Table users has 7328974 rows.

-4

Jeśli znasz liczbę tabel i ich nazwy i zakładając, że każda z nich ma klucze podstawowe, możesz użyć połączenia krzyżowego w połączeniu z, COUNT(distinct [column])aby uzyskać wiersze pochodzące z każdej tabeli:

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

Oto przykład SQL Fiddle .

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.