如何在 PHP 中构建跨数据库查询？

How do I construct a cross database query in PHP?

提问by Avery

In our last episode (How I constructed a cross database query in MySQL) I learned how to construct a cross database query in MySQL. This worked great but when our hero tried to use this newfound knowledge in PHP he found his best friend FAIL waiting for him.

在上一集（我如何在 MySQL 中构建跨数据库查询）中，我学习了如何在 MySQL中构建跨数据库查询。这很有效，但是当我们的英雄试图在 PHP 中使用这些新知识时，他发现他最好的朋友 FAIL 等着他。

I took a look at mysql_select_dbfor PHP. This seems to imply that if I want to use MySQL with PHP, I have a couple of choices:

我看了一下mysql_select_dbPHP。这似乎暗示如果我想将 MySQL 与 PHP 一起使用，我有几个选择：

1. Use mysql_select_dbbut be stuck with only using one db at a time. This is our current setup and putting a database as a namespace identifier doesn't seem to work (it works fine in the MySQL shell so I know it's not a problem with our MySQL server setup).

2. Don't use mysql_select_db. From some of the examples I've seen, this seems to mean that I have to specify the db for every query that I make. This makes sense since I haven't used mysql_select_dbto tell PHP what db I want to access. This also makes sad since I don't want to go through all my code and prepend a db name to every query.

1. 使用mysql_select_db但一次只能使用一个数据库。这是我们当前的设置，将数据库作为命名空间标识符似乎不起作用（它在 MySQL shell 中工作正常，所以我知道我们的 MySQL 服务器设置没有问题）。

2. 不要使用mysql_select_db. 从我看到的一些例子来看，这似乎意味着我必须为我所做的每个查询指定数据库。这是有道理的，因为我还没有mysql_select_db告诉 PHP 我想访问什么数据库。这也令人难过，因为我不想遍历我的所有代码并在每个查询前添加一个 db 名称。

Is there something better than this? Is there a way for me to do a cross db MySQL query in PHP without having to something crazy like (2)?

还有比这更好的吗？有没有办法让我在 PHP 中进行跨数据库 MySQL 查询而不必像 (2) 那样疯狂？

CLARIFICATION: None of the proposed answers actually let me do a cross db query. Instead, they allow me to access two different DBs separately. I want a solution that allows me to do something like SELECT foreign_db.login.username, firstname, lastname from foreign_db.login, user where ...NOT just make different queries to different DBs. For what it's worth, (2) doesn't work for me.

澄清：没有一个建议的答案实际上让我进行跨数据库查询。相反，它们允许我分别访问两个不同的数据库。我想要一个解决方案，让我可以做一些事情，而SELECT foreign_db.login.username, firstname, lastname from foreign_db.login, user where ...不仅仅是对不同的数据库进行不同的查询。对于它的价值，（2）对我不起作用。

回答by Benoit Vidis

You will need your databases to run on the same host.

您将需要您的数据库在同一台主机上运行。

If so, you should be able to use mysql_select_db on your favourite/default db and manually specify a foreign database.

如果是这样，您应该能够在您最喜欢的/默认数据库上使用 mysql_select_db 并手动指定外部数据库。

$db = mysql_connect($hots, $user, $password);
mysql_select_db('my_most_used_db', $db);

