todas - seleccionar varios campos de una tabla en mysql




Obtener recuentos de registros para todas las tablas en la base de datos MySQL (11)

¿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?


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)

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

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

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
    

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

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

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 .


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.

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.


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.


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





rowcount