使用PerlDBI DBD::mysql读取JSON型别的MySQL列（相对于TEXT）时的UTF-8译码-编程知识-白鹭情

这是一个作业单元测验中的问题。我认为这要么是 DBI DBD::mysql 中的错误，关于它如何处理 MySQL JSON 列，要么是我大脑中的错误。

use strict;
use warnings;

use utf8;

use Test2::V0;
use Test2::Plugin::UTF8;
use Test2::Plugin::NoWarnings echo => 1;

use DBI;
use DBD::mysql 4.041;  # 4.041  required for utf8mb4
use JSON 4.01 qw//;
use Encode;

#
# setup $dbh, create test table
#
my $dbname = '';
my $host = 'localhost';
my $user = '';
my $pass = '';

my $dbh = DBI->connect(
  "DBI:mysql:" . ($dbname || '') . ";host=" . $host,
  $user, 
  $pass || undef, 
  { RaiseError => 1, PrintError => 0, AutoCommit=> 1 }
);

$dbh->{'mysql_enable_utf8mb4'} = 1;
$dbh->{'charset'} = 'utf8';

$dbh->do("CREATE TABLE IF NOT EXISTS `test` (id int unsigned, `my_json` json NOT NULL, `my_text` mediumtext NOT NULL)");


#
# create and insert test data
#

# A use case for spam! Got this junk from spam inbox
my $utf8str = "ion?? été e??????ulière vs? Ch? ???????????Sho是ab 期待您x";  
my $hash = { test => $utf8str };
my $json = JSON->new->encode( $hash );

my $id = time;
$dbh->do("INSERT INTO test SET id=?, my_json=?, my_text=?", undef, $id, $json, $json);

#
# retrieve test data and check it
#
my ( $my_json, $my_text ) = $dbh->selectrow_array("SELECT my_json, my_text FROM test WHERE id=$id");

is( $my_text, $json );  # ok

is( $my_json, $json );  # fails. got {"test": "ion?\na\N{U 80}￠ ??t .... 

is( decode('UTF-8', $my_json), $json );  # ok'ish. mysql adds a space between "test":"..." but value looks ok

#
# another test, independent of JSON encoder, using hand-built json
#
$id  ;
$json = '{"test":"' . $utf8str . '"}';
$dbh->do("INSERT INTO test SET id=?, my_json=?, my_text=?", undef, $id, $json, $json);

( $my_json, $my_text ) = $dbh->selectrow_array("SELECT my_json, my_text FROM test WHERE id=$id");

is( $my_text, $json );  # ok

is( $my_json, $json );  # fails. got {"test": "ion?\na\N{U 80}￠ ??t .... 

printf "%vX", $my_json;  # 7B.22.74.65.73.74.22.3A.20.22.69.6F.6E.3F.E2.80.A2.20.C3.A9.74.C3.A9.20.65.F0.9F.98.8D.F0.9F.92.8B.F0.9F.94.A5.75.6C.69.C3.A8.72.65.20.76.73.E2.80.A2.20.43.68.E2.80.A2.20.F0.9F.98.8A.E2.AD.90.F0.9F.91.89.F0.9F.8F.BB.F0.9F.94.9E.F0.9F.8D.86.53.68.6F.E6.98.AF.61.62.20.E6.9C.9F.E5.BE.85.E6.82.A8.78.22.7D

printf "%vX", $json;  # 7B.22.74.65.73.74.22.3A.22.69.6F.6E.3F.2022.20.E9.74.E9.20.65.1F60D.1F48B.1F525.75.6C.69.E8.72.65.20.76.73.2022.20.43.68.2022.20.1F60A.2B50.1F449.1F3FB.1F51E.1F346.53.68.6F.662F.61.62.20.671F.5F85.60A8.78.22.7D

is( decode('UTF-8', $my_json), $json );  # ok'ish. mysql adds a space between "test":"..." but value looks ok

#
# cleanup
#
$dbh->do("DROP TABLE `test`");

$dbh->disconnect();

done_testing();

我的理解是 JSON 标准需要 UTF-8。此外，MySQL 还要求/使用关于 JSON 列的 UTF-8，如下所述：

我的理解也是 DBI 处理 UTF-8 编码/译码，并且应该像对 mediumtext 列所做的那样回传译码的 UTF-8，如下所述：

但是，它似乎不适用于 JSON 列。从 JSON 列检索资料后，似乎需要显式译码。

那么它是什么... DBI/DBD::mysql 中的错误，还是我大脑中的错误？

编辑：好讯息，这不是我的大脑。坏讯息，似乎是一个已知的错误。https://github.com/perl5-dbi/DBD-mysql/issues/309

因此，我现在正在寻找的答案是一种向后兼容的解决方法，即，如果/当 DBD::mysql 修复时，这种解决方法不会中断。双译码不好。

uj5u.com热心网友回复：

您可以尝试通过创建一个测验表来确定是否存在 JSON 译码错误，在该表中插入一个具有已知 UTF-8 编码且字节长度大于 1 的非 ascii 字符。例如：

$dbh->do("DROP TABLE IF EXISTS json_decode_test");
$dbh->do("CREATE TABLE json_decode_test (id int unsigned, `my_json` json NOT NULL)");
my $unicode_str = "是";  # This character will always have a UTF-8 encoding with
                         # byte length > 1
my $hash = { test_str => $unicode_str };
my $json = JSON->new;
my $json_str = $json->encode( $hash );
my $id = time;
my $attrs = undef;
$dbh->do("INSERT INTO json_decode_test SET id=?, my_json=?", $attrs, $id, $json_str);
my ( $json_str2 ) = $dbh->selectrow_array(
    "SELECT my_json FROM json_decode_test WHERE id=$id");
my $hash2 = $json->decode( $json_str2 );
my $unicode_str2 = $hash2->{test_str};
# If the json unicode bug is present, $unicode_str2 will not be decoded. Instead
#  it will be a string of length 3 representing the UTF-8 encoding of $unicode_str
#  (printf "%vX\n", $unicode_str2) gives output : E6.98.AF
my $json_unicode_bug = (length $unicode_str2) > 1;
if ( $json_unicode_bug ) {
    say "unicode bug is present..";
    # need to run decode_utf8() on every returned json object from DBI
}

uj5u.com热心网友回复：

更改 SQL 请求以创建test表，如下所示

my $query = "
CREATE TABLE IF NOT EXISTS `test` (
    `id`       INT UNSIGNED,
    `my_json`  JSON NOT NULL,
    `my_text`  MEDIUMTEXT NOT NULL
) ENGINE=InnoDB DEFAULT
  CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
";

$dbh->do($query);