MySQL Metadata

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

• Query result information:Number of records affected by SELECT, UPDATE, or DELETE statements.

• Information about the database and data table:Contains the structure information of the database and data table.

• MySQL Server Information:Contains the current status of the database server, version number, etc.

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

Get the number of rows affected by the query

PERL Example

In DBI scripts, The number of rows affected by the statement is returned by the function do( ) or execute( ):

# Method　1
# Use do( ) to execute $query　
my $count = $dbh->do( $query );
# If an error occurs, it will output 0
printf("%d rows affected\n", (defined($count) ? $count : 0));
# Method　2
# Use prepare( ) and execute( ) to execute $query　
my $sth = $dbh->prepare( $query );
my $count = $sth->execute( );
printf("%d rows affected\n", (defined($count) ? $count : 0));

PHP Example

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

$result_id = mysqli_query($conn_id, $query);
# If the query fails, return　
$count = ($result_id ? mysqli_affected_rows($conn_id) : 0);
print("$count rows affected\n");

Database and Table List

You can easily get the list of databases and 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 statement to get the list of databases and tables.

PERL Example

# Get all available tables in the current database.
my @tables = $dbh->tables ( );
foreach ($table as @tables) {
　　　print "Table name $table\n";
}

PHP Example

The following example output all databases on the MySQL server:

<?php
$dbhost = 'localhost';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = ''123456';　　　　　　　　　　//　MySQL username and password
$conn　=　mysqli_connect($dbhost,　$dbuser,　$dbpass);
if(!　$conn　)
{
　　　　die('Connection failed:　'　.　mysqli_error($conn));
}
//　Set encoding to prevent Chinese garbled characters
$db_list　=　mysqli_query($conn,　'SHOW　DATABASES');
while　($db　=　mysqli_fetch_object($db_list))
{
　　echo　$db->Database　.　"<br　/>";
}
mysqli_close($conn);
?>

Get server metadata

The following command statements can be used in MySQL's command prompt, as well as in scripts Usage, such as PHP scripts.