从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(ALTER TABLE ,table_name, ADD COLUMN is_qc TINYINT NOT NULL DEFAULT 0 COMMENT 是否QC;) FROM INFORMATION_SCHEMA.TABLES where table_schemabill202606 and table_name like bill_% and table_name not in( SELECT TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA bill202606 AND TABLE_NAME like bill% AND COLUMN_NAME is_qc )查看当前数据库所有表的字符集SELECT t.TABLE_NAME AS 表名,c.CHARACTER_SET_NAME AS 默认字符集,t.TABLE_COLLATION AS 表排序规则 FROM information_schema.TABLES t LEFT JOIN information_schema.COLLATIONS c ON t.TABLE_COLLATION c.COLLATION_NAME WHERE t.TABLE_SCHEMA bill AND t.TABLE_TYPE BASE TABLE; #发现不一致的可以修改 ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;查询慢查询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_LENGTHINDEX_LENGTH,TABLE_ROWS,concat(round((DATA_LENGTHINDEX_LENGTH)/1024/1024,2), MB) as data FROM information_schema.tables WHERE TABLE_SCHEMA数据库名称 ORDER BY DATA_LENGTHINDEX_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_typeBASE TABLE SELECT table_name,table_type,engine FROM INFORMATION_SCHEMA.TABLES WHERE table_schema db and table_name like bms_% AND table_typeBASE 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_SCHEMAsz8_news and IS_NULLABLEYES 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 x0; /*主要改进了这里把空值也纳入判断条件中去即如果字段为null或空*/ set sqlconcat(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 x0 then set sqlDropconcat(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_schemayourdatabase and data_free0;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.userMySQL是通过权限表来控制用户对数据库访问的权限表存放在mysql数据库中主要的权限表有以下几个user,db,host,table_priv,columns_priv和procs_priv先带你了解的是user表。mysql中所有的用户都是存放在user表中的这些字段可以分为4类(mysql 5.7为例)用户列.权限列.安全列.资源控制列.用户列Host主机名双主键之一值为%时表示匹配所有主机。User用户名双主键之一。Password密码名。权限列权限列决定了用户的权限描述了用户在全局范围内允许对数据库和数据库表进行的操作字段类型都是枚举Enum值只能是Y或NY表示有权限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 Dbcall;user里面是登录到mysql数据库用户及其拥有的权限db里面是针对数据库某个用户拥有的权限。mysql usage权限就是空权限默认create user的权限只能连库啥也不能干GRANT USAGE ON *.* TO user%新建数据库后使用的是user权限
