How to view the status and version number data table structure information of MySQL database?

MySQL Metadata

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

• Query result information: The number of records affected by the SELECT, UPDATE or DELETE statement.
• Information about databases and data tables: Contains the structure information of the database and data table.
• MySQL server information: Contains the current state 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 get it.Next we will introduce in detail.

Get the number of records affected by the query statement

PERL instance

In DBI scripts, the number of records affected by the statement is returned by the functions do( ) or execute( ):

# 方法 1
# 使用do( ) 执行  $query 
my $count = $dbh->do ($query);
# 如果发生错误会输出 0
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);
# 方法 2
# 使用prepare( ) 及 execute( ) 执行  $query 
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);

PHP instance

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

$result_id = mysqli_query ($conn_id, $query);
# 如果查询失败返回 
$count = ($result_id ? mysqli_affected_rows ($conn_id) : 0);
print ("$count 条数据被影响\n");

List of databases and data tables

You can easily get a 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 a list of databases and data tables.

PERL instance

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

PHP instance

The following example outputs all databases on the MySQL server:

<?
php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
$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 at the MySQL command prompt or in scripts, such as PHP scripts.