mysql | Incorrect string value: ‘\xE7) \xE5\xA4\xB1...‘

原创已于 2022-12-09 18:44:18 修改 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #golang

于 2022-12-08 19:38:00 首次发布

探讨MySQL中UTF-8编码的细节，包括BMP与辅助字符的处理、surrogate编码的影响，以及如何在Go语言中处理这些情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

版权

本文为原创, 遵循 CC 4.0 BY-SA 版权协议, 转载需注明出处: https://blog.youkuaiyun.com/big_cheng/article/details/128237943.

mysql

最近mysql 插入数据时遇到错误: “Incorrect string value: '\xE7) \xE5\xA4\xB1…”. 经搜索一般原因是字符集不匹配, 如向latin1 列插入utf8 数据.

进一步分析发现: 插入数据无法转换为目标字符集/编码格式时, 均可能发生该错误(即使字符集相同).

10.6.3-MariaDB. 字符集utf8mb4.

SELECT HEX('A'), HEX('世'), HEX('界'), HEX('𠀀');
---- 结果:
            41    E4B896    E7958C    F0A08080

注意: 如果结果与上不同, 一般是查询工具字符编码设置的问题.

见[参考1], 一般汉字utf8 编码为3个字节. 例如"世" 3个字节依次是: 0xe4 0xb8 0x96. 超出BMP([参考3]) 的一个辅助字符(supplementary character) utf8mb4 编码为4个字节. 例如"𠀀" (U+20000) 4个字节依次是: 0xf0 0xa0 0x80 0x80. mysql utf8mb4 支持辅助字符而utf8mb3(别名即utf8) 只支持BMP 字符([参考1]).

测试表

