About information_schema in MySQL

The “information_schema“ is a system database in MySQL that contains metadata information about databases, tables, columns, indexes, users, permissions, and more within the MySQL server. It serves as an interface for querying metadata information in MySQL. This information is stored in tabular form within tables in the “information_schema” database, and users can retrieve metadata information by using SQL statements to query these tables.

Here are some common tables in the ‘information_schema’ database:

1. SCHEMATA: Stores information about all databases, including database names, character sets, etc.
2. TABLES: Stores information about all tables, including table names, table types, engines, etc.
3. COLUMNS: Stores information about all columns, including column names, column data types, column constraints, etc.
4. STATISTICS: Stores information about all indexes, including index names, index types, the table where the index is located, index columns, etc.
5. USER_PRIVILEGES: Stores information about user privileges, including user accounts, permissions, etc.

By querying the tables in the information_schema database, you can obtain information about various aspects of databases, tables, columns, indexes, users, and permissions. This is very helpful for database management and maintenance.

Next, let’s take a look at an example of finding key information through the information_schema database:

1. Query information about the current user:

   SELECT current_user();

2. Query information about the user for the current session:

   SELECT session_user();

3. Get the path of the database:

   SELECT @@datadir;

4. Get the installation path of MySQL:

   select @@basedir;

5. Retrieve information about the operating system version:

   SELECT @@version_compile_os;