What is information_schema in MySQL

This article mainly introduces what information_schema is in MySQL, which has certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article. Let Xiaobian take you to understand it together.

In MySQL, the information_schema is thought of as a database, specifically an information database. It holds information about all other databases maintained by MySQL Server. Such as database name, database table, table column data type and access rights, etc.

information_schema Introduction

In INFORMATION_SCHEMA, there are several read-only tables. They are actually views, not base tables, so you won't be able to see any files associated with them.

information_schema Database table description:

SCHEMATA table: Provides information about all databases in the current instance of mysql. The results of showdatabases are taken from this table.

TABLES tables: Provides information about tables in the database, including views. Details which schema a table belongs to, table type, table engine, creation time and other information. The results of showtables from schema are taken from this table.

COLUMNS Table: Provides column information in the table. Details all columns of a table and information about each column. The results of showcolumnsfromschemaname.tablename are taken from this table.

STATISTICS Table: Provides information about the table index. Results for showindexfromschemaname.tablename are taken from this table.

USER_PRIVILEGES table: Gives information about global permissions. This information is derived from the mysql.user authorization table. Non-standard table.

SCHEMA_PRIVILEGES table: Gives information about schema (database) permissions. This information comes from the mysql.db authorization table. Non-standard table.

TABLE_PRIVILEGES table: Gives information about table permissions. This information is derived from the mysql.tables_priv authorization table. Non-standard table.

COLUMN_PRIVILEGES table: Gives information about column permissions. This information is derived from the mysql.columns_priv authorization table. Non-standard table.

CHARACTER_SETS table: Provides information about the character sets available for mysql instances. Yes SHOWCHARACTERSET result set taken from this table.

COLLATIONS table: Provides information about the collation of character sets.

COLLATION_CHARACTER_SET_APPLICATION table: Indicates the character set available for collation. These columns are equivalent to the first two display fields of SHOWCOLLATION.

TABLE_CONSTRAINTS table: Describes the table where constraints exist. And the constraint type of the table.

KEY_COLUMN_USAGE table: Describes key columns with constraints.

ROUTINES table: Provides information about stored subroutines (stored procedures and functions). At this point, the ROUTINES table does not contain custom functions (UDFs). A column named "mysql.procname" indicates the mysql.proc table column corresponding to the INFORMATION_SCHEMA.ROUTINES table.

VIEWS table: Gives information about views in the database. View information cannot be viewed unless you have showviews permission.

TRIGGERS table: provides information about triggers. You must have super permission to view this table

Thank you for reading this article carefully. I hope that the article "What is information_schema in MySQL" shared by Xiaobian will be helpful to everyone. At the same time, I hope that everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!