如何在MySQL中的所有表/视图中获取所有列的计数

发布于 2025-02-05 17:25:34 字数 612 浏览 3 评论 0原文

如何在给定的MySQL数据库中的所有视图/表中获取所有列的计数？

例如，给定的架构类似于以下架构：

table_1	table_2	table_1_col_1
table_2_col_1	view_1	view_1_col_1
table_1_col_2

我得到：

table_name	column_count_count_count
table_1	2
table_2 table_1	1
view_1 1	1

原文

How do I get the count of all the columns in all views/tables in a given mysql database?

For example given a schema similar to:

TABLE_1	TABLE_2	VIEW_1
TABLE_1_COL_1	TABLE_2_COL_1	VIEW_1_COL_1
TABLE_1_COL_2

I'd get:

table_name	column_count
TABLE_1	2
TABLE_2	1
VIEW_1	1

分享到QQ

分享到微博

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

发布评论

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

内心荒芜 2025-02-12 17:25:35

您可以使用内置的 Information_schema 表来检索以下操作：

SELECT information_schema.columns.table_name, count(*) as column_count
FROM information_schema.columns
WHERE information_schema.columns.TABLE_SCHEMA = "YOUR_DB_NAME"
GROUP BY information_schema.columns.TABLE_NAME;

此外，您可以通过添加到Where子句来过滤；例如，您只能过滤具有前缀 v _ 的表/视图，通过将其添加到Where子句如下：

SELECT information_schema.columns.table_name, count(*) as column_count
FROM information_schema.columns
WHERE information_schema.columns.TABLE_NAME LIKE 'v\_%' AND
information_schema.columns.TABLE_SCHEMA = "YOUR_DB_NAME"
GROUP BY information_schema.columns.TABLE_NAME ;

You can use the built-in information_schema table to retrieve this:

SELECT information_schema.columns.table_name, count(*) as column_count
FROM information_schema.columns
WHERE information_schema.columns.TABLE_SCHEMA = "YOUR_DB_NAME"
GROUP BY information_schema.columns.TABLE_NAME;

Furthermore you can filter by adding to the where clause; for example you can filter only the tables/views that have the prefix v_ by adding to the where clause as follows:

SELECT information_schema.columns.table_name, count(*) as column_count
FROM information_schema.columns
WHERE information_schema.columns.TABLE_NAME LIKE 'v\_%' AND
information_schema.columns.TABLE_SCHEMA = "YOUR_DB_NAME"
GROUP BY information_schema.columns.TABLE_NAME ;

回复收藏 0 原文

~没有更多了~