如何在 PHP 中选择要与 PDO 一起使用的 MySQL 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8705195/
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 I select a MySQL database to use with PDO in PHP?
提问by T. Brian Jones
I want to select a MySQL database to use after a PHP PDO object has already been created. How do I do this?
我想在创建 PHP PDO 对象后选择要使用的 MySQL 数据库。我该怎么做呢？
// create PDO object and connect to MySQL
$dbh = new PDO( 'mysql:host=localhost;', 'name', 'pass' );
// create a database named 'database_name'
// select the database we just created ( this does not work )
$dbh->select_db( 'database_name' );
Is there a PDO equivalent to mysqli::select_db?
是否有等效于 mysqli::select_db 的 PDO？
Perhaps I'm trying to use PDO improperly? Please help or explain.
也许我试图不正确地使用 PDO？请帮助或解释。
EDIT
编辑
Should I not be using PDO to create new databases? I understand that the majority of benefits from using PDO are lost on a rarely used operation that does not insert data like CREATE DATABASE, but it seems strange to have to use a different connection to create the database, then create a PDO connection to make other calls.
我不应该使用 PDO 创建新数据库吗？我知道使用 PDO 的大部分好处在一个很少使用的操作中丢失了，它不会像 那样插入数据CREATE DATABASE，但是必须使用不同的连接来创建数据库，然后创建一个 PDO 连接来进行其他调用似乎很奇怪。
回答by Bill Karwin
Typically you would specify the database in the DSN when you connect. But if you're creating a new database, obviously you can't specify that database the DSN before you create it.
通常，您会在连接时在 DSN 中指定数据库。但是，如果您正在创建一个新数据库，显然您不能在创建之前将该数据库指定为 DSN。
You can change your default database with the USEstatement:
您可以使用以下USE语句更改默认数据库：
$dbh = new PDO("mysql:host=...;dbname=mysql", ...);
$dbh->query("create database newdatabase");
$dbh->query("use newdatabase");
Subsequent CREATE TABLEstatements will be created in your newdatabase.
后续CREATE TABLE语句将在您的新数据库中创建。
Re comment from @Mike:
来自@Mike 的评论：
When you switch databases like that it appears to force PDO to emulate prepared statements. Setting PDO::ATTR_EMULATE_PREPARES to false and then trying to use another database will fail.
当您像这样切换数据库时，它似乎会强制 PDO 模拟准备好的语句。将 PDO::ATTR_EMULATE_PREPARES 设置为 false，然后尝试使用另一个数据库将失败。
I just did some tests and I don't see that happening. Changing the database only happens on the server, and it does not change anything about PDO's configuration in the client. Here's an example:
我只是做了一些测试，我没有看到这种情况发生。更改数据库仅发生在服务器上，它不会更改客户端中 PDO 配置的任何内容。下面是一个例子：
<?php
// connect to database
try {
$pdo = new PDO('mysql:host=huey;dbname=test', 'root', 'root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
} catch(PDOException $err) {
die($err->getMessage());
}
$stmt = $pdo->prepare("select * from foo WHERE i = :i");
$result = $stmt->execute(array("i"=>123));
print_r($stmt->fetchAll(PDO::FETCH_ASSOC));
$pdo->exec("use test2");
$stmt = $pdo->prepare("select * from foo2 WHERE i = :i AND i = :i");
$result = $stmt->execute(array("i"=>456));
print_r($stmt->fetchAll(PDO::FETCH_ASSOC));
If what you're saying is true, then this should work without error. PDO can use a given named parameter more than once only if PDO::ATTR_EMULATE_PREPARES is true. So if you're saying that this attribute is set to true as a side effect of changing databases, then it should work.
如果你说的是真的，那么这应该没有错误。只有当 PDO::ATTR_EMULATE_PREPARES 为真时，PDO 才能多次使用给定的命名参数。因此，如果您说这个属性设置为 true 作为更改数据库的副作用，那么它应该可以工作。
But it doesn't work -- it gets an error "Invalid parameter number" which indicates that non-emulated prepared statements remains in effect.
但它不起作用——它得到一个错误“无效的参数号”，表明非模拟的准备好的语句仍然有效。
回答by MrGlass
You should be setting the database when you create the PDO object. An example (from here)
您应该在创建 PDO 对象时设置数据库。一个例子（来自这里）
<?php
$hostname = "localhost";
$username = "your_username";
$password = "your_password";
try {
$dbh = new PDO("mysql:host=$hostname;dbname=mysql", $username, $password);
echo "Connected to database"; // check for connection
}
catch(PDOException $e)
{
echo $e->getMessage();
}
?>
回答by Varma Amit
Alternatively, you can select a MySQL database to use after a PHP PDO object has already been created as below:
或者，您可以在创建 PHP PDO 对象后选择要使用的 MySQL 数据库，如下所示：
With USE STATEMENT. But remember here USE STATEMENT is mysql command
使用语句。但请记住这里的 USE STATEMENT 是 mysql 命令
try
{
$conn = new PDO("mysql:host=$servername;", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec("use databasename");
//application logic
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
I hope my code is helpful for requested
我希望我的代码对请求有帮助
回答by Ignas
As far as I know, you have to create a new object for each connection. You can always extend the PDO class with a method which connects to multiple databases. And then use it as you like:
据我所知，您必须为每个连接创建一个新对象。您始终可以使用连接到多个数据库的方法来扩展 PDO 类。然后随意使用它：
public function pickDatabase($db) {
if($db == 'main') {
return $this->db['main']; //instance of PDO object
else
return $this->db['secondary']; //another instance of PDO object
}
and use it like $yourclass->pickDatabase('main')->fetchAll('your stuff');
并像使用它一样 $yourclass->pickDatabase('main')->fetchAll('your stuff');
