在MySQL导出/导入中丢失特殊字符 [英] Special characters get lost in MySQL export/import

问题描述

我试图将MySQL 3.23.58数据库移动到运行5.5.19的不同服务器。

旧的一个具有指定的latin1编码，据我所知，底层数据确实是老实的latin1。我尝试了很多东西，主要是：

• 从mysqldump和latin1编码标志导出终端。


• 
  
• 在vim中编辑以将MySQLTYPE = InnoDB更改为ENGINE = InnoDB。


• 从终端导入到新服务器。

浏览旧服务器（在Mac的Sequel Pro或PC上的MySQL查询浏览器），特殊字符并不总是正确显示，在十六进制的二进制）。 （在任何情况下，它与PHP Web应用程序一起工作。）

浏览新服务器，所有特殊字符似乎已被问号替换。我知道有时特殊字符可以显示为问号（或 ），如果指定了错误的编码。但是这些似乎是在二进制级别上的真正的直向编码ASCII问号。

任何想法为什么？

我知道有很多事情可能会出错编码，许多不同的东西在错误。我已经阅读了几天（在这里和其他地方），尝试设置所有正确的字符编码，尝试UTF-8，尝试转换和转换，尝试Sequel Pro的导出/导入（而不是终端）等，但是我很累。

解决方案

很好，看起来我们已经缩小了你的问题。我找到此信息

如果你的文本编辑器是vim，那么很可能< 92>是扩展ASCII字符的
十六进制代码。在这种情况下，它是
Hex（92）或Oct（222）或Dec（146），这是右单引号
mark;

从您的文件中删除所有非ASCII字符的一种方法可能是 -

perl -plne的/ [^ [：ascii：]] // g'< your_file>

否则，请使用适当的字符搜索并替换导出文件中的< 92>和< 97>。

我不是VIM用户， a href =http://aditya.sublucid.com/2008/01/18/replacing-those-pesky-smart-quotes-in-vim/ =nofollow>替换< 92>智能引用字符

对于您在文件中看到的每个值，只需执行字符串
替换，如下所示： / p>

：％s /< 93> / \'/ g

当然，你不能只在那里输入< 93>，所以要使用
。

在最近从Excel中导出的CSV文件中，已显示十六进制91-97。

I'm trying to move a MySQL 3.23.58 database to a different server running 5.5.19.

The old one has latin1 encoding specified, and as far as I can tell the underlying data is indeed honestly latin1. I have tried many things, chiefly:

• exporting from terminal with mysqldump and the latin1 encoding flag.
• editing in vim to change "TYPE=InnoDB" to "ENGINE=InnoDB" for MySQL 5 compatibility.
• importing to the new server from terminal.

Browsing the old server (in Sequel Pro for Mac, or MySQL Query Browser on PC), special characters don't always show properly, but they're there (looking at the binary in hex). (And in any case it works with the PHP web app.)

Browsing the new server, all special characters appear to have been replaced by question marks. I know that sometimes special characters can display as a question mark (or �) if the wrong encoding is specified. But these appear to be genuine straight-up encoded ASCII question marks on a binary level. The special characters (chiefly curly quotation marks and dashes) appear to have been lost, or destroyed, in the export/import.

Any idea why?

I know there are many things that can go wrong with encoding, with many different things at fault. I have been reading about this for several days (here and elsewhere) and tried setting all the right character encodings, tried UTF-8, tried casting and converting, tried Sequel Pro's export/import (as opposed to the terminal), etc. But I am stumped.

解决方案

Good, it looks like we've narrowed down your problem. I found this post

If your text editor is vim, then most likely the "<92>" is the hexadecimal code of an extended ASCII character. In this case, it is Hex(92) or Oct(222) or Dec(146) , which is "right single quotation mark"; not to confused with "single quote" which is ASCII Dec code 39.

One way to remove all non-ASCII characters from your file could be -

perl -plne 's/[^[:ascii:]]//g' <your_file>

Otherwise just search and replace "<92>" and "<97>" in your exported file with an appropriate character.

[Edit]

I'm not a VIM user but this post addresses the issue of replacing the <92> smart quote characters

For each value that you see in your file, just do a string substitution, like so:

:%s/<93>/\’/g

of course, you can’t just type that <93> in there, so to get it in there you use

CTRL-V x 93

which inserts hex 93 in place.

In recently exported CSV’s from excel, I’ve seen hex 91-97.

这篇关于在MySQL导出/导入中丢失特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！