2.6.1.15. What is INFORMATION_SCHEMA

For more detailed information, please refer to the official documentation.

INFORMATION_SCHEMA provides access to metadata for databases and offers information about their structure — such as database names, table names, column data types, and more.

It looks like a database with several tables that are read-only. In fact, the tables are views, and the database itself is virtual. It is not stored on the server as files but is created in memory when MySQL starts up.

Advantages of INFORMATION_SCHEMA:

  • The SELECT syntax is used, which is more convenient than the SHOW syntax.
  • Greater flexibility in data selection — you can filter, combine, group, and sort results, etc.
  • You can retrieve more data than when using SHOW.
  • It complies with the SQL standard, making it easier to migrate apps between databases.

Examples of INFORMATION_SCHEMA tables and the data they provide:

  • CHARACTER_SETS — a list of available charsets.
  • COLLATIONS — a list of available collations.
  • COLUMNS — a list of all columns in all tables in the database.
  • ENGINES — a list of storage types.
  • PROCESSLIST — a list of running processes.
  • STATISTICS — a list of all indexes in all database tables.
  • TABLES — a list of all database tables.
  • And others.

Due to the nature of shared hosting, some tables in INFORMATION_SCHEMA are disabled or unavailable to users.

Content