# Mysql 字典表

Mysql 元数据

---

Mysql元数据

概念

数据库是数据的结构化集合。元数据是“有关数据的数据”，除了表之外的都是元数据，都是基表，比如库表属性、状态信息、权限等。

information_schema库

infromation_schema库中的表，保存的是Mysql的元数据，是一个虚拟数据库，物理上并不存在相关的目录和文件。该库中包含的元数据表如下。

• ​ CHARACTER_SETS：保存所有Mysql可用的字符集。相当于命令SHOW CHARACTER SET

SELECT * from information_schema.CHARACTER_SETS  #查看Mysql可用的字符集

information_schema.COLUMNS：保存的是所有数据库的列信息

SELECT * from information_schema.`COLUMNS` #查看所有数据库的列信息

TABLE_CATALOG
TABLE_SCHEMA #库名
TABLE_NAME #表名
COLUMN_NAME #列名
ORDINAL_POSITION #应该是该列在该表中的顺序
COLUMN_DEFAULT #列的默认值
IS_NULLABLE #是否可以为NULL
DATA_TYPE #数据类型
CHARACTER_MAXIMUM_LENGTH #数据的长度
CHARACTER_OCTET_LENGTH #数据的存储长度
NUMERIC_PRECISION
NUMERIC_SCALE
CHARACTER_SET_NAME #列的字符编码
COLLATION_NAME
COLUMN_TYPE #列的类型，例如varchar(20)
COLUMN_KEY #如果等于PRI，表示是主键
EXTRA #定义列的时候的其他信息，例如自增，主键
PRIVILEGES #操作权限有：select,insert,update,references （ 官方说明 ）
COLUMN_COMMENT #列的备注

• REFERENTIAL_CONSTRAINTS

SELECT * from information_schema.REFERENTIAL_CONSTRAINTS #查看数据库中的所有外键信息

• SHEMATA

SELECT * from information_schema.SCHEMATA #查看所有的数据库

• STATISTICS

SELECT * from information_schema.STATISTICS #查看所有的索引

information_schema.TABLES

SELECT * from information_schema.`TABLES` #查看所有的数据库中的表信息

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME  #表名
TABLE_TYPE #表的类型
ENGINE #表的存储引擎
VERSION #表的版本
ROW_FORMAT
TABLE_ROWS #表的行数
AVG_ROW_LENGTH #平均一行的长度
DATA_LENGTH #数据长度
MAX_DATA_LENGTH #最大一行的数据长度
INDEX_LENGTH #索引的长度
DATA_FREE
AUTO_INCREMENT #自增到哪个数
CREATE_TIME #创建时间
UPDATE_TIME #最后修改表结构的时间
CHECK_TIME
TABLE_COLLATION #表的编码
CHECKSUM
CREATE_OPTIONS
TABLE_COMMENT #表注释

实际应用

统计库里表的个数

SELECT
	table_schema,
	count(table_name)
FROM
	information_schema. TABLES
GROUP BY
	TABLE_SCHEMA

统计所有非系统表

SELECT
	table_schema,
	table_name,
	ENGINE
FROM
	information_schema.`TABLES`
WHERE
	table_schema NOT IN (
		'mysql',
		'information_schema',
		'performance_schema'
	)

统计每个库总的数据量

数据量计算，表的行数乘以行的长度

SELECT table_schema,SUM(table_rows * AVG_ROW_LENGTH + INDEX_LENGTH)/1024/1024 AS "total_mb"
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','mysql','performance_schema')
GROUP BY table_schema
ORDER BY total_mb DESC;