create table t (
  id   bigint      auto_increment primary key,
  name varchar(24)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入BMP 汉字

mysql 目前不支持直接书写unicode escape (https://bugs.mysql.com/bug.php?id=10199), 可以改用编码值构造对应字符.

例如"世" 的utf8 编码3字节: 0xE4B896 = 14989462:

SELECT CHAR(14989462 USING UTF8);
----  结果:
            世

汉字 => code point

单个汉字转换为UTF32 编码后, 编码的4个字节内容就是汉字的Unicode code point 值([参考2]).

SELECT CONVERT('世' USING UTF32), HEX(CONVERT('世' USING UTF32));
---- 结果:
            世    00004E16

“世” 的code point = 0x4E16 (U+4E16) = 19990.

code point => 汉字

SELECT CHAR(19990  USING UTF32);
---- 结果:
            世

UTF-8 编码

按[参考4] “Table 3-6. UTF-8 Bit Distribution”, 对"世" 字编码如下:

世   0x4e16=19990  0100 1110 0001 0110
zzzzyyyy yyxxxxxx  =>  1110zzzz 10yyyyyy 10xxxxxx
01001110 00010110  =>  11100100 10111000 10010110
                       228      184      150
                       e4       b8       96

对"𠀀" 字编码如下:

𠀀   0x20000=131072  0000 0010 0000 0000 0000 0000
000uuuuu zzzzyyyy yyxxxxxx  =>  11110uuu 10uuzzzz 10yyyyyy 10xxxxxx
00000010 00000000 00000000  =>  11110000 10100000 10000000 10000000
                                240      160      128      128
                                f0       a0       80       80

surrogate

SELECT HEX('𠀀'), HEX(CONVERT('𠀀' USING UTF32));
---- 结果:
            F0A08080    00020000

辅助字符"𠀀" (U+20000) utf8mb4 编码为4个字节: 0xf0 0xa0 0x80 0x80.
见[参考3], 该字符的leading surrogate = 0xD840 = 55360, trailing surrogate = 0xDC00 = 56320.

单个surrogate code 的utf8 编码

注: [参考4] 3.9 “Unicode Encoding Forms” (D84 “Ill-formed”, D89, D92), 单个的surrogate code (0xD800~0xDFFF) 不应该被编码. 包含此类编码的unicode string 是ill-formed (不规范) 的.

虽然不规范, 但(实践上) 可以进行编码. 按[参考4] “Table 3-6. UTF-8 Bit Distribution”, 对"𠀀" 字的2个surrogate codes UTF8 编码如下:

0xD840=55360  1101 1000 0100 0000
zzzzyyyy yyxxxxxx  =>  1110zzzz 10yyyyyy 10xxxxxx
11011000 01000000  =>  11101101 10100001 10000000
                       237      161      128
                       ed       a1       80
0xDC00=56320  1101 1100 0000 0000
zzzzyyyy yyxxxxxx  =>  1110zzzz 10yyyyyy 10xxxxxx
11011100 00000000  =>  11101101 10110000 10000000
                       237      176      128
                       ed       b0       80

0xD840 => 0xeda180 = 15573376.
0xDC00 => 0xedb080 = 15577216.

插入残缺的汉字

“世” utf8编码=> 0xe4b896. 如果残缺末字节: 0xe4b8 = 58552.

INSERT INTO t (NAME)  SELECT CHAR(58552  USING UTF8)
-- 报错: Invalid utf8mb3 character string: 'E4B8'

插入单个surrogate code

以"𠀀" 字trailing surrogate 为例: 0xDC00 = 56320.

INSERT INTO t (NAME)  SELECT CHAR(56320  USING UTF32); -- 成功

SELECT id, NAME,
       LENGTH(NAME), CHAR_LENGTH(NAME),
       HEX(NAME), HEX(CONVERT(name USING UTF32))
FROM t;
---- 结果:
            1    <乱码>
            3    1
            EDB080    0000DC00

编码结果0xedb080 与前面手工推算的一致.

可见, mysql 不允许插入残缺的汉字(即本文开头所说的编码错误), 但允许插入单个surrogate code.

golang

[参考5] “Conversions to and from a string type” 里说: “Converting a slice of bytes to a string type yields a string whose successive bytes are the elements of the slice”. 表明[]byte => string 时字节内容不变(即不检查内容的编码, 允许ill-formed). 同样又说: “Converting a value of a string type to a slice of bytes type yields a slice whose successive elements are the bytes of the string”. 可以推测[]byte 与string 互转只是改变类型, 实际内容不动(即没有runtime cost).

验证:

func t_str_conv() {
	var ba []byte = []byte{0x41}      // "A"
	ba = append(ba, 0xe4, 0xb8)       // "世" 残缺(0XE4 0XB8 0X96)
	ba = append(ba, 0xed, 0xb0, 0x80) // 0xDC00, 单个surrogate code
	ba = append(ba, 0xe7, 0x95, 0x8c) // "界"

	s := string(ba)
	fmt.Printf("string %q len=%d\n", s, len(s))

	ba = []byte(s)
	fmt.Printf("[]byte [% #x] len=%d\n", ba, len(ba))
}

结果:

string "A\xe4\xb8\xed\xb0\x80界" len=9
[]byte [0x41 0xe4 0xb8 0xed 0xb0 0x80 0xe7 0x95 0x8c] len=9

即golang string 里可以是任意的字节内容, 不一定是规范编码的utf8.

插入测试

package main

import (
	"database/sql"

	_ "github.com/go-sql-driver/mysql"
)

func testInsertSurrogateCode(db *sql.DB) {
	_, err := db.Exec("insert into t (name) values (?)", []byte{0xed, 0xb0, 0x80})
	if err != nil {
		panic(err)
	}
}

func testInsertBrokenHan(db *sql.DB) {
	_, err := db.Exec("insert into t (name) values (?)", []byte{0xe4, 0xb8})
	if err != nil {
		panic(err)
	}
}

func main() {
	dsn := "root:1@tcp(127.0.0.1:3306)/testdb?collation=utf8mb4_general_ci"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	testInsertSurrogateCode(db)
	testInsertBrokenHan(db)
}

注: 链接配置"collation" 正确指定了编码.

运行结果testInsertBrokenHan 报错:

panic: Error 1366: Incorrect string value: '\xE4\xB8' for column `testdb`.`t`.`name` at row 1

查询数据库, testInsertSurrogateCode 已成功插入, 内容同之前sql 手工插入的.

补充

跨语言可能产生编码问题. 例如JavaScript string 是UTF-16 编码([参考6], 8.4 “The String Type”):

var s = "A\uD840\uDC00\B"; // 'A𠀀B'
s.substring(2); // '\uDC00B' - 截断了辅助字符

又例如golang string 是字节下标, 以golang 实现的JavaScript 解释器的String.substring 方法也可能采用字节下标, 从而可以将3字节的汉字utf8 编码从中截断.

参考

[1] https://dev.mysql.com/doc/refman/5.6/en/charset-unicode.html
Unicode Support

[2] https://dev.mysql.com/doc/refman/5.6/en/cast-functions.html
12.11 Cast Functions and Operators

[3] https://blog.youkuaiyun.com/big_cheng/article/details/123441548
hello, unicode surrogate

[4] https://www.unicode.org/versions/Unicode14.0.0/ch03.pdf
Conformance

[5] https://go.dev/ref/spec#Conversions
Conversions

[6] https://262.ecma-international.org/5.1/
ECMAScript 5.1