YouTip

MySQL metadata is information about a database and its objects (such as tables, columns, indexes, etc.). Metadata is stored in system tables, which are located in the `information_schema` database within MySQL. By querying these system tables, you can obtain detailed information about the database structure, objects, and other related information. You might want to know the following three types of information in MySQL: * **Query result information:** The number of records affected by SELECT, UPDATE, or DELETE statements. * **Database and table information:** Contains structural information about databases and tables. * **MySQL server information:** Contains the current status of the database server, version number, etc. In the MySQL command prompt, we can easily obtain the above server information. However, if using scripting languages like Perl or PHP, you need to call specific interface functions to obtain it. We will detail this next. Here are some common MySQL metadata queries: View all databases: ```sql SHOW DATABASES; Select a database: ```sql USE database_name; View all tables in a database: ```sql SHOW TABLES; View the structure of a table: ```sql DESC table_name; View the indexes of a table: ```sql SHOW INDEX FROM table_name; View the CREATE TABLE statement for a table: ```sql SHOW CREATE TABLE table_name; View the row count of a table: ```sql SELECT COUNT(*) FROM table_name; View column information: ```sql 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: ```sql 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 and table names. * * * ## The information_schema Database `information_schema` is a system database within MySQL that contains metadata information about the database server. This information is stored in the form of tables within the `information_schema` database. ### SCHEMATA Table Stores information about databases, such as database name, character set, collation, etc. ```sql SELECT * FROM information_schema.SCHEMATA; ### TABLES Table Contains information about all tables in a database, such as table name, database name, engine, row count, etc. ```sql SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name'; ### COLUMNS Table Contains information about columns in a table, such as column name, data type, whether NULL is allowed, etc. ```sql 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. ```sql 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 a table, such as foreign key name, column name, related table, etc. ```sql 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, related table, etc. ```sql SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name'; These tables provide rich metadata information, which 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 and table names. * * * ## Getting the Number of Records Affected by a Query ### Perl Example In a DBI script, the number of records affected by a statement is returned via the `do()` or `execute()` functions: ```perl # Method 1 # Execute $query using do() my $count = $dbh->do ($query); # If an error occurs, it will output 0 printf "%d rows were affectedn", (defined ($count) ? $count : 0); # Method 2 # Execute $query using prepare() and execute() my $sth = $dbh->prepare ($query); my $count = $sth->execute ( ); printf "%d rows were affectedn", (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. ```php $result_id = mysqli_query ($conn_id, $query); # If the query fails, return 0 $count = ($result_id ? mysqli_affected_rows ($conn_id) : 0); print ("$count rows were affectedn"); * * * ## Database and Table List You can easily get the list of databases and tables on a MySQL server. If you do not have sufficient privileges, the result will return null. You can also use the `SHOW TABLES` or `SHOW DATABASES` statements to get the list of databases and tables. ### Perl Example ```perl # Get all available tables in the current database. my @tables = $dbh->tables ( ); foreach $table (@tables ){ print "Table name $tablen"; } ### PHP Example The following example outputs all databases on the MySQL server: ## View All Databases ```php Database . "
"; } mysqli_close($conn); ?> * * * ## Getting Server Metadata The following command statements can be used in the MySQL command prompt and also 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 |