PHP从MariaDB转JSON出现�乱码，编码问题求助

服务器基础信息 #

PHPMyAdmin显示的服务器信息：

Server type: MariaDB
Server version: 10.3.39-MariaDB-log - MariaDB Server
Server charset: ISO 8859-2 Central European (latin2)
Server connection collation: utf8mb4_unicode_ci

注：无法修改SQL服务器配置，由网站托管商提供

当前环境配置情况 #

• 所有数据库、表及列均使用utf8mb4_unicode_ci排序规则
• 所有文件编码为UTF-8
• 数据在PHPMyAdmin和MySQLWorkbench中显示正常，网站其他脚本可正常显示英文、俄文、中文等多语言内容，但目标脚本出现异常
• 尝试过通过PHPMA、Workbench及目标脚本插入数据

PDO数据库连接代码 #

<?php
if (!isset($pdo))
{
    $DBHOST = 'localhost';
    $DBNAME = '***';
    $DBUSER = '***';
    $DBPASS = '***';
    $DBCHRS = 'utf8mb4';

    $dsn = 'mysql:host='.$DBHOST.';dbname='.$DBNAME.';charset='.$DBCHRS;
    $options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false];
    try
    {
        $pdo = new PDO($dsn, $DBUSER, $DBPASS, $options);
    }
    catch (\PDOException $e)
    {
        echo 'Could not connect to the database!<br>Message: ', $e->getMessage(), '<br>Code: ', $e->getCode();
        exit();
    }
}
?>

查询及JSON生成代码 #

$json = [];
$json['people'] = [];
$json['relations'] = [];

$stmt = $pdo->prepare('SELECT * FROM `tree_people`;');
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
    $json['people'][] = $row;

$stmt = $pdo->prepare('SELECT * FROM `tree_relations`;');
$stmt->execute();
while($row = $stmt->fetch(PDO::FETCH_ASSOC))
    $json['relations'][] = $row;

/*/
header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json; charset=utf-8');
/*/
header('Content-Type: text/html; charset=utf-8');
//*/

//*/
echo '<pre>';
print_r($json);
echo '</pre>';
//*/

echo '<pre>';
echo json_encode($json, JSON_UNESCAPED_UNICODE);
echo '</pre>';

异常现象 #

• JSON生成失败（为空）
• 使用print_r显示内容时，所有特殊字符均显示为�
• 仅能正常读取通过自有编辑器写入数据库的数据，通过PMA写入的数据读取异常；部分初始通过PMA插入的数据，现在在PMA或Workbench中读取也显示乱码

字符集配置查询结果 #

MySQL Workbench执行SHOW VARIABLES LIKE 'char%';结果 #

character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  utf8mb4
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    latin2
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/

PHPMyAdmin执行SHOW VARIABLES LIKE 'char%';结果 #

character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  utf8mb4
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    latin2
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/

PHP PDO执行SHOW VARIABLES LIKE 'char%';结果 #

Array
(
    [0] => stdClass Object
        (
            [Variable_name] => character_set_client
            [Value] => latin2
        )

    [1] => stdClass Object
        (
            [Variable_name] => character_set_connection
            [Value] => latin2
        )

    [2] => stdClass Object
        (
            [Variable_name] => character_set_database
            [Value] => utf8mb4
        )

    [3] => stdClass Object
        (
            [Variable_name] => character_set_filesystem
            [Value] => binary
        )

    [4] => stdClass Object
        (
            [Variable_name] => character_set_results
            [Value] => latin2
        )

    [5] => stdClass Object
        (
            [Variable_name] => character_set_server
            [Value] => latin2
        )

    [6] => stdClass Object
        (
            [Variable_name] => character_set_system
            [Value] => utf8
        )

    [7] => stdClass Object
        (
            [Variable_name] => character_sets_dir
            [Value] => /usr/share/mysql/charsets/
        )

)

疑问 #

数据库是否应该自动处理编码匹配问题？为何PMA/Workbench与脚本的编码存在不匹配？

内容的提问来源于stack exchange，提问作者herhor67