Pobieranie rekordów dla wszystkich tabel w bazie danych MySQL

Prawdopodobnie możesz złożyć coś razem z Tables table . Nigdy tego nie robiłem, ale wygląda na to, że ma kolumnę dla TABLE_ROWS i jedną dla Nazwa tabeli.

Aby uzyskać wiersze dla tabeli, możesz użyć zapytania takiego:

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

Podobnie jak @ Venkatramanan i inni znalazłem INFORMATION_SCHEMA.TABLES unreliable (using InnoDB, MySQL 5.1.44), dający różne liczenia wierszy za każdym razem, gdy uruchamiam go nawet na zadanych tabelach. Oto stosunkowo hakerski (ale elastyczny/elastyczny) sposób generowania dużej instrukcji SQL, którą można wkleić do nowego zapytania, bez instalowania klejnotów Ruby i takich tam.

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

Generuje wynik 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 

Kopiuj i wklej, z wyjątkiem ostatniego związku, aby uzyskać ładne wyjście, 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)

Po prostu biegam:

show table status;

To daje liczbę wierszy dla każdej tabeli oraz kilka innych informacji. Kiedyś korzystałem z wybranej powyżej 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.

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

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

Prosty sposób:

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

Jest trochę hack / obejście tego problemu oszacowania.

Auto_Increment-z jakiegoś powodu zwraca znacznie dokładniejszą liczbę wierszy dla twojej bazy danych, jeśli masz ustawioną automatyczną inkrementację na tabelach.

Znalazłem to podczas badania, dlaczego show table info nie pasuje do rzeczywistych danych.

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)

Możesz następnie łatwo użyć PHP lub czegokolwiek innego, aby zwrócić max z 2 kolumn danych, aby dać "najlepsze oszacowanie" dla liczby wierszy.

Tzn.

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 Przyrost zawsze będzie wynosił +1 * (liczba tabel) wiersze wyłączone, ale nawet w przypadku 4000 tabel i 3 milionów wierszy, to dokładność 99,9%. Znacznie lepiej niż szacowane rzędy.

Piękno tego polega na tym, że liczba wierszy zwróconych w performance_schema jest również usuwana, ponieważ greatest nie działa na null. Może to być problem, jeśli nie masz tabel z automatyczną inkrementacją.

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

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;

Jeśli używasz bazy danych information_schema, możesz użyć tego kodu mysql (część where sprawia, że zapytanie nie pokazuje tabel, które mają wartość null dla wierszy):

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

Jeszcze jedna opcja: dla non InnoDB wykorzystuje dane z information_schema.Tabel (ponieważ jest to szybsze), dla InnoDB-select count (*), aby uzyskać dokładną liczbę. Ignoruje również poglądy.

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 twoja baza danych ma wiele dużych tabel InnoDB liczenie wszystkich wierszy może zająć więcej czasu.

Poniższe zapytanie tworzy (inne) zapytanie, które otrzyma wartość count (*) dla każdej tabeli, z każdego schematu, wymienionego w information_schema.stoły. Cały wynik zapytania pokazanego tutaj - wszystkie wiersze razem wzięte-zawierają poprawne polecenie SQL zakończone średnikiem-bez zwisającego "union". Unia zwisająca jest unikana przez użycie Unii 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);';

To jest to, co robię, aby uzyskać rzeczywistą liczbę (nie za pomocą schematu)

Jest to dwuetapowy proces w

1. Pobierz listę tabel dla Twojego db. Możesz go uzyskać za pomocą

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

2. Tworzenie i przypisywanie listy tabel do zmiennej array w tym skrypcie bash (oddzielone pojedynczym spacją, tak jak w kodzie poniżej)

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

3. Uruchom GO:

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

Tak zliczam 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.";

Poster chciał zliczać wiersze bez zliczania, ale nie sprecyzował, który silnik tabeli. Z InnoDB znam tylko jeden sposób, czyli liczyć.

Tak 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ę na ten temat, poza tym, że jest to naprawdę brzydki, ale skuteczny sposób, aby dowiedzieć się, ile wierszy istnieje w każdej tabeli w bazie danych, niezależnie od silnika tabeli i bez konieczności posiadania uprawnień do instalowania procedur składowanych i bez konieczności instalowania Rubiego lub php. Tak., jest zardzewiały. Tak, liczy się. liczba (*) jest dokładna.

Bazując na powyższej odpowiedzi @ Nathan, ale bez potrzeby "usuwania ostatecznego związku" i z opcją sortowania danych wyjściowych, używam następującego SQL. Generuje kolejne polecenie SQL, które następnie uruchamia:

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 zmiennej serwera group_concat_max_len, ale od MariaDb 10.2.4 powinna ona domyślnie wynosić 1M.

Poniższy kod generuje zapytanie select dla wszystkich bajek. Po prostu usuń Ostatnie "wszystkie zjednoczenie" 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'

Jeśli chcesz podać dokładne liczby, 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

Wyjście:

Table users has 7328974 rows.

Jeśli znasz liczbę tabel i ich nazwy i zakładając, że każda z nich ma klucze podstawowe, możesz użyć łącznika krzyżowego w kombinacji 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.