The Geek Diary

CONCEPTS | BASICS | HOWTO

How to obtain MySQL metadata (metadata access methods)

By

A database is a structured collection of data. Metadata is “data about data”. Using the following methods, MySQL provides access to metadata for databases, tables, and other objects managed by the database server:

1. INFORMATION_SCHEMA: The MySQL server contains a data dictionary implemented as a database (schema) named INFORMATION_SCHEMAthat includes a number of objects that appear to be tables.

2. SHOW statements: Proprietary syntax to obtain data on server statistics, schemas, and schema objects:

  • SHOW DATABASES and SHOW TABLES: Return lists of database and table names.
  • SHOW COLUMNS: Produces definitions of columns in a table.
  • The SELECT privilege is required for use of SHOW statements.

3. DESCRIBE: A SQL statement shortcut you can use to inspect table structure and column properties.

4. mysqlshow: The client program that you can use as a command-line front end to a few of the SHOW statements. The arguments you set determine the information to display, and then the program issues the appropriate SHOW statement and displays the results of the statement.

INFORMATION_SCHEMA Database

The INFORMATION_SCHEMA database serves as a central repository for database metadata. It is a “virtual database” in the sense that it is not stored anywhere on disk; however, it contains tables like any other database, and the contents of its tables can be accessed using SELECTlike any other tables. Furthermore, SELECT statements can be used to obtain INFORMATION_SCHEMAtables

Because it is a virtual database, MySQL builds query results by reading table and other object metadata. When you execute a query that reads information about many tables, MySQL must execute many disk-level operations, which can take a lot of time.

INFORMATION_SCHEMA Tables

To list all tables in the INFORMATION_SCHEMA database, use the below query: 

mysql> SELECT   TABLE_NAME
    -> FROM     INFORMATION_SCHEMA.TABLES
    -> WHERE    TABLE_SCHEMA = 'information_schema'
    -> ORDER BY TABLE_NAME; 
 +---------------------------------------+ 
 | TABLE_NAME                            | 
 +---------------------------------------+ 
 | CHARACTER_SETS                        | 
 | COLLATIONS                            | 
 | COLLATION_CHARACTER_SET_APPLICABILITY | 
 | COLUMNS                               | 
 | COLUMN_PRIVILEGES                     | 
 ... 
 | USER_PRIVILEGES                       | 
 | VIEWS                                 | 
 +---------------------------------------+

The INFORMATION_SCHEMAtables contain the following types of information:

Table Information

  • COLUMNS:Columns in tables and views
  • ENGINES:Storage engines
  • SCHEMATA:Databases
  • TABLES:Tables in databases
  • VIEWS:Views in databases

Partitioning

  • PARTITIONS: Table partitions
  • FILES: Files in which MySQL NDB disk data tables are stored

Privileges

  • COLUMN_PRIVILEGES: Column privileges held by MySQL user accounts
  • SCHEMA_PRIVILEGES: Database privileges held by MySQL user accounts
  • TABLE_PRIVILEGES: Table privileges held by MySQL user accounts
  • USER_PRIVILEGES: Global privileges held by MySQL user accounts

Character Set Support

  • CHARACTER_SETS: Available character sets
  • COLLATIONS: Collations for each character set
  • COLLATION_CHARACTER_SET_APPLICABILITY: Collations that are applicable to a particular character set

Constraints and Indexes

  • KEY_COLUMN_USAGE: Constraints on key columns
  • REFERENTIAL_CONSTRAINTS: Foreign keys
  • STATISTICS: Table indexes
  • TABLE_CONSTRAINTS: Constraints on tables

Server Settings and Status

  • KEY_COLUMN_USAGE: Constraints
  • GLOBAL_STATUS: The status values for all connections to MySQL
  • GLOBAL_VARIABLES: The values that are used for new connections to MySQL
  • PLUGINS: Server plugins
  • ROCESSLIST: Indicates which threads are running
  • SESSION_STATUS: The status values for the current connection to MySQL
  • SESSION_VARIABLES: The values that are in effect for the current connection to MySQL

Routines and Related Information

  • EVENTS: Scheduled events
  • ROUTINES: Stored procedures and functions
  • TRIGGERS: Triggers in databases
  • PARAMETERS: Stored procedure and function parameters, and stored functions

InnoDB

  • INNODB_CMPand INNODB_CMP_RESET: Status on operations related to compressed InnoDB tables
  • INNODB_CMPMEMand INNODB_CMPMEM_RESET: Status on compressed pages within the InnoDB buffer pool
  • INNODB_LOCKS: Each lock that an InnoDB transaction has requested and holds
  • INNODB_LOCK_WAITS: One or more row locks for each blocked InnoDB transaction
  • INNODB_TRX: Every transaction currently executing inside InnoDB
  • TABLESPACES: Active tablespaces

Using SELECT with INFORMATION_SCHEMA

