2.7.15. What is INFORMATION_SCHEMA
INFORMATION_SCHEMA provides access to metadata Database and provides information about its structure — the name of the database, tables, data types of columns, etc.
Looks like a database with multiple tables available only for reading... In fact, tables are views, and the database itself is virtual. It is not stored on the server as files and is generated in memory when MySQL is started.
- The SELECT syntax is used, which is more convenient than the SHOW syntax.
- More freedom in the choice of data — you can limit the selection, combine, group, sort the results, etc.
- More data can be received than with SHOW.
- Works according to the SQL standard, applications are easier to transfer between databases.
Examples of INFORMATION tables_SCHEMA and the data they provide:
- CHARACTER_SETS - list of available character sets.
- COLLATIONS — list of available comparisons.
- COLUMNS is a list of all columns in all tables in the database.
- ENGINES - list of storage types.
- PROCESSLIST - list of current processes.
- STATISTICS is a list of all indexes in all tables in the database.
- TABLES - list of all tables in the database.
Due to the peculiarities of virtual hosting, some of the tables in INFORMATION_SCHEMA is disabled or unavailable to users.