何时以及如何使用PHP（PDO）使用多个MySQL查询

What are the benefits of using multiple MySQL queries in a statement, other than to minimize code.

除了最小化代码之外,在语句中使用多个MySQL查询有什么好处。

How do you execute, retrieve, and display the results of multiple MySQL queries sent in one statement using PHP (and preferably PDO).

如何使用PHP(最好是PDO)执行,检索和显示在一个语句中发送的多个MySQL查询的结果。

4 个解决方案

#1

Regardless of which database you are using, it can be more efficient to put multiple queries into one statement. If you perform the queries separately, you have to make a call to the database across the network (or at least, between processes if on the same machine), get a connection to it (including autheticating), pass in the query, return the resultset, and release the connection for each query.

无论您使用哪个数据库,将多个查询放入一个语句中效率会更高。如果单独执行查询,则必须通过网络(或至少在进程之间,如果在同一台计算机上)调用数据库,获取与它的连接(包括验证),传入查询,返回结果集,并释放每个查询的连接。

Even if you use connection pooling, you are still passing more requests back and forth than is necessary.

即使您使用连接池,您仍然需要来回传递更多请求。

So, for example, if you combine two queries into one, you have saved all of that extra calling back and forth for the second query. The more queries you combine, then, the more efficient your app can become.

因此,例如,如果将两个查询合并为一个,则为第二个查询来回保存了所有额外的调用。您组合的查询越多,您的应用就越有效率。

For another example, many apps populate lists (such as for dropdownlists) when they start up. That can be a number of queries. Performing them all in one call can accelerate startup time.

再举一个例子,许多应用程序在启动时会填充列表(例如下拉列表)。这可能是一些查询。在一次通话中执行所有这些操作可以加快启动时间。

#2

mysql_query() doesn't support multiple queries. However, there are some workarounds:

mysql_query()不支持多个查询。但是,有一些解决方法:

http://www.dev-explorer.com/articles/multiple-mysql-queries
http://php.net/function.mysql-query

#3

I was doing some research tonight and stumbled across this thread. None of the answers above provide a viable solution to this simple problem.

我今晚正在做一些研究,偶然发现了这个问题。上述答案都没有为这个简单的问题提供可行的解决方案。

Using the OO Approach, you can perform multiple query statements in a single connection by using the multi_query() method.

使用OO方法,您可以使用multi_query()方法在单个连接中执行多个查询语句。

$db_connection->multi_query($query);

Queries should be well-formed and separated by semicolons -> ;

查询应该是格式良好的,并用分号分隔 - >;

see The PHP manual for more details on how to handle SELECT data

有关如何处理SELECT数据的更多详细信息,请参阅PHP手册

#4

Multiple queries on the same call are especially beneficial when you need to work with session variables and temporary tables:

当您需要使用会话变量和临时表时,对同一调用的多个查询特别有用:

select somecol, @somevar := group_concat( distinct somecol2 separator '|')
from sometbl
where
    somecol LIKE "fueh"
group by somecol;

select somecol2
from  sometbl
where
    somecol LIKE "nyan"
and
    vc_wp regexp concat( '(', @somevar, ')' )
order by somecol;

#1