php 如何在一个网页上连接多个 MySQL 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/274892/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you connect to multiple MySQL databases on a single webpage?
提问by JoshFinnie
I have information spread out across a few databases and want to put all the information onto one webpage using PHP. I was wondering how I can connect to multiple databases on a single PHP webpage.
我的信息分布在几个数据库中，并希望使用 PHP 将所有信息放到一个网页上。我想知道如何在单个 PHP 网页上连接到多个数据库。
I know how to connect to a single database using:
我知道如何使用以下方法连接到单个数据库：
$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
However, can I just use multiple "mysql_connect" commands to open the other databases, and how would PHP know what database I want the information pulled from if I do have multiple databases connected.
但是，我是否可以只使用多个“mysql_connect”命令来打开其他数据库，如果我连接了多个数据库，PHP 如何知道我想要从哪个数据库中提取信息。
回答by Tom Haigh
Warning :mysql_xxfunctions are deprecated since php 5.5 and removed since php 7.0 (see http://php.net/manual/intro.mysql.php), use mysqli_xxfunctions or see the answer below from @Troelskn
警告：mysql_xx函数自 php 5.5 起已弃用，自 php 7.0 起已删除（请参阅http://php.net/manual/intro.mysql.php），请使用mysqli_xx函数或查看以下来自 @Troelskn 的答案
You can make multiple calls to mysql_connect(), but if the parameters are the same you need to pass true for the '$new_link' (fourth) parameter, otherwise the same connection is reused. For example:
您可以多次调用mysql_connect()，但如果参数相同，则需要为“ $new_link”（第四个）参数传递 true ，否则将重复使用相同的连接。例如：
$dbh1 = mysql_connect($hostname, $username, $password);
$dbh2 = mysql_connect($hostname, $username, $password, true);
mysql_select_db('database1', $dbh1);
mysql_select_db('database2', $dbh2);
Then to query database 1 pass the first link identifier:
然后查询数据库 1 传递第一个链接标识符：
mysql_query('select * from tablename', $dbh1);
and for database 2 pass the second:
对于数据库 2，通过第二个：
mysql_query('select * from tablename', $dbh2);
If you do not pass a link identifier then the last connection created is used (in this case the one represented by $dbh2) e.g.:
如果您不传递链接标识符，则使用创建的最后一个连接（在这种情况下，由 表示的连接$dbh2）例如：
mysql_query('select * from tablename');
Other options
其他选项
If the MySQL user has access to both databases and they are on the same host (i.e. both DBs are accessible from the same connection) you could:
如果 MySQL 用户可以访问两个数据库并且它们在同一主机上（即两个数据库都可以从同一个连接访问），您可以：
- Keep one connection open and call
mysql_select_db()to swap between as necessary. I am not sure this is a clean solution and you could end up querying the wrong database. - Specify the database name when you reference tables within your queries (e.g.
SELECT * FROM database2.tablename). This is likely to be a pain to implement.
- 保持一个连接打开并
mysql_select_db()在必要时调用以在它们之间进行交换。我不确定这是一个干净的解决方案，您最终可能会查询错误的数据库。 - 当您在查询中引用表时指定数据库名称（例如
SELECT * FROM database2.tablename）。这可能很难实施。
Also please read troelskn's answer because that is a better approach if you are able to use PDO rather than the older extensions.
另请阅读 troelskn 的回答，因为如果您能够使用 PDO 而不是旧的扩展，那是更好的方法。
回答by troelskn
If you use PHP5 (And you should, given that PHP4 has been deprecated), you should use PDO, since this is slowly becoming the new standard. One (very) important benefit of PDO, is that it supports bound parameters, which makes for much more secure code.
如果您使用 PHP5（并且您应该，因为 PHP4 已被弃用），您应该使用PDO，因为这正在慢慢成为新标准。PDO 的一个（非常）重要的好处是它支持绑定参数，这使得代码更加安全。
You would connect through PDO, like this:
您将通过 PDO 连接，如下所示：
try {
$db = new PDO('mysql:dbname=databasename;host=127.0.0.1', 'username', 'password');
} catch (PDOException $ex) {
echo 'Connection failed: ' . $ex->getMessage();
}
(Of course replace databasename, username and password above)
（当然替换上面的数据库名，用户名和密码）
You can then query the database like this:
然后，您可以像这样查询数据库：
$result = $db->query("select * from tablename");
foreach ($result as $row) {
echo $row['foo'] . "\n";
}
Or, if you have variables:
或者，如果您有变量：
$stmt = $db->prepare("select * from tablename where id = :id");
$stmt->execute(array(':id' => 42));
$row = $stmt->fetch();
If you need multiple connections open at once, you can simply create multiple instances of PDO:
如果您需要一次打开多个连接，您可以简单地创建多个 PDO 实例：
try {
$db1 = new PDO('mysql:dbname=databas1;host=127.0.0.1', 'username', 'password');
$db2 = new PDO('mysql:dbname=databas2;host=127.0.0.1', 'username', 'password');
} catch (PDOException $ex) {
echo 'Connection failed: ' . $ex->getMessage();
}
回答by Ihsan Kusasi
I just made my life simple:
我只是让我的生活变得简单：
CREATE VIEW another_table AS SELECT * FROM another_database.another_table;
hope it is helpful... cheers...
希望它有帮助...干杯...
回答by kaushik
Instead of mysql_connectuse mysqli_connect.
而不是mysql_connect使用mysqli_connect。
mysqli is provide a functionality for connect multiple database at a time.
mysqli 提供了一次连接多个数据库的功能。
$Db1 = new mysqli($hostname,$username,$password,$db_name1);
// this is connection 1 for DB 1
$Db2 = new mysqli($hostname,$username,$password,$db_name2);
// this is connection 2 for DB 2
回答by Paks
Try below code:
试试下面的代码：
$conn = mysql_connect("hostname","username","password");
mysql_select_db("db1",$conn);
mysql_select_db("db2",$conn);
$query1 = "SELECT * FROM db1.table";
$query2 = "SELECT * FROM db2.table";
You can fetch data of above query from both database as below
您可以从两个数据库中获取上述查询的数据，如下所示
$rs = mysql_query($query1);
while($row = mysql_fetch_assoc($rs)) {
$data1[] = $row;
}
$rs = mysql_query($query2);
while($row = mysql_fetch_assoc($rs)) {
$data2[] = $row;
}
print_r($data1);
print_r($data2);
回答by Lazy Fellow
$dbh1 = mysql_connect($hostname, $username, $password);
$dbh2 = mysql_connect($hostname, $username, $password, true);
mysql_select_db('database1', $dbh1);
mysql_select_db('database2',$dbh2);
mysql_query('select * from tablename', $dbh1);
mysql_query('select * from tablename', $dbh2);
This is the most obvious solution that I use but just remember, if the username / password for both the database is exactly same in the same host, this solution will always be using the first connection. So don't be confused that this is not working in such case. What you need to do is, create 2 different users for the 2 databases and it will work.
这是我使用的最明显的解决方案，但请记住，如果两个数据库的用户名/密码在同一主机中完全相同，则此解决方案将始终使用第一个连接。所以不要混淆这在这种情况下不起作用。您需要做的是，为 2 个数据库创建 2 个不同的用户，它将起作用。
回答by Michael Ratcliffe
Unless you really need to have more than one instance of a PDO object in play, consider the following:
除非您确实需要有多个 PDO 对象的实例在运行，否则请考虑以下事项：
$con = new PDO('mysql:host=localhost', $username, $password,
array(PDO::ATTR_PERSISTENT => true));
Notice the absence of dbname=in the construction arguments.
注意dbname=构造参数中没有。
When you connect to MySQL via a terminal or other tool, the database name is not needed off the bat. You can switch between databases by using the USE dbnamestatement via the PDO::exec()method.
当您通过终端或其他工具连接到 MySQL 时，不需要立即使用数据库名称。您可以USE dbname通过PDO::exec()方法使用语句在数据库之间切换。
$con->exec("USE someDatabase");
$con->exec("USE anotherDatabase");
Of course you may want to wrap this in a catch try statement.
当然，您可能希望将其包装在 catch try 语句中。
回答by user3857891
You might be able to use MySQLi syntax, which would allow you to handle it better.
您也许可以使用 MySQLi 语法，这样可以更好地处理它。
Define the database connections, then whenever you want to query one of the database, specify the right connection.
定义数据库连接，然后每当您要查询其中一个数据库时，指定正确的连接。
E.g.:
例如：
$Db1 = new mysqli('$DB_HOST','USERNAME','PASSWORD'); // 1st database connection
$Db2 = new mysqli('$DB_HOST','USERNAME','PASSWORD'); // 2nd database connection
Then to query them on the same page, use something like:
然后要在同一页面上查询它们，请使用以下内容：
$query = $Db1->query("select * from tablename")
$query2 = $Db2->query("select * from tablename")
die("$Db1->error");
Changing to MySQLi in this way will help you.
以这种方式更改为 MySQLi 会对您有所帮助。
回答by Nagibaba
You don't actually need select_db. You can send a query to two databases at the same time. First, give a grant to DB1to select from DB2by GRANT select ON DB2.* TO DB1@localhost;. Then, FLUSH PRIVILEGES;. Finally, you are able to do 'multiple-database query' like SELECT DB1.TABLE1.id, DB2.TABLE1.username FROM DB1,DB2etc. (Don't forget that you need 'root' access to use grant command)
你实际上不需要select_db. 您可以同时向两个数据库发送查询。首先，授予DB1to select from DB2by GRANT select ON DB2.* TO DB1@localhost;。然后，FLUSH PRIVILEGES;。最后，您可以执行“多数据库查询”SELECT DB1.TABLE1.id, DB2.TABLE1.username FROM DB1,DB2等操作（不要忘记您需要“root”访问权限才能使用授权命令）
回答by Kamal Bunkar
if you are using mysqli and have two db_connection file. like first one is
如果您使用的是 mysqli 并且有两个 db_connection 文件。就像第一个是
define('HOST','localhost');
define('USER','user');
define('PASS','passs');
define('**DB1**','database_name1');
$connMitra = new mysqli(HOST, USER, PASS, **DB1**);
second one is
第二个是
define('HOST','localhost');
define('USER','user');
define('PASS','passs');
define(**'DB2**','database_name1');
$connMitra = new mysqli(HOST, USER, PASS, **DB2**);
SO just change the name of parameter pass in mysqli like DB1 and DB2. if you pass same parameter in mysqli suppose DB1 in both file then second database will no connect any more. So remember when you use two or more connection pass different parameter name in mysqli function
所以只需更改 mysqli 中参数传递的名称，如 DB1 和 DB2。如果您在 mysqli 中传递相同的参数，假设两个文件中的 DB1 都将不再连接第二个数据库。所以记住当你在 mysqli 函数中使用两个或多个连接传递不同的参数名称时
