如何从MySQL的information_schema获取表架构、表名及列总数？

需求是从MySQL的information_schema中提取所有模式（table_schema）、表名（table_name）以及每张表的总列数。以下是满足需求的SQL语句：

SELECT CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME) as entity, count(c.COLUMN_NAME)
FROM information_schema.TABLES t JOIN information_schema.STATISTICS s on
s.TABLE_CATALOG = t.TABLE_CATALOG
AND s.TABLE_SCHEMA = t.TABLE_SCHEMA
AND s.TABLE_NAME = t.TABLE_NAME
AND s.INDEX_NAME  = 'PRIMARY'
AND s.COLUMN_NAME = 'PK'
AND s.SEQ_IN_INDEX  = 1
JOIN information_schema.`COLUMNS` c on
c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND c.COLUMN_NAME = 'VERSION'
WHERE 1=1 
AND t.TABLE_NAME LIKE '%\_%'
AND t.TABLE_SCHEMA LIKE '%\_%'
AND BINARY(t.TABLE_NAME) != LOWER(t.TABLE_NAME)

关键说明 #

• entity字段是模式名与表名的拼接结果，方便识别唯一表
• 关联STATISTICS表是为了筛选出拥有名为PK的主键列且该列为主键第一个字段的表
• 关联COLUMNS表是为了筛选出包含VERSION列的表
• 最后的WHERE子句添加了额外过滤：仅保留表名和模式名包含下划线、且表名不全为小写的记录

内容的提问来源于stack exchange，提问作者vasu