从 sqlite 表中保存图像
我有一个 sqlite 表，其中包含 500 条记录，每条记录都将图像存储为 blob 数据。这些图像都是大尺寸的，我需要为每个图像制作缩略图大小的版本，然后将它们保存在单独的字段中。
有谁知道完成这项任务的最佳方法是什么？
另一个问题 - 是否建议使用一个单独的表来存储图像？
提前致谢
I have an sqlite table w/ 500 records each with images stored as blob data. The images are all large size and I need to make thumbnail sized versions of each image and then save them in a separate field.
Does anyone know what would be the optimum way of approaching this task?
One other question - is it recommended to have a separate table to store the images?
thanks in advance
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
最简单的方法是首先执行查询以获取要处理的所有行键，但没有 BLOB 数据。然后迭代该列表（不是在 SQL 中）并从特定行获取 BLOB 数据，通过任何方式生成缩略图，然后将其写回该行的缩略图列。这可能需要一些时间，但至少您不必担心达到内存限制或其他此类令人沮丧的废话。当您可以确定没有其他东西在使用数据库时，最好这样做，这样您就可以不必担心事务，并且一旦完成一次，请更新您的行插入/更新代码，以便保留缩略图在处理过程中更新，而不是作为单独的批处理步骤。
至于图像是否应该在数据库中，这是一个很好的选择。缩略图很可能应该是这样，但是图像数据本身可能相当大（尤其是行数较多），因此也许应该作为单独的文件存在，只有数据库中的文件名。您需要平衡正常情况下更快地处理数据的能力（通过不将所有图像数据存储在其中）与保持所有数据一致的能力（如果图像数据中出现问题，很容易陷入问题）。我无法真正为您回答这个问题，因为它需要对应用程序有更深入的了解 - 这是您的工作 - 但我希望这能告诉您在做出决定时应该考虑什么。
The easiest way is to first do a query to get all of the row keys to process, but no BLOB data. Then iterate over that list (not in SQL) and fetch the BLOB data from the particular row, generate the thumbnail by whatever means, and then write it back to the thumbnail column for the row. It may take some time, but at least you won't have to worry about hitting memory limits or other such frustrating nonsense. It's a good idea to do this when you can be sure that nothing else is using the DB so you can avoid having to worry about transactions, and once you've done it once, update your row insert/update code so that thumbnails are kept up to date during processing rather than as a separate batch step.
As for whether the images should be in the DB, that's a good one. The thumbnails quite possibly should be, but the image data itself could be fairly large (especially with more rows) so perhaps should live as separate files with only the filenames in the DB. You need to balance the ability to normally process the data faster (by not having all that image data in there) against the ability to keep all the data consistent (too easy to get into problems if something is poking around in the image data). I can't really answer that for you as it requires deeper knowledge of the application – it's your job – but I hope this tells you what you should be thinking about when making the decision.
正如 Jauzsika 所说，不要将图像存储在数据库中，但是，如果您有包含图像的数据库，您可以循环遍历所有表并检查哪一列是 BLOB，然后循环遍历所有行 - 从单元格获取数据并且您有图像（好的，数据- 但当你获得数据时，创建图像很容易）;-)
我知道，我的英语语法非常出色。
检查：http://www.sqlite.org/faq.html#q7
As Jauzsika said don't store image on database, howeever if you have database with images you could loop over all tables and check which column is BLOB, then just loop over all rows - get data from cell and you have image (ok, data - but it easy to create image when you got data) ;-)
i know, my english grammer is brilliant.
check: http://www.sqlite.org/faq.html#q7