9. information_schema应用

1. 查询所有库及表信息

select table_schema,table_name from information_schema.tables;

每天都要查看的信息，核对库和表

2. 统计每个库的表的个数

select table_schema,count(table_name) from information_schema.tables group by table_schema;

3. 统计school库下的所有表的行数信息

select table_name,table_rows from information_schema.tables where table_schema='school';

4. 统计每个数据库的数据量

select table_schema,sum(avg_row_length*table_rows+index_length)/1024 as size_kb
from information_schema.tables
group by table_schema;

5. 统计总数据量

select sum(avg_row_length*table_rows+index_length)/1024 as size_kb
fromo information_schema.tables;

6. 查询所有innodb的表

select * from information_schema.tables where engine='innodb';

7. 把zabbix库下的所有表引擎（innodb）替换为Tokudb

alter table zabbix.t1 engine=tokudb;

select concat("alter table ",table_schema,".",table_name," engine=myisam;") 
from information_schema.tables
where table_schema='school';

8. 使用concat导出sql文件

MySQL5.6以后，要把查询的信息导出到外部，需要设置安全路径

vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
#重启mysql生效

alter table world.city discard tablespace;
alter table world.city import tablespace;

导出sql文件

select concat("alter table ",table_schema,".",table_name," discard tablespace;") 
from information_schema.tables 
where table_schema='world' into outfile '/tmp/discard.sql';

select concat("alter table ",table_schema,".",table_name," import tablespace;") 
from information_schema.tables 
where table_schema='world' into outfile '/tmp/discard.sql';

9. show语句整理

show databases;
show tables;
show tables from world;
show table status from world\G
show table status from world like 'city'\G
show create database world;
show create table world.city;
desc city;
shwo charset;       #查看所有字符集
show collation;     #查看所有校对规则

show engines;       #查看所有存储引擎
show status;
show variables;
show processlist;   #查看所有连接情况
shwo engine innodb status\G     #查看innodb存储详细状态

select current_user();
select user();

---

最后更新: 2022-02-20 08:44:07