Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.