PHP - Manual: Executing statements
2024-12-23
Executing statements
Statements can be executed with the mysqli::query(), mysqli::real_query() and mysqli::multi_query(). The mysqli::query() function is the most common, and combines the executing statement with a buffered fetch of its result set, if any, in one call. Calling mysqli::query() is identical to calling mysqli::real_query() followed by mysqli::store_result().
示例 #1 Executing queries
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
Buffered result sets
After statement execution, results can be either retrieved all at once or read row by row from the server. Client-side result set buffering allows the server to free resources associated with the statement's results as early as possible. Generally speaking, clients are slow consuming result sets. Therefore, it is recommended to use buffered result sets. mysqli::query() combines statement execution and result set buffering.
PHP applications can navigate freely through buffered results. Navigation is fast because the result sets are held in client memory. Please, keep in mind that it is often easier to scale by client than it is to scale the server.
示例 #2 Navigation through buffered results
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$mysqli->query("DROP TABLE IF EXISTS test");
$mysqli->query("CREATE TABLE test(id INT)");
$mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");
$result = $mysqli->query("SELECT id FROM test ORDER BY id ASC");
echo "Reverse order...\n";
for ($row_no = $result->num_rows - 1; $row_no >= 0; $row_no--) {
$result->data_seek($row_no);
$row = $result->fetch_assoc();
echo " id = " . $row['id'] . "\n";
}
echo "Result set order...\n";
foreach ($result as $row) {
echo " id = " . $row['id'] . "\n";
}
以上例程会输出：
Reverse order... id = 3 id = 2 id = 1 Result set order... id = 1 id = 2 id = 3
Unbuffered result sets
If client memory is a short resource and freeing server resources as early as possible to keep server load low is not needed, unbuffered results can be used. Scrolling through unbuffered results is not possible before all rows have been read.
示例 #3 Navigation through unbuffered results
<?php
$mysqli->real_query("SELECT id FROM test ORDER BY id ASC");
$result = $mysqli->use_result();
echo "Result set order...\n";
foreach ($result as $row) {
echo " id = " . $row['id'] . "\n";
}
Result set values data types
The mysqli::query(), mysqli::real_query()
and mysqli::multi_query() functions are used to execute
non-prepared statements. At the level of the MySQL Client Server Protocol,
the command COM_QUERY and the text protocol are used
for statement execution. With the text protocol, the MySQL server converts
all data of a result sets into strings before sending. This conversion is done
regardless of the SQL result set column data type. The mysql client libraries
receive all column values as strings. No further client-side casting is done
to convert columns back to their native types. Instead, all values are
provided as PHP strings.
示例 #4 Text protocol returns strings by default
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("example.com", "user", "password", "database");
$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')");
$result = $mysqli->query("SELECT id, label FROM test WHERE id = 1");
$row = $result->fetch_assoc();
printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
以上例程会输出：
id = 1 (string) label = a (string)
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. If set, the mysqlnd library will
check the result set meta data column types and convert numeric SQL columns
to PHP numbers, if the PHP data type value range allows for it.
This way, for example, SQL INT columns are returned as integers.
示例 #5 Native data types with mysqlnd and connection option
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli();
$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);
$mysqli->real_connect("example.com", "user", "password", "database");
$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')");
$result = $mysqli->query("SELECT id, label FROM test WHERE id = 1");
$row = $result->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)
See also
add a note
User Contributed Notes
There are no user contributed notes for this page.
官方地址：https://www.php.net/manual/en/mysqli.quickstart.statements.php
IT
PHP
编程语言
Linux
开发编程
科技
Elasticsearch
HTML/CSS/XML
网络
数据库
JAVA
面试
NoSQL
C/C++
Golang
Git
算法
操作系统
正则表达式
Redis
互联网
MySql
JavaScript
运维
软件
国际
架构设计
Mac OS
TCP/IP
Excel
Vim
Windows
Socket
Oracle
VR
MongoDB
Python
运营
MemCache
商业
硬件
电子
娱乐
设计
nginx
摄影
游戏
WordPress
HTTP
团建
数码电器
广告
广告
php7 安装fileinfo扩展
preg_split — 通过一个正则表达式分隔字符串
php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题
[PHP] inet_pton/inet_ntop IP地址转换函数
[鸟哥]PHP_INT_MIN 和 -9223372036854775808
PHP 8.1
ADORecordSet对象
adodb手册
opcache预加载
Composer的Packagist资源
array_multisort() 多字段排序
mysql面试题
PHPMailer设置utf8 PHPMailer字符集CharSet
PHP json解析（json_decode）页面工具
[原创]诡异的php-fpm超时问题
php中几种注释规范
PHP获取指定函数定义在哪个文件中及行号
php上周一、本周一、下周一日期
[鸟哥]PHP FFI详解 - 一种全新的PHP扩展方式
php扩展开发