MySQL 中的 information_schema 和 mysql.user

从MySQL 5 开始 ,  你可以看到多了一个系统数据库 information_schema information_schema 数据库跟 performance_schema 一样，都是 MySQL 自带的信息数据库。其中 performance_schema 用于性能分析，而 information_schema 用于存储数据库元数据(关于数据的数据)，例如数据库名、表名、列的数据类型、访问权限等。information_schema 存贮了其他所有数据库的信息。 information_schema是一个虚拟数据库，并不物理存在。Mysql的INFORMATION_SCHEMA数据库包含了一些表和视图，提供了访问数据库元数据的方式，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在information_schema表里面. 让我们来看看几个使用这个数据库的例子

查询慢查询

select CONCAT('KILL ',ID,';') from information_schema.processlist WHERE STATE = 'Sending data';

查询用户下的连接信息

select `USER`,COUNT(`USER`) AS CNT from information_schema.PROCESSLIST GROUP BY `USER` ORDER BY CNT DESC;

查询当前ip的连接信息

select substring_index(host,':',1) 'ip',count(*) from information_schema.processlist group by substring_index(host,':',1);

查看数据库下连接信息 (kill id)

select id,host,time,state,info from information_schema.processlist where COMMAND != 'Sleep' and DB='数据库名' and info not like '%information_schema.processlist%';

查看表下的索引信息

SELECT TABLE_NAME,INDEX_NAME,GROUP_CONCAT(DISTINCT COLUMN_NAME) index_column,INDEX_TYPE,CASE NON_UNIQUE WHEN 1 THEN '否' ELSE '是' END '唯一索引' FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = '数据库名' and INDEX_NAME!='PRIMARY' and TABLE_NAME='表名' GROUP BY TABLE_NAME,INDEX_NAME;

统计某个数据库中有多少张表

SELECT count(*) TABLES, table_schema FROM information_schema.TABLES where table_schema = '数据库名称' GROUP BY table_schema;

查看数据库各个表数据占用空间大小,各个表的数据条数

SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') as data FROM information_schema.tables WHERE TABLE_SCHEMA='数据库名称' ORDER BY DATA_LENGTH+INDEX_LENGTH desc;

查看数据库中各个表字段个数

SELECT TABLE_NAME,count(TABLE_NAME) field_num from information_schema.COLUMNS where TABLE_SCHEMA='数据库名称' GROUP BY TABLE_NAME;

查询数据库dj214中表数据超过 1000 行的表

select concat(table_schema,'.',table_name) as table_name,table_rows from information_schema.tables where table_rows > 1000  and table_schema = 'dj214' order by table_rows desc;  

查询数据库dj214 中所有没有主键的表

SELECT CONCAT(t.table_schema,".",t.table_name) as table_name  
FROM information_schema.TABLES t  
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc  
ON t.table_schema = tc.table_schema  
AND t.table_name = tc.table_name  
AND tc.constraint_type = 'PRIMARY KEY'  
WHERE tc.constraint_name IS NULL  
AND t.table_type = 'BASE TABLE'  AND t.table_schema = 'dj214' ; 

查询所有数据库中10张最大表

SELECT concat(table_schema,'.',table_name) '表名称', concat(round(data_length/(1024*1024),2),'M') '表大小'  
FROM information_schema.TABLES ORDER BY data_length DESC LIMIT 10;

查看MYSQL数据库下所有的数据库

SELECT SCHEMA_NAME AS 'database' FROM INFORMATION_SCHEMA.SCHEMATA LIMIT 0 , 30 

列出指定数据库中的所有表名称

SELECT table_name, table_type, engine FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'dj214' AND table_type='BASE TABLE'

SELECT table_name,table_type,engine FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db' and table_name like 'bms_%' AND table_type='BASE TABLE'

列出指定数据库下指定表的表结构

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'systemlog' AND table_schema = 'dj214'

SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'systemlog'

SELECT table_name FROM information_schema.columns 
WHERE table_name = 'systemlog' AND table_schema = 'dj214' AND column_name = 'operator'

一段MYSQL存储过程 [ 删除指定库中所有的空表 ]

	begin
	/*局部变量的定义,默认值为空 */
	declare tmpName varchar(200) default '' ;
	
	/*定义游标*/
    DECLARE reslutList Cursor FOR select table_name from information_schema.tables where table_rows <1 and table_schema = 'sz8_news' order by table_rows desc;
	declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;
	OPEN reslutList;/*打开游标*/
	FETCH reslutList into tmpname; -- 取数据
	/* 循环体 */
WHILE ( tmpname is not null) DO
	 set @sql = concat('drop table sz8_news.',tmpname,";");
	 PREPARE stmt1 FROM @sql ;
	 EXECUTE stmt1 ;
	 DEALLOCATE PREPARE stmt1;
	  /*游标向下走一步*/
	 FETCH reslutList INTO tmpname;
END WHILE;
CLOSE reslutList; /*关闭游标*/
end

一段 MYSQL存储过程 [ 删除指定库下所有表中的空列，即表中的任何一条记录该列都没有值 ]

