MySQL metadata

MySQL Metadata

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

• Query result information: The number of records affected by the SELECT, UPDATE or DELETE statement.

• Database and data table information: Contains structural 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 obtain the above server information. But if you use a scripting language such as Perl or PHP, you need to call a specific interface function to obtain it. We will introduce it in detail next.

Get the number of records affected by the query statement

PERL instance

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

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

# 方法 2
# 使用prepare( ) 及 execute( ) 执行  $query 
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

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

$result_id = mysql_query ($query, $conn_id);
# 如果查询失败返回 
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affected\n")

Database and data table list

You can easily get the database and data table list in the MySQL server. If you do not have sufficient permissions, the result will be null.

You can also use the SHOW TABLES or SHOW DATABASES statement to obtain a list of databases and data tables.

PERL Example

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

<?php
$con = mysql_connect("localhost", "userid", "password");
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}
$db_list = mysql_list_dbs($con);
while ($db = mysql_fetch_object($db_list))
{
  echo $db->Database . "<br />";
}
mysql_close($con);
?>

Get server metadata

The following command statements can be used in the MySQL command prompt or in a script Use, for example, a PHP script.