MYSQL：西欧字符使用LOAD DATA LOCAL INFILE获取Mangled [英] MYSQL: West European Characters get Mangled using LOAD DATA LOCAL INFILE

问题描述

当我在MySQL命令行运行这个时，它工作正常：

  INSERT INTO MYTABLE VALUES（NULL，101942， '2015-05-08'，'sähkötupakalle'）; 
  

'ä'和'ö'最终会出现在MySQL varchar列中。 p>

但是，当我把相同的数据放在一个文件中，并使用

  LOAD DATA LOCAL INFILE 
  

然后'ä'和'ö'与MySQL varchar列中的数据类似，如下所示：

 sí¤¤hkötupakalle
  

有关如何使用LOAD DATA LOCAL INFILE可以正确加载这些字符的任何想法？ FYI，我的表有CHARSET = utf8。

解决方案

显示你正在加载的文件正确编码utf8？但是您没有包含 CHARACTER SET utf8 子句？

Mojibake症状： strong>
当选择文本时，每个非英语字符将被2-3个字符替换，您可以将其称为乱码或垃圾。

• 客户端的字节 INSERTed 编码为utf8（good），


• 连接的字符集为 latin1 （例如，通过 SET NAMES latin1 ）和


• 表列已声明为 CHARACTER SET latin1

如何修正文字和表格：

执行两步 ALTER ：

  ALTER TABLE TBL MODIFY COLUMN col VARBINARY（...）...; 
 ALTER TABLE TBL MODIFY COLUMN col VARCHAR（...）... CHARACTER SET utf8 ...; 
  

其中长度足够大，其他...

在转换列定义的同时单独保留位。

如何修复代码（一般）：

• 将客户端的字符集声明更改为 utf8 - 通过 SET NAMES utf8 或同等字体。

When I run this at the MySQL command line, it works fine:

INSERT INTO MYTABLE VALUES(NULL,101942,'2015-05-08','sähkötupakalle');

The 'ä' and the 'ö' end up in the MySQL varchar column just fine.

However, when I put the same data in a file, and use

LOAD DATA LOCAL INFILE

then the 'ä' and the 'ö' get mangled, and I end up with data in the MySQL varchar column that looks like this:

sähkötupakalle

Any ideas for how I can get these characters to load correctly using "LOAD DATA LOCAL INFILE" ?? FYI, my table has CHARSET=utf8.

解决方案

Apparently the file you are loading is correctly encoded with utf8? But you did not include the CHARACTER SET utf8 clause?

Symptom of "Mojibake": When SELECTing the text, each non-english character is replaced by 2-3 characters that you could call jibberish or garbage.

How you got in the mess:

• The client's bytes to be INSERTed into the table were encoded as utf8 (good), and
• The charset for the connection was latin1 (eg, via SET NAMES latin1), and
• The table column was declared CHARACTER SET latin1

How to fix the text and the table:

Do the 2-step ALTER:

ALTER TABLE Tbl MODIFY COLUMN col VARBINARY(...) ...;
ALTER TABLE Tbl MODIFY COLUMN col VARCHAR(...) ... CHARACTER SET utf8 ...;

where the lengths are big enough and the other "..." have whatever else (NOT NULL, etc) was already on the column.

That converts the column definition while leaving the bits alone.

How to fix the code (in general):

• Change the client's declaration of charset to utf8 - via SET NAMES utf8 or equivalent.

这篇关于MYSQL：西欧字符使用LOAD DATA LOCAL INFILE获取Mangled的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！