英文:
Golang MySQL 1366 incorrect string value error
问题
我正在将字符串插入数据库，但是遇到了MySQL 1366错误，提示无效的字符串字节序列。
我的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)
我的数据库连接池配置如下：
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;")
我还缺少什么？
英文:
I am inserting strings into my database but getting the MySQL 1366 error for invalid string byte sequences.
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> 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?
答案1
得分: 7
这些不是有效的UTF-8字符串；它们是二进制数据（第一个是PNG文件！）。你需要将它们存储在真正的二进制列中，因为MySQL执行UTF-8特定的操作，如大小写折叠和语言排序（Go不会强制对字符串进行UTF-8编码，所以Go不会报错。Go只使用UTF-8来编码字符串字面量，但\x转义序列会覆盖此行为。当然，range、[]rune转换和各种包都假设字符串是UTF-8编码的。）
你可以使用utf8.ValidString()来检查一个字符串是否是有效的序列。
英文:
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.)
You can check if a string is a valid sequence with utf8.ValidString().
答案2
得分: 2
请使用BLOB（可能是MEDIUMBLOB）数据类型来存储包含图像的列。使用TEXT会导致对编码进行检查。PNG图像不包含正确编码的utf8字符，因此会出现错误。
你对utf8mb4的其他用法可能是正确的。
英文:
Use a BLOB (maybe MEDIUMBLOB) datatype for the column containing images. Using TEXT leads to checking the encoding. A PNG does not contain correctly encoded utf8 characters, hence the errors.
The rest of your usage of utf8mb4 is probably fine.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论