`
hudeyong926
  • 浏览: 2033016 次
  • 来自: 武汉
社区版块
存档分类
最新评论

Mysql Information_schema

阅读更多

 

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

http://hudeyong926.iteye.com/blog/2380734

 

查询数据库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 COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT 
FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'systemlog' AND table_schema = 'dj214'

一段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

 

 

 

分享到:
评论

相关推荐

    MySQL中information_schema是什么

    SELECT schema_name FROM information_schema.schemata; ``` 2. **获取某个数据库下的所有表**: ```sql SELECT table_name FROM information_schema.tables WHERE table_schema = 'your_database'; ``` 3. *...

    使用information_schema.tables查询数据库和数据表信息1

    mysql -uroot -pxxxx -D information_schema -e "SELECT TABLE_SCHEMA, table_name FROM tables WHERE table_schema LIKE 'hsm_syslog_%'" ``` 这里的参数`-u`指定用户名,`-p`后面跟随密码,`-D`指定了数据库,`-e`...

    关于MySQL绕过授予information_schema中对象时报ERROR 1044(4200)错误

    在MySQL中,`information_schema`是一个特殊的数据库,它包含了所有数据库服务器中的元数据信息,比如表、列、索引和权限等。用户通常可以通过查询`information_schema`来获取数据库的相关信息,例如表结构、约束等...

    mysql数据库中的information_schema和mysql可以删除吗?

    SELECT DATABASE() AS 'Current_DB', SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA; ``` 如果你尝试删除`information_schema`,MySQL服务将无法正常工作,因为很多操作(如查询、创建、修改数据库对象)都需要...

    information_schema数据库在SQL注入中的应用.docx

    Schemata 表提供了当前 MySQL 数据库中所有数据库的信息,其中 SCHEMA_NAME 字段保存了所有的数据库名。“show databases;”命令的结果即取自此表。通过查询 Schemata 表,可以获取到所有数据库的名称,例如,执行...

    解析MySQL的information_schema数据库

    USER_PRIVILEGES、SCHEMA_PRIVILEGES、TABLE_PRIVILEGES和COLUMN_PRIVILEGES这四张表与权限管理紧密相关,分别提供了全局、数据库级、表级和列级的权限信息,这些信息来自于mysql.*授权表,帮助管理员管理用户访问...

    MySQL5.7-information

    ### 22.22 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table 此表列出了每个数据库的权限信息,这对于管理数据库级权限非常重要。 ### 22.23 The INFORMATION_SCHEMA STATISTICS Table 该表包含了索引统计信息,...

    MySQL8.0-INFORMATION_SCHEMA增强

    Coinciding with the new native data dictionary in MySQL 8.0, we have made a number of useful enhancements to our INFORMATION_SCHEMA subsystem design in MySQL 8.0. In this post I will first go th

    出现错误mysql Table ‘performance_schema…解决办法

    同时,`information_schema`是另一个重要的内置数据库,包含所有数据库对象的元数据,如表、列、索引等信息,而`mysql`数据库则包含了系统和管理相关的表。 总的来说,`performance_schema`是MySQL提供的一种强大...

    MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句

    代码如下:select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),’ MB’) as data_size,concat(truncate(sum(index_length)/1024/1024,2),’MB’) as index_sizefrom information_schema.tables...

    查看mySQL数据库索引

    `INFORMATION_SCHEMA.STATISTICS`表中包含了关于MySQL中所有表的索引统计信息,包括索引名称、索引类型、索引列等。 #### 三、解读`INFORMATION_SCHEMA.STATISTICS`表 `INFORMATION_SCHEMA.STATISTICS`表包含了多...

    MYSQL-系统表

    SELECT table_name FROM information_schema.tables WHERE table_schema = (SELECT schema_name FROM information_schema.schemata LIMIT N,1); ``` CHARACTER_SETS 表 CHARACTER_SETS 表提供了 MYSQL 数据库中所有...

    mysql知识分享及SQL优化

    SCHEMA_NAME AS db, IF(SUM_NO_GOOD_INDEX_USED &gt; 0 OR SUM_NO_INDEX_USED &gt; 0, '*', '') AS full_scan, COUNT_STAR AS exec_count, SUM_ERRORS AS err_count, SUM_WARNINGS AS warn_count, (SUM_TIMER...

    MySQL 8.0统计信息不准确的原因

    在数据库管理领域,MySQL作为一款开源的关系型数据库管理系统,被广泛应用于各种应用中。而版本更新是软件发展的必然趋势,新版本的推出往往会带来一些新的特性、性能改进和用户体验的提升,同时可能引入一些新的...

    第20章_MySQL的数据目录.pdf

    sys 数据库是 MySQL 的系统数据库,主要是通过视图的形式把 information_schema 和 performance_schema 结合起来,帮助系统管理员和开发人员监控 MySQL 的技术性能。 在 MySQL 中,可以使用 `SHOW DATABASES;` 命令...

    枚举数据库库表列.docx

    SELECT schema_name FROM information_schema.schemata;` 3. 获取特定数据库的表:`SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema = '数据库名'` 4. 列出所有数据库的所有表...

Global site tag (gtag.js) - Google Analytics