PDO查询返回很多\ uXXXX字符代码，我无法将其转换为unicode字符 [英] PDO query returns lots of \uXXXX character codes which I can't convert to unicode characters

问题描述

我有一个MySQL数据库表，其中以不同的语言存储国家/地区的名称，但我无法获得以Unicode字符显示的数据-我只能在特殊字符应显示的位置显示\ uXXXX代码.

I have a MySQL database table in which I store the names of countries in different languages, and I can't get the data to display in unicode characters - I can only get \uXXXX codes displayed where the special characters should be.

该查询用于AJAX请求中，并将结果编码为JSON对象.

The query is used in an AJAX request with the results encoded as a JSON object.

这是表格(已截断):

CREATE TABLE IF NOT EXISTS `tbl_countries` (
  `ccode` varchar(2) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `country_en` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `country_fr` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL,
  `country_de` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL,
  `country_es` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL,
  `country_ru` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL,
  `country_tr` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL,
  `country_ar` varchar(100) character set utf8 collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`ccode`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `tbl_countries`
--

INSERT INTO `tbl_countries` (`ccode`, `country_en`, `country_fr`, `country_de`, `country_es`, `country_ru`, `country_tr`, `country_ar`) VALUES
('AF', 'Afghanistan', 'Afghanistan', 'Afghanistan', 'Afganistán', 'Афганистан', 'Afganistan', 'أفغانستان'),
('AX', 'Aland Islands', 'Îles Åland', 'Alandinseln', 'Islas Åland', 'Аландские острова', 'Aland Adaları', 'جزر أولان'),
('AL', 'Albania', 'Albanie', 'Albanien', 'Albania', 'Албания', 'Arnavutluk', 'ألبانيا'),
('DZ', 'Algeria', 'Algérie', 'Algerien', 'Argelia', 'Алжир', 'Cezayir', 'الجزائر'),
('AS', 'American Samoa', 'Samoa américaines', 'Amerikanisch-Samoa', 'Samoa Americana', 'Американское Самоа', 'Amerikan Samoası', 'ساموا الأمريكية');

这是创建PDO的代码:

This is the code to create the PDO:

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",
    $dbuser,
    $dbpass,
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
);

$return_arr = array ();

if ($conn) {
    $ac_term = $_GET['term'];
    $query = "SELECT * FROM `tbl_countries` WHERE `country_en` LIKE :term";
    $result = $conn->prepare ($query);
    $result->bindValue (":term", "%".$ac_term."%");
    $result->execute ();

    /* Retrieve and store in array the results of the query.*/
    while ($row = $result->fetch (PDO::FETCH_ASSOC)) {
        $row_array['country_en'] = $row['country_en'];
        $row_array['country_de'] = $row['country_de'];
        $row_array['country_es'] = $row['country_es'];
        $row_array['country_fr'] = $row['country_fr'];
        $row_array['country_ru'] = $row['country_ru'];
        $row_array['country_tr'] = $row['country_tr'];
        $row_array['country_ar'] = $row['country_ar'];
        $row_array['ccode'] = $row['ccode'];
        array_push ($return_arr, $row_array);
    }
}

unset ($conn);

echo json_encode ($return_arr);

PHP脚本的开头是以下行:

At the start of the PHP script is the following line:

header('Content-Type: text/html; charset=utf-8');

这是我输入搜索词united%20king时得到的典型输出:

This is the typical output I get if I enter the search term united%20king:

[{
   "country_en":"United Kingdom",
   "country_de":"Vereinigtes K\u00f6nigreich",
   "country_es":"Reino Unido",
   "country_fr":"Royaume-Uni",
   "country_ru":"\u0412\u0435\u043b\u0438\u043a\u043e\u0431\u0440\u0438\u0442\u0430\u043d\u0438\u044f",
   "country_tr":"Birle\u015fik Krall\u0131k",
   "country_ar":"\u0627\u0644\u0645\u0645\u0644\u0643\u0629 \u0627\u0644\u0645\u062a\u062d\u062f\u0629",
   "ccode":"GB"
}]

在PHP代码中，我尝试使用htmlentities，但它 only 仅显示德语输出的特殊字符:

In the PHP code, I tried using htmlentities, but it only showed the special characters for the German output:

$row_array['country_de'] = htmlentities ($row['country_de'], ENT_QUOTES, "UTF-8");

我想念什么?感谢您的阅读.

What am I missing? Thanks for reading.

推荐答案

这不是PDO，而是json_encode的常规行为.在现代的PHP版本中，您可以将其关闭，但这两种方式都不应该成为问题.

It is not PDO but regular behavior of json_encode. In modern PHP versions you can turn it off, but it shouldn't be a problem either way.

我不知道您为什么要回显原始的json，但是通常不打算将其直接回显为HTML，而是由某些JS代码使用.而且JS可以解决这种编码问题.但是，为了减少数据量，由于可以使用5.4 JSON_UNESCAPED_UNICODE标志，所以.

I have no idea why you wanted to echo raw json out, but usually it is not intended to be echoed directly into HTML but rather used by some JS code. And JS can sort this encoding out. However, to reduce the amount of data, since 5.4 JSON_UNESCAPED_UNICODE flag can be used, .

我也建议您在提出问题之前先调试一下代码.
如果要检查PDO的输出，请对PDO(而不是json)进行处理.验证程序执行的每个步骤，以找出破坏数据的步骤.

Also let me suggest you to debug your code a little before asking a question.
If you want to check PDO's output, do it for PDO, not for json. Verify every step your program does, to find one which spoils the data.

这篇关于PDO查询返回很多\ uXXXX字符代码，我无法将其转换为unicode字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！