[MySQL] How to obtain metadata

[MySQL] methods for obtaining metadata MySQL provides three methods for obtaining metadata of database objects: 1) show Statement 2) query related tables from INFORMATION_SCHEMA database 3) command line program, for example, mysqlshow and mysqldump use the SHOW statement to obtain metadata. MySQL uses the show statement to obtain metadata.

[MySQL] methods for obtaining metadata MySQL provides three methods for obtaining metadata of database objects: 1) show Statement 2) query related tables from INFORMATION_SCHEMA database 3) command line program, for example, mysqlshow and mysqldump use the SHOW statement to obtain metadata. MySQL uses the show statement to obtain metadata.

[MySQL] How to obtain metadata

MySQL provides the following three methods to obtain metadata of database objects:

1) show statement

2) query related tables from the INFORMATION_SCHEMA Database

3) command line programs, such as mysqlshow and mysqldump

Use the SHOW statement to obtain metadata

MySQL uses the show statement to obtain metadata. The following typical usage is provided:

Show databases; -- list all databases show create database db_name; -- View database DDLshow tables; -- list all tables of the default database show tables from db_name; -- list all tables of the specified database show table status; -- view the descriptive information of the table show table status from db_name; show create table tbl_name; -- view the table DDLshow columns from tbl_name; -- view the column information show index from tbl_name; -- view the index information

There are several show statements that can also contain the like 'pattern' clause to limit the output range of the statement. 'pattern' allows wildcards '%' and, for example, the following statement returns all columns starting with s in the domaininfo table:

show columns from domaininfo like 's%';

All show statements that support the like clause can be rewritten as a where clause, for example:

show columns from domaininfo where field='sysdomain';

Note: The effect of desc tbl_name and explain tbl_name is the same as that of show columns from tbl_name.

Query related tables from INFORMATION_SCHEMA Database

INFORMATION_SCHEMA is a built-in system database of MySQL. It stores all the metadata and obtains the expected metadata through the related tables in the select statement. Compared with show statements, it is more troublesome, but its advantage is that standard SQL statements are more portable and flexible. You can obtain the information you actually need through various expressions.

The first two methods to obtain metadata from the command line must be executed in the MySQL command line, while mysqlshow and mysqldump provide methods to obtain metabase from the OS command line, such:

Mysqlshow -- list all databases mysqlshow db_name -- list all tables of a given database mysqlshow db_name tbl_name -- list all columns of a given database table mysqlshow -- keys db_name tbl_name -- List index information mysqlshow -- status db_name -- list descriptive information about the database

Mysqldump allows you to see the create table statement (just like the show create table statement), such:

mysqldump --no-data db_name [tbl_name] ...

Note: When you use mysqldump to view the table structure, you must add -- no-data. Otherwise, you will see data in the database table.