When you retrieve metadata from the INFORMATION_SCHEMA tables by using SELECT statements, you can use any of the usual SELECT features. You can retrieve the result of an INFORMATION_SCHEMA query into another table by using the CREATE TABLE…SELECT statement or the INSERT…SELECT statement. This enables you to save the results and use them later in other statements.

INFORMATION_SCHEMA: Examples

mysql> SELECT TABLE_NAME, ENGINE 
    -> FROM INFORMATION_SCHEMA.TABLES 
    -> WHERE  TABLE_SCHEMA = 'world_innodb';
	
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
    -> FROM   INFORMATION_SCHEMA.COLUMNS 
    -> WHERE  DATA_TYPE = 'set';
	
mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME 
    -> FROM   INFORMATION_SCHEMA.COLLATIONS 
    -> WHERE  IS_DEFAULT = 'Yes';

mysql> SELECT TABLE_SCHEMA, COUNT(*) 
    -> FROM   INFORMATION_SCHEMA.TABLES 
    -> GROUP BY TABLE_SCHEMA;
	
mysql> DELETE FROM INFORMATION_SCHEMA.VIEWS; 
ERROR 1044 (42000): Access denied for user 
'root'@'localhost' to database 'information_schema'

The examples above, demonstrates how to exploit various features of SELECT to pull out information in different ways from INFORMATION_SCHEMA tables:

  1. Displays the storage engines used for the tables in a given database
  2. Finds all the tables that contain SET columns
  3. Displays the default collation for each character set
  4. Displays the number of tables in each database
  5. The INFORMATION_SCHEMAtables are read-only and cannot be modified with statements such as INSERT, DELETE, or UPDATE. The server produces an error if you execute these types of statements in an attempt to change the data in the INFORMATION_SCHEMAtables

Creating Shell Commands with INFORMATION_SCHEMA Tables

We can use the INFORMATION_SCHEMA tables to obtain information about creating shell commands. Using the CONCAT function, you can combine string content to create shell scripts that can be executed from the command line. As shown in the example below, the SQL statement produces an output that dumps only those tables from the world_innodbdatabase that begin with the word “Country”. 

mysql> SELECT CONCAT("mysqldump -uroot -p ", 
    ->   TABLE_SCHEMA," ", TABLE_NAME, " >> ", 
	->   TABLE_SCHEMA,".bak.sql") 
	-> FROM TABLES WHERE TABLE_NAME LIKE 'Country%';

– Results in the following shell commands: 

shell> mysqldump -uroot -p world_innodb Country 
       >> world_innodb.bak.sql 
shell> mysqldump -uroot -p world_innodb 
       CountryLanguage >> world_innodb.bak.sql

The next step would be to store this output in a batch file that could be executed from a shell command line. This is done by adding the clause INTO OUTFILE: 

mysql> SELECT CONCAT("mysqldump -uroot -p ", 
    -> TABLE_SCHEMA, " ",TABLE_NAME, " >> ",TABLE_SCHEMA,".sql") 
	-> FROM TABLES WHERE TABLE_NAME LIKE 'Country%' 
	-> INTO OUTFILE 'https://cdn.thegeekdiary.com/Country_Dump.sh';

This file could then be executed from the command line, which runs the two mysqldump commands shown in the slide: 

shell> \tmp\Country_Dump.sh 
shell> \tmp\mysqldump -uroot -poracle world_innodb Country >> world_innodb.sql 
shell> \tmp\mysqldump -uroot -poracle world_innodb Country_Language >> world_innodb.sql

Creating SQL Statements with INFORMATION_SCHEMA Tables

Use the mysql command to create SQL statements. Use the -e option to enter a SELECT/CONCAT statement: 

shell> mysql -uroot -p --silent --skip-column-names -e 
"SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.', 
TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.', 
TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'world_innodb';"

The above query results in the following statements sent to standard output: 

CREATE TABLE world_innodb.City_backup LIKE 
        world_innodb.City; 
CREATE TABLE world_innodb.Country_backup LIKE 
        world_innodb.Country_backup; 
CREATE TABLE world_innodb.CountryLanguage_backup LIKE 
        world_innodb.CountryLanguage_backup;

The INFORMATION_SCHEMA tables create SQL statements that you can execute from the command line. The example in the slide uses the mysql command to execute a statement that makes an exact copy of all the tables in the world_innodbdatabase. This command creates a SQL output that, if executed, would create three backup tables based on the tables in the world_innodbdatabase.

NOTE: The –silent command removes the column heading from the output, and the –skip-column-names command removes the formatting around the output (the formatting that makes the output look like a table). These two commands are used to ensure that the commands themselves are interpreted properly without any problems with external formatting or header rows interfering with execution

Adding the pipe symbol ( | ) followed by the execution of the mysql command sends these SQL statements to the MySQL server to be executed:

