How to use information_schema database table in MySQL

Today, I will talk to you about how to use the information_schema database table in MySQL. Many people may not know much about it. In order to let you know more, Xiaobian summarizes the following contents for you. I hope you can gain something according to this article.

information_schema Database table description:

SCHEMATA table: Provides information about all databases in the current instance of mysql. The results of show databases 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. Show tables from schemame.

COLUMNS Table: Provides column information in the table. Details all columns of a table and information about each column. is the result of show columns from schemaname.tablename taken from this table.

STATISTICS Table: Provides information about the table index. is the result of show index from schemaname.tablename 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. is the SHOW CHARACTER SET 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 SHOW COLLATION.

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.proc name" indicates the mysql.proc table column corresponding to the INFORMATION_SCHEMA.ROUTINES table.

VIEWS table: Gives information about views in the database. You need show views permission, otherwise you cannot view view information.

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

Here are some examples:

MySQL To see which table in which database a field is in:

mysql> use information_schema;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

For example, check which database table the APPLY_ID field is in:

mysql> select TABLE_SCHEMA,TABLE_NAME from COLUMNS where COLUMN_NAME='APPLY_ID';

+----------------------+------------+

| TABLE_SCHEMA | TABLE_NAME |

+----------------------+------------+

| ixinnuo_financial | f_apply |

| ixinnuo_financial-_T | f_apply |

| test | f_apply |

+----------------------+------------+

3 rows in set (0.06 sec)

MySQL queries which database a table is in:

For example, check which database t_zcfzb table is in:

mysql> select TABLE_SCHEMA from tables where table_name='t_zcfzb';

+----------------------+

| TABLE_SCHEMA |

+----------------------+

| ixinnuo_financial |

| ixinnuo_financial-_T |

| test |

+----------------------+

3 rows in set (0.00 sec)

The results show that t_zcfzb is present in all three data sets.

Query how many tables are in a database in MySQL:

For example, check the test database:

mysql> SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES WHERE table_schema = 'test' GROUP BY table_schema;

+--------+--------------+

| TABLES | table_schema |

+--------+--------------+

| 34 | test |

+--------+--------------+

1 row in set (0.00 sec)

Query how many columns there are in a table in a database in MySQL:

For example:

mysql> select count(*) from columns where TABLE_NAME='t_zcfzb' and TABLE_SCHEMA='test';

+----------+

| count(*) |

+----------+

| 13 |

+----------+

1 row in set (0.00 sec)

The result shows that t_zcfzb table in test library has 13 columns.

After reading the above, do you have any further understanding of how to use the information_schema database table in MySQL? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.