MySQL开发必备-INFORMATION_SCHEMA库总结

admin管理员组

文章数量:1638814

文章目录

• • • 【前言】
  • （一）基础理论
  • 1. 认识
  • • 2. 替代show语句
    • 3. 权限访问
  • （二）实践整理
  • • 1. 查询MySQL某个数据库下所有的表信息
    • 2. 查询某个数据表字段列和属性信息
    • 3. 查询数据库存储引擎属性
    • 4. 查询当前正在执行的线程的信息
    • 5. 查询所有数据库模式信息
    • 6. 查询所有的触发器
    • 7. 查询库中所有用户的权限信息
    • 8. 查询所有的视图信息
  • （三）INFORMATION_SCHEMA实例数据表参考文档

【前言】

因为最近研究了代码自动生成框架，其中可以将数据库中所有的表自动生成JAVA、HTML、JS等文件，在熟悉的过程中发现在数据表操作上用到了INFORMATION_SCHEMA，这个在此之前并不是很熟悉，在进一步了解学习之后发现MySQL提供的INFORMATION_SCHEMA实例在实际开发中有很多有用的功能，比如查询已创建的所有数据库、数据表、字段列以及字段的各个属性信息、以及数据库用户权限检查，还有查询目前连接的用户以及正在执行的SQL操作等等，下面是我整理了官方文档以及我自己使用的一些实践总结

（一）基础理论

1. 认识

INFORMATION_SCHEMA 提供对数据库元数据、有关MySQL服务器的信息（如数据库或表的名称、列的数据类型或访问权限）的访问。有时用于此信息的其他术语是数据字典和系统目录。

INFORMATION_SCHEMA在每一个MySQL都存在一个实例对象，该实例可以使用，但是只能读取表的内容，不能对其执行INSERT、UPDATE、DELETE操作

2. 替代show语句

数据库语言中可以通过SHOW DATABASES, SHOW TABLES查看数据库以及数据表信息，不过也可以使用INFORMATION_SCHEMA替代上面的SHOW方式，MySQL官方也是更加推荐这种方式，下面是官方给出的优势分析：
1：这种方式更符合Codd’s rules规则，因为所有的操作都是建立在数据表之上的
2：您可以使用熟悉的SELECT语句语法，只需要学习一些表名和列名。
3：您可以过滤、排序、连接并将来自信息模式查询的结果转换为应用程序所需的任何格式，例如要解析的数据结构或文本表示。
4：这种技术与其他数据库系统的互操作性更强。例如，Oracle数据库用户熟悉在Oracle数据字典中查询表。

3. 权限访问

对于INFORMATION_SCHEMA中的大多数信息，每个MySQL用户都有权访问它们，但只能看到表中与用户具有适当访问权限的对象相对应的行。在使用表时注意INFORMATION_SCHEMA您必须对某个对象具有某种特权才能查看有关该对象的信息

（二）实践整理

测试数据库

1. 查询MySQL某个数据库下所有的表信息

SELECT table_name  表名称 , table_type 表类型  , engine  存储引擎
       FROM information_schema.tables
			 -- 数据库名称
       WHERE table_schema = 'test'
       ORDER BY table_name;

说明：该语句请求数据库test中所有表的列表，只显示三条信息：表的名称、类型和存储引擎。

【扩展】如果不想指定具体的数据库而是当前登录数据库可以通过下面俩种方式解决，俩个在Mysql是同义的
SELECT SCHEMA()
SELECT DATABASE()

SELECT table_name  表名称 , table_type 表类型  , engine  存储引擎
       FROM information_schema.tables
       WHERE table_schema = ( 	SELECT DATABASE()	)
       ORDER BY table_name;

2. 查询某个数据表字段列和属性信息

SELECT  * FROM information_schema.COLUMNS		
保存了数据库中所有的列信息

【示例：查询当前数据库某个表列以及列的属性信息】

scores表

SELECT column_name     -- 列名称    
	,     CASE              
		WHEN is_nullable = 'no'
		AND column_key != 'PRI' THEN '1'
		ELSE NULL
	END AS is_required        --  是否必须
	, CASE 
		WHEN column_key = 'PRI' THEN '1'
		ELSE '0'
	END AS is_pk,             -- 是否主键
	ordinal_position AS sort,  -- 列位置
	 column_comment      -- 列注释
	, CASE   
		WHEN extra = 'auto_increment' THEN '1'
		ELSE '0'          
	END AS is_increment   -- 是否自增
	, column_type      -- 列类型
FROM information_schema.columns
WHERE table_schema = (
		SELECT DATABASE()
	)
	AND table_name = 'scores'
ORDER BY ordinal_position;

3. 查询数据库存储引擎属性

SELECT *  from INFORMATION_SCHEMA.ENGINES	  

4. 查询当前正在执行的线程的信息

MySQL进程列表指示服务器中执行的线程集当前正在执行的操作。PROCESSLIST表是进程信息的一个来源。

SELECT *  from INFORMATION_SCHEMA.PROCESSLIST			

5. 查询所有数据库模式信息

SELECT *  from INFORMATION_SCHEMA.SCHEMATA

6. 查询所有的触发器

SELECT *  from INFORMATION_SCHEMA.TRIGGERS									

7. 查询库中所有用户的权限信息

用户权限表提供有关全局权限的信息。它从mysql.user系统表中获取其值。

SELECT *  from INFORMATION_SCHEMA.USER_PRIVILEGES

8. 查询所有的视图信息

SELECT *  from INFORMATION_SCHEMA.VIEWS	

（三）INFORMATION_SCHEMA实例数据表参考文档

详细的表信息和字段描述参见MySQL文档
https://dev.mysql/doc/refman/8.0/en/information-schema-table-reference.html

本文标签： mysqlinformationschema