PHP 与 MySQL 交互

Mysqli 扩展

mysqli 扩展采用了双接口，面向对象和面向过程 (The mysqli extension features a dual interface, dual procedural and object-oriented interface)。

建立连接

面向过程接口

$mysqli = mysqli_connect("localhost", "db_user", "db_passwd", "test");
$res = mysqli_query($mysqli, "SELECT 'Please, do not use ' AS _msg FROM DUAL");
$row = mysqli_fetch_assoc($res);
echo $row['_msg'];

// 对比原 mysql 扩展
$mysql = mysql_connect("localhost", "db_user", "db_passwd");
mysql_select_db("test");
$res = mysql_query("SELECT 'the mysql extension for new developments.' AS _msg FROM DUAL", $mysql);
$row = mysql_fetch_assoc($res);
echo $row['_msg'];

输出：

Please, do not use the mysql extension for new developments.

面向对象接口

$mysqli = new mysqli("localhost", "db_user", "db_passwd", "test");

// 错误控制
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
}
// 即使不做错误控制，在 PHP 错误级别为 "E_ALL" 时仍会出现
// Warning: mysqli::mysqli() [mysqli.mysqli]: (28000/1045): Access denied...

$res = $mysqli->query("SELECT 'A world full of choices to please everybody.' AS _msg FROM DUAL");
$row = $res->fetch_assoc();
echo $row['_msg'];

The MySQL server supports the use of different transport layers (传输层) for connections. Connections use TCP/IP, Unix domain sockets or Windows named pipes.

The hostname localhost has a special meaning. It is bound to the use of Unix domain sockets. It is not possible to open a TCP/IP connection using the hostname localhost you must use 127.0.0.1 instead.

$mysqli = new mysqli("localhost", "db_user", "db_passwd", "test");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
echo $mysqli->host_info . "\n";
// Localhost via UNIX socket

$mysqli = new mysqli("127.0.0.1", "db_user", "db_passwd", "test");
echo $mysqli->host_info . "\n";
// 127.0.0.1 via TCP/IP

但是在 Windows 主机由于不支持，都显示为 TCP/IP

执行SQL语句

(Executing statements)

Buffered result sets

mysqli_query() 整合了语句执行和结果集缓冲 (combines statement execution and result set buffering)。

$mysqli = new mysqli("localhost", "db_user", "db_passwd", "test");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT)") ||
    !$mysqli->query("INSERT INTO test(id) VALUES (1)")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

...
$mysqli->query("INSERT INTO test(id) VALUES (2), (3)");
$res = $mysqli->query("SELECT id FROM test ORDER BY id ASC");

echo "Reverse order...\n";
for ($row_no = $res->num_rows - 1; $row_no >= 0; $row_no--) {
    $res->data_seek($row_no);
    $row = $res->fetch_assoc();
    echo " id = " . $row['id'] . "\n";
}

echo "Result set order...\n";
$res->data_seek(0);
while ($row = $res->fetch_assoc()) {
    echo " id = " . $row['id'] . "\n";
}

Unbuffered result sets

$mysqli->real_query("SELECT id FROM test ORDER BY id ASC");
$res = $mysqli->use_result();

echo "Result set order...\n";
while ($row = $res->fetch_assoc()) {
    echo " id = " . $row['id'] . "\n";
}

Calling mysqli_query() is identical to (等同于) calling mysqli_real_query() followed by mysqli_store_result().

The mysqli_query(), mysqli_real_query() and mysqli_multi_query() functions are used to execute non-prepared statements. With the text protocol, the MySQL server converts all data of a result sets into strings before sending. It is possible to convert integer and float columns back to PHP numbers by setting the MYSQLI_OPT_INT_AND_FLOAT_NATIVE connection option, if using the mysqlnd library.

mysqlnd 到 PHP 5.3.0 和以上版本才添加 http://www.php.net/manual/zh/mysqlnd.install.php ，下述代码需要 PHP 5.3.0 及以上版本支持

$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
$mysqli->real_connect("localhost", "db_user", "db_passwd", "test");

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

$res = $mysqli->query("SELECT id, label FROM test WHERE id = 1");
$row = $res->fetch_assoc();

printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));

//  id = 1 (integer)
//  label = a (string)

设置执行语句的字符编码 mysqli_query("set names utf8");

