如何将Python数组存入MySQL数据库 对应字段类型及存储方案选择

问题根因 #

你触发报错的直接原因是直接用字符串格式化拼接SQL语句：pickle.dumps生成的是bytes类型，直接拼接入SQL时会把b'\x80\x03...'这类带标识的repr字符串直接写入SQL语法，触发语法错误，同时这种写法还有SQL注入风险，属于完全不推荐的写法。

最优方案选型 #

根据使用场景可以从以下方案中选择，优先推荐JSON序列化方案：

方案1：JSON序列化（首选，适合绝大多数通用场景） #

适合存储仅包含基础类型（数字、字符串、布尔、嵌套列表/字典）的数组，可读性强、支持跨语言读取，MySQL原生支持JSON类型的内部查询。

• 字段选择：MySQL 5.7及以上版本选JSON类型，旧版本选TEXT类型即可
• 排序规则：用JSON类型无需单独指定；用TEXT类型需将排序规则改为utf8mb4_general_ci，不要使用原有的latin1系列规则，避免特殊字符编码错误
• 代码示例：

import json

fn_arr = [2,3,4,5,6,7,8,12]
# 序列化为JSON字符串
fn_json = json.dumps(fn_arr)
# 用参数化查询传参，不要拼接SQL
insert_sql = "insert into signature (signature) values (%s)"
cur.execute(insert_sql, (fn_json,))

# 读取时反序列化
cur.execute("select signature from signature where id = 你的记录ID")
res = cur.fetchone()[0]
fn_arr = json.loads(res)

方案2：Pickle序列化（仅适合存储Python特有对象） #

仅当你的数组包含Python独有的类型（比如自定义类实例、函数等），且不需要跨语言读取时使用，注意存在安全风险：读取不可信来源的pickle数据会触发任意代码执行。

• 字段选择：选BLOB/MEDIUMBLOB/LONGBLOB二进制类型，不要用TEXT类型，避免编码损坏二进制数据
• 排序规则：二进制类型无排序规则要求，无需配置
• 代码示例：

import pickle

fn_arr = [2,3,4,5,6,7,8,12]
fn_pickle = pickle.dumps(fn_arr)
insert_sql = "insert into signature (signature) values (%s)"
# 直接传bytes参数即可，pymysql会自动处理二进制写入
cur.execute(insert_sql, (fn_pickle,))

# 读取时反序列化
cur.execute("select signature from signature where id = 你的记录ID")
res = cur.fetchone()[0]
fn_arr = pickle.loads(res)

方案3：关联表存储（适合需要对数组元素做SQL查询的场景） #

如果后续需要通过SQL直接过滤、统计数组中的元素值，可以单独建关联表存储每个元素：

• 主表signature存公共信息，关联表signature_items结构为：id、signature_id（关联主表ID）、value（数组元素值）
• 优点是支持直接用SQL查询包含特定元素的记录，缺点是写入和读取都需要多表操作，复杂度更高

选型建议 #

你的场景是普通整数数组存储，优先选方案1的JSON序列化，无二进制兼容问题，调试方便，性能足够。

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