Python将二进制数据插入和检索到MySQL

11 投票

2 回答

27652 浏览

提问于 2025-04-16 13:18

我正在使用MySQLdb这个包来和MySQL数据库进行交互。但是我在进行数据类型转换时遇到了一些麻烦。

我把一个16字节的二进制UUID作为表的主键，同时还有一个mediumblob字段用来存储经过zlib压缩的JSON信息。

我使用的数据库结构如下：

CREATE TABLE repositories (
    added_id int auto_increment not null,
    id binary(16) not null,
    data mediumblob not null,
    create_date int not null,
    update_date int not null,
    PRIMARY KEY (added_id),
    UNIQUE(id)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ENGINE=InnoDB;

然后我用以下代码在表中创建了一行新的数据：

data = zlib.compress(json.dumps({'hello':'how are you :D'})
row_id = uuid.uuid(4).hex
added_id = cursor.execute('
    INSERT INTO repositories (id, data, create_date, update_date) 
    VALUES (%s, %s, %s, %s)',
    binascii.a2b_hex(row_id), 
    data, 
    time.time(), 
    time.time()
)

接着，我用类似的查询来获取数据：

query = cursor.execute('SELECT added_id, id, data, create_date, update_date ' \
    'FROM repositories WHERE id = %s',
    binascii.a2b_hex(row_id)
)

但是查询的结果却是空的。

如果有人能帮忙就太好了。另外，顺便问一下，存储Unix时间戳是用整数好还是用TIMESTAMP好呢？

注意：我并不是在插入数据时遇到问题，只是在从数据库中检索数据时遇到麻烦。当我通过mysqlclient检查时，确实能看到这一行数据。

非常感谢！

mysql uuid data retrieval binary data mediumblob zlib compression json storage timestamp vs integer

2 个回答

为了补充已有的回答，在处理查询中的二进制字符串时，还会出现以下警告问题：

Warning: (1300, "Invalid utf8 character string: 'ABCDEF'")

这个问题可以通过以下方式重现：

cursor.execute('''
    CREATE TABLE `table`(
        bin_field` BINARY(16) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
''')

bin_value = uuid.uuid4().bytes
cursor.execute('INSERT INTO `table`(bin_field) VALUES(%s)', (bin_value,))

每当MySQL发现查询中的字符串字面量与当前的 character_set_connection 不匹配时，就会发出警告。解决这个问题的方法有几种：

明确设置 _binary 字符集字面量

 INSERT INTO `table`(bin_field) VALUES(_binary %s)

手动构建查询，使用十六进制字面量

 INSERT INTO `table`(bin_field) VALUES(x'abcdef')

如果你只处理二进制字符串，可以更改连接字符集

更多详细信息请查看 MySQL Bug 79317。

更新

正如 @charlax 指出的那样，可以将 binary_prefix 标志传递给连接的初始化器，这样在插入参数时会自动添加 _binary 前缀。这个功能在最近版本的 mysql-client 和 pymysql 中都得到了支持。

回答于 2025-04-16 由 Python大师

分享举报

有一个小建议：你可以使用 uuid.uuid4().bytes 来获取原始的字节数据。至于时间戳，如果你想在SQL中处理时间或日期，使用真正的TIMESTAMP类型通常会更简单。

我创建了一个测试表来尝试重现你遇到的问题：

CREATE TABLE xyz (
    added_id INT AUTO_INCREMENT NOT NULL,
    id BINARY(16) NOT NULL,
    PRIMARY KEY (added_id),
    UNIQUE (id)
) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ENGINE=InnoDB;

我的脚本能够顺利地使用二进制字段作为关键字插入和查询数据。也许你在获取或遍历游标返回的结果时出了问题？

import binascii
import MySQLdb
import uuid

conn = MySQLdb.connect(host='localhost')

key = uuid.uuid4()
print 'inserting', repr(key.bytes)
r = conn.cursor()
r.execute('INSERT INTO xyz (id) VALUES (%s)', key.bytes)
conn.commit()

print 'selecting', repr(key.bytes)
r.execute('SELECT added_id, id FROM xyz WHERE id = %s', key.bytes)
for row in r.fetchall():
    print row[0], binascii.b2a_hex(row[1])

输出：

% python qu.py    
inserting '\x96\xc5\xa4\xc3Z+L\xf0\x86\x1e\x05\xebt\xf7\\\xd5'
selecting '\x96\xc5\xa4\xc3Z+L\xf0\x86\x1e\x05\xebt\xf7\\\xd5'
1 96c5a4c35a2b4cf0861e05eb74f75cd5
% python qu.py
inserting '\xac\xc9,jn\xb2O@\xbb\xa27h\xcd<B\xda'
selecting '\xac\xc9,jn\xb2O@\xbb\xa27h\xcd<B\xda'
2 acc92c6a6eb24f40bba23768cd3c42da

回答于 2025-04-16 由 Python大师

分享举报

Python将二进制数据插入和检索到MySQL

2 个回答

更新

撰写回答