Mysqli 简明教程

MySQLi - Database Info

Obtaining and Using MySQL Metadata

有三种类型的信息你希望从 MySQL 获得。

There are three types of information, which you would like to have from MySQL.

  1. Information about the result of queries − This includes the number of records affected by any SELECT, UPDATE or DELETE statement.

  2. Information about the tables and databases − This includes information pertaining to the structure of the tables and the databases.

  3. Information about the MySQL server − This includes the status of the database server, version number, etc.

在MySQL提示符下获取所有这些信息非常容易，但在使用PERL或PHP API时，我们需要显式调用各种API才能获取所有这些信息。

It is very easy to get all this information at the MySQL prompt, but while using PERL or PHP APIs, we need to call various APIs explicitly to obtain all this information.

Obtaining the Number of Rows Affected by a Query

现在让我们看看如何获取此信息。

Let is now see how to obtain this information.

PERL Example

在 DBI 脚本中，受影响的行数由 do( ) 或 execute( ) 命令返回，具体取决于如何执行查询。

In DBI scripts, the affected row count is returned by the do( ) or by the execute( ) command, depending on how you execute the query.

# Method 1
# execute $query using do( )
my $count = $dbh→do ($query);
# report 0 rows if an error occurred
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

# Method 2
# execute query using prepare( ) plus execute( )
my $sth = $dbh→prepare ($query);
my $count = $sth→execute ( );
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

PHP Example

在 PHP 中，调用 mysql_affected_rows( ) 函数来找出查询更改了多少行。

In PHP, invoke the mysql_affected_rows( ) function to find out how many rows a query changed.

$result_id = mysql_query ($query, $conn_id);
# report 0 rows if the query failed
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affected\n");

Listing Tables and Databases

列出所有数据库以及数据库服务器可用的表非常容易。如果你没有足够的权限，你的结果可能是 null 。

It is very easy to list down all the databases and the tables available with a database server. Your result may be null if you don’t have the sufficient privileges.

除了在以下代码块中显示的方法外，你还可以使用 SHOW TABLES 或 SHOW DATABASES 查询以在 PHP 或 PERL 中获取表或数据库列表。

Apart from the method which is shown in the following code block, you can use SHOW TABLES or SHOW DATABASES queries to get the list of tables or databases either in PHP or in PERL.

PERL Example

# Get all the tables available in current database.
my @tables = $dbh→tables ( );

foreach $table (@tables ){
   print "Table Name $table\n";
}

PHP Example

尝试以下示例以获取数据库信息 −

Try the following example to get database info −

将以下示例复制粘贴为 mysql_example.php：

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Getting MySQL Database Info</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         $tutorial_count = null;

         if($mysqli→connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli→connect_error);
            exit();
         }
         printf('Connected successfully.<br />');

         if ($result = mysqli_query($mysqli, "SELECT DATABASE()")) {
            $row = mysqli_fetch_row($result);
            printf("Default database is %s<br />", $row[0]);
            mysqli_free_result($result);
         }
         $mysqli→close();
      ?>
   </body>
</html>

Output

访问部署在 Apache Web 服务器上的 mysql_example.php, 并验证输出。

Access the mysql_example.php deployed on apache web server and verify the output.

Connected successfully.
Default database is tutorials

Getting Server Metadata

MySQL 中有几个重要命令可以在 MySQL 提示符处执行，也可以使用 PHP 等任何脚本来获取有关数据库服务器的各种重要信息。

There are a few important commands in MySQL which can be executed either at the MySQL prompt or by using any script like PHP to get various important information about the database server.

Sr.No.

Command & Description

1

SELECT VERSION( ) Server version string

2

SELECT DATABASE( ) Current database name (empty if none)

3

SELECT USER( ) Current username

4

SHOW STATUS Server status indicators

5

SHOW VARIABLES Server configuration variables