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.
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.
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:
- COLUMNS:Columns in tables and views
- ENGINES:Storage engines
- TABLES:Tables in databases
- VIEWS:Views in databases
- PARTITIONS: Table partitions
- FILES: Files in which MySQL NDB disk data tables are stored
- 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_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.
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:
- Displays the storage engines used for the tables in a given database
- Finds all the tables that contain SET columns
- Displays the default collation for each character set
- Displays the number of tables in each database
- 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.
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;
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 is equivalent to DESCRIBE when given a table name as its parameter:
mysql> EXPLAIN table_name;
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.
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.
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:
- With no arguments, mysqlshow displays a result similar to that of 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 COLUMNSoutput only for that column.
- 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.