MySQL INFORMATION_SCHEMA Database

Overview

In this tutorial, we will learn about MySQL INFORMATION_SCHEMA database. The INFORMATION_SCHEMA is an information database, the place that stores information about all the
other databases that the MySQL database server maintains.

INFORMATION_SCHEMA is a special database.  It contains several read-only system views. The server doesn’t create a database directory with the name. The schema contains system views and not base tables, so there are no associated database files. It is possible to only to read the contents of the schema. We cannot insert data or delete from the database.

INFORMATION_SCHEMA

We can select the INFORMATION_SCHEMA database with a USE statement.

mysql> SHOW DATABASES;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+——————–+
6 rows in set (0.02 sec)

mysql> USE information_schema;
Database changed

 

MySQL Information_Schema Database

INFORMATION_SCHEMA Tables

In INFORMATION_SCHEMA there are several read-only tables. These are system views, not base tables,
so there are no files associated with them. It is possible only to read the contents of tables. We cannot insert, update , or delete from the tables.

The following is the list of the available tables in the INFORMATION_SCHEMA:

mysql> SHOW TABLES;
+—————————————+
| Tables_in_information_schema |
+—————————————+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS |
| APPLICABLE_ROLES |
| CHARACTER_SETS |
| CHECK_CONSTRAINTS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLLATIONS |
| COLUMN_PRIVILEGES |
| COLUMN_STATISTICS |
| COLUMNS |
| COLUMNS_EXTENSIONS |
| ENABLED_ROLES |
| ENGINES |
| EVENTS |
| FILES |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_CACHED_INDEXES |
| INNODB_CMP |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_CMP_RESET |
| INNODB_CMPMEM |
| INNODB_CMPMEM_RESET |
| INNODB_COLUMNS |
| INNODB_DATAFILES |
| INNODB_FIELDS |
| INNODB_FOREIGN |
| INNODB_FOREIGN_COLS |
| INNODB_FT_BEING_DELETED |
| INNODB_FT_CONFIG |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_DELETED |
| INNODB_FT_INDEX_CACHE |
| INNODB_FT_INDEX_TABLE |
| INNODB_INDEXES |
| INNODB_METRICS |
| INNODB_SESSION_TEMP_TABLESPACES |
| INNODB_TABLES |
| INNODB_TABLESPACES |
| INNODB_TABLESPACES_BRIEF |
| INNODB_TABLESTATS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_TRX |
| INNODB_VIRTUAL |
| KEY_COLUMN_USAGE |
| KEYWORDS |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| RESOURCE_GROUPS |
| ROLE_COLUMN_GRANTS |
| ROLE_ROUTINE_GRANTS |
| ROLE_TABLE_GRANTS |
| ROUTINES |
| SCHEMA_PRIVILEGES |
| SCHEMATA |
| SCHEMATA_EXTENSIONS |
| ST_GEOMETRY_COLUMNS |
| ST_SPATIAL_REFERENCE_SYSTEMS |
| ST_UNITS_OF_MEASURE |
| STATISTICS |
| TABLE_CONSTRAINTS |
| TABLE_CONSTRAINTS_EXTENSIONS |
| TABLE_PRIVILEGES |
| TABLES |
| TABLES_EXTENSIONS |
| TABLESPACES |
| TABLESPACES_EXTENSIONS |
| TRIGGERS |
| USER_ATTRIBUTES |
| USER_PRIVILEGES |
| VIEW_ROUTINE_USAGE |
| VIEW_TABLE_USAGE |
| VIEWS |
+—————————————+

MySQL Information_Schema Tables

 

Some tables descriptions are as follows:

 

INFORMATION_SCHEMA Table Description
SCHEMATA Information about databases
TABLES Information about database Tables
COLUMNS Information about Columns in database tables
ROUTINES Information about Stored procedures and Functions
TRIGGERS Information about Triggers
VIEWS Information about Views
TABLE_CONSTRAINTS Information about Constraints on database tables
TABLE_PRIVILEGES Information about Table privileges held by MySQL users
 

USER_PRIVILEGES

 Information about Global privileges held by MySQL users
CHARACTER_SETS Information about available Character sets

Example

Here is an example of a statement that retrieves metadata information:

mysql> SELECT Table_name, Table_type, Engine
-> FROM Information_Schema.Tables
-> WHERE Table_schema= ‘world’;
+—————–+————+——–+
| TABLE_NAME | TABLE_TYPE | ENGINE |
+—————–+————+——–+
| city | BASE TABLE | InnoDB |
| country | BASE TABLE | InnoDB |
| countrylanguage | BASE TABLE | InnoDB |
| v_city | VIEW | NULL |
+—————–+————+——–+
4 rows in set (0.00 sec)

Retrieving metadata from INFROMATION_SCHEMA

 

Each MySQL user has the right to access these tables, but can see only the rows in the tables
that correspond to objects for which the user has the proper access privileges. The above statement gets the list of all the tables in the world database

The columns show:

  • The name of the Table
  • Table type
  • Table Storage engine

—

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/

For more information on MySQL Database:

https://www.mysql.com/

Related Posts