una - Obtener recuentos de registros para todas las tablas en la base de datos MySQL

seleccionar base de datos mysql

Question

¿Hay alguna manera de obtener el recuento de filas en todas las tablas en una base de datos MySQL sin ejecutar un SELECT count() en cada tabla?

Answer 1

Al igual que @Venkatramanan y otros, me pareció que INFORMATION_SCHEMA.TABLES no era confiable (usando InnoDB, MySQL 5.1.44), dando diferentes conteos de filas cada vez que lo ejecuto, incluso en tablas inactivas. Aquí hay una forma relativamente intrincada (pero flexible / adaptable) de generar una gran declaración SQL que puede pegar en una nueva consulta, sin instalar gemas de Ruby y esas cosas.

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

Produce una salida como esta:

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 

Copie y pegue a excepción de la última UNION para obtener un buen resultado como,

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

Answer 2

Así es como cuento las TABLAS y TODOS LOS REGISTROS usando 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.";

Answer 3

El póster quería el número de filas sin contar, pero no especificó qué motor de tabla. Con InnoDB, solo conozco una forma, que es contar.

Así es como cojo mis papas:

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

No estoy haciendo ninguna otra afirmación acerca de esto, excepto que esta es una manera realmente fea pero efectiva de obtener cuántas filas existen en cada tabla en la base de datos, independientemente del motor de la tabla y sin tener que tener permiso para instalar procedimientos almacenados y sin necesidad de instalar ruby o php. Sí, está oxidado. Sí cuenta. cuenta (*) es precisa.

Answer 4

Este procedimiento almacenado enumera las tablas, cuenta los registros y produce un número total de registros al final.

Para ejecutarlo después de agregar este procedimiento:

CALL `COUNT_ALL_RECORDS_BY_TABLE` ();

-

El procedimiento:

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

Answer 5

Esto es lo que hago para obtener el recuento real (sin usar el esquema)

Es más lento pero más preciso.

Es un proceso de dos pasos en

1. Obtener lista de tablas para su db. Puedes conseguirlo usando

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

2. Cree y asigne la lista de tablas a la variable de matriz en este script de bash (separado por un solo espacio como en el código a continuación)

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

3. Ejecutarlo:

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

Answer 6

Hay un poco de truco / solución para este problema de estimación.

Auto_Increment: por alguna razón, esto devuelve un recuento de filas mucho más preciso para su base de datos si tiene un incremento automático configurado en las tablas.

Encontré esto al explorar por qué la información de la tabla de visualización no coincide con los datos reales.

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)

Luego, puede usar PHP o lo que sea para devolver el máximo de las 2 columnas de datos para obtener la "mejor estimación" para el recuento de filas.

es decir

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;

El Incremento automático siempre tendrá filas +1 * (conteo de tablas) desactivadas, pero incluso con 4,000 tablas y 3 millones de filas, eso es 99.9% de precisión. Mucho mejor que las filas estimadas.

La belleza de esto es que los recuentos de filas devueltos en performance_schema también se borran para usted, porque el mayor no funciona en valores nulos. Sin embargo, esto puede ser un problema si no tiene tablas con incremento automático.

Answer 7

La siguiente consulta produce una consulta (nother) que obtendrá el valor de count (*) para cada tabla, de cada esquema, enumerada en information_schema.tables. El resultado completo de la consulta que se muestra aquí, todas las filas tomadas juntas, comprende una declaración SQL válida que termina en un punto y coma, sin ''unión''. La unión colgante se evita mediante el uso de una unión en la siguiente consulta.

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

Answer 8

Probablemente puedas poner algo junto con la tabla Tablas . Nunca lo he hecho, pero parece que tiene una columna para TABLE_ROWS y una para TABLE NAME .

Para obtener filas por tabla, puede utilizar una consulta como esta:

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

Answer 9

Puedes probar esto. Está funcionando bien para mí.

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;

Answer 10

Si conoce el número de tablas y sus nombres, y suponiendo que cada una tiene claves primarias, puede usar una combinación cruzada en combinación con COUNT(distinct [column]) para obtener las filas que provienen de cada tabla:

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

Aquí hay un ejemplo de SQL Fiddle .

Answer 11

Si desea los números exactos, use el siguiente script de ruby. Necesitas Ruby y RubyGems.

Instalar las siguientes gemas:

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

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

Vuelve a la línea de comandos:

$> ruby count_table_records.rb

Salida:

Table users has 7328974 rows.

Answer 12

Si usa la base de datos information_schema, puede usar este código mysql (la parte donde la consulta hace que no muestren tablas que tengan un valor nulo para las filas):

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

Answer 13

Solo corro

show table status;

Esto le dará el recuento de filas para CADA tabla más un montón de otra información. Solía ​​usar la respuesta seleccionada arriba, pero esto es mucho más fácil.

No estoy seguro de si esto funciona con todas las versiones, pero estoy usando 5.5 con el motor InnoDB.

Answer 14

Una opción más: para los que no son InnoDB usa datos de information_schema.TABLES (ya que es más rápido), para InnoDB: seleccione conteo (*) para obtener el conteo exacto. También ignora las vistas.

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 su base de datos tiene muchas tablas grandes de InnoDB, el conteo de todas las filas puede llevar más tiempo.

Answer 15

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

Eso es todo lo que necesitas.

Answer 16

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

Nota de la documentación, sin embargo: para las tablas InnoDB, el recuento de filas es solo una estimación aproximada utilizada en la optimización de SQL. Deberá usar COUNT (*) para los recuentos exactos (que es más caro).