Shulou(Shulou.com)05/31 Report--

This article mainly talks about "what is MySQL metadata". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is MySQL metadata?"

1. MySQL metadata

1.1. Metadata access method

A database is a structured collection of data. Metadata is "data about data". MySQL provides access to metadata in the following ways:

The INFORMATION_SCHEMA:MySQL server contains a data dictionary of a database (schema) called INFORMATION_SCHEMA, which contains many objects displayed as tables.

SHOW statement: a special syntax for getting server statistics, schema, and data related to schema objects. SHOW DATABASES and SHOW TABLES return a list of database and table names; SHOW COLUMNS generates the definition of the columns in the table; and SELECT permission is required to use the SHOW statement.

DESCRIBE: SQL statement shortcuts that can be used to query table structures and column properties

Mysqlshow: used as a command line command to point to some SHOW statements. The parameters you set will determine the information to be displayed, and then the program will issue the corresponding SHOW statement and display the result of the statement.

The methods of accessing metadata in these four are described in detail below.

1.2. INFORMATION_SCHEMA database

The INFORMATION_SCHEMA database acts as a central system repository for database metadata, containing schema and schema objects, server statistics (state variables, settings, connections). It is not stored on the hard disk, and in this respect, it is a "virtual database"; however, it contains tables like any other database, and like any other table, you can use SELECT to access the contents of the tables.

1) query tables in INFORMATION_SCHEMA

Mysql > SELECT TABLE_NAME

-> FROM INFORMATION_SCHEMA.TABLES

-> WHERE TABLE_SCHEMA = 'information_schema'

-> ORDER BY TABLE_NAME

The INFORMATION_SCHEMA table contains the following types of information:

Zero table information

COLUMNS: columns in tables and views

ENGINES: storage engine

SCHEMATA: database

TABLES: tables in the database

VIEWS: views in the database

Zero partition

PARTITIONS: table partition

FILES: the file that stores the MySQL NDB disk data table

ZR permission

Column permissions for the COLUMN_PRIVILEGES:MySQL user account

Database permissions held by the SCHEMA_PRIVILEGES:MySQL user account

Table permissions owned by the TABLE_PRIVILEGES:MySQL user account

Global permissions held by the USER_PRIVILEGES:MySQL user account

Support for the dummy character set

CHARACTER_SETS: available character sets

COLLATIONS: collation of each character set

COLLATION_CHARACTER_SET_APPLICABILITY: suitable for collation of specific character sets

ZR constraint and index

KEY_COLUMN_USAGE: constraints for key columns

REFERENTIAL_CONSTRAINTS: foreign key

STATISTICS: table index

TABLE_CONSTRAINTS: constraints for tabl

Z. server settings and status

KEY_COLUMN_USAGE: constraint

GLOBAL_STATUS: status value of all MySQL connections

GLOBAL_VARIABLES: the value for the new MySQL connection

PLUGINS: server plug-in

PROCESSLIST: indicates which threads are running

SESSION_STATUS: status value of the current MySQL connection

SESSION_VARIABLES: the effective value of the current MySQL connection

Zero routine and related information

EVENTS: scheduled event

ROUTINES: stored procedures and functions

TRIGGERS: triggers in the database

PARAMETERS: stored procedures and functional parameters and stored functions

Dead InnoDB

INNODB_CMP and INNODB_CMP_RESET: status of related operations on compressed InnoDB tables

Status of compressed pages in INNODB_CMPMEM and INNODB_CMPMEM_RESET:InnoDB buffer pools

Every lock requested and held by the INNODB_LOCKS:InnoDB firm

INNODB_LOCK_WAITS: one or more row locks for each blocked InnoDB transaction

INNODB_TRX: all transactions currently being executed within InnoDB

TABLESPACES: active tablespace

For more information about INFORMATION_SCHEMA, refer to the MySQL reference Manual:

Http://dev.mysql.com/doc/refman/5.6/en/information-schema.html .

For example: query INFORMATION_SCHEMA database table columns:

Mysql > SELECT COLUMN_NAME

-> FROM INFORMATION_SCHEMA.COLUMNS

-> WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'

-> AND TABLE_NAME = 'VIEWS'

When you use the SELECT statement to retrieve metadata in an INFORMATION_SCHEMA table, you can use any common SELECT functionality. You can retrieve the results of an INFORMATION_SCHEMA query to other tables by using CREATE TABLE...SELECT statements or INSERT...SELECT statements. You can save the results so that you can use them later in other statements.

a. Displays the storage engine used for tables in a given database

Mysql > SELECT TABLE_NAME, ENGINE

-> FROM INFORMATION_SCHEMA.TABLES

-> WHERE TABLE_SCHEMA = 'world_innodb'

b. Find all tables that contain SET columns

Mysql > SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

-> FROM INFORMATION_SCHEMA.COLUMNS

-> WHERE DATA_TYPE = 'set'

c. Displays the default collation for each character set

Mysql > SELECT CHARACTER_SET_NAME, COLLATION_NAME

-> FROM INFORMATION_SCHEMA.COLLATIONS

-> WHERE IS_DEFAULT = 'Yes'

d. Display the number of tables in each database

