PHP PDO：字符集，设置名称？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4361459/
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
PHP PDO: charset, set names?
提问by Karem
I had this previously in my normal mysql_* connection:
我以前在正常的 mysql_* 连接中有这个：
mysql_set_charset("utf8",$link);
mysql_query("SET NAMES 'UTF8'");
Do I need it for the PDO? And where should I have it?
我需要它用于 PDO 吗？我应该在哪里拥有它？
$connect = new PDO("mysql:host=$host;dbname=$db", $user, $pass, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
回答by Cobra_Fast
You'll have it in your connection string like:
您将在连接字符串中使用它，例如：
"mysql:host=$host;dbname=$db;charset=utf8"
HOWEVER, prior to PHP 5.3.6, the charset option was ignored. If you're running an older version of PHP, you must do it like this:
然而，在 PHP 5.3.6 之前，字符集选项被忽略。如果您运行的是旧版本的 PHP，则必须这样做：
$dbh = new PDO("mysql:$connstr", $user, $password);
$dbh->exec("set names utf8");
回答by 9nix00
Prior to PHP 5.3.6, the charset option was ignored. If you're running an older version of PHP, you must do it like this:
在 PHP 5.3.6 之前，字符集选项被忽略。如果您运行的是旧版本的 PHP，则必须这样做：
<?php
$dbh = new PDO("mysql:$connstr", $user, $password);
$dbh -> exec("set names utf8");
?>
回答by Jpsy
This is probably the most elegant way to do it.
Right in the PDO constructor call, but avoiding the buggy charset option (as mentioned above):
这可能是最优雅的方式。
就在 PDO 构造函数调用中，但要避免错误的字符集选项（如上所述）：
$connect = new PDO(
"mysql:host=$host;dbname=$db",
$user,
$pass,
array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
)
);
Works great for me.
对我很有用。
回答by álvaro González
For completeness, there're actually threeways to set the encoding when connecting to MySQL from PDO and which ones are available depend on your PHP version. The order of preference would be:
为了完整起见，当从 PDO 连接到 MySQL 时，实际上有三种方法可以设置编码，哪些可用取决于您的 PHP 版本。优先顺序是：
charsetparameter in the DSN string- Run
SET NAMES utf8withPDO::MYSQL_ATTR_INIT_COMMANDconnection option - Run
SET NAMES utf8manually
charsetDSN 字符串中的参数SET NAMES utf8使用PDO::MYSQL_ATTR_INIT_COMMAND连接选项运行SET NAMES utf8手动运行
This sample code implements all three:
此示例代码实现了所有三个：
<?php
define('DB_HOST', 'localhost');
define('DB_SCHEMA', 'test');
define('DB_USER', 'test');
define('DB_PASSWORD', 'test');
define('DB_ENCODING', 'utf8');
$dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_SCHEMA;
$options = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
);
if( version_compare(PHP_VERSION, '5.3.6', '<') ){
if( defined('PDO::MYSQL_ATTR_INIT_COMMAND') ){
$options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . DB_ENCODING;
}
}else{
$dsn .= ';charset=' . DB_ENCODING;
}
$conn = @new PDO($dsn, DB_USER, DB_PASSWORD, $options);
if( version_compare(PHP_VERSION, '5.3.6', '<') && !defined('PDO::MYSQL_ATTR_INIT_COMMAND') ){
$sql = 'SET NAMES ' . DB_ENCODING;
$conn->exec($sql);
}
Doing all three is probably overkill (unless you're writing a class you plan to distribute or reuse).
做这三件事可能有点矫枉过正（除非你正在编写一个计划分发或重用的类）。
回答by Medda86
I just want to add that you have to make sure your database is created with COLLATE utf8_general_ci or whichever collation you want to use, Else you might end up with another one than intended.
我只想补充一点，您必须确保您的数据库是使用 COLLATE utf8_general_ci 或您想要使用的任何排序规则创建的，否则您最终可能会得到另一个超出预期的排序规则。
In phpmyadmin you can see the collation by clicking your database and choose operations. If you try create tables with another collation than your database, your tables will end up with the database collation anyways.
在 phpmyadmin 中，您可以通过单击您的数据库并选择操作来查看排序规则。如果您尝试使用不同于数据库的其他排序规则创建表，则无论如何您的表都会以数据库排序规则结束。
So make sure the collation for your database is right before creating tables. Hope this saves someone a few hours lol
因此，在创建表之前，请确保数据库的排序规则正确。希望这可以为某人节省几个小时，哈哈
回答by Berdir
I think you need an additionally query because the charset option in the DSN is actually ignored. see link posted in the comment of the other answer.
我认为您需要额外的查询，因为实际上忽略了 DSN 中的字符集选项。请参阅其他答案的评论中发布的链接。
Looking at how Drupal 7 is doing it in http://api.drupal.org/api/drupal/includes--database--mysql--database.inc/function/DatabaseConnection_mysql%3A%3A__construct/7:
在http://api.drupal.org/api/drupal/includes--database--mysql--database.inc/function/DatabaseConnection_mysql%3A%3A__construct/7 中查看Drupal 7 是如何做到的：
// Force MySQL to use the UTF-8 character set. Also set the collation, if a
// certain one has been set; otherwise, MySQL defaults to 'utf8_general_ci'
// for UTF-8.
if (!empty($connection_options['collation'])) {
$this->exec('SET NAMES utf8 COLLATE ' . $connection_options['collation']);
}
else {
$this->exec('SET NAMES utf8');
}
回答by gauravbhai daxini
$conn = new PDO("mysql:host=$host;dbname=$db;charset=utf8", $user, $pass);
回答by elite andot
I test this code and
我测试这个代码和
$db=new PDO('mysql:host=localhost;dbname=cwDB','root','',
array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$sql="select * from products ";
$stmt=$db->prepare($sql);
$stmt->execute();
while($result=$stmt->fetch(PDO::FETCH_ASSOC)){
$id=$result['id'];
}
回答by Jayanit Satani
$con="";
$MODE="";
$dbhost = "localhost";
$dbuser = "root";
$dbpassword = "";
$database = "name";
$con = new PDO ( "mysql:host=$dbhost;dbname=$database", "$dbuser", "$dbpassword", array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
$con->setAttribute ( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
回答by Dilmurod
$con = new PDO ( "mysql:host=$dbhost;dbname=$database;charset=$encoding", "$dbuser", "$dbpassword");
$con = new PDO ( "mysql:host=$dbhost;dbname=$database; charset=$encoding", "$dbuser", "$dbpassword");
