information_schema.tables 基礎(chǔ)應(yīng)用
tables的結(jié)構(gòu)
mysql> use information_schema;
mysql> desc tables;
TABLE_SCHEMA? ? # 表所在的庫
TABLE_NAME? ? ? # 表名
ENGINE? ? ? ? ? # 表的存儲(chǔ)引擎
TABLE_ROWS? ? ? # 表的數(shù)據(jù)行(有可能不準(zhǔn))
AVG_ROW_LENGTH? # 平均行長度
DATA_LENGTH? ? # 數(shù)據(jù)的存儲(chǔ)大?。ㄓ锌赡懿粶?zhǔn)確）
INDEX_LENGTH? ? # 索引的存儲(chǔ)大小
DATA_FREE? ? ? # 碎片的數(shù)量
CREATE_TIME? ? # 創(chuàng)建時(shí)間
UPDATE_TIME? ? # 更新時(shí)間
TABLE_COMMENT? # 表注釋
使用tables視圖
--- 1. 統(tǒng)計(jì)每個(gè)庫的表個(gè)數(shù)和表名
select table_schema,count(table_name),group_concat(table_name)
from information_schema.tables
group by table_schema;
--- 2. 資產(chǎn)統(tǒng)計(jì)：統(tǒng)計(jì)全庫數(shù)據(jù)量
SELECT SUM(table_rows*AVG_ROW_LENGTH+index_length)/1024/1024? AS total_mb
FROM information_schema.tables;
--- 3. 統(tǒng)計(jì)每個(gè)業(yè)務(wù)庫的數(shù)據(jù)量，表個(gè)數(shù)，表名列表
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. 查詢業(yè)務(wù)庫中，非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';
查詢業(yè)務(wù)庫中，非InnoDB的表批量替換為InnoDB
修改配置文件
vim /etc/my.cnf
#添加配置并重啟
secure-file-priv=/tmp
查看所有非innodb表,除系統(tǒng)庫
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';