$q = mysql_query("
    SELECT *
    FROM   table_on_default_db a, `another_db`.`table_on_another_db` b
    WHERE  a.id = b.fk_id
");

If your databases run on a different host, you won't be able to join directly. But you can then make 2 queries.

如果您的数据库运行在不同的主机上，您将无法直接加入。但是您可以进行 2 次查询。

$db1 = mysql_connect($host1, $user1, $password1);
$db2 = mysql_connect($host2, $user2, $password2);

$q1 = mysql_query("
    SELECT id
    FROM   table
    WHERE  [..your criteria for db1 here..]
", $db1);
$tmp = array();
while($val = mysql_fetch_array($q1))
    $tmp[] = $val['id'];

$q2 = mysql_query("
    SELECT *
    FROM   table2
    WHERE  fk_id in (".implode(', ', $tmp).")
", $db2);

回答by Roland Bouman

After reading your clarification, I am under the impression that you actually want to query tables residing in two separate MySQL server instances. At least, your clarification text:

阅读您的说明后，我的印象是您实际上想要查询驻留在两个单独的 MySQL 服务器实例中的表。至少，您的说明文字：

SELECT foreign_db.login.username, firstname, lastname from foreign_db.login, user where

SELECT foreign_db.login.username, firstname, lastname from foreign_db.login, user where

suggests that you want to run one query while being logged in as two users (which may or may not reside on the same mysql server instance).

建议您希望在以两个用户身份登录时运行一个查询（可能驻留在同一个 mysql 服务器实例上，也可能不驻留）。

In your question, you said you wanted to query data from two different databases, but it is important to realize that one MySQL instance can have many, many databases. For multiple databases managed by the same mysql instance, the solution proposed in the question you linked to simply works: just prefix the table name with the name of the databases, separating database and table names with a dot: <db-name>.<table-name>.

在你的问题中，你说你想从两个不同的数据库查询数据，但重要的是要意识到一个 MySQL 实例可以有很多很多数据库。对于由同一个 mysql 实例管理的多个数据库，您链接到的问题中提出的解决方案很简单：只需在表名前加上数据库名，用点分隔数据库名和表名：<db-name>.<table-name>。

But, like i pointed out, this only works if:

但是，就像我指出的那样，这只适用于：

1. all databases you access in one query reside on the same server - that is, are managed by the same MySQL instance
2. the user that is connected to the database has the right privileges to access both tables.

1. 您在一个查询中访问的所有数据库都位于同一台服务器上——也就是说，由同一个 MySQL 实例管理
2. 连接到数据库的用户有权访问这两个表。

Scenario1: databases on same host: grant appopriate privileges and qualify table names

场景 1：同一主机上的数据库：授予适当的权限并限定表名

So if the tables actually reside on the same mysql instance, there is no need for a second login or connection - simply grant the database user you use to connect to the datbase the appropriate privileges to select from all tables you need. You can do that with the GRANTsyntax, documented here: http://dev.mysql.com/doc/refman/5.1/en/grant.html

因此，如果表实际上驻留在同一个 mysql 实例上，则不需要第二次登录或连接 - 只需授予您用于连接到数据库的数据库用户适当的权限，以从您需要的所有表中进行选择。您可以使用GRANT此处记录的语法来做到这一点：http: //dev.mysql.com/doc/refman/5.1/en/grant.html

For example, GRANT SELECT ON sakila.film TO 'test'@'%'will allow the user test@%to select data from the filmtable in the sakiladatabase. After doing that, said user can refer to this table using sakila.film(so-called qualified table name), or if the current database is set to sakila, simply as film

例如，GRANT SELECT ON sakila.film TO 'test'@'%'将允许用户test@%从数据库中的film表中选择数据sakila。这样做之后，该用户可以使用sakila.film（所谓的限定表名）来引用这个表，或者如果当前数据库设置为sakila，简单地作为film

Scenario2: databases managed by different MySQL instances: FEDERATED engine

场景 2：由不同 MySQL 实例管理的数据库：FEDERATED 引擎

If the tables you want to access are actually managed by two different MySQL instances, there is one trick that may or may not work, depending on your configuration. Since MySQL 5.0 mysql supports the FEDERATEDstorage engine. This lets you create a table that is not actually a table, but a peephole to a table on a remote server. This engine is documented here: http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html

如果您要访问的表实际上由两个不同的 MySQL 实例管理，则根据您的配置，有一种技巧可能有效，也可能无效。由于 MySQL 5.0 mysql 支持FEDERATED存储引擎。这使您可以创建一个实际上不是表，而是远程服务器上表的窥视孔的表。该引擎记录在此处：http: //dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html

For example, if you know there is this table in the miscdatabase on the remote host:

例如，如果您知道misc远程主机上的数据库中有此表：

CREATE TABLE t (
    id int not null primary key
,   name varchar(10) not null unique
)

you can make a local 'pointer' to that remote table using this:

您可以使用以下方法创建指向该远程表的本地“指针”：

CREATE TABLE t (
    id int not null primary key
,   name varchar(10) not null unique
)
ENGINE = FEDERATED
CONNECTION='mysql://<user>@<remote-server>:<remote-port>/misc/t';

Unfortunately, the FEDERATEDengine is not always available, so you have to check first if you can even use that. But suppose it is, then you can simply use the local table t in your queries, just like any other table, and MySQL will communicate with the remote server and perform the appropriate operations on the physical table on the other side.

不幸的是，FEDERATED引擎并不总是可用，所以你必须先检查你是否可以使用它。但是假设是这样，那么您可以在查询中简单地使用本地表 t，就像任何其他表一样，MySQL 将与远程服务器通信并对另一侧的物理表执行适当的操作。

Caveat: there are several optimization issues with FEDERATED tables. You should find out if and to what extent these apply to you. For instance, applying a WHEREto a federated table can in many cases result in the entire table contents being pullled over the wire to your local server, where the actual filtering will be appplied. Another issue is with table creation: you have to be very sure that the definitions of the federated table and the table it is pointing to match exacty, except for the ENGINE clause (and CONNECTION). If you have for example a different character set, the data may arrive completely garbled after travelling over the wire.

警告： FEDERATED 表有几个优化问题。您应该了解这些是否以及在多大程度上适用于您。例如，将 aWHERE应用于联合表在许多情况下会导致整个表内容通过网络被拉到本地服务器，在那里将应用实际的过滤。另一个问题是表创建：除了 ENGINE 子句（和 CONNECTION）之外，您必须非常确定联合表的定义和它指向的表完全匹配。例如，如果您有不同的字符集，则数据在通过网络传输后可能会完全乱码。

If you want to use FEDERATEDtables, do read this article http://oreilly.com/pub/a/databases/2006/08/10/mysql-federated-tables.htmlto decide if its right for your particular use case.

如果您想使用FEDERATED表格，请阅读这篇文章http://oreilly.com/pub/a/databases/2006/08/10/mysql-federated-tables.html以确定它是否适合您的特定用例。

If you think you do need it, I have a utility to create federated tables here: http://forge.mysql.com/tools/tool.php?id=54

如果您认为确实需要它，我有一个实用程序可以在此处创建联合表：http: //forge.mysql.com/tools/tool.php?id=54

Scenario3: can't use FEDERATED, but tables on different MySQL instances

场景 3：不能使用 FEDERATED，但不同 MySQL 实例上的表

Finally, if you have tables on different MySQL instances, but cannot for some reason use the federated table engine, your a out of luck I'm afraid. You are simply going to have to execute queries to both MySQL instances, receive the results and do something intelligent with it in PHP. depending on your exact requirements, this may be a perfectly viable solution

最后，如果您在不同的 MySQL 实例上有表，但由于某种原因不能使用联合表引擎，恐怕您很不走运。您只需要对两个 MySQL 实例执行查询，接收结果并在 PHP 中用它做一些智能的事情。根据您的确切要求，这可能是一个完美可行的解决方案

I guess you need to decide for yourself which part of my answer best appeals to your problem, and add a comment in case you need more help. TIA Roland.

我想您需要自己决定我回答的哪一部分最适合您的问题，并添加评论以防您需要更多帮助。蒂亚罗兰。

回答by Pascal MARTIN

A solution might be to :

一个解决方案可能是：

• use mysql_select_dbto select the "default" (i.e. most used)database
• and specify the DB name only in queries that have to work with the "second" (i.e. least used) database.

• 用于mysql_select_db选择“默认” （即最常用）数据库
• 并仅在必须使用“第二个”（即最少使用）数据库的查询中指定数据库名称。

But this is only a viable solution if you have one DB that's more used than the other...

但这只是一个可行的解决方案，如果你有一个比另一个更常用的数据库......

Out of curiosity : did you try establishing several connections to your DB server -- i.e. one for each database ?

出于好奇：您是否尝试与数据库服务器建立多个连接 - 即每个数据库一个连接？

You might be able to :

你或许能够：

• connect to the first DB with mysql_connect, and, then, select the first DB with mysql_select_db
• and, then, connect to the second DB, passing truefor the new_linkparameter of mysql_connectif necessary, and, then, selecting the second DB with mysql_select_db

• 使用 连接到第一个 DB mysql_connect，然后使用 选择第一个 DBmysql_select_db
• 并且，然后，连接到第二DB，传递true用于new_link的参数mysql_connect，如果必要的，并且，然后，选择所述第二DB与mysql_select_db

Then, work with the connection identifier returned by the first, or second, call to mysql_connect, depending on which DB you want to issue queries.

然后，mysql_connect根据您要发出查询的数据库，使用第一次或第二次调用返回的连接标识符。

As a sidenote : the "best" / "cleanest" solution would be not using mysql_*functions directly, but working with some kind of ORM framework, that would have the ability to work with several DB connections at the same time (not sure, but maybe Doctrinecan do that -- it's a real good ORM)

作为旁注：“最佳”/“最干净”的解决方案不是mysql_*直接使用函数，而是使用某种 ORM 框架，这将能够同时使用多个数据库连接（不确定，但也许Doctrine可以做到这一点——这是一个非常好的 ORM）

回答by Bill Karwin

I set up tables in separate test databases as follows:

我在单独的测试数据库中设置表如下：

mysql> use test;
mysql> create table foo as select 42 as id from dual;
mysql> create database test2;
mysql> use test2;
mysql> create table bar as select 42 as id from dual;

I ran the following PHP script with MySQL 5.1.41 and PHP 5.3.1 on Mac OS X:

我在 Mac OS X 上使用 MySQL 5.1.41 和 PHP 5.3.1 运行了以下 PHP 脚本：

<?php

$link = mysql_connect('localhost', 'root', 'XXXX')
 or die('There was a problem connecting to the database.');
mysql_select_db('test');

$sql = "SELECT * FROM foo JOIN test2.bar USING (id)";
if (($result = mysql_query($sql)) === FALSE) {
  die(mysql_error());
}

while ($row = mysql_fetch_array($result)) {
  print_r($row);
}

This test succeeds. The result is the join between the two tables in separate databases.

此测试成功。结果是单独数据库中的两个表之间的连接。

You should always be able to select from table(s) qualified by their respective database names in SQL. The mysql API in PHP does not restrict you to querying one database.

您应该始终能够从 SQL 中由其各自数据库名称限定的表中进行选择。PHP 中的 mysql API 不限制您查询一个数据库。

You should always be able to omit the database qualifier for the "current" database, which you declare with mysql_select_db().

您应该始终能够省略“当前”数据库的数据库限定符，您使用mysql_select_db().

回答by matpie

Whenever you are SELECTing from multiple tables you have to sepcify an alias. So it's pretty simple from there:

每当您SELECT从多个表中访问时，您都必须分离别名。所以从那里开始非常简单：

SELECT
    a.id, a.name, a.phone,
    b.service, b.provider

FROM
    `people` AS a,

LEFT JOIN
    `other_database`.`providers` AS b ON a.id = b.userid

WHERE
    a.username = 'sirlancelot'

As others on this page have mentioned, the database must be on the same host and instance. You cannot query a database from another server with this syntax.

正如本页上的其他人所提到的，数据库必须位于同一主机和实例上。您不能使用此语法从另一台服务器查询数据库。

回答by amir beygi

Maybe this is the code that you want

也许这是你想要的代码

---

---

//create links
$link1?=?mysql_connect('localhost',?'mysql_user',?'mysql_password');
$link2 = mysql_connect('localhost', 'mysql_user', 'mysql_password');

//set db on every link
mysql_select_db('foo',?$link1);
mysql_select_db('bar',?$link2);

//do query with specified link
$result1?=?mysql_query($query1,$link1);
$result2 =?mysql_query($query2,$link2);

Note that we didn't do a mysql_select_db between queries , and we didn't use the database name in the query either.

请注意，我们没有在查询之间执行 mysql_select_db，也没有在查询中使用数据库名称。

回答by Alix Axel

The less verbose option you have is provided by the MySQL Manualitself:

MySQL 手册本身提供了较不冗长的选项：

The following example accesses the author table from the db1 database and the editor table from the db2 database:

以下示例访问 db1 数据库中的 author 表和 db2 数据库中的 editor 表：

USE db1;
SELECT author_name, editor_name FROM author, db2.editor
WHERE author.editor_id = db2.editor.editor_id;

回答by Horia Dragomir

You can use option two: "Don't use mysql_select_db" and then use mysql_db_queryinstead of mysql_query ... which is a simple find and replace.

您可以使用选项二：“不要使用mysql_select_db”，然后使用mysql_db_query代替 mysql_query ...这是一个简单的查找和替换。

Best of luck!

祝你好运！

回答by álvaro González

I've just tried this simple code in my computer and it works perfectly:

我刚刚在我的电脑上尝试了这个简单的代码，它运行良好：

<?php

$conn = mysql_connect('localhost', 'root', '....');
mysql_select_db('aliro');

$sql = 'select * ' .
    'from aliro_permissions a ' .
    'left join cmsmadesimple.cms_permissions b on a.id=b.permission_id ';

$res = mysql_query($sql)
    or die(mysql_error());
while($row = mysql_fetch_assoc($res)){
    print_r($row);
}

?>

(Of course, the query itself is meaningless, it's just an example.)

（当然，查询本身是没有意义的，这只是一个例子。）

So I can't really see what your exact problem is. If you want a syntax that's simpler that this, you'll have to provide an example of what kind of SQL you want to write.

所以我真的看不出你的确切问题是什么。如果您想要一个比这更简单的语法，您必须提供一个示例，说明您要编写的 SQL 类型。