使用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，如下所述：

MySQL 使用 utf8mb4 字符集和 utf8mb4_bin 排序規則處理 JSON 背景關系中使用的字串。其他字符集中的字串根據需要轉換為 utf8mb4。（https://dev.mysql.com/doc/refman/8.0/en/json.html）

我的理解也是 DBI 處理 UTF-8 編碼/解碼，并且應該像對 mediumtext 列所做的那樣回傳解碼的 UTF-8，如下所述：

該屬性決定 DBD::mysql 是否應該假定存盤在資料庫中的字串是 utf8。此功能默認為關閉。

設定后，從文本列型別（char、varchar 等）檢索的資料將在必要時打開 UTF-8 標志。這會在該字串上啟用字符語意。 https://metacpan.org/pod/DBD::mysql#mysql_enable_utf8

但是，它似乎不適用于 JSON 列。從 JSON 列檢索資料后，似乎需要顯式解碼。

那么它是什么... DBI/DBD::mysql 中的錯誤，還是我大腦中的錯誤？

編輯：好訊息，這不是我的大腦。壞訊息，似乎是一個已知的錯誤。https://github.com/perl5-dbi/DBD-mysql/issues/309

因此，我現在正在尋找的答案是一種向后兼容的解決方法，即，如果/當 DBD::mysql 修復時，這種解決方法不會中斷。雙解碼不好。

uj5u.com熱心網友回復：

因此，我現在正在尋找的答案是一種向后兼容的解決方法，即，如果/當 DBD::mysql 修復時，這種解決方法不會中斷。雙解碼不好。

您可以嘗試通過創建一個測驗表來確定是否存在 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);

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/412283.html

標籤：

上一篇：Bash腳本-需要find命令來輸出用雙引號括起來的檔案路徑而沒有換行符

下一篇：日期和時間計算