MySQL Mysql2::Error: 不正确的字符串值：'\xE2\x80\xA8\x09

Mysql2::Error: Incorrect string value: '\xE2\x80\xA8\x09

提问by Rober

I have a rails application. Sometimes, when a user writes in a text field and a query is executed to update this field in the MySQL database, I get this error log:

我有一个 Rails 应用程序。有时，当用户在文本字段中写入并执行查询以更新 MySQL 数据库中的此字段时，我会收到以下错误日志：

UPDATE boats SET description = 'Vive la experiencia única de navegar abordo de un clásico de madera de lujo como Mako. ? Te emocionará.', updated_at = '2015-03-10 20:10:32' WHERE boats.id = 1

更新船 SET 描述 = 'Vive la experiencia única de navegar abordo de un clásico de madera de lujo como Mako。? Te emocionará.', updated_at = '2015-03-10 20:10:32' WHEREboats.id = 1

    E, [2015-03-10T20:10:32.223430 #20343] ERROR -- : Mysql2::Error: Incorrect string value: '\xE2\x80\xA8\x09Te...' for column 'description' at row 1: UPDATE boats SET description = 'Vive la experiencia única de navegar abordo de un clásico de madera de lujo como Mako. ?        Te emocionará.', updated_at = '2015-03-10 20:10:32' WHERE boats.id = 1

NOTE: Sorry, I′m not able to put the code above as code. There must be a special character.

注意：对不起，我不能把上面的代码作为代码。必须有一个特殊的字符。

I would like the user could add any character without errors.

我希望用户可以添加任何字符而不会出错。

I have a development and production environment. The error is only happening in production.

我有一个开发和生产环境。错误只发生在生产中。

I saw this post that looks the same problem as mine: Mysql2::Error: Incorrect string value

我看到这篇文章和我的问题一样：Mysql2::Error: Incorrect string value

I run this query show variables like 'char%';to check the database character config and: Development:

我运行此查询show variables like 'char%';以检查数据库字符配置和： 开发：

'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'utf8'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8'
'character_set_server', 'utf8'
'character_set_system', 'utf8'
'character_sets_dir', '/usr/local/Cellar/mysql/5.6.19/share/mysql/charsets/'

Production:

生产：

'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'latin1'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8'
'character_set_server', 'latin1'
'character_set_system', 'utf8'
'character_sets_dir', '/usr/share/mysql/charsets/'

So, I executed ALTER DATABASE yanpyprod CHARACTER SET utf8 COLLATE utf8_general_ci;to update my database character set to utf8.

因此，我执行ALTER DATABASE yanpyprod CHARACTER SET utf8 COLLATE utf8_general_ci;将我的数据库字符集更新为 utf8。

However, after the charecter set changed to utf8, I still get the same error.

但是，将字符集更改为 utf8 后，仍然出现相同的错误。

回答by Rober

It works if you run ALTER TABLE your_database_name.your_table CONVERT TO CHARACTER SET utf8instead of the query to updated character set in the database above.

如果您运行ALTER TABLE your_database_name.your_table CONVERT TO CHARACTER SET utf8而不是查询上面数据库中更新的字符集，它会起作用。

The solution is the the attached post, at the very end.

解决方案是最后附上的帖子。

回答by Shantha Kumara

There are two ways to overcome this

有两种方法可以克服这个问题

1. Change the default character set of the table
2. Change the default character set of the specific field

1. 更改默认字符集的表
2. 更改特定字段的默认字符集

Options 1
As in accepted answer:

选项 1
如接受的答案：

ALTER TABLE your_database_name.your_table CONVERT TO CHARACTER SET utf8;

Option 2
If you need to keep the table's default character set, then you can modify the specific field or set of fields which are having the issue with.

选项 2
如果您需要保留表的默认字符集，那么您可以修改有问题的特定字段或字段集。

I have got the same issue with two fields in a table, and those fields are storing content from rich-text fields values. Those fields are used to enter HRMLas well as some contents which is causing the error.

我在表中的两个字段中遇到了同样的问题，这些字段存储来自富文本字段值的内容。这些字段用于输入HRML以及导致错误的一些内容。

So if the issue is with only in a field or set of fields you can set the character set of that specific field, following type of ALTER query can be used set the character set of a field.

因此，如果问题仅出现在一个字段或一组字段中，您可以设置该特定字段的字符集，可以使用以下类型的 ALTER 查询来设置字段的字符集。

ALTER TABLE your_db_name.table_name MODIFY COLUMN column_name text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

NOTE: Update the relevant names and values according to your requirements fitting to the environment. The only thing needs to highlight here is CHARACTER SET utf8

注意：根据适合环境的要求更新相关名称和值。这里唯一需要强调的是CHARACTER SET utf8

回答by Morgan Christiansson

Adding encoding: latin1option to ActiveRecord solved it for me.

向encoding: latin1ActiveRecord添加选项为我解决了这个问题。

Also I found this workaround that forces mysql to treat latin1 as utf-8: https://github.com/rails/rails/issues/9834#issuecomment-15210861

我还发现了这种强制 mysql 将 latin1 视为 utf-8 的解决方法：https: //github.com/rails/rails/issues/9834#issuecomment-15210861