MySQL - Obtaining metadata

mysql

mysqlshow is a command-line front end for a few SHOW statements. When invoked, it examines its arguments to determine what information to display, issues the appropriate SHOW statement, and displays the results.

SHOW and mysqlshow have been available since very early. As of MySQL 5, metadata access is enhanced through two additions:

  1. The INFORMATION_SCHEMA database, part of standard, is implemented. SHOW is MySQL-specific extension.
  2. SHOW statement syntax is extended to support a WHERE clause for describing which rows to display. Some SHOW statements support a LIKE clause for applying pattern match to the rows of the result, but WHERE is more flexible.

You can use SELECT to obtain information about INFORMATION_SCHEMA itself. For example, to list tables in INFORMATION_SCHEMA:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA' 
 ORDER BY TABLE_NAME;

To display the names of the columns in a given table in INFORMATION_SCHEMA:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA' 
 AND TABLE_NAME = 'VIEWS';

The names of the INFORMATION_SCHEMA database, its tables, and columns are not case sensitive:

SELECT column_name FROM information_schema.columns WHERE table_schema = 'information_schema' 
 AND table_name = 'views';

When you retrieve metadata from INFORMATION_SCHEMA by using SELECT, you have the freedom to use any of the usual SELECT features.

INFORMATION_SCHEMA is read-only. Its tables cannot be modified with statements such as INSERT, DELETE, or UPDATE.

For some SHOW statements, you can give a LIKE clause to perform a pattern-match operation that determines which rows to display. SHOW DATABASES, SHOW TABLES, and SHOW COLUMNS support this feature. SHOW also support the use of a WHERE clause. As with the LIKE clause, WHERE determines which rows to display, but WHERE is more flexible because you can use any kind of test, not just a pattern match.

To display the available character sets:

SHOW CHARACTER SET;

To displays the collations for each character set:

SHOW COLLATION;

DESCRIBE is equivalent to SHOW COLUMNS. The following two statements display the same information:

DESCRIBE table_name;
SHOW COLUMNS FROM table_name;

However, SHOW COLUMNS supports the optional FULL keyword, where DESCRIBE does not.

SHOW and DESCRIBE can be used on INFORMATION_SCHEMA

mysqlshow [options] [db_name [table_name [column_name]]]

The action performed by mysqlshow depends on the number of non-option arguments you provide. With no arguments, mysqlshow displays a result similar to SHOW DATABASES. With a single argument, mysqlshow interprets it as a database name, and displays a result similar to that of SHOW TABLES for the database. With two arguments, mysqlshow interprets them as a database and table name and displays a result similar to that of SHOW FULL COLUMNS for the table. With three arguments, the output is the same as for two arguments except that mysqlshow takes the third argument as a column name and displays SHOW FULL COLUMNS output only for that column.

When mysqlshow is used to display table structure, the —keys option may be given to display index structure as well. This information is similar to the output of SHOW INDEX for the table.

If the final argument on the command line contains special character, mysqlshow interprets the argument as a pattern and displays only the names that match the pattern. The special characters are %, or * to match any sequence of characters, and _ or ? to match any single character. For example, the following command shows only those databases with a name that begins with 'w':

shell> mysqlshow "w%"

The pattern characters might be treated as special by your shell. An argument that contains any such characters should be quoted. * can be used without quoting on Windows, and % can be used without quoting on Unix.

Advantages of INFORMATION_SCHEMA over SHOW:

  1. INFORMATION_SCHEMA is a feature of standard SQL, whereas SHOW is a MySQL-specific statement.
  2. With INFORMATION_SCHEMA, you always use SELECT syntax to obtain metadata, regardless of the type of information in which you're interested. SHOW involves a different statement for each type of metadata, and they don't all have the same syntax.
  3. With SELECT and INFORMATION_SCHEMA, you have complete flexibility to choose what to retrieve. You can name which columns to select, apply arbitrary conditions for restricting which rows to retrieve, and sort the result. SHOW is not so versatile. Some form of SHOW support a LIKE clause to restrict which rows to display, and MySQL 5 adds a WHERE clause as a more flexible way to restrict the rows. But in either case, the rows returned are in a fixed order. But in either case, the rows returned are in a fixed order. They also consist of a fixed set of columns. You cannot omit columns in which you're not interested.
  4. Because the information in INFORMATION_SCHEMA can be retrieved with all the flexibility of SELECT, you can use joins, unions, and subqueries. You can not do the same with SHOW statements.
  5. By using CREATE TABLE … SELECT or INSERT … SELECT, the contents of INFORMATION_SCHEMA can be retrieved or stored into another table for use in subsequent statements. Information produced by SHOW can be retrieved for display only. It can not be stored in another table.

Advantage of SHOW over INFORMATION_SCHEMA:

  1. SHOW is availabe for releases of MySQL older than MySQL 5.
  2. SHOW is often more concise

The INFORMATION_SCHEMA implementation is fairly complete, but there are some kinds of information that it does not yet contain. For example, there are no tables for routine parameters or referential constraints for foreign keys.

INFORMATION_SCHEMA serves solely as means of accessing metadata. SHOW statements serve this purpose, but the domain of SHOW extends into other area as well. Certain SHOW statements provide information about the server's configuration or operational state.

SHOW VARIABLES displays system variables that describe server configuration.

SHOW STATUS displays status variables that provide information about current server operation.

SHOW ENGINES list the storage engine that the server knows about.

SHOW PROCESSLIST provides information about the currently executing server threads.

SHOW MASTER STATUS and SHOW SLAVE STATUS provide information about replication servers.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License