是否有一种方法可以获得MySQL数据库中所有表的行计数,而无需在每个表上运行SELECT count() ?
当前回答
基于上面@Nathan的回答,但不需要“删除最终的联合”,并带有对输出进行排序的选项,我使用以下SQL。它生成另一个SQL语句,然后运行:
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
您确实需要一个足够大的group_concat_max_len服务器变量的值,但是从MariaDb 10.2.4开始,它应该默认为1M。
其他回答
对于这个估算问题,有一点hack/workaround。
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;
Auto Increment将始终是+1 *(表数)行,但即使有4000个表和300万行,这也是99.9%的准确性。比估计的行数好多了。
这样做的好处是,performance_schema中返回的行计数也会被擦除,因为greatest对null无效。但是,如果没有带有自动递增功能的表,这可能是个问题。
大多数其他答案建议使用INFORMATION_SCHEMA。但是在MySQL 8中它已经不存在了。行数已移动到INFORMATION_SCHEMA.INNODB_TABLESTATS。
你可以用以下方法查询:
SELECT *
FROM information_schema.INNODB_TABLESTATS
WHERE NAME LIKE "YOUR_DB_NAME/%"
ORDER BY NUM_ROWS DESC
请注意,这仍然是一个近似值,像以前一样,不是一个确切的数字。
SELECT TABLE_NAME,SUM(TABLE_ROWS)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_db'
GROUP BY TABLE_NAME;
这就是你所需要的。
我不知道为什么这么难,但这就是生活。 下面是执行实际计数的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
这是我如何使用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.";
推荐文章
- 将值从同一表中的一列复制到另一列
- GROUP BY with MAX(DATE)
- 删除id与其他表不匹配的sql行
- 等价的限制和偏移SQL Server?
- MySQL CPU使用率高
- INT和VARCHAR主键之间有真正的性能差异吗?
- 拒绝访问;您需要(至少一个)SUPER特权来执行此操作
- 为什么我不能在DELETE语句中使用别名?
- 在SQL Server Management Studio中保存带有标题的结果
- 从存储引擎得到错误28
- "where 1=1"语句
- 如何选择一个记录和更新它,与一个单一的查询集在Django?
- 多语句表值函数vs内联表值函数
- 如何从Oracle的表中获取列名?
- 可能做MySQL外键的两个可能的表之一?