MySQL Metadata

MySQL metadata is information about the database and its objects (such as tables, columns, indexes, etc.).

Metadata is stored in system tables, which are located in the information_schema database of the MySQL database. By querying these system tables, you can obtain detailed information about database structure, objects, and other related information.

You may want to know the following three kinds of information about MySQL:

• Query result information:The number of records affected by SELECT, UPDATE, or DELETE statements.
• Database and data table information:Contains structural information about databases and data tables.
• MySQL server information:Contains the current status, version number, etc. of the database server.

In the MySQL command prompt, we can easily obtain the above server information, but if you use scripting languages such as Perl or PHP, you need to call specific interface functions to obtain it. We will introduce this in detail next.

The following are some commonly used MySQL metadata queries:

View all databases:

SHOW DATABASES;

Select database:

USE database_name;

View all tables in the database:

SHOW TABLES;

View table structure:

DESC table_name;

View table indexes:

SHOW INDEX FROM table_name;

View the table creation statement:

SHOW CREATE TABLE table_name;

View the number of rows in the table:

SELECT COUNT(*) FROM table_name;

View column information:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

In the above SQL statements, 'your_database_name' and 'your_table_name' are your database name and table name, respectively.

View foreign key information:

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    TABLE_SCHEMA = 'your_database_name'
    AND TABLE_NAME = 'your_table_name'
    AND REFERENCED_TABLE_NAME IS NOT NULL;

Please replace 'your_database_name' and 'your_table_name' in the above SQL statements with the actual database name and table name.

---

information_schema database

information_schema is a system database in the MySQL database. It contains metadata information about the database server, and this information is stored in the form of tables in the information_schema database.

SCHEMATA table

Stores information about databases, such as database name, character set, collation, etc.

SELECT * FROM information_schema.SCHEMATA;

TABLES table

Contains information about all tables in the database, such as table name, database name, engine, row count, etc.

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name';

COLUMNS table

Contains information about columns in tables, such as column name, data type, whether NULL is allowed, etc.

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

STATISTICS table

Provides statistical information about table indexes, such as index name, column name, uniqueness, etc.

SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

KEY_COLUMN_USAGE table

Contains information about foreign keys in tables, such as foreign key name, column name, referenced table, etc.

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

REFERENTIAL_CONSTRAINTS table

Stores information about foreign key constraints, such as constraint name, referenced table, etc.

SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

These tables provide rich metadata information that can be used to query database structure, table information, column information, index information, etc.

Please note that you need to replace 'your_database_name' and 'your_table_name' in the queries with the actual database name and table name.

---

Get the number of records affected by a query statement

PERL Example

In DBI scripts, the number of records affected by a statement is returned through the do( ) or execute( ) function:

# 方法 1
# 使用do( ) 执行  $query 
my $count = $dbh->do ($query);
# 如果发生错误会输出 0
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);

# 方法 2
# 使用prepare( ) 及 execute( ) 执行  $query 
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);

PHP Example

In PHP, you can use the mysqli_affected_rows( ) function to get the number of records affected by a query statement.

$result_id = mysqli_query ($conn_id, $query);
# 如果查询失败返回 
$count = ($result_id ? mysqli_affected_rows ($conn_id) : 0);
print ("$count 条数据被影响\n");

---

Database and data table list

You can easily get the list of databases and data tables in the MySQL server. If you do not have sufficient permissions, the result will return null.

You can also use the SHOW TABLES or SHOW DATABASES statements to get the list of databases and data tables.

PERL Example

# 获取当前数据库中所有可用的表。
my @tables = $dbh->tables ( );
foreach $table (@tables ){
   print "表名 $table\n";
}

PHP Example

The following example outputs all databases on the MySQL server:

---

Get server metadata

The following command statements can be used in the MySQL command prompt, and can also be used in scripts, such as PHP scripts.

Command	Description
SELECT VERSION( )	Server version information
SELECT DATABASE( )	Current database name (or returns empty)
SELECT USER( )	Current username
SHOW STATUS	Server status
SHOW VARIABLES	Server configuration variables

Other extensions