MySQL查询表位置和列注释等

; # 查询表在哪个数据库与注释
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COMMENT FROM information_schema.TABLES WHERE 1=1
# AND TABLE_SCHEMA = '数据库名'
AND TABLE_NAME = '表名'
# AND TABLE_COMMENT = '表注释'

; # 查询列在哪个表与注释
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, t.TABLE_COMMENT, COLUMN_NAME, COLUMN_COMMENT, COLUMN_TYPE, IS_NULLABLE
FROM information_schema.COLUMNS c JOIN information_schema.TABLES t ON t.TABLE_NAME = c.TABLE_NAME  WHERE 1=1
# AND TABLE_SCHEMA = '数据库名'
# AND TABLE_NAME = '表名'
AND c.COLUMN_NAME = '列名'
# AND c.COLUMN_COMMENT = '列注释'

; # 查询所有列名与注释并用逗号分隔
SELECT
  TABLE_SCHEMA, TABLE_NAME,
  group_concat(COLUMN_NAME SEPARATOR ', ') AS COLUMN_NAMES,

  CASE COLUMN_COMMENT
  WHEN '' THEN NULL
  ELSE group_concat(concat_ws(' AS ', COLUMN_NAME, COLUMN_COMMENT) SEPARATOR ', ')
  END                                      AS COLUMN_NAME_AS_COMMENTS,

  CASE COLUMN_COMMENT
  WHEN '' THEN NULL
  ELSE group_concat(COLUMN_COMMENT SEPARATOR ', ')
  END                                      AS COLUMN_COMMENTS
FROM information_schema.COLUMNS WHERE 1=1
AND TABLE_SCHEMA = 'test'
# AND TABLE_NAME = '表名'
GROUP BY TABLE_NAME