是否有一种方法可以获得MySQL数据库中所有表的行计数,而无需在每个表上运行SELECT count() ?
当前回答
像许多其他人一样,我很难用InnoDB在INFORMATION_SCHEMA表上获得准确的值,并且能够通过count()进行查询将无限受益,并且希望在一次查询中完成它。
首先,确保启用大规模group_concats:
SET SESSION group_concat_max_len = 1000000;
然后运行此查询以获得将为数据库运行的结果查询。
SELECT CONCAT('SELECT ', GROUP_CONCAT(table1.count SEPARATOR ',\n')) FROM (
SELECT concat('(SELECT count(id) AS \'',table_name,' Count\' ','FROM ',table_name,') AS ',table_name,'_Count') AS 'count'
FROM information_schema.tables
WHERE table_schema = '**YOUR_DATABASE_HERE**'
) AS table1
这将生成诸如…
SELECT (SELECT count(id) AS 'table1 Count' FROM table1) AS table1_Count,
(SELECT count(id) AS 'table2 Count' FROM table2) AS table2_Count,
(SELECT count(id) AS 'table3 Count' FROM table3) AS table3_Count;
这反过来又产生了以下结果:
*************************** 1. row ***************************
table1_Count: 1
table2_Count: 1
table3_Count: 0
其他回答
我不知道为什么这么难,但这就是生活。 下面是执行实际计数的bash脚本。只需将其保存为(例如count_rows.sh),使其可执行(例如chmod 755 count_rows.sh),并运行它(例如。/count_rows.sh)
#!/bin/bash
readarray -t TABLES < <(mysql --skip-column-names -u myuser -pmypassword mydbname -e "show tables")
# now we have an array like:
# TABLES='([0]="customer" [1]="order" [2]="product")'
# You can print out the array with:
#declare -p TABLES
for i in "${TABLES[@]}"
do
#echo $i
COUNT=$(mysql --skip-column-names -u username -pmypassword mydbname -e "select count(*) from $i")
echo $i : $COUNT
done
像@Venkatramanan和其他人一样,我找到了INFORMATION_SCHEMA。TABLES不可靠(使用InnoDB, MySQL 5.1.44),每次运行时给出不同的行数,即使是在静态表上。这里有一种生成大型SQL语句的相对hack(但是灵活/适应性强)的方法,您可以将其粘贴到新的查询中,而不需要安装Ruby宝石之类的东西。
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)
还有一个选择:对于非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表,计算所有行会花费更多的时间。
如果需要精确的数字,请使用下面的ruby脚本。你需要Ruby和RubyGems。
安装以下Gems:
$> 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.
下面的查询生成一个(另一个)查询,该查询将从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);';
推荐文章
- 如何关闭mysql密码验证?
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- MySQL区分大小写查询
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- MySQL数据库表中的最大记录数
- 原则-如何打印出真正的sql,而不仅仅是准备好的语句?
- PHP/MySQL插入一行然后获取id
- 我如何循环通过一组记录在SQL Server?
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询,还可以获得总行数
- 当恢复sql时,psql无效命令\N