pymysql提示Warning: (1300, “Invalid utf8mb4 character string: ‘F9876A‘“)

错误原因

使用pymysql连接MySQL数据库，新增数据的字段中有blob类型的数据，在新增的时候报Warning: (1300, "Invalid utf8mb4 character string: 'F9876A'")

解决方法

• 未修改的代码如下

def insert_vector(table_name ,vector_id,vector_str):
    connect = None
    cursor = None
    try:
        connect = pymysql.connect(hostname,user,password,db_name)
        cursor = connect.cursor()
        #新增数据的sql语句,VECTOR字段是BLOB类型的数据
        sql = "INSERT INTO {} (VECTOR_ID,VECTOR) VALUES (%s,%s)".format(table_name)
        cursor.execute(sql, (str(vector_id), vector_str))
        connect.commit()
    except Error as e:
        #if occur error,rollback event
        connect.rollback()
        return -1,e.args[1]
    finally:
        if cursor:
            cursor.close()
        if connect:
            connect.close()
    return 0,""

#接口调用代码
a = np.random.uniform(0,1,512)
print(insert_vector("test",1,a.tostring()))

• 解决方法

只需要在SQL语句的BLOB类型的字段前面添加_binary的前缀即可，修改之后的代码如下

sql = "INSERT INTO {} (VECTOR_ID,VECTOR) VALUES (%s,_binary %s)".format(table_name)

参考：https://github.com/PyMySQL/PyMySQL/issues/644

https://github.com/PyMySQL/PyMySQL/issues/549