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

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 |
+—————————————+

Some tables descriptions are as follows:

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)

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/