MySQL in information_schema description

1. Description

When you install or use MySQL, you will find a INFORMATION_SCHEMA database in addition to the database you have installed. The INFORMATION_SCHEMA database is a MySQL-band that provides a way to access database metadata. Metadata is data about the data, such as the database name or table name, the data type of the column, or access permissions. Some of the other terms used to express this information include the data dictionary and the system catalog.

INFORMATION_SCHEMA is an information database that holds information about all the other databases maintained by the MySQL server. In Information_schema, there are a few read-only tables. They are actually views, not basic tables, so you will not be able to see any files associated with them, that is, Information_schema says a virtual database, which does not physically exist. Specific look:

In fact, although it is not necessary to generate a file named Information_schema, we still provide a new database named INFORMATION_SCHEMA. Information_schema can be selected as the default database using the USE statement, but the only way to access the tables contained in the database is to use the SELECT statement. You cannot insert content in it, you cannot update them, and you cannot delete the contents of them

Each MySQL user has access to these tables, but only to specific rows in the table, where the user has the appropriate access rights to the object

compared with show, SELECT The advantages

SELECT ... The purpose of the FROM INFORMATION_SCHEMA statement is to provide a more consistent way to access the information provided by the various show statements supported by MySQL (show DATABASES, show tables, and so on). There are several advantages to using select compared to show "

· Complies with Codd rules. In other words, all accesses are made on the table.

· You do not need to know the syntax of the new statement. Because they already know how select works, you just need to know the object name.

· Realize that people don't have to worry about adding keyword issues.

· There are millions of possible output variations, not one. This provides greater flexibility for applications that have different requirements for metadata.

· Porting is easier because other DBMS also uses this type of approach.

However, because show is popular among employees and users of MySQL, if show disappears, it can lead to confusion, so the traditional syntax cannot give enough reason to eliminate show. In fact, in MySQL 5.1, many enhancements have been made to show. For an introduction to this, see "Extending the show statement ."

2, information_schema under the table

2.1 schemata Table

the schemata table provides information about all databases in the current MySQL instance. The results of show databases are taken from this table. The table corresponding columns are described below:

Column Name	Chinese explanation	Example
Catalog_name
Schema_name	Database name
Default_character_set_name	Database default encoding	Utf8
Default_collation_name		Utf8_general_ci
Sql_path		Null

notes: The total of the Sql_path column is null.

The following statements are equivalent:

Select Schema_name as ' database '
From Information_schema.schemata
[where schema_name like ' db_ca_ods ']

Show databases
[like ' Db_ca_ods ']

2.2 Tables Table

The Tables table provides information about the tables in the database, including views. Describes in detail which schema, table type, table engine, creation time, and so on, a table belongs to. This table is the result of show tables from db_ca_ods; "Note Db_ca_ods is the database name".

Column Name	Chinese explanation	Example/description
Table_catalog		Null
Table_schema	Database name	Information_schema
table_name	Table name	Tables table, belonging to the INFORMATION_SCHEMA database
Table_type	Table type	Should be a base table or view. If the table is temporary, Table_type = temporary. (There is no temporary view, so there is no ambiguity)
Engine	The engine used for the table	InnoDB
Version	Version
Row_format	Row_format
Table_rows	Total rows in table
Avg_row_length	Avg_row_length
Data_length	Data_length
Max_data_length	Max_data_length
Index_length	Index_length
Data_free	Data_free
Auto_increment	Auto_increment
Create_time	Create_time
Update_time	Update_time
Check_time	Check_time
Table_collation	Collation
Checksum	Checksum
Create_options	Create_options
Table_comment	Comment	The description of the table, and the value of comment in the CREATE statement

The following statements are equivalent:

Select table_name from Information_schema.tables
[Where Table_schema = ' db_name ']
[Where|and table_name like ' wild ']

Show tables
[From Db_name]
[like ' Wild ']

MySQL in information_schema description