mysql - information_schema.tables视图

查看information_schema.tables:

DESC information_schema.TABLES
TABLE_SCHEMA    ---->库名
TABLE_NAME      ---->表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数
AVG_ROW_LENGTH  ---->表中行的平均行(字节)
INDEX_LENGTH    ---->索引的占用空间大小(字节)

使用案例:

查询整个数据库中所有库和所对应的表信息:

SELECT table_schema,GROUP_CONCAT(table_name)
FROM  information_schema.tables
GROUP BY table_schema;

统计所有库下的表个数:

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

查询所有innodb引擎的表及所在的库:

SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`
WHERE ENGINE=‘innodb‘;

统计world数据库下每张表的磁盘空间占用:

SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")  AS size_KB
FROM information_schema.tables WHERE TABLE_SCHEMA=‘world‘;

统计所有数据库的总的磁盘空间占用:

SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;
mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,‘ KB‘) AS Total_KB FROM information_schema.tables GROUP BY table_schema;"

生成整个数据库下的所有表的单独备份语句:

# 模板语句:
mysqldump
-uroot -p123 world city >/tmp/world_city.sql
# 实例1: SELECT CONCAT(
"mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" ) FROM information_schema.tables WHERE table_schema NOT IN(‘information_schema‘,‘performance_schema‘,‘sys‘) INTO OUTFILE ‘/tmp/bak.sh‘ ; # PS:字符串拼接函数 CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )

107张表,都需要执行以下2条语句:

# 107张表,需要执行的2条语句:
ALTER TABLE world.city DISCARD TABLESPACE; ALTER TABLE world.city IMPORT TABLESPACE;
# 实现第一条语句: SELECT CONCAT(
"alter table ",table_schema,".",table_name," discard tablespace") FROM information_schema.tables WHERE table_schema=‘world‘ INTO OUTFILE ‘/tmp/dis.sql‘;

 

mysql - information_schema.tables视图

上一篇:MySQL多版本并发控制——MVCC机制分析


下一篇:mysql - select + 函数操作