查询统计所有表中的准确记录数

发布于 2024-09-26 11:45:37 字数 385 浏览 13 评论 0原文

我您好，

我正在尝试计算 MySQL 数据库中的每个表其中有多少记录。

我的所有表恰好都在 InnoDB 中，并且这个查询

SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'myschema';

远远超出了估计（有 +846 条记录，它告诉我只有 +-400 条记录））

有没有办法通过类似的查询来计算更准确的行数？查询运行多长时间并不重要。（PS不使用php或类似语言）

原文

IHello,

I'm trying to count for each table in my MySQL database how many records there are in it.

All of my tables happen to be in InnoDB and this query

SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'myschema';

is way much too much of an estimate (There are +846 records and it tells me there are only +-400)

Is there a way to count a more exact number of rows with a similar query? It doen't matter how long the query takes tot run. (P.S. not using php or a similar language)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

星軌x 2024-10-03 11:45:37

如果查询的长度并不重要，那么对数据库中的每个表执行以下操作：

select Count(*) from MyTable

If the length of the query doesn't matter then what about doing the following for each table in your database:

select Count(*) from MyTable

回复收藏 0 原文

你的背包 2024-10-03 11:45:37

如果您要使用系统视图，那么您需要确保数据库统计信息已更新，否则系统架构视图（例如 information_schema.x）中的行数可能不正确。

使用

select count(*) yourtable;

上面答案中所述的方法来获取

Analyze table yourtable;

更新统计数据的确切数字，然后检查系统视图中的计数，它们应该在该点匹配行数。

有关更多详细信息以及对整个数据库执行此操作的方法，请参阅此网站：

MySQL参考手册

If your are going to use the system views then you need to make sure your database statiscs are updated or the counts of rows will potentially be incorrect in the system schema views such as information_schema.x.

Use the

select count(*) yourtable;

as stated in the answer above to get the exact number

Analyze table yourtable;

to update the statisitcs then check the count in the system view they should match at ths point for number of rows.

see this website for more details and a way to do this for your entire database:

MySQL reference manual

回复收藏 0 原文

泪冰清 2024-10-03 11:45:37

正如此答案中发布的

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 
   'insert into #rowcount select ''?'', count(*) from ?'
select * from #rowcount
    order by tablename
drop table #rowcount

As posted in this Answer

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable 
   'insert into #rowcount select ''?'', count(*) from ?'
select * from #rowcount
    order by tablename
drop table #rowcount

回复收藏 0 原文

~没有更多了~