PHP 8.2 新特性 — MySQLi: 新增 mysqli_execute_query 函数和 mysqli::execute_query 方法

PHP 8.2 中，MySQLi 扩展提供了更直接的方式，使用新增的 mysqli_execute_query 函数和 mysqli::execute_query() 方法，在 SQL 中准备(prepare)、绑定(bind)、执行(execute)及查询(retrieve)结果。

新增的 mysqli_execute_query 函数和 mysqli::execute_query 方法，接收SQL查询及可选数组参数。查询会在函数/方法中准备、绑定(如果传入参数)、执行。查询成功返回 mysqli_result 对象，查询失败返回 false。

作为简化形式，mysqli_execute_query/mysqli::execute_query 是 mysqli::prepare()、 mysqli_stmt::bind_param()、 mysqli_stmt::execute、及 mysqli_stmt::get_result() 调用的快捷方式。

如果没有 mysqli_execute_query 函数，参数化查询的执行过程需要很多模板代码：

$query = 'SELECT uid, username FROM users WHERE uid = ?';
$statement = $connection->prepare($query);
$statement->bind_param('s', $uid);
$statement->execute();
$result = $statement->get_result();

mysqli_execute_query 的引入，使得上述代码片段极大简化：

$query = 'SELECT uid, username FROM users WHERE uid = ?';
$result = $mysqli->execute_query($sql, [$uid]);

过程化 API 的例子:

  $query = 'SELECT uid, username FROM users WHERE uid = ?';
- $statement = mysqli_prepare($connection, $query);
- mysqli_stmt_bind_param($statement, 's', $uid);
- mysqli_stmt_execute($statement);
- $result = mysqli_stmt_get_result($statement);
+ $result = mysqli_execute_query($connection, $query, $uid);

在 PHP 8.1 中，可以直接通过传入 bind_param() 的调用到 execute()  中绑定函数 。

mysqli_execute_query 函数

/**
 * Prepares an SQL statement, binds parameters, executes, and returns the result.
 * @param mysqli $mysql A mysqli object returned by mysqli_connect() or mysqli_init()
 * @param string $query  The query, as a string. It must consist of a single SQL statement.  The SQL statement may contain zero or more parameter markers represented by question mark (?) characters at the appropriate positions.
 * @param ?array $params An optional list array with as many elements as there are bound parameters in the SQL statement being executed. Each value is treated as a string.
 * @return mysqli_result|bool Results as a mysqli_result object, or false if the operation failed.
 */
function mysqli_execute_query(mysqli $mysql, string $query, ?array $params = null): mysqli_result|bool {}

mysqli::execute_query 方法

class mysqli {  
  // ...  

  /**  
 * Prepares an SQL statement, binds parameters, executes, and returns the result. * @param string $query The query, as a string. It must consist of a single SQL statement.  The SQL statement may contain zero or more parameter markers represented by question mark (?) characters at the appropriate positions.  
 * @param ?array $params An optional list array with as many elements as there are bound parameters in the SQL statement being executed. Each value is treated as a string.  
 * @return mysqli_result|bool Results as a mysqli_result object, or false if the operation failed.  
 */  
  public function execute_query(string $query, ?array $params = null): mysqli_result|bool {}  
}

Polyfill

可以在用户 PHP 空间中声明新增 mysqli_execute_query  函数，实现对旧版本的兼容。在类中继承 mysqli_stmt 类，也能添加 execute_query() 方法。

以下是 mysqli_execute_query 函数在用户空间中实现的例子，以兼容 PHP 8.1 版本以上版本。

/**
 * Prepares an SQL statement, binds parameters, executes, and returns the result.
 * @param mysqli $mysql A mysqli object returned by mysqli_connect() or mysqli_init()
 * @param string $query  The query, as a string. It must consist of a single SQL statement.  The SQL statement may contain zero or more parameter markers represented by question mark (?) characters at the appropriate positions.
 * @param ?array $params An optional list array with as many elements as there are bound parameters in the SQL statement being executed. Each value is treated as a string.
 * @return mysqli_result|bool Results as a mysqli_result object, or false if the operation failed.
 */
function mysqli_execute_query(mysqli $mysqli, string $sql, ?array $params = null): mysqli_stmt|bool {
  $driver = new mysqli_driver();

  $stmt = $mysqli->prepare($sql);
  if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $mysqli->error) {
    return false;
  }

  $stmt->execute($params);
  if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $stmt->error) {
    return false;
  }

  return $stmt->get_result();
}

以下是用户空间中实现的例子，以兼容 PHP 7.1 以上版本。

Following is a user-land PHP polyfill that should work in PHP 7.1 and later:

/**  
 * Prepares an SQL statement, binds parameters, executes, and returns the result. * @param mysqli $mysql A mysqli object returned by mysqli_connect() or mysqli_init()
 * @param mysqli $mysql A mysqli object returned by mysqli_connect() or mysqli_init()
 * @param string $query The query, as a string. It must consist of a single SQL statement.  The SQL statement may contain zero or more parameter markers represented by question mark (?) characters at the appropriate positions.  
 * @param ?array $params An optional list array with as many elements as there are bound parameters in the SQL statement being executed. Each value is treated as a string.  
 * @return mysqli_result|bool Results as a mysqli_result object, or false if the operation failed.  
 */  
function mysqli_execute_query(mysqli $mysqli, string $sql, array $params = null) {  
  $driver = new mysqli_driver();  

  $stmt = $mysqli->prepare($sql);  
  if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $mysqli->error) {  
    return false;  
  }  

  if (!empty($params)) {  
    mysqli_stmt_bind_param($stmt, str_repeat('s', count($params)), ...$params);  
    if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $stmt->error) {  
      return false;  
    }  
  }  

  $stmt->execute();  
  if (!($driver->report_mode & MYSQLI_REPORT_STRICT) && $stmt->error) {  
    return false;  
  }

  return $stmt->get_result();  
}

向下兼容性影响

新增的 mysqli_execute_query 函数和 mysqli::execute_query 方法，可以在 PHP 8.1 中通过直接的方式在用户 PHP 代码空间中实现。也可以在更老的版本中实现，不过需要更多步骤，因为缺少 PHP 8.1 版本的执行中绑定特性。

如果应用中在全局空间中声明了自己的 mysqli_execute_query 函数，在 PHP 8.2 中会因为重复声明导致出错。