information_schema 数据库
前言:
information_schema 数据库跟 performance_schema 一样，都是 MySQL 自带的信息数据库。
- 其中 performance_schema 用于性能分析
- 而 information_schema 用于存储数据库元数据(关于数据的数据)，例如数据库名、表名、列的数据类型、访问权限等。
information_schema简介:
- 在MySQL中，把 information_schema 看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。
- 在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。
information_schema 库中常用的表
表名:
MariaDB [(none)]> use information_schema;
Database changed
MariaDB [information_schema]> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| ALL_PLUGINS |
| APPLICABLE_ROLES |
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENABLED_ROLES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_CACHES |
| KEY_COLUMN_USAGE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| SYSTEM_VARIABLES |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| GEOMETRY_COLUMNS |
| SPATIAL_REF_SYS |
| CLIENT_STATISTICS |
| INDEX_STATISTICS |
| INNODB_SYS_DATAFILES |
| USER_STATISTICS |
| INNODB_SYS_TABLESTATS |
| INNODB_LOCKS |
| INNODB_MUTEXES |
| INNODB_CMPMEM |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP |
| INNODB_FT_DELETED |
| INNODB_CMP_RESET |
| INNODB_LOCK_WAITS |
| TABLE_STATISTICS |
| INNODB_TABLESPACES_ENCRYPTION |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_SYS_FIELDS |
| INNODB_CMPMEM_RESET |
| INNODB_SYS_COLUMNS |
| INNODB_FT_INDEX_TABLE |
| INNODB_CMP_PER_INDEX_RESET |
| user_variables |
| INNODB_FT_INDEX_CACHE |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_FT_BEING_DELETED |
| INNODB_BUFFER_POOL_STATS |
| INNODB_TRX |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLES |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_CONFIG |
| INNODB_BUFFER_PAGE |
| INNODB_SYS_TABLESPACES |
| INNODB_METRICS |
| INNODB_SYS_INDEXES |
| INNODB_SYS_VIRTUAL |
| INNODB_TABLESPACES_SCRUBBING |
| INNODB_SYS_SEMAPHORE_WAITS |
+---------------------------------------+
75 rows in set (0.000 sec)
MariaDB [information_schema]>
简介:
- SCHEMATA表：
提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。 - TABLES表：
提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。 - COLUMNS表：
提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。 - STATISTICS表：
提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。 - USER_PRIVILEGES（用户权限）表：
给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。 - SCHEMA_PRIVILEGES（方案权限）表：
给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。 - TABLE_PRIVILEGES（表权限）表：
给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。 - COLUMN_PRIVILEGES（列权限）表：
给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。 - CHARACTER_SETS（字符集）表：
提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。 - COLLATIONS表：
提供了关于各字符集的对照信息。 - COLLATION_CHARACTER_SET_APPLICABILITY表：
指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。 - TABLE_CONSTRAINTS表：
描述了存在约束的表。以及表的约束类型。 - KEY_COLUMN_USAGE表：
描述了具有约束的键列。 - ROUTINES表：
提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对 INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。 - VIEWS表：
给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。 - TRIGGERS表：
提供了关于触发程序的信息。必须有super权限才能查看该表
经典案例:
1、查看数据库各个表 ,数据占用空间大小 和 行数
DATA_LENGTH + INDEX_LENGTH ==>数据长度+索引长度
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='jtdb' ORDER BY DATA_LENGTH+INDEX_LENGTH DESC;
2、 查看数据库中各个表字段个数
SELECT TABLE_NAME,COUNT(TABLE_NAME) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='db20'
GROUP BY TABLE_NAME;
3、查看数据库各个表的数据条数
- TABLE_ROWS
SELECT TABLE_NAME,TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='jtdb';
常用表介绍
SCHEMATA 表
- 当前 mysql 实例中所有数据库的信息。
SHOW DATABASES; 命令从这个表获取数据。
mysql> SELECT * FROM SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def | information_schema | utf8 | utf8_general_ci | NULL |
| def | mysql | latin1 | latin1_swedish_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
| def | sys | utf8 | utf8_general_ci | NULL |
| def | test | utf8 | utf8_unicode_ci | NULL |
+--------------+--------------------+----------------------------+------------------------+----------+
10 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
10 rows in set (0.00 sec)
TABLES 表
- 存储数据库中所有的表信息（包括视图），包括表属于哪个数据库，表的类型、存储引擎、创建时间等信息。
SELECT * FROM TABLES;
SHOW TABLES FROM XX; 命令从这个数据库获取表信息。
COLUMNS 表
- 存储所有数据库中的,所有表,的列信息，包括表有多少列、每个列的类型等
SELECT * FROM COLUMNS 查询所有表中的列信息
SHOW COLUMNS FROM schemaname.tablename 命令从这个表获取结果。
SELECT * FROM COLUMNS WHERE TABLE_SCHEMA ='db10'
SHOW COLUMNS FROM db10.emp
查看数据库中各个表字段个数
SELECT TABLE_NAME,COUNT(TABLE_NAME) FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='db20'
GROUP BY TABLE_NAME;
STATISTICS 表
- 表索引的信息
SHOW INDEX FROM schemaname.tablename; 命令从这个表获取结果。
SHOW INDEX FROM jtdb.tb_user
CHARACTER_SETS 表
- 提供了 mysql 可用字符集的信息。
SHOW CHARACTER SET; 命令从这个表获取结果。
mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
...
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.07 sec)
mysql> SELECT * FROM CHARACTER_SETS;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |
| dec8 | dec8_swedish_ci | DEC West European | 1 |
| cp850 | cp850_general_ci | DOS West European | 1 |
...
| eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 |
| gb18030 | gb18030_chinese_ci | China National Standard GB18030 | 4 |
+--------------------+----------------------+---------------------------------+--------+
41 rows in set (0.00 sec)
USER_PRIVILEGES 表
- 用户权限表。内容源自 mysql.user 授权表。是非标准表。
mysql> SELECT * FROM USER_PRIVILEGES;
+-------------------------+---------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+-------------------------+--------------+
| 'mysql.sys'@'localhost' | def | USAGE | NO |
| 'root'@'%' | def | SELECT | YES |
| 'root'@'%' | def | INSERT | YES |
| 'root'@'%' | def | UPDATE | YES |
| 'root'@'%' | def | DELETE | YES |
| 'root'@'%' | def | CREATE | YES |
| 'root'@'%' | def | DROP | YES |
| 'root'@'%' | def | RELOAD | YES |
| 'root'@'%' | def | SHUTDOWN | YES |
| 'root'@'%' | def | PROCESS | YES |
| 'root'@'%' | def | FILE | YES |
| 'root'@'%' | def | REFERENCES | YES |
| 'root'@'%' | def | INDEX | YES |
| 'root'@'%' | def | ALTER | YES |
| 'root'@'%' | def | SHOW DATABASES | YES |
| 'root'@'%' | def | SUPER | YES |
| 'root'@'%' | def | CREATE TEMPORARY TABLES | YES |
| 'root'@'%' | def | LOCK TABLES | YES |
| 'root'@'%' | def | EXECUTE | YES |
| 'root'@'%' | def | REPLICATION SLAVE | YES |
| 'root'@'%' | def | REPLICATION CLIENT | YES |
| 'root'@'%' | def | CREATE VIEW | YES |
| 'root'@'%' | def | SHOW VIEW | YES |
| 'root'@'%' | def | CREATE ROUTINE | YES |
| 'root'@'%' | def | ALTER ROUTINE | YES |
| 'root'@'%' | def | CREATE USER | YES |
| 'root'@'%' | def | EVENT | YES |
| 'root'@'%' | def | TRIGGER | YES |
| 'root'@'%' | def | CREATE TABLESPACE | YES |
+-------------------------+---------------+-------------------------+--------------+
29 rows in set (0.00 sec)
SCHEMA_PRIVILEGES 表
- 方案权限表。给出了关于方案（数据库）权限的信息。内容来自 mysql.db 授权表。是非标准表。
mysql> SELECT * FROM SCHEMA_PRIVILEGES;
+-------------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+--------------+-------------------------+--------------+
| 'mysql.sys'@'localhost' | def | sys | TRIGGER | NO |
| 'root'@'%' | def | mysql | SELECT | YES |
| 'root'@'%' | def | mysql | INSERT | YES |
| 'root'@'%' | def | mysql | UPDATE | YES |
| 'root'@'%' | def | mysql | DELETE | YES |
| 'root'@'%' | def | mysql | CREATE | YES |
| 'root'@'%' | def | mysql | DROP | YES |
| 'root'@'%' | def | mysql | REFERENCES | YES |
| 'root'@'%' | def | mysql | INDEX | YES |
| 'root'@'%' | def | mysql | ALTER | YES |
| 'root'@'%' | def | mysql | CREATE TEMPORARY TABLES | YES |
| 'root'@'%' | def | mysql | LOCK TABLES | YES |
| 'root'@'%' | def | mysql | EXECUTE | YES |
| 'root'@'%' | def | mysql | CREATE VIEW | YES |
| 'root'@'%' | def | mysql | SHOW VIEW | YES |
| 'root'@'%' | def | mysql | CREATE ROUTINE | YES |
| 'root'@'%' | def | mysql | ALTER ROUTINE | YES |
| 'root'@'%' | def | mysql | EVENT | YES |
| 'root'@'%' | def | mysql | TRIGGER | YES |
+-------------------------+---------------+--------------+-------------------------+--------------+
19 rows in set (0.00 sec)
TABLE_PRIVILEGES 表
- 表权限表。给出了关于表权限的信息。内容源自 mysql.tables_priv 授权表。是非标准表。
mysql> SELECT * FROM TABLE_PRIVILEGES;
+-------------------------+---------------+--------------+------------+----------------+--------------+
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+--------------+------------+----------------+--------------+
| 'mysql.sys'@'localhost' | def | sys | sys_config | SELECT | NO |
+-------------------------+---------------+--------------+------------+----------------+--------------+
1 row in set (0.00 sec)
COLUMN_PRIVILEGES 表
- 列权限表。给出了关于列权限的信息。内容源自 mysql.columns_priv 授权表。是非标准表。
mysql> SELECT * FROM COLUMN_PRIVILEGES;
Empty set (0.00 sec)
COLLATIONS 表
- 提供了关于各字符集的对照信息。SHOW COLLATION; 命令从这个表获取结果。
mysql> SELECT * FROM COLLATIONS;
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------------+--------------------+-----+------------+-------------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
...
| gb18030_bin | gb18030 | 249 | | Yes | 1 |
| gb18030_unicode_520_ci | gb18030 | 250 | | Yes | 8 |
+--------------------------+--------------------+-----+------------+-------------+---------+
222 rows in set (0.03 sec)
TABLE_CONSTRAINTS 表
- 描述了存在约束的表。以及表的约束类型。
SELECT * FROM TABLE_CONSTRAINTS;
KEY_COLUMN_USAGE 表
- 描述了具有约束的键列。
SELECT * FROM KEY_COLUMN_USAGE;
VIEWS 表
- 给出了关于数据库中的视图的信息。需要有 show views 权限，否则无法查看视图信息。
SELECT * FROM VIEWS LIMIT 1\G
TRIGGERS 表
- 提供了关于触发程序的信息。必须有 super 权限才能查看该表。
SELECT * FROM TRIGGERS
1039
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
