作者:周祥兴
网名:无为
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;