如何将UTF-8数据与Latin1 MySQL数据库表中保存的相同数据进行比较

我有一个繁琐的任务,即转换包含Latin1编码数据的MySQL表.

CREATE TABLE q_data (
    q_id int(11) NOT NULL DEFAULT '0',
    label varchar(8) NOT NULL DEFAULT '',
    text text NOT NULL,
    points decimal(8,3) NOT NULL DEFAULT '0.000',
    date_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY q_id (q_id)
) ENGINE=InnoDB DEFAULT CHARSET = latin1

这是ALTER TABLE我在桌子上执行的命令.

SET NAMES utf8;
ALTER TABLE q_data change text text blob;
ALTER TABLE q_data CONVERT TO CHARACTER SET utf8, change text text text;

现在我需要验证我的所有数据是否都正确转换.所以我尝试编写一个小的Perl代码来获取数据并进行比较.但我得到错配错误.我不确定我在这里做错了什么.

use strict;
use warnings;

use FindBin;
use lib File::Spec->catdir( $FindBin::RealBin, File::Spec->updir, 'lib' );
use DBI qw(:utils);

# DB Handle for the new DB where I have altered the table
my $dbh_utf8 = DBI->connect("DBI:mysql:database=data;host=xx-dev-1.xxxxx.com;port=3209","aaaa","xxxxxxx",{PrintError => 1, mysql_enable_utf8 => 1 }) || die "fail connecting to db_from";

# DB Handle for the OLD DB
my $dbh_latin = DBI->connect("DBI:mysql:database=data;host=xx-dev-1.xxxxx.com;port=3214","aaaa","xxxxxxx",{PrintError => 1, RaiseError=> 0 }) || die "fail connecting to db_to";

# Fetching the rows which has UTF-8 Characters 
my $sql = qq|SELECT *
 FROM  q_data
 WHERE length(text) <> char_length(text) 
 ORDER BY q_id desc
 LIMIT 10000|;

# Fetching the original data from the old DB in the UTF8 Format
my $sql1 = qq|SELECT q.*, CONVERT(CAST(CONVERT(q.text USING latin1) AS BINARY) USING utf8) AS text FROM q_data q WHERE q_id=? and label=?|;`

my $sth = $dbh_utf8->prepare($sql);
$sth->execute;`

while( my $data = $sth->fetchrow_hashref ) {
    my $latin_data = $dbh_latin->selectrow_hashref($sql1, undef, $data->{q_id}, $data->{label});
    print $latin_data->{text}, "\n";
    if (not defined $latin_data ) {
        #print $data->{q_id}, "\t", "No Latin Data", "\n";
    }
    elsif ( $latin_data->{text} ne $data->{text} ) {
         print $data->{q_id}, "\t", $data->{label}, "\n", "\n", $latin_data->{text}, "\n", $data->{text}, "\n";
    }
}
$sth->finish;
$dbh_utf8->disconnect;
$dbh_latin->disconnect;

我在q_data表格中有这种数据

INSERT INTO q_data (q_id, label, text, points, date_updated)
VALUES('1880941','o14-l1','Clearly states classroom diversity – noting considerations for modalities of learning, exceptional students and students with special needs.','0.000','2015-12-03 09:50:57');

根据我的假设,我不应该得到任何不匹配的数据.如果我错了,请纠正我.有没有其他方法来验证这个？也许在MySQL本身？

这是我运行此脚本时打印的输出:

1889941 o14-l2

Clearly states classroom diversity â?? noting considerations for only one of the following: modalities of learning, exceptional students and students with special needs.

Clearly states classroom diversity â noting considerations for only one of the following: modalities of learning, exceptional students and students with special needs.

Grant McLean.. 6

在我看来,您的数据已通过双重编码损坏.让我们通过一个例子来理解我的意思.

短划线字符' - '(EN DASH)的Unicode字符是U + 2013.所以在Perl中你可以将角色表示为"\x{2013}".但是在UTF-8中,该字符表示为三个字节:E2 80 93.我们可以看到使用十六进制转储实用程序:

