Blob 和存储要求
我需要将用户上传的文件存储到数据库（文件系统不是一个选项）。
上传的文件有不同的类型（例如PDF、EXCEL等）。
我在决定是否使用 MEDIUMBLOB 作为类型来将这些文件存储为二进制数据时遇到问题。
由于这些文件的大小差异巨大，因此产生了混乱。有些文件有几百千字节（例如 114 KB），但其他一些文件高达 1.5 兆字节。
所以我确实需要使用 MEDIUMBLOB 作为列类型。但我没有什么困惑，因为实际占用的内存取决于上传文件本身的大小或文件大小本身被忽略，内存只是根据数据类型分配 。这非常重要，因为每天上传的大多数文件（很多）的大小非常小，并且磁盘中可用的内存（空间）是有限的。
当我阅读 MySql 文档的以下部分时：
L 表示给定字符串值的实际长度（以字节为单位）。
Data type Storage Required
========================================================
TINYBLOB, TINYTEXT L + 1 bytes, where L < 2 ^ 8
BLOB, TEXT L + 2 bytes, where L < 2 ^ 16
MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 2 ^ 24
LONGBLOB, LONGTEXT L + 4 bytes, where L < 2 ^ 32
我认为使用的内存取决于上传的实际文件的大小。即，如果我的列类型为 MEDIUMBLOB 并且上传大小为 114 KB 的文件，则将仅使用 114 KB + 3 字节 磁盘内存，而不是(2 ^ 24) 字节 + 3 字节。
我想得对吗？或者我会因为在 MEDIUMBLOB 字段中存储大量文件（大小为 100 到 300 KB）而浪费大量磁盘内存。
I have a requirement to store user uploaded files to the database (filesystem is not an option).
The files that are uploaded are different types (e.g. PDF, EXCEL, etc).
I have a problem on deciding whether or not to use MEDIUMBLOB as type to store these files as binary data.
And the confusion arises due to the fact that the size of these files vary with huge difference. Like some of the files are a few hundred KiloBytes (e.g. 114 KB) but some others are upto 1.5 MegaBytes.
So I really need to use MEDIUMBLOB as the column type. But I have little confusion as the actually memory taken up depends on the size of the uploaded file itself or file size itself is ignored and memory is just allocated based on the data type. This is really important because most of the files that will uploaded everyday (lot of them) will really small in size and memory (space) available in the disk is limited.
As I read the following section of MySql doc:
10.5. Data Type Storage Requirements
L represents the actual length in bytes of a given string value.
Data type Storage Required
========================================================
TINYBLOB, TINYTEXT L + 1 bytes, where L < 2 ^ 8
BLOB, TEXT L + 2 bytes, where L < 2 ^ 16
MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 2 ^ 24
LONGBLOB, LONGTEXT L + 4 bytes, where L < 2 ^ 32
I think the memory used depends on the size of the actual file that's uploaded. i.e. If I have the column type as MEDIUMBLOB and if I upload a file that's 114 KB in size then the only 114 KBytes + 3 Bytes of disk memory will be used and not (2 ^ 24) Bytes + 3 Bytes.
Am I thinking right? Or will I be wasting a lots of disk memory by storing a lot of files (that are 100 to 300 KB in size) in a MEDIUMBLOB field.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
MySQL 将使用 SHOW TABLE STATUS 为您提供每个表的大小。因此，如果您上传一些测试文件，您应该能够估计它们在数据库中占用的实际大小。每个表确实有一些开销，因此我会使用尽可能多的文件进行测试。
MySQL will give you the size of each table using SHOW TABLE STATUS. So if you upload a few test files you should be able to estimate the actual size they'll take up in the database. Each table does have some overhead so I would use as many files as reasonable for testing.