BEGIN
	DECLARE done INT DEFAULT 0;
	DECLARE cTbl varchar(64);
	DECLARE cCol varchar(64);
	DECLARE cur1 CURSOR FOR
	select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS 
	where TABLE_SCHEMA='sz8_news' and IS_NULLABLE='YES' order by TABLE_NAME;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	set @sqlDrop='';
	OPEN cur1;
	FETCH cur1 INTO cTbl, cCol;/*得到表名及列名*/
WHILE done = 0 DO
	set @x=0;
	/*主要改进了这里把空值也纳入判断条件中去，即如果字段为null或空*/
	set @sql=concat('select 1 into @x from ',cTbl,' where ',cCol,' is not null and ',cCol,' !=""  limit 1');
	PREPARE stmt1 FROM @sql;
	EXECUTE stmt1;
	DEALLOCATE PREPARE stmt1;
	if @x=0 then
	    set @sqlDrop=concat('alter table `',cTbl,'` drop COLUMN`',cCol,'`;');
	    PREPARE stmt1 FROM @sqlDrop;
	    EXECUTE stmt1;
	    DEALLOCATE PREPARE stmt1;
	end if ;
	set done = 0;
	FETCH cur1 INTO cTbl, cCol;
END WHILE;
CLOSE cur1;
END

定期清理释放空间

1.查询数据库空间碎片：

select table_name,data_free,engine from information_schema.tables where table_schema='yourdatabase' and data_free>0;

2.对数据表优化：

optimize table `table_name`;

每当MySQL从你的列表中删除了一行内容，该段空间就会被留空。而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大。

当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分。如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用。

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。注意，在OPTIMIZE TABLE运行过程中，MySQL会锁定表。

mysql中count(*)和information_schema.tables结果值不同

mysql官方文档说：针对 MyISAM引擎的表，行数是确定的值；

但针对InnoDB引擎来说（我们平常的库都是用这个引擎），行数就是个大概值，误差最大可能会差距在40%-50%的，所以还是用count（*）统计其真实行数。

mysql.user

MySQL是通过权限表来控制用户对数据库访问的，权限表存放在mysql数据库中，主要的权限表有以下几个：user,db,host,table_priv,columns_priv和procs_priv，先带你了解的是user表。

mysql中所有的用户都是存放在user表中的，这些字段可以分为4类：(mysql 5.7为例)用户列.权限列.安全列.资源控制列.

用户列

• Host：主机名，双主键之一，值为%时表示匹配所有主机。
• User：用户名，双主键之一。Password：密码名。

权限列

权限列决定了用户的权限，描述了用户在全局范围内允许对数据库和数据库表进行的操作，字段类型都是枚举Enum，值只能是Y或N，Y表示有权限，N表示没有权限。

• Select_priv    确定用户是否可以通过SELECT命令选择数据
• Insert_priv    确定用户是否可以通过INSERT命令插入数据
• Update_priv    确定用户是否可以通过UPDATE命令修改现有数据
• Delete_priv    确定用户是否可以通过DELETE命令删除现有数据
• Create_priv    确定用户是否可以创建新的数据库和表
• Drop_priv    确定用户是否可以删除现有数据库和表、视图的权限，包括truncate table命令
• Reload_priv    确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令，包括日志、权限、主机、查询和表，拥有该权限的用户可以使用FLUSH语句
• Shutdown_priv    确定用户是否可以关闭MySQL服务器。在将此权限提供给root账户之外的任何用户时，都应当非常谨慎
• Process_priv    确定用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程
• File_priv    确定用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令
• Grant_priv    确定用户是否可以将已经授予给该用户自己的权限再授予其他用户
• References_priv    目前只是某些未来功能的占位符；现在没有作用
• Index_priv    确定用户是否可以创建和删除表索引
• Alter_priv    确定用户是否可以重命名和修改表结构
• Show_db_priv    确定用户是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库
• Super_priv    确定用户是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程，使用SET GLOBAL修改全局MySQL变量，执行关于复制和日志的各种命令
• Create_tmp_table_priv    确定用户是否可以创建临时表
• Lock_tables_priv    确定用户是否可以使用LOCK TABLES命令阻止对表的访问/修改
• Execute_priv    确定用户是否可以执行存储过程
• Repl_slave_priv    确定用户是否可以读取用于维护复制数据库环境的二进制日志文件。此用户位于主系统中，有利于主机和客户机之间的通信
• Repl_client_priv    确定用户是否可以确定复制从服务器和主服务器的位置
• Create_view_priv    确定用户是否可以创建视图
• Show_view_priv    确定用户是否可以查看视图或了解视图如何执行
• Create_routine_priv    确定用户是否可以更改或放弃存储过程和函数
• Alter_routine_priv    确定用户是否可以修改或删除存储函数及函数
• Create_user_priv    确定用户是否可以执行CREATE USER命令，这个命令用于创建新的MySQL账户
• Event_priv             确定用户能否创建、修改和删除事件
• Trigger_priv          确定用户能否创建和删除触发器
• Create_role_priv   允许创建角色
• Drop_role_priv      允许删除角色

安全列

 资源控制列

查看demo 用户在数据库call下有没有drop权限：

select Drop_priv from mysql.db where user = 'demo' and Db='call';

user里面是登录到mysql数据库用户及其拥有的权限，db里面是针对数据库某个用户拥有的权限。mysql usage权限就是空权限，默认create user的权限，只能连库，啥也不能干

GRANT USAGE ON *.* TO `user`@`%`

新建数据库后使用的是user权限