PHP/MySQL 中的 PDO 和 UTF-8 特殊字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8002822/
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
PDO and UTF-8 special characters in PHP / MySQL?
提问by sophie
I am using MySQL and PHP 5.3 and tried this code.
我正在使用 MySQL 和 PHP 5.3 并尝试了此代码。
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$con = mysql_connect("localhost", "root", "");
mysql_set_charset('utf8');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("kdict", $con);
$sql = "SELECT * FROM `en-kh` where english='a'";
echo $sql;
$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{
echo $row['english'] . " </br> " . $row['khmer'];
echo "<br />";
}
?>
=> I got good UTF-8 render display, well done.
=> 我得到了很好的 UTF-8 渲染显示，做得很好。
But for now I create a class PDO to keep easy to extend and more easy
但是现在我创建了一个类 PDO 以保持易于扩展和更容易
class crud {
// code..
public function conn()
{
isset($this->username);
isset($this->password);
if (!$this->db instanceof PDO)
{
$this->db = new PDO($this->dsn, $this->username, $this->password);
$this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
}
}
/*more code here*/
}
/*** a new crud object ***/
$crud = new crud();
/*** The DSN ***/
$crud->dsn = "mysql:dbname=kdict;host=localhost";
/*** MySQL username and password ***/
$crud->username = 'root';
$crud->password = '';
/*** select all records from table ***/
$records = $crud->rawSelect("SELECT * FROM `en-kh` where english='a'");
/*** fetch only associative array of values ***/
$rows = $records->fetchAll(PDO::FETCH_ASSOC);
/*** display the records ***/
foreach($rows as $row)
{
foreach($row as $fieldname=>$value)
{
echo $fieldname.' = '.$value.'<br />';
}
echo '<hr />';
}
?>
But it displays my character something like this '????'
但它显示我的角色是这样的'????'
I found this link on Stack Overflow, it looks like the same problem i met Special characters in PHP / MySQL
我在 Stack Overflow 上找到了这个链接，看起来和我在 PHP/MySQL 中遇到特殊字符的问题一样
It looks the same as my problem => I tried to fix it, but I still doesn't work.
它看起来和我的问题一样 => 我试图修复它，但我仍然无法正常工作。
$this->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAME'utf8'");
Can anyone tell me what the problem is? How can I correct it?
谁能告诉我是什么问题？我该如何纠正？
thanks
谢谢
回答by Alix Axel
You're missing an S: it's SET NAMESand not SET NAME:
你缺少一个S：它是SET NAMES而不是SET NAME：
$this->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
You also need to un-comment it of course.Also, PDO::MYSQL_ATTR_INIT_COMMANDcan notbe setwith PDO::setAttribute()after you've established your database connection (the constant name says it all), you've to specify it in the constructorusing the $driver_optionsargument, like this:
当然，您还需要取消注释。此外，PDO::MYSQL_ATTR_INIT_COMMAND可以不设置与PDO::setAttribute()你已经建立了自己的数据库连接（常量名字说明了一切），您在指定之后的构造函数使用$driver_options参数，如下所示：
$this->db = new PDO($this->dsn, $this->username, $this->password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
An alternative to this is to just execute that very same query immediately after connecting:
另一种方法是在连接后立即执行相同的查询：
$this->db = new PDO($this->dsn, $this->username, $this->password);
$this->db->exec("SET NAMES 'utf8';");
回答by dorogz
I had the same trouble and found out after trying out 1000000 different options that the default mysql engine still remains MyISAM.
我遇到了同样的问题，在尝试了 1000000 个不同的选项后发现默认的 mysql 引擎仍然是 MyISAM。
Verifying that InnoDB is the Default Storage Engine as such:
验证 InnoDB 是默认存储引擎，如下所示：
Issue the command SHOW VARIABLES LIKE 'have_innodb';to confirm that InnoDB is available at all.
发出命令SHOW VARIABLES LIKE 'have_innodb';以确认 InnoDB 完全可用。
Then issue the command SHOW ENGINES;to see all the different MySQL storage engines.
然后发出命令SHOW ENGINES;以查看所有不同的 MySQL 存储引擎。
Look for DEFAULTin the InnoDBline and NOT in the MyISAMline. I realized that my provided had a setting preventing me from changing the default engine using SET storage_engine=MYISAM;. In my case, I contacted my provider and was directed to where I could change the option to be able to change the default engine. Hope this helps!
寻找DEFAULT的InnoDB线，而不是在MyISAM行。我意识到我提供的设置阻止我使用SET storage_engine=MYISAM;. 就我而言，我联系了我的提供商，并被引导到我可以更改选项以更改默认引擎的地方。希望这可以帮助！
回答by Amarjit Kushwaha
The possible way to insert Special characters using PDO, just follow the these to steps:
使用 PDO 插入特殊字符的可能方法，只需按照以下步骤操作：
1) Set the Attribute to you connection class.
1) 将属性设置为您的连接类。
$this->db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAME'utf8'");
2) Now you can use htmlspecialchars while query creation and insertion to database:
2) 现在您可以在查询创建和插入数据库时使用 htmlspecialchars：
$ShortDescription = htmlspecialchars($_POST['ShortDescription'], ENT_QUOTES);
$LongDescription = htmlspecialchars($_POST['LongDescription'],ENT_QUOTES);
And it will work fine.
它会正常工作。
回答by Eduardo Zechmeister
I suggest always connecting to a database via PDO.
我建议始终通过 PDO 连接到数据库。
Below is a sample code;
下面是一个示例代码；
<?php
class connMysql {
protected static $instance;
private static $database_type = "mysql";
private static $hostname = "localhost";
private static $user = “database_user”;
private static $password = “database_user_password”;
private static $database_name = “your_database”;
private static $persistent = false;
private function __construct() {
try {
self::$instance = new \PDO(self::$database_type . ":host=" . self::$hostname . ";dbname=" . self::$database_name
, self::$user
, self::$password
, array(
\PDO::ATTR_PERSISTENT => self::$persistent,
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::ATTR_STRINGIFY_FETCHES => true,
\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"
));
} catch (PDOException $e) {
echo "Connection Error: " . $e->getMessage();
}
}
public static function getInstance() {
if (!self::$instance) {
new connMysql();
}
return self::$instance;
}
public static function close() {
self::$instance = null;
}
}
