为何无法同时实现数据库特殊字符存储与二进制人脸向量正常读取

问题根因 #

• 特殊字符𝕲属于4字节Unicode字符，MySQL默认的utf8编码实际是utf8mb3，仅支持最多3字节的Unicode字符，无法存储4字节字符，所以会报1366错误。
• 开启字符集设置后，连接会默认将所有返回字段按UTF-8解码为字符串，原本是二进制类型的BLOB字段被强制转码导致内容损坏，所以pickle反序列化失败。

修复步骤 #

1. 数据库/表/字段字符集统一改为utf8mb4，这是MySQL真正支持全量Unicode的编码。
2. 创建MySQL连接时显式指定binary_prefix=True参数，让驱动对BLOB类型字段直接返回二进制bytes，不做字符串转码。
3. 建库、建表时显式指定字符集，避免继承系统默认配置，插入数据后记得提交事务。

修复后完整代码 #

import cv2
import pickle
import face_recognition
import mysql.connector as mysql        

# 连接时指定binary_prefix=True，BLOB字段直接返回二进制
conn = mysql.connect(
      host = 'localhost',
      user = 'root',
      passwd = 'superpassword',
      binary_prefix=True
)

# 加载图片生成人脸向量
img = face_recognition.load_image_file('test.jpg')
face_vector = face_recognition.face_encodings(img)[0]

cur = conn.cursor(buffered=True)
# 建库时指定utf8mb4字符集
cur.execute("CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;")
cur.execute("USE test;")

# 设置连接字符集为utf8mb4
cur.execute("SET NAMES 'utf8mb4';")
cur.execute("SET CHARACTER SET utf8mb4;")

# 建表时指定字符集为utf8mb4
cur.execute("CREATE TABLE IF NOT EXISTS faces(bug_char VARCHAR(32), vectors BLOB) DEFAULT CHARSET=utf8mb4;")

data_insert = ('𝕲', pickle.dumps(face_vector))
cur.execute('INSERT INTO faces(bug_char, vectors) VALUES(%s, %s)', data_insert)
conn.commit() # 提交事务，确保数据写入数据库

cur.execute("SELECT * FROM faces;")
face_data = cur.fetchall()

for f in face_data:
    print(pickle.loads(f[1]))

注意事项 #

• 如果之前已经创建了表，需要手动修改表和字段的字符集为utf8mb4，否则还是会出现字符存储错误。
• 不要对pickle.dumps返回的二进制数据做任何编码、解码操作，直接作为参数传入SQL语句即可。

内容的提问来源于stack exchange，提问作者user3391185