[MySQL分享]--查看MySQL数据字典SQL（包含索引）

---

 版权声明：本文档可以转载，须署名原作者
作者：周祥兴
网名：无为
QQ：490073687 
Email：zhou.xiangxing210@163.com

---

查看单表数据结构：show create table t\G;

查看单表上的索引：show index from t;show keys from t;

下面通过mysql自带的系统表，提取整个库的数据字典信息：

-- 表结构
SELECT  a.TABLE_NAME "表", 
	a.COLUMN_NAME "列", 
	a.COLUMN_TYPE "类型", 	
	a.COLUMN_DEFAULT "默认值", 
	a.IS_NULLABLE "是否为空", 
	a.CHARACTER_SET_NAME "表字符集",
	a.COLLATION_NAME  "校验字符集",
	CONCAT(a.COLUMN_COMMENT,a.COLUMN_KEY, a.EXTRA) "列备注",
	b.TABLE_COMMENT "表备注" ,
	b.ENGINE "引擎"
FROM information_schema.COLUMNS a,information_schema.TABLES b 
WHERE a.TABLE_SCHEMA=b.TABLE_SCHEMA 
AND a.TABLE_SCHEMA='test'
AND a.TABLE_NAME=b.TABLE_NAME;

-- 索引信息
SELECT
        TABLE_SCHEMA,
        TABLE_NAME,
        NON_UNIQUE,
        INDEX_NAME,
        SEQ_IN_INDEX,
        COLUMN_NAME,
        INDEX_TYPE,
        CONCAT(COMMENT,INDEX_COMMENT)    INDEX_COMMENT
 FROM  INFORMATION_SCHEMA.STATISTICS
WHERE  TABLE_SCHEMA = 'test'
ORDER BY TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX;

--注意组合索引的字段顺序，以及约束。

-- 索引信息 方便生成alter语句
SELECT
  a.TABLE_SCHEMA,
  a.TABLE_NAME,
  a.INDEX_NAME,
  MAX(a.NON_UNIQUE)    NON_UNIQUE,
  MAX(a.INDEX_TYPE)    INDEX_TYPE,
  MAX(a.INDEX_COMMENT)    INDEX_COMMENT,
  GROUP_CONCAT(a.COLUMN_NAME)    COLUMN_NAME
FROM (SELECT
        TABLE_SCHEMA,
        TABLE_NAME,
        NON_UNIQUE,
        INDEX_NAME,
        SEQ_IN_INDEX,
        COLUMN_NAME,
        INDEX_TYPE,
        CONCAT(COMMENT,INDEX_COMMENT)    INDEX_COMMENT
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = 'test'
      ORDER BY TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX) a
GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.INDEX_NAME;