为什么mysql在似乎没有时会给我一个语法错误？

The following code is runnable and works fine but if I change $dbh->do("set names utf8"); to $dbh->do("set names gbk");, I will receive a syntax error:

以下代码是可运行的并且工作正常，但如果我更改$ dbh-> do（“set names utf8”）;到$ dbh-> do（“set names gbk”）;,我将收到语法错误：

use strict;
use warnings;
use DBD::mysql;

my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost","root","password");
$dbh->do("set names utf8");

open  my $jpg, '<', 'test.jpg' or die "$!";
binmode $jpg;
my $image = do{local $/; <$jpg>};
close $jpg;

 my $sql = "INSERT INTO student VALUES (?, ?)";

 my $sth = $dbh->prepare($sql);
 $sth->bind_param(1, 'Yang Liu');
 $sth->bind_param(2, $image);   
 $sth->execute();
 $sth->finish;
 exit;

The syntax error reads as follows:

语法错误如下：

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the m
anual that corresponds to your MySQL server version for the right syntax to use
near 'id=\'W5M0MpCehiHzreSzNTczkc9d\'?>\n<x:xmpmeta xmlns:x=\'adobe:ns:meta/\' x
:xmptk' at line 1 at D:\sqltest.pl line 22.
DBD::mysql::st execute failed: You have an error in your SQL syntax; check the m
anual that corresponds to your MySQL server version for the right syntax to use
near 'id=\'W5M0MpCehiHzreSzNTczkc9d\'?>\n<x:xmpmeta xmlns:x=\'adobe:ns:meta/\' x
:xmptk' at line 1 at D:\sqltest.pl line 22.

And the suspicious line that reads 'id=\'W5M0MpCehiHzreSzNTczkc9d\'?>\n<x:xmpmeta xmlns:x=\'adobe:ns:meta/\' is associated with the test.jpg image file which I'm trying to store to the longblob as I can find the same string if I open the image file using a text editor.

读取'id = \'的可疑行W5M0MpCehiHzreSzNTczkc9d \'？> \ n ：xmpmeta>

It's weird that code also works if I change $dbh->do("set names utf8"); to $dbh->do("set names gb2312"); Why the set names gbk statement gives me a syntax error? Is there something obvious that I'm doing wrong?

如果我更改$ dbh-> do（“set names utf8”），代码也可以工作，这很奇怪。 to $ dbh-> do（“set names gb2312”）;为什么set name gbk语句给我一个语法错误？有什么明显的东西我做错了吗？

Thanks in advance:)

提前致谢：）

I'm running ActivePerl 5.10.1 and MySQL 5.5.

我正在运行ActivePerl 5.10.1和MySQL 5.5。

UPDATE

UPDATE

I've found the culprit!

我找到了罪魁祸首！

This line local $/; is causing a MySQL syntax error. To fix the problem, simply comment out this line.

这行本地$ /;导致MySQL语法错误。要解决此问题，只需注释掉这一行。

My Perl script is encoded in GBK and my database table encoding is also GBK, but I have to also add the set names to gbk to the DBI in perl, otherwise, GBK characters inserted into the table won't display properly in a GBK environment.

我的Perl脚本以GBK编码，我的数据库表编码也是GBK，但是我还必须在glk中将设置名称添加到perl中的DBI，否则插入表中的GBK字符将无法在GBK环境中正确显示。

UPDATE2

UPDATE2

I thought I had fixed the problem but the problem is actually still there. When I delete the local $/; line, there's no error message but the blob field contains a corrupted image file.

我以为我已经解决了问题，但问题实际上仍然存在。当我删除本地$ /;行，没有错误消息，但blob字段包含损坏的图像文件。

1 个解决方案

#1

GBK is outdated, you must not use it anymore; and MySQL does not support the current standard GB 18030.

GBK已过时，您不能再使用它了;和MySQL不支持当前的标准GB 18030。

http://dev.mysql.com/doc/refman/5.5/en/faqs-cjk.html#qandaitem-B-11-1-1
http://dev.mysql.com/doc/refman/5.5/en/faqs-cjk.html#qandaitem-B-11-1-1
http://www.gb18030.com/
http://www.gb18030.com/
http://zh.wikipedia.org/wiki/GB_18030
http://zh.wikipedia.org/wiki/GB_18030

Work-around: do not set names, and deal with encoding of text in Perl only. Keep binary data such as images as octets.

解决方法：不要设置名称，只处理Perl中的文本编码。将二进制数据（如图像）保持为八位字节。

use utf8;
use Encode qw(encode);
use Encode::HanExtra qw();
⋮
my $name = encode('GB18030', '刘阳', Encode::FB_CROAK | Encode::LEAVE_SRC);
$dbh->do(
    'INSERT INTO student VALUES (?, ?)',
    {},
    $name, $image
);

(I have not tested this code.)

（我没有测试过这段代码。）

#1