Mysql > SELECT TABLE_SCHEMA, COUNT (*)

-> FROM INFORMATION_SCHEMA.TABLES

-> GROUP BY TABLE_SCHEMA

The E. INFORMATION_SCHEMA table is read-only and cannot be modified with statements such as INSERT, DELETE, or UPDATE. If you execute these types of statements to try to change the data in the INFORMATION_SCHEMA table, the server generates an error.

Mysql > DELETE FROM INFORMATION_SCHEMA.VIEWS

ERROR 1044 (42000): Access denied for user

'root'@'localhost' to database 'information_schema'

2) use INFORMATION_SCHEMA table to create Shell command

Use the CONCAT feature to combine the contents of strings to create shell scripts that can be executed on the command line. As shown in the example, the SQL statement produces an output that dumps only those tables in the world_innodb database that start with the word "Country". The output produces a shell script that can be executed correctly on the shell command line. The next step is to store this output in a batch file that can be executed on the 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'/ Country_Dump.sh'

You can then execute this file on the command line, which runs the sample's two mysqldump commands:

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

3) use INFORMATION_SCHEMA table to create SQL statement

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

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'; "

Causes the following statement to be 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 table creates SQL statements that can be executed on the command line. This example uses the mysql command to execute a statement to make an exact copy of all the tables in the world_innodb database. This command creates the SQL output, and if executed, creates three backup tables based on the tables in the world_innodb database.

Note:-- the silent command removes the column header from the output, and the-- skip-column-names command removes the format in the output (making the output similar to the table format). These two commands are used to ensure that the interpretation of the command itself is correct and that there are no external formatting or title line problems that interfere with execution.

Adding a pipe symbol (|) and then executing the mysql command sends these SQL statements to the MySQL server for execution:

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

1.3. SHOW statement

In addition to the INFORMATION_SCHEMA table, MySQL also supports SHOW and DESCRIBE statements as alternative ways to access metadata. SHOW and DESCRIBE syntax are not as flexible as using INFORMATION_SCHEMA queries, but SHOW and DESCRIBE syntax are sufficient for most uses. In these cases, using MySQL-specific syntax is usually faster and easier. The SHOW statements supported by MySQL are:

SHOW DATABASES

SHOW TABLES

SHOW TABLE STATUS

SHOW CREATE TABLE

SHOW OPEN TABLES

SHOW INDEX

SHOW COLUMNS

SHOW PROCESSLIST

SHOW COLLATION

SHOW CHARACTER SET

Example of a SHOW statement:

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

Mysql > SHOW DATABASES LIKE'm%'

Mysql > SHOW COLUMNS FROM Country WHERE `Default` IS NULL

Mysql > SHOW INDEX FROM City\ G

Mysql > SHOW CHARACTER SET

Mysql > SHOW COLLATION

1.4. DESCRIBE statement

DESCRIBE is equivalent to SHOW COLUMNS and can be abbreviated to DESC

Mysql > DESCRIBE

Display INFORMATION_SCHEMA 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 | |

+-+ +

For example:

Mysql > DESCRIBE table_name

Mysql > DESC table_name

The following statement is equivalent to the above DESCRIBE/DESC example:

Mysql > SHOW COLUMNS FROM table_name

However, SHOW COLUMNS supports the optional LIKE and WHERE clauses, while DESCRIBE does not.

EXPLAIN: when you specify a table name as a parameter, EXPLAIN is equivalent to DESCRIBE:

Mysql > EXPLAIN table_name

1.5. Mysqlshow command

Mysqlshow provides a command-line interface for SHOW statements in various formats that list the name of the database, tables in the database, or information about table columns or indexes.

The options section of mysqlshow can contain any standard connection parameter option, such as-- host or-- user. If the default connection parameters are not appropriate, an option must be provided. Mysqlshow also accepts options specific to its own operation. Call mysqlshow with the-- help option to see a complete list of its options. The actions performed by mysqlshow depend on the number of non-option parameters that have been provided.

Mysqlshow example

Displays information about all databases or specific databases, tables, and / or columns:

a. In the absence of parameters, mysqlshow will display results similar to SHOW DATABASES

Shell > mysqlshow-u-p

+-+

| | Databases |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

| | world_innodb |

+-+

b. In the case of a single parameter, mysqlshow interprets the parameter as a database name and displays results similar to SHOW TABLES for that database.

Shell > mysqlshow world_innodb

c. In the case of two parameters, mysqlshow interprets the parameters as database and table names and displays results similar to SHOW FULL COLUMNS for the table.

Shell > mysqlshow world_innodb City

d. In the case of three parameters, the output is the same as in the case of two parameters, except that mysqlshow uses the third parameter as the column name and displays the SHOW FULL COLUMNS output only for that column.

Shell > mysqlshow world_innodb City CountryCode

e. If the last parameter on the command line contains special characters, mysqlshow interprets the parameter as a pattern and displays only the names that match the pattern. Special characters include:% or * (matches any character sequence) and _ or? (matches any single character). The command in this example shows only those databases whose names start with w.

Shell > mysqlshow "w"

Note: these examples require that the user and password be used as parameters when executing the command.

At this point, I believe you have a deeper understanding of "what is MySQL metadata?". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.