参见：Mysqli 扩展手册

PDO 扩展

关闭连接

连接数据成功后，返回一个 PDO 类的实例给脚本，此连接在 PDO 对象的生存周期中保持活动。要想关闭连接，需要销毁对象以确保所有剩余到它的引用都被删除，可以赋一个 NULL 值给对象变量。如果不明确地这么做，PHP 在脚本结束时会自动关闭连接。

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
// 在此使用连接
...
// 现在运行完成，在此关闭连接
$dbh = null;

fetch_style

默认为 PDO::FETCH_BOTH

• PDO::FETCH_NUM：返回一个索引为以0开始的结果集列号的数组
• PDO::FETCH_ASSOC：返回一个索引为结果集列名的数组
• PDO::FETCH_KEY_PAIR：获取一个有两列的结果集到一个数组，其中第一列为键名，第二列为值。自 PHP 5.2.3 起可用。
• 想要返回一个包含结果集中单独一列所有值的数组，需要指定 PDO::FETCH_COLUMN 。通过指定 column-index 参数获取想要的列。
• 想要获取结果集中单独一列的唯一值，需要将 PDO::FETCH_COLUMN 和 PDO::FETCH_UNIQUE 按位或。
• 想要返回一个根据指定列把值分组后的关联数组，需要将 PDO::FETCH_COLUMN 和 PDO::FETCH_GROUP 按位或。

e.g.

$pdo = new PDO;
$pdo->query($q)->fetch(PDO::FETCH_COLUMN, 0)

SQL Debug

$res = $dbh->query($q);
if ($res === false) // Not lost $res = 0
     print_r($dbh->errorInfo());

参见：预定义常量 Introduction to PHP PDO

Mysql 扩展

This extension is deprecated as(不赞成使用) of PHP 5.5.0, and will be removed in the future.

http://php.net/manual/zh/ref.mysql.php

持久连接

持久连接相对与非持久连接，在功能上没有任何增加，仅有的一个优势是高效性；持久连接仅在 PHP 作为 Apache 模块运行时起作用。

SQL 辅助函数及方法

将数组转换成 in condition

$arr = array(1,2,3);
$condition = implode(',', $arr);
$sql = "SELECT id from table_name where id IN ($condition)";

批量插入数据 SQL

// 简单方法
$picked_words = array();
$picked_words = implode("'),('", $picked_words);
$sql = "INSERT IGNORE INTO tbl_vocabulary(words) VALUES('$picked_words')";

// 第二种处理方法
$n = 0;
foreach ($xmlObj as $data) {
    if ($n==0)
        $sql = "INSERT INTO table (`id`,`name`,...) VALUES ( $data->id, '$data->name',...)";
    else
        $sql .= ", ($data->id, '$data->name',...)";
    $n++;
}

// 第三种处理方法以及过量数据的处理
$sql = $sql_head = "INSERT INTO table (`id`,`name`,...) VALUES ";
$n = 0;
foreach ($xmlObj as $data) {
    if ($n%200 == 0) { // 指定200行后重新生成 sql 头部
        $sql = rtrim($sql, ',');
        $sql .= ";\n$sql_head";
    }
    $sql .= "($data->id, '$data->name',...),";
    $n++;
}
$sql = rtrim($sql, ',');

处理 SQL 中的特殊字符

例如 sql 里字段值含单引号的字符串需要得到其转义字符串（escaped string）

$title = "It's me";
$sql = "INSERT INTO table (`name`) VALUES ('$title');

/* 自行处理 */
$title = str_replace("'", "\'", $title);

/* Mysqli */
string mysqli::real_escape_string ( string $escapestr ) // 别名 escape_string
string mysqli_real_escape_string ( mysqli $link , string $escapestr ) // 别名 mysqli_escape_string

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$title = $mysqli->real_escape_string($title);

$link = mysqli_connect("localhost", "my_user", "my_password", "world");
$title = mysqli_real_escape_string($link, $title);

/* PDO */
$conn = new PDO('sqlite:/home/lynn/music.sql3');
$title = $conn->quote($conn); // POD::quote 不同之处是，会给两端加单引号
$sql = "INSERT INTO table (`name`) VALUES ($title);

• 创作共同 (Creative Commons) 版权协议
• 适用于小屏幕移动设备浏览