~$ perl -CO -E 'say "\x{2013}"' | xxd
0000000: e280 930a                                ....

所以它是三个字节,它们共同代表一个字符.

现在让我们假设,一些进程读取这三个字节但不解码UTF-8.相反,该过程将每个字节解释为Latin-1字符:

Latin-1中的E2是â(带有CIRCUMFLEX的小写字母A)

Latin-1中的80是一个不可打印的控制字符(PADDING CHARACTER)

Latin-1中的93是一个不可打印的控制字符(SET TRANSMIT STATE)

现在这三个角色显然是胡言乱语,但请耐心等待.假设我们用UTF8编码每个字符:

U + 00E2变为C3 A2

U + 0080变为C2 80

U + 0093变为C2 93

因此一个字符" - "变为6个字节:C3 A2 C2 80 C2 93

现在,如果我们再次犯同样的错误,并且在不将UTF-8解码为3个字符的情况下读取这6个字节,而是最终得到6个Latin-1字符.这次不使用真正的Latin-1,我们将使用Win-Latin-1(CP1252)来解释每个字节,区别在于不可打印的控制字符被替换为可打印的字符,如智能引号:

C3是Ã

A2是¢

C2是Â

80是€

C2是Â

93是"

这六个字符组合在一起:âÂ" - 您可以从样本数据中识别出来.

因此总的来说,沿着这条线看起来你已经采用了一串UTF-8字节而没有将其解码为Perl字符串(它应该在DBI连接上启用mysql_enable_utf8时自动发生).然后你已经将已经是UTF-8的东西转换成了UTF-8,但是在这个过程中损坏了数据.是否重复了这里的粘贴是否给了第二级腐败还不清楚.

最好避免展开双重编码 - 返回原始源数据并重试.

在我看来,您的数据已通过双重编码损坏.让我们通过一个例子来理解我的意思.

短划线字符' - '(EN DASH)的Unicode字符是U + 2013.所以在Perl中你可以将角色表示为"\x{2013}".但是在UTF-8中,该字符表示为三个字节:E2 80 93.我们可以看到使用十六进制转储实用程序:

~$ perl -CO -E 'say "\x{2013}"' | xxd
0000000: e280 930a                                ....

所以它是三个字节,它们共同代表一个字符.

现在让我们假设,一些进程读取这三个字节但不解码UTF-8.相反,该过程将每个字节解释为Latin-1字符:

Latin-1中的E2是â(带有CIRCUMFLEX的小写字母A)

Latin-1中的80是一个不可打印的控制字符(PADDING CHARACTER)

Latin-1中的93是一个不可打印的控制字符(SET TRANSMIT STATE)

现在这三个角色显然是胡言乱语,但请耐心等待.假设我们用UTF8编码每个字符:

U + 00E2变为C3 A2

U + 0080变为C2 80

U + 0093变为C2 93

因此一个字符" - "变为6个字节:C3 A2 C2 80 C2 93

现在,如果我们再次犯同样的错误,并且在不将UTF-8解码为3个字符的情况下读取这6个字节,而是最终得到6个Latin-1字符.这次不使用真正的Latin-1,我们将使用Win-Latin-1(CP1252)来解释每个字节,区别在于不可打印的控制字符被替换为可打印的字符,如智能引号:

C3是Ã

A2是¢

C2是Â

80是€

C2是Â

93是"

这六个字符组合在一起:âÂ" - 您可以从样本数据中识别出来.

因此总的来说,沿着这条线看起来你已经采用了一串UTF-8字节而没有将其解码为Perl字符串(它应该在DBI连接上启用mysql_enable_utf8时自动发生).然后你已经将已经是UTF-8的东西转换成了UTF-8,但是在这个过程中损坏了数据.是否重复了这里的粘贴是否给了第二级腐败还不清楚.

最好避免展开双重编码 - 返回原始源数据并重试.