Python插入二进制数据到MySQL报1366 Incorrect string value错误如何解决

你向Current_Password字段插入的是二进制字节数据，而该字段在表中设置的是字符串类型（如varchar、text等），MySQL会默认将传入的字节数据按照表/库的字符集（通常为utf8、utf8mb4）解码为字符串再存储，但你传入的二进制串中包含大量不符合目标字符集编码规则的字节，因此触发1366编码错误。同时mysql.connector驱动在接收bytes类型参数传入字符串字段时，会默认执行转码操作，转码失败就会抛出该报错。

可根据你的业务需求二选一：

方案1：修改字段类型为二进制存储类型 #

如果Current_Password本身就是用于存储加密哈希、加密串等二进制数据，直接将字段类型修改为二进制类即可，无需修改代码：

• 存储长度固定可选择BINARY类型
• 存储长度不固定可选择VARBINARY类型，数据量更大可选TINYBLOB/BLOB系列类型
  改表SQL示例：

ALTER TABLE mydb.mycol MODIFY COLUMN Current_Password VARBINARY(256) NOT NULL;

修改完成后直接运行现有代码即可插入成功。

方案2：二进制转可安全存储的字符串后插入 #

如果不想修改表结构，可将二进制数据编码为base64字符串或十六进制字符串后再插入，使用时解码还原即可，示例代码如下：

import base64
import mysql.connector

conn = mysql.connector.connect(user='***', password='***', host='*.*.*.*',database='**',auth_plugin='mysql_native_password')
bin_value = b'a\x92\x1b\x86\xbd\x8a\x0f\xce \x1c+\xae1\xcd\x8d\x92'
# 二进制转base64编码字符串
encoded_pwd = base64.b64encode(bin_value).decode('ascii')
cursor = conn.cursor()
sql = "INSERT INTO mydb.mycol(ID, Username,Email,Age,Current_Password,Old_Password_1,Old_Password_2,Old_Password_3,Old_Password_4,Old_Password_5,Old_Password_6,Old_Password_7,Old_Password_8,Old_Password_9,Old_Password_10) VALUES (%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s, %s,%s)"
val = (2333, "nati","test@gmail.cim",55,encoded_pwd ,"1","2","3","4","5","6","7","8","9","10")
cursor.execute(sql,val)
print(cursor.rowcount, "record inserted.")
conn.commit()
# 注意原代码此处漏写括号，需加上才会执行关闭连接操作
conn.close()

读取数据时调用base64.b64decode(查询得到的encoded_pwd字符串)即可还原为原始二进制数据。

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