首页 文章

获取MySQL数据库中所有表的记录计数

提问于
浏览
279

有没有办法获取MySQL数据库中所有表中的行数而不在每个表上运行 SELECT count()

16 回答

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

    Note from the docs though:对于InnoDB表, the row count is only a rough estimate 用于SQL优化 . 您需要使用COUNT(*)来获得准确的计数(这更昂贵) .

  • 0

    你可以把东西放在一起Tables table . 我从来没有这样做过,但看起来它有一个TABLE_ROWS列和一个TABLE NAME列 .

    要获取每个表的行,您可以使用如下查询:

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

    像@Venkatramanan和其他人一样,我发现INFORMATION_SCHEMA.TABLES不可靠(使用InnoDB,MySQL 5.1.44),每次运行它时都会提供不同的行数,即使在静默表上也是如此 . 这是一种相对hacky(但灵活/适应性)的生成大型SQL语句的方法,您可以将其粘贴到新查询中,而无需安装Ruby gems和东西 .

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

    它产生如下输出:

    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
    

    复制并粘贴除了最后一个UNION以获得良好的输出,如,

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

    我跑了:

    show table status;
    

    这将为您提供每个表的行数以及一堆其他信息 . 我曾经使用上面选择的答案,但这更容易 .

    我不确定这是否适用于所有版本,但我使用的是InnoDB引擎 .

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

    这就是你所需要的 .

  • 0

    此存储过程列出表,计算记录,并在最后生成记录总数 .

    要在添加此过程后运行它:

    CALL `COUNT_ALL_RECORDS_BY_TABLE` ();
    

    程序,流程:

    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
    
  • 2

    如果使用数据库information_schema,则可以使用此mysql代码(where部分使查询不显示对行具有空值的表):

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

    你可以试试这个 . 它对我来说很好 .

    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;
    
  • 345

    这个估计问题有一些黑客/解决方法 .

    Auto_Increment - 由于某种原因,如果在表上设置了自动增量,则会为数据库返回更准确的行数 .

    在探索为什么显示表信息与实际数据不匹配时发现这一点 .

    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)
    

    然后,您可以轻松地使用PHP或其他任何内容返回2个数据列的最大值,以便为行计数提供“最佳估计值” .

    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;
    

    自动增量将始终为1 *(表计数)行,但即使有4,000个表和300万行,也准确率为99.9% . 比估计的行好多了 .

    这样做的好处是,performance_schema中返回的行计数也会被删除,因为最大值不能用于空值 . 但是,如果没有自动增量的表,这可能是一个问题 .

  • 9

    以下查询生成(nother)查询,该查询将从information_schema.tables中列出的每个模式获取每个表的count(*)值 . 这里显示的查询的整个结果 - 所有行一起 - 包含以分号结尾的有效SQL语句 - 没有悬空的“联合” . 通过在下面的查询中使用联合来避免悬空联合 .

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

    这是我做的实际计数(不使用架构)

    它更慢但更准确 .

    这是一个两步过程

    • 获取数据库的表列表 . 你可以使用它
    mysql -uroot -p mydb -e "show tables"
    
    • 在此bash脚本中创建表并将其分配给数组变量(由单个空格分隔,就像在下面的代码中一样)
    array=( table1 table2 table3 )
    
    for i in "${array[@]}"
    do
        echo $i
        mysql -uroot mydb -e "select count(*) from $i"
    done
    
    • 运行它:
    chmod +x script.sh; ./script.sh
    
  • -3

    如果您想要确切的数字,请使用以下ruby脚本 . 你需要Ruby和RubyGems .

    安装以下宝石:

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

    文件: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
    

    回到命令行:

    $> ruby count_table_records.rb
    

    输出:

    Table users has 7328974 rows.
    
  • 2

    这就是我使用PHP计算TABLES和ALL RECORDS的方法:

    $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.";
    
  • 9

    海报想要行计数而不计算,但没有指定哪个表引擎 . 使用InnoDB,我只知道一种方法,这是计数 .

    这就是我挑选土 beans 的方法:

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

    除了这是一个非常丑陋但有效的方法来获取数据库中每个表中存在多少行而不管表引擎并且无需安装存储过程的权限,并且无需安装红宝石或PHP . 是的,它生锈了 . 是的,很重要 . count(*)是准确的 .

  • 0

    还有一个选择:对于非InnoDB,它使用来自information_schema.TABLES的数据(因为它更快),对于InnoDB - 选择count(*)来获得准确的计数 . 它也忽略了观点 .

    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;
    

    如果您的数据库有很多计算所有行的大型InnoDB表可能需要更多时间 .

  • 2

    如果您知道表的数量及其名称,并假设它们各自具有主键,则可以将交叉连接与 COUNT(distinct [column]) 结合使用以获取来自每个表的行:

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

    这是一个SQL Fiddle示例 .

相关问题