shell> mysql -uroot -p --silent --skip-column-names -e 
"SELECT CONCAT('CREATE TABLE ', TABLE_SCHEMA, '.', 
TABLE_NAME, '_backup LIKE ', TABLE_SCHEMA, '.', 
TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'world_innodb';" | mysql -uroot -poracle

MySQL-Supported SHOW Statements

In addition to INFORMATION_SCHEMA tables, MySQL also supports the SHOW and DESCRIBE statements as alternative means of accessing metadata. The SHOW and DESCRIBE syntax are not as flexible as using INFORMATION_SCHEMAqueries, but for many purposes the SHOW and DESCRIBE syntax is sufficient. In those cases, it is often quicker and easier for you to use this MySQL specific syntax.

The SHOW statement can be used in many forms, as follows:

  • SHOW DATABASES: Lists the names of the available databases
  • SHOW TABLES: Lists the tables in the default database
  • SHOW TABLES FROM [database_name]: Lists the tables in the specified database
  • SHOW COLUMNS FROM [table_name]: Displays column structure for the table
  • SHOW INDEX FROM [table_name]: Displays information about the indexes and index columns inthe table
  • SHOW CHARACTER SET: Displays the available character sets along with their default collations
  • SHOW COLLATION: Displays the collations for each character set

SHOW Statement: Examples

Some commonly used SHOW statements: 

mysql> SHOW DATABASES; 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| test               | 
| world_innodb       | 
+--------------------+
MySQL> SHOW TABLES; 
MySQL> SHOW TABLES FROM mysql; 
MySQL> SHOW TABLES FROM INFORMATION_SCHEMA; 
MySQL> SHOW COLUMNS FROM CountryLanguage; 
MySQL> SHOW FULL COLUMNS FROM CountryLanguage\G

Additional SHOW Statement Examples

1. SHOW with LIKE and WHERE:

MySQL> SHOW DATABASES LIKE 'm%'; 
MySQL> SHOW COLUMNS FROM Country 
   -> WHERE `Default` IS NULL;

2. Other SHOW statements:

MySQL> SHOW INDEX FROM City\G 
MySQL> SHOW CHARACTER SET; 
MySQL> SHOW COLLATION;

DESCRIBE Statement

DESCRIBE

DESCRIBEcan be abbreviated as DESC, as follows; 

mysql> DESCRIBE table_name; 
and 
mysql> DESC table_name;

Example below shows INFORMATION_SCHEMA table information 

MySQL> DESCRIBE INFORMATION_SCHEMA.CHARACTER_SETS; 
+----------------------+-------------+------+-----+---------+-------+ 
| Field                | Type        | Null | Key | Default | Extra | 
+----------------------+-------------+------+-----+---------+-------+ 
| CHARACTER_SET_NAME   | varchar(64) | NO   |     |         |       | 
| DEFAULT_COLLATE_NAME | varchar(64) | NO   |     |         |       | 
| DESCRIPTION          | varchar(60) | NO   |     |         |       | 
| MAXLEN               | bigint(3)   | NO   |     | 0       |       | 
+----------------------+-------------+------+-----+---------+-------+

The following is equivalent to the above DESCRIBE/DESC examples: 

mysql> SHOW COLUMNS FROM table_name;

However, whereas SHOW COLUMNS supports an optional LIKE and WHERE clause, DESCRIBE does not.

EXPLAIN

EXPLAIN is equivalent to DESCRIBE when given a table name as its parameter: 

mysql> EXPLAIN table_name;

mysqlshow Client

The mysqlshow client provides a command-line interface to various forms of the SHOW statement that list the names of databases, tables within a database, or information about table columns or indexes.

General syntax:

shell> mysqlshow [options] [db_name [table_name [column_name]]]

The options part of the mysqlshow client can include any of the standard connection parameter options, such as –host or –user. You must supply options if the default connection parameters are not appropriate. mysqlshowalso accepts options that are specific to its own operation.

Invoke mysqlshow with the –help option to see a complete list of its options. The action performed by mysqlshow depends on the number of non-option arguments that are provided.

mysqlshow: Examples

You can show information for all databases, or for a specific database, table, and/or column using the mysqlshow query. For example:

shell> mysqlshow -u -p 
+--------------------+ 
|     Databases      | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| test               | 
| world_innodb       | 
+--------------------+
shell> mysqlshow world_innodb 
shell> mysqlshow world_innodb City 
shell> mysqlshow world_innodb City CountryCode 
shell> mysqlshow "w%"

The examples above demonstrate some uses of the mysqlshow client:

  1. With no arguments, mysqlshow displays a result similar to that of SHOW DATABASES.
  2. With a single argument, mysqlshow interprets it as a database name and displays a result similar to that of SHOW TABLES for the database.
  3. 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.
  4. 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 COLUMNSoutput only for that column.
  5. If the final argument on the command line contains special characters, 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. This example command shows only those databases with a name that begins with w.
NOTE These examples require the use of user and password parameters as part of command execution.

You May Also Like