Golang MySQL 1366不正确的字符串值错误

I am inserting strings into my database but getting the MySQL 1366 error for invalid string byte sequences.

我正在向数据库中插入字符串，但是获得无效字符串字节序列的MySQL 1366错误。

2016/11/04 13:33:40 Error 1366: Incorrect string value: '\x89PNG\x0D\x0A...' for column 'text' at row 1
2016/11/04 13:33:56 Error 1366: Incorrect string value: '\xB6\xEB\xE4\x0B\x92\xEE...' for column 'text' at row 1
2016/11/04 13:33:56 Error 1366: Incorrect string value: '\xFF\xD8\xFF\xE0\x00\x10...' for column 'text' at row 1
2016/11/04 13:34:35 Error 1366: Incorrect string value: '\x9C]\x91\xD1k\xC2...' for column 'text' at row 1

My MySQL config is set for utf8mb4 as shown below:

我的MySQL配置为utf8mb4，如下所示:

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

My database connection pool looks like this:

我的数据库连接池看起来是这样的:

db, err = sql.Open("mysql", config.User+":"+config.Password+"@tcp("+config.Host+")/"+config.Database)
if err != nil {
    log.Fatal(err)
}

db.Exec("SET NAMES 'utf8mb4'; SET CHARACTER SET utf8mb4;")

What am I still missing?

我还缺少什么?

2 个解决方案

#1

Those are not valid UTF-8 strings; those are binary data (the first is a PNG file!). You'll need to store them in a real binary column, since MySQL does do UTF-8-specific operations like case folding and language collation. (Go does not enforce UTF-8 encoding on strings, so Go doesn't complain. Go only uses UTF-8 to encode string literals, but the \x escape sequence overrides this. And of course, range, []rune conversion, and various packages assume strings are UTF-8.)

这些不是有效的UTF-8字符串;这些是二进制数据(第一个是PNG文件!)您需要将它们存储在一个真正的二进制列中，因为MySQL确实执行特定于utf -8的操作，比如大小写折叠和语言排序。Go不会对字符串执行UTF-8编码，所以Go不会抱怨。Go只使用UTF-8对字符串进行编码，但是\x转义序列覆盖了这一点。当然，range、[]rune转换和各种包都假设字符串是UTF-8)。

You can check if a string is a valid sequence with utf8.ValidString().

您可以使用utf8.ValidString()检查字符串是否为有效序列。

#2

Use a BLOB (maybe MEDIUMBLOB) datatype for the column containing images. Using TEXT leads to checking the encoding. A PNG does hot contain correctly encoded utf8 characters, hence the errors.

为包含图像的列使用BLOB(可能是MEDIUMBLOB)数据类型。使用文本会检查编码。PNG是否热包含正确编码的utf8字符，因此会出现错误。

The rest of your usage of utf8mb4 is probably fine.

使用utf8mb4的其余部分可能没问题。

#1