[mysql tutorial] 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, version number, etc. of the database server.

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 Example

In the DBI script, the number of records affected by the statement is returned through 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);

PHP Example

In PHP, you can use the 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 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 Example

<?php
$con = mysql_connect("localhost", "userid", "password");
if (!$con)
{
  die(&#39;Could not connect: &#39; . 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 used in PHP scripts.

Command

Description

SELECT VERSION( ) Server version information

SELECT DATABASE( ) Current Database name (or return empty)

SELECT USER( ) Current user name

SHOW STATUS Server status

SHOW VARIABLES Server configuration variables

The above is [ mysql tutorial】MySQL metadata content, for more related content, please pay attention to the PHP Chinese website (www.php.cn)!