Perl DBD连接MariaDB插入德语字符报错，求编码适配方案

问题背景 #

三年前曾因字符集错误无法向表中插入数据，如今使用同一脚本处理新数据时再次遇到相同问题。本次问题出在Ilpo Järvinen中的德语变音字符，报错信息如下：

DBD::mysql::st execute failed: Incorrect string value: '\xE4rvine...' for column `lsv6`.`xyz_content`.`fulltext` at row 1 at myscript.pl line 467.

待处理邮件的编码为iso-8859-1，具体信息：

Content-type: text/plain; charset="iso-8859-1"
Content-transfer-encoding: quoted-printable

数据库相关核心代码片段：

sub db_connect($) {
    ...
    return DBI->connect("DBI:mysql:database=$DB{'db'};host=$DB{'host'}", $DB{'user'}, $DB{'pass'}, { PrintError => 1, RaiseError => 1, mysql_enable_utf8mb4 => 1 } )
    ...
}

$fullText               = $dbh->quote($fullText);
my $sql                 = <<EOF;
INSERT INTO xuxgc_content (title, alias, introtext, `fulltext`, state, catid, created, created_by, created_by_alias, modified, modified_by, checked_out, checked_out_time, publish_up, publish_down, images, urls, attribs, version, ordering, metakey, metadesc, metadata, access, hits, language)
VALUES ($title, "$title_alias", $introText, $fullText, $state, $catid, $created, $created_by, $created_by_alias, $modified, $modified_by, $checked_out, $checked_out_time, $publish_up, $publish_down, $images, $urls, $attribs, $version, $ordering, $metakey, $metadesc, $metadata, $access, $hits, $language);
EOF

my $sth = $dbh->prepare($sql);
$sth->execute();
db_disconnect($dbh);

移除非ASCII字符后插入可正常执行：

$fullText               =~ s/\xE4//g;                                                                                                                                                                              
$fullText               =~ s/\xFC//g;                                                                                                                                                                              
$fullText               =~ s/\xE5//g;                    

疑问：是否应使用iso-8859-1编码替代utf8mb4？

解决方案：无需替换utf8mb4，转码才是正确路径 #

utf8mb4是MySQL对Unicode支持最完整的编码，能兼容德语变音、emoji等所有特殊字符，换成iso-8859-1反而会限制字符支持范围，后续必然再出问题。

问题本质是：脚本直接将iso-8859-1编码的字符串当作utf8mb4传入数据库，编码不匹配导致报错。正确做法是先把邮件内容从iso-8859-1转成UTF-8（mysql_enable_utf8mb4要求传入的字符串为UTF-8编码）。

具体修改步骤 #

1. 引入编码转换模块：在Perl脚本开头添加：

use Encode;

2. 转码处理字段：在调用$dbh->quote之前，将iso-8859-1编码的字符串转成Perl内部UTF-8字符串：

# 对fullText转码，其他含特殊字符的字段如title、introText也需同理处理
$fullText = Encode::decode('iso-8859-1', $fullText);

3. 推荐使用参数绑定替代SQL拼接：当前用quote拼接SQL存在注入风险，参数绑定更安全且能自动处理编码：

# 替换原有的quote和SQL拼接代码
my $sql = <<EOF;
INSERT INTO xuxgc_content (title, alias, introtext, `fulltext`, state, catid, created, created_by, created_by_alias, modified, modified_by, checked_out, checked_out_time, publish_up, publish_down, images, urls, attribs, version, ordering, metakey, metadesc, metadata, access, hits, language)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
EOF
my $sth = $dbh->prepare($sql);
$sth->execute($title, $title_alias, $introText, $fullText, $state, $catid, $created, $created_by, $created_by_alias, $modified, $modified_by, $checked_out, $checked_out_time, $publish_up, $publish_down, $images, $urls, $attribs, $version, $ordering, $metakey, $metadesc, $metadata, $access, $hits, $language);

额外验证项 #

• 确认数据库表的fulltext字段字符集为utf8mb4，校对规则推荐utf8mb4_unicode_ci
• 确保所有包含特殊字符的字段都完成了转码处理

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