当前位置：文江博客话题详情

Mysql - 联合显示表？

发布于 2024-10-11 18:07:17 字数 38 浏览 2 评论 0原文

有没有办法使用 UNION 或 JOIN 列出所有表和列名称？

原文

分享到QQ

分享到微博

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

发布评论

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

顾挽 2024-10-18 18:07:17

如果您想要一个模式中的所有表和列，则无需使用 UNION 和 BIND，只需连接

information_schema.columns
information_schema.tables

中的数据即可。有关两者的详细信息，请参见：

http://dev.mysql.com /doc/refman/5.0/en/information-schema.html

一个可以实现您的最低目标的示例查询是：

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS

但同样，可能需要根据表名连接两者 - - 取决于您的具体目标。

如果您只想要名称中包含 UNION/BIND 的表和名称中包含 UNION/BIND 的列，则可以执行两个简单的查询

SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%UNION%' or TABLE_NAME LIKE '%BIND%'

：

SELECT TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%UNION%' or TABLE_NAME LIKE '%BIND%'

If you want all tables and columns in a schema, no need to use UNION and BIND, just joining the data in

information_schema.columns
information_schema.tables

will do the trick. See details on both at:

http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

An example query that would achieve the minimum of what appears to be your goal would be:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS

But again, joining the two based on table name might be needed -- depends on your precise goal.

If you just want the tables with UNION/BIND in the names and the columns with UNION/BIND in the names, two simple queries to do that would be:

SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%UNION%' or TABLE_NAME LIKE '%BIND%'

and

SELECT TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%UNION%' or TABLE_NAME LIKE '%BIND%'

回复收藏 0 原文

注定孤独终老 2024-10-18 18:07:17

您可以使用 INFORMATION_SCHEMA。下面的查询会很有用：

SELECT 'COLUMN' as Match_Type, 
       column_name as MATCH_NAME, 
       table_name, 
       table_schema
 FROM INFORMATION_sCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%UNION%' 
   OR COLUMN_NAME LIKE '%BIND%'
UNION ALL
SELECT 'TABLE' as Match_Type, 
        table_name  as MATCH_NAME, 
        table_name, 
        table_schema
 FROM INFORMATION_sCHEMA.TABLES
WHERE TABLE_NAME LIKE '%UNION%'
   OR TABLE_NAME LIKE '%BIND%'

You can use INFORMATION_SCHEMA. The query below would be useful:

SELECT 'COLUMN' as Match_Type, 
       column_name as MATCH_NAME, 
       table_name, 
       table_schema
 FROM INFORMATION_sCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%UNION%' 
   OR COLUMN_NAME LIKE '%BIND%'
UNION ALL
SELECT 'TABLE' as Match_Type, 
        table_name  as MATCH_NAME, 
        table_name, 
        table_schema
 FROM INFORMATION_sCHEMA.TABLES
WHERE TABLE_NAME LIKE '%UNION%'
   OR TABLE_NAME LIKE '%BIND%'

回复收藏 0 原文

~没有更多了~