英文:
Get table_schema, Table_name and total number of columns from information_schema in mysql
问题
我需要从mysql的information_schema中获取table_schema、Table_name和列的总数（简而言之，所有模式中的所有表名，以及每个表包含的列数）。
如果有人方便的话，请告诉我。
英文:
I need to get table_schema, Table_name and total number of columns from information_schema in mysql (In short all the table names, with no.of columns each table is having in all schemas).
Let me know if there someone is having handy.
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)
答案1
得分: 2
你过于复杂化了它。
SELECT TABLE_NAME, TABLE_SCHEMA, COUNT(*) COLUMNS
FROM information_schema.COLUMNS
GROUP BY TABLE_NAME, TABLE_SCHEMA
你可以添加各种 WHERE 子句等内容。
而且，在StackOverflow上，解释一下你尝试过什么以及当你尝试时出现了什么问题是很有帮助的。
英文:
You're overcomplicating it.
SELECT TABLE_NAME, TABLE_SCHEMA, COUNT(*) COLUMNS
FROM information_schema.COLUMNS
GROUP BY TABLE_NAME, TABLE_SCHEMA
You can add all sorts of WHERE clauses etc to this.
And, in StackOverflow it's good to explain what you tried, and what went wrong when you tried it.
答案2
得分: -1
我找到了解决方案，如果有人在寻找类似的解决方案，
问题：获取mysql中跨数据库的所有表名及其列数：
选择CONCAT(t.TABLE_SCHEMA，'。'，t.TABLE_NAME)作为实体，
计数(c.COLUMN_NAME)从information_schema.TABLES t
加入information_schema.STATISTICS s
on s.TABLE_CATALOG = t.TABLE_CATALOG
并且s.TABLE_SCHEMA = t.TABLE_SCHEMA
并且s.TABLE_NAME = t.TABLE_NAME
并且s.INDEX_NAME = 'PRIMARY'
并且s.COLUMN_NAME = 'PK'
并且s.SEQ_IN_INDEX = 1
加入information_schema.`COLUMNS` c
on c.TABLE_CATALOG = t.TABLE_CATALOG并且c.TABLE_SCHEMA = t.TABLE_SCHEMA并且c.TABLE_NAME = t.TABLE_NAME WHERE 1=1 AND BINARY(t.TABLE_NAME) != LOWER(t.TABLE_NAME) GROUP BY t.TABLE_NAME, t.TABLE_SCHEMA;
英文:
I got the solution, if some one is looking for similar one,
Question: Get all the table names, and their column count across databases in mysql:
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 WHERE 1=1 AND BINARY(t.TABLE_NAME) != LOWER(t.TABLE_NAME) GROUP BY t.TABLE_NAME, t.TABLE_SCHEMA;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论