information_schema.tables 基础应用
tables的结构
mysql> use information_schema;
mysql> desc tables;
TABLE_SCHEMA # 表所在的库
TABLE_NAME # 表名
ENGINE # 表的存储引擎
TABLE_ROWS # 表的数据行(有可能不准)
AVG_ROW_LENGTH # 平均行长度
DATA_LENGTH # 数据的存储大小（有可能不准确）
INDEX_LENGTH # 索引的存储大小
DATA_FREE # 碎片的数量
CREATE_TIME # 创建时间
UPDATE_TIME # 更新时间
TABLE_COMMENT # 表注释
使用tables视图
--- 1. 统计每个库的表个数和表名
select table_schema,count(table_name),group_concat(table_name)
from information_schema.tables
group by table_schema;
--- 2. 资产统计：统计全库数据量
SELECT SUM(table_rows*AVG_ROW_LENGTH+index_length)/1024/1024 AS total_mb
FROM information_schema.tables;
--- 3. 统计每个业务库的数据量，表个数，表名列表
SELECT table_schema,
COUNT(table_name),
GROUP_CONCAT(table_name),
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','performance_schema','mysql')
GROUP BY table_schema;
--- 4. 查询业务库中，非InnoDB的表。
SELECT table_schema,table_name,ENGINE
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
AND ENGINE!='INNODB';
查询业务库中，非InnoDB的表批量替换为InnoDB
修改配置文件
vim /etc/my.cnf
#添加配置并重启
secure-file-priv=/tmp
查看所有非innodb表,除系统库
SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," ENGINE=INNODB;")
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
AND ENGINE!='INNODB' INTO OUTFILE '/tmp/alter.sql';
