最後更新: 2022-12-15

目錄

• pdo_mysql, mysqli, mysqlnd 分別
• PHP Code Example
• Timeout Settings
• mysql settings
• mysqli settings
• mysqlnd settings

 

---

pdo_mysql, mysql, mysqli, mysqlnd 分別

 

The pdo_mysql, mysqli, mysql extensions are lightweight wrappers on top of a C client library.

The extensions can either use the mysqlnd library or the libmysqlclient library.

(Choosing a library is a compile time decision)

mysqlnd and libmysqlclient use different networking APIs.

mysqlnd uses PHP streams, whereas libmysqlclient uses its own wrapper around the operating level network calls.

PHP -> pdo_mysql / mysqli / mysql -> mysqlnd / libmysqlclient

Driver

• mysqlnd: Part of the PHP distribution, 以 extension 形式寫成
• libmysqlclient: 要另外安裝

Notes

• nd = Native Driver
• i = improved

Checking

# php 7.X, 8.X

php -i | grep mysql

mysqli

Client API library version    mysqlnd 5.0.12-dev - 20150407 - $Id: b5c590... $

pdo_mysql

Client API version    mysqlnd 5.0.12-dev - 20150407 - $Id: b5c590... $

 * mysqli 及 pdo_mysql 可以使用不同的 "Client API version"

Compile time decision

// Default

./configure --with-mysqli --with-pdo-mysql --with-mysql

// Recommended, compiles with mysqlnd

./configure --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd --with-mysql=mysqlnd

// Not recommended, compiles with libmysqlclient

./configure --with-mysqli=/path/to/mysql_config --with-pdo-mysql=/path/to/mysql_config --with-mysql=/path/to/mysql_config

 

---

PHP Code Example

 

Check Connection Only

<?php
    $server = "localhost";
    $user = "username";
    $pw = "password";
    $db = "myDB";

    // Create connection
    @$conn= mysqli_connect($server, $user, $pw, $db);
    // Check connection
    if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
    }
    echo "Connected Successfully.<br>";
    // ...
    mysql_close($conn);
?>

Run SELECT Command

<?
    // ...
    function checkStatus($conn, $item){
        $sql = "SHOW STATUS where Variable_name=\"$item\"";
        $r = mysqli_query($conn, $sql);
        $row = mysqli_fetch_array($r);
        echo "$item: $row[1]<br>";
        mysqli_free_result($r);
    }
    checkStatus($conn, "Uptime");
    checkStatus($conn, "Connections");
?>

mysqli_fetch_array

Returns an array that corresponds to the fetched row or null if there are no more rows for the result set.

• 同時有 numeric indices 及 associative indices
• Field names returned by this function are case-sensitive.

echo "$item: $row[1]<br>";

print_r ($row);

Array ( [0] => Uptime [Variable_name] => Uptime [1] => 65726 [Value] => 65726 )

 

---

Timeout Settings

 

mysqlnd

PHP, by default, sets a read timeout of 60s for streams.

This is set via php.ini ( default_socket_timeout )

mysqlnd.net_read_timeout

gets used by any extension (ext/mysql, ext/mysqli, PDO_MySQL) that uses mysqlnd

 * only for TCP/IP connections

Over => "2006 - MySQL Server has gone away"

MySQL Client Library

Default timeout: 24 * 3600 seconds (1 day) and waits for other timeouts to occur

(such as TCP/IP timeouts)

注意

• Before PHP 7.2.0 the default value was "31536000"

 

 

---

mysql settings

 

Settings

mysql.allow_persistent=1
mysql.max_persistent=-1
mysql.max_links=-1
mysql.connect_timeout=60

mysql.max_persistent

The maximum number of persistent MySQL connections per process

mysql.max_links

The maximum number of MySQL connections per process

mysql.connect_timeout

Connect timeout in seconds.
On Linux this timeout is also used for waiting for the first answer from the server.

 

---

mysqli settings

 

i = improved

Settings

mysqli.allow_persistent = 1
mysqli.max_persistent = -1
mysqli.max_links = -1
mysqli.reconnect = Off

Note

The php.ini setting mysqli.reconnect is ignored by the mysqlnd driver, so automatic reconnection is never attempted.

 

---

mysqlnd

 

nd = Native Driver

Settings

 

---