如何处理数据以避免MySQL的“incorrect string value”错误？

Question

我正在尝试使用Rake任务将一些遗留数据从MS Access迁移到MySQL。我正在Windows XP上工作，使用Ruby 1.8.6。

我已经在database.yml中将Rails的编码设置为“utf8”。

此外，MySQL的默认字符集是utf8。

99％的数据都能正常导入，但偶尔会出现一些列值会给我一个类似于以下错误的错误：

Mysql::Error: Incorrect string value: '\x92 Comm...' for column 'name' 
  at row 1: 
  INSERT INTO `organizations` ( [...] ) 
  VALUES('Lawyers’ Committee', [...] )

看起来给MySQL带来麻烦的是单词“Lawyers”中“s”后面的撇号。

这是另一个例子...

Mysql::Error: Incorrect string value: '\x99 aoc' for column 'department' 
  at row 1: 
  INSERT INTO `addresses` 
[...]
  'TRInfo™ aoc'
[....]

看起来它在“TRInfo”后面的“TM”上出现了问题。

是否有任何Ruby或Rails方法可以清洁数据，以使MySQL不会出现问题？

理想情况下，最好用更易接受的字符替换它们--用单引号替换撇号，用字符串“(TM)”替换TM符号。

或者，如果我可以以某种方式配置MySQL将这些字符存储为原样而没有错误，那就太好了。

- Ethan

Answer 1

看起来您的输入数据不是utf-8编码。

我进行了一些调查，发现律师事务所中使用的样式引用在Windows-1252编码中被编码为\x92，但在utf-8中会变成无意义字符（当我将其解码并编码为utf-8时，得到的结果是\xe2\x80\x99）。

因此，您需要将输入字符串从Windows-1252转换为utf-8（或Unicode）。

- Kathy Van Stone

---

这个解决方案并没有回答原帖问题“有没有任何方法...” - Gary

Answer 2

通常情况下，这种情况发生在您将字符串插入到具有不兼容编码/排序规则的列中时。

当我有触发器继承服务器排序规则时，我遇到了这个错误，原因不明。 而且mysql的默认设置（至少在Ubuntu上）是拉丁-1和瑞典排序规则。 即使我已经将数据库和所有表设置为UTF-8，我仍然需要设置my.cnf：

/etc/mysql/my.cnf :

[mysqld]
character-set-server=utf8
default-character-set=utf8

这必须列出所有以 utf8-* 开头的触发器：

select TRIGGER_SCHEMA, TRIGGER_NAME, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION from information_schema.TRIGGERS

此外，此列表中列出的某些变量也应该具有 utf-8-* 编码（不是 latin-1 或其他编码）：

show variables like 'char%';

- Ondra Žižka

Answer 3

当我使用Java将UTF-16编码文件的内容（通常每个字符存储在一个16位块中）放入mysql表格时，遇到了同样的问题。问题在于UTF-16编码字符串包含所谓的代理对。这意味着两个连续的16位UTF-16块编码一个特殊字符，但不能单独地转换为相应的UTF-8编码。请参见wikipedia以获取更多解释。

解决方案是简单地用空格替换这些字符。这是您可能想要从字符串中删除的字符范围：U+D800–U+DFFF

- lex82

Answer 4

我曾经遇到过同样的问题，使用Php从SQL Server导入数据到MySql。 我的解决方案是在插入到MySql时使用utf8_encode()，并在从MySql检索以显示到浏览器时使用utf8_decode()。 这里是完整的代码，可以很好地工作。

//For string values
$Gro2=(is_null($row["GrpNm"]))?"NULL":"\"".mysql_escape_string(utf8_encode($row["GrpNm"]))."\"";


$sqlMy ="INSERT INTO `tbl_name` VALUES ($Gro2)";

请注意：对于新项目，请使用。

mysqli_escape_string()

链接

- IgorAlves

Answer 5

在weirdcolumn之前添加二进制可以解决问题。

在我的情况下，我有一个更新触发器在tableA上，将数据插入到其他表中。 列weirdcolumn中有一些特殊字符，更新失败并显示错误消息：“ERROR 1366 (HY000): Incorrect string value: '\xE7....'”

经过大量挖掘，我发现通过在字符串列名之前添加二进制或使用cast(weirdcolumn as binary)可以解决该问题。

希望这能帮到你。

- Richardhe2007

Answer 6

今天我也遇到了同样的问题。 经过多次尝试，我最终找到了原因并解决了它。 对于使用默认MySQL字符集和排序规则（latin1、latin1_swedish_ci）存储数据的应用程序，您需要在创建数据库或表时指定字符集和排序规则为utf8/utf8_general_ci。 例如： $sql = "CREATE TABLE " . $table_name . " ( id mediumint(9) NOT NULL AUTO_INCREMENT, bookname varchar(128) NOT NULL, author varchar(64) NOT NULL, PRIMARY KEY (id), KEY (bookname) )CHARACTER SET utf8 COLLATE utf8_general_ci;";

参考资料： 《mysql create table problem? SOLVED!!!!!!!!!!!》 http://forums.mysql.com/read.php?121,193883,193883 《10.1.5. Configuring the Character Set and Collation for Applications》 http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html

希望这可以帮助到您。

- Zhifeng Hu

Answer 7

看起来你的旧数据库是以一种字符串格式（utf8？）存储的，而你的Rails期望得到其他格式。如果你的输入是utf8格式的，你尝试过配置你的Rails以支持它吗？

- Todd Gardner

---

谢谢，一切都设置为utf8了。 - Ethan