mysql - MySQL显示总计和计数查询中的行数不同

Question

这个问题已经有答案了:

Why is the estimated rows count very different in phpmyadmin results? (3 个回答)

已关闭 4 年前。

我对 MySQL 中的行数感到困惑。 phpMyAdmin 和 count 查询返回的总行数不同。

我的表结构是InnoDB .

Showing rows 0 - 24 (655537 total, Query took 0.1527 seconds.)

这是浏览表格时的结果。

我的计数查询是

SELECT count(*) FROM `table_name`

这将返回为 602030

Answer 1

当您使用 phpmyadmin 或 heidiSQL 等工具浏览时，会执行如下查询:

SHOW TABLE STATUS LIKE 'table';

这个值不准确，如果运行多次，它总是会给出不同的结果。

相反的查询:

select count(*) from table

实际上正在计算记录，并给出正确的结果。

正如mysql文档中提到的https://dev.mysql.com/doc/refman/8.0/en/show-table-status.html :

Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.