MySQL metadata

You may wonder MySQL the following three types of information:

• Query Result Information: SELECT, UPDATE, or DELETE affect the number of records statement.
• Information database and data tables: contains information database structure and data tables.
• MySQL server information: contains the current state of the version number of the database server.

In the MySQL command prompt, we can easily get more information about the server. However, if you use Perl or PHP scripting language, you need to call function to obtain a specific interface. Next we will detail.

Get the number of records affected by the statement of the query

PERL examples

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

# 方法 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);

PHP Examples

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

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

Databases and data tables listing

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

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

PERL examples

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

PHP Examples

<?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 syntax can be used in the MySQL command prompt can also be used in the script, such as PHP scripts.