mysql 查询表字段名,注释，以及sql拼接查询出的内容

小鱼儿 2024-03-27 18:53 99阅读 0赞

\#sql查询字段名，注释操作拼接

\#查询字段名和注释  
select COLUMN\_NAME,COLUMN\_COMMENT from information\_schema.COLUMNS where table\_name = '表名' and table\_schema = '库名' order by ordinal\_position  
\#查询整个内容  
select \* from information\_schema.COLUMNS where table\_name = '表名' and table\_schema = '库名' order by ordinal\_position

\#拼接 group\_concat(field，SEPARATOR '拼接字符')  
select group\_concat(COLUMN\_NAME SEPARATOR ';'),group\_concat(COLUMN\_COMMENT SEPARATOR ';') from information\_schema.COLUMNS where table\_name = '表名' and table\_schema = '库名' order by ordinal\_position

\#按顺序排字段拼接 group\_concat(field order by field,SEPARATOR '字符')  
select group\_concat(COLUMN\_NAME order by ordinal\_position SEPARATOR ';'),group\_concat(COLUMN\_COMMENT ORDER BY ordinal\_position SEPARATOR ';') from information\_schema.COLUMNS where table\_name = '表名' and table\_schema = '库名' order by ordinal\_position

\#整个库下的所有表  
select TABLE\_NAME,group\_concat(COLUMN\_NAME order by ordinal\_position SEPARATOR ';'),group\_concat(COLUMN\_COMMENT ORDER BY ordinal\_position SEPARATOR ';') from information\_schema.COLUMNS where table\_schema = '库名' GROUP BY TABLE\_NAME

\#整个服务器下的所有库中表，每个表的字段和注释的拼接  
select TABLE\_SCHEMA,TABLE\_NAME,group\_concat(COLUMN\_NAME order by ordinal\_position SEPARATOR ';'),group\_concat(COLUMN\_COMMENT ORDER BY ordinal\_position SEPARATOR ';') from information\_schema.COLUMNS GROUP BY TABLE\_NAME,TABLE\_SCHEMA ORDER BY TABLE\_SCHEMA

**\#拼接数据库名和表名**  
select CONCAT(TABLE\_SCHEMA,'.',TABLE\_NAME) from information\_schema.COLUMNS GROUP BY TABLE\_NAME,TABLE\_SCHEMA ORDER BY TABLE\_SCHEMA;