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

最新推荐文章于 2024-02-23 16:20:38 发布

jerry-89

最新推荐文章于 2024-02-23 16:20:38 发布

阅读量973

点赞数 1

分类专栏： MYSQL主从文章标签： sql 数据库

本文链接：https://blog.csdn.net/eagle89/article/details/133947967

版权

MYSQL主从专栏收录该内容

154 篇文章 18 订阅

订阅专栏

#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;