MySQL 查询返回、Schema_Name、Table_NAme、Count、Size_of_Table、Primary_Key 和 Auto_Increment_Key

发布于 2024-10-22 06:32:17 字数 790 浏览 1 评论 0原文

我正在尝试解决另一个棘手的 MySQL 查询。我需要一个查询，该查询将在单个命令中返回以下内容：

+----------+------------------------------+-----------+-------------+----------------+--------------------+
| Database | Table_Name                   | Count     | Size        | Primary-Key    | Auto_Increment_Key |
+----------+------------------------------+-----------+-------------+----------------+--------------------+
| mydb     | account_length               |      2408 | 0.04 Mb     | account_id     | account_id         |
| mydb     | account_log                  |      1225 | 0.09 Mb     | log_id         | NULL               |

我对 MySQL 仍然是新手。我已经尝试了针对 information_schema 的各种查询，但无法获得返回我需要的数据的查询。

该查询将简单地获取表的行数、它的大小（以 MB 为单位）、它的主键列和它的 auto_increment 键列。

我很感激任何帮助。

谢谢

原文

I have another tough MySQL query that I am trying to work out. I need a query that will return the following in a single command:

+----------+------------------------------+-----------+-------------+----------------+--------------------+
| Database | Table_Name                   | Count     | Size        | Primary-Key    | Auto_Increment_Key |
+----------+------------------------------+-----------+-------------+----------------+--------------------+
| mydb     | account_length               |      2408 | 0.04 Mb     | account_id     | account_id         |
| mydb     | account_log                  |      1225 | 0.09 Mb     | log_id         | NULL               |

I am still new to MySQL. I have tried various queries against the information_schema, but have not been able to get one that returns the data I need.

The query would simply get a table's row count, it's size in MB, it's primary-key column and it's auto_increment key column.

I appreciate any help.

Thanks

分享到QQ

分享到微博

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

发布评论

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

燃情 2024-10-29 06:32:17

像这样的事情会帮助你：

SELECT t.table_schema, 
       t.table_name, 
       Round(( t.data_length + t.index_length ) / 1048576, 2) AS sizemb, 
       c1.column_name                                         AS primarycol, 
       c2.column_name                                         AS 
       autoincrementcol 
FROM   information_schema.tables t 
       LEFT JOIN information_schema.columns c1 
         ON t.table_schema = c1.table_schema 
            AND t.table_name = c1.table_name 
            AND c1.column_key = 'PRI' 
       LEFT JOIN information_schema.columns c2 
         ON t.table_schema = c2.table_schema 
            AND t.table_name = c2.table_name 
            AND c2.extra = 'auto_increment' 
WHERE  t.table_schema = '............'

Something like this will help you:

SELECT t.table_schema, 
       t.table_name, 
       Round(( t.data_length + t.index_length ) / 1048576, 2) AS sizemb, 
       c1.column_name                                         AS primarycol, 
       c2.column_name                                         AS 
       autoincrementcol 
FROM   information_schema.tables t 
       LEFT JOIN information_schema.columns c1 
         ON t.table_schema = c1.table_schema 
            AND t.table_name = c1.table_name 
            AND c1.column_key = 'PRI' 
       LEFT JOIN information_schema.columns c2 
         ON t.table_schema = c2.table_schema 
            AND t.table_name = c2.table_name 
            AND c2.extra = 'auto_increment' 
WHERE  t.table_schema = '............'

回复收藏 0 原文

~没有更多了~