Python中使用MySQL的BLOB字串类型

一直想在MySQL中直接存储二进制数据，尝试到今天也没有解决这个问题，但仍有几点要记录一下，比较乱：

1. 可惜设置不了0编号，就将就着在这里写下版本吧。Python 2.4.4，MySQLdb 1.2.1_p2，MySQL 5.0.32
2. 不要使用'%r'，比如'INSERT INTO mytbl VALUES (%r)'，这样做就很没意思了，读取一个二进文件内容到data，然后再使用file.write('%r' % data)写入到文件，打开看以下你就会明白。使用'%r'还不如使用base64编码。
3. MySQLdb.escape_string对二进制数据使用没有问题，escape后的数据只增大了一点。据说新的版本下这个可以省略。
4. 使用'INSERT INTO mytbl VALUES (%s)' % MySQLdb.escape_string(data)应该是对的，但会提示UnicodeDecodeError。
5. 第4点中的错误，怀疑是数据库的charset关系，我的db的character_set_name()返回"latin1"；也有可能是组织SQL命令字串时就出现的问题。
6. Python Codebook, Page 355, Chapter 8.7 Storing a BLOB in a MYSQL Database，有时间仔细看看。
7. 使用MySQL的LOAD_FILE函数是跳过以上问题的好方法，看开始时我总是得到NULL，折腾了半天发现要使用这个函数需要得到file权限，这个权限必须设置给全局（又折腾了一会），(using ON *.* syntax)。详细可以参见MySQL的GRANT语法。
   GRANT FILE ON *.* to you@localhost identified by "secret";
8. 使用LOAD_FILE得到的结果正是我所需要的，如果第4步成功，也应该是这样。
9. 更正：第5点中的错误是从cursors.py的146行产生的：query = query.encode(charset)。带二进制的MySQL命令字串是可以产生的，但对它使用execute就会有这样的问题。MySQL中倒是有binary的charset类型，但python中没有，所以不能在创建数据库时指定charset为binary。MySQL字符集相关资料可以参见这里。
10. 万般无奈之下我决定抄下Python Codebook, Chapter 8.7.2的代码来运行，竟然没遇到折磨我的encode错误，再把程序中的二进制数据换成我测试用的图片数据，还是正常。仔细看下，原来我用execute的方式不一样：
    cursor.execute(query % binary_data)
    而codebook里是这样写的：
    cursor.execute(query, binary_data)
    看看"/usr/lib/python2.4/site-packages/MySQLdb/cursors.py"中的execute，就知道区别在那里了，codebook的代码当然没有encode错误，因为不会对二进制数据操作。

终于结束摸索了，总结一下：

• 插入BLOB不需要使用特别的CHARACTER SET，和这个问题无关
• 如果要插入二进制数据，使用"execure(query, binary_data)"的方式
• 在新的版本下不要对binary_data使用escape_string函数
• 从数据库中获取到的BLOB的数据类型是array，我通常使用array.tofile来直接保存到文件，当然也可以使用其它的方式，请自己查找python::array的资料
• 如果使用LOAD_FILE，请确保你的MySQL用户有FILE的权限，另外文件要可访问并且不要太大。

Welcome to PicVew.com