Blob和存储要求 [英] Blob and Storage Requirement
问题描述
我需要将用户上传的文件存储到数据库（文件系统不是一个选项）。
I have a requirement to store user uploaded files to the database (filesystem is not an option).
上传的文件是不同类型EXCEL等）。
The files that are uploaded are different types (e.g. PDF, EXCEL, etc).
我有一个问题决定是否使用 MEDIUMBLOB 作为类型将这些文件存储为二进制数据。
I have a problem on deciding whether or not to use MEDIUMBLOB as type to store these files as binary data.
由于这些文件的大小随着巨大的差异而变化的事实导致了混乱。像一些文件是几百千字节（例如114 KB），但其他一些是高达1.5兆字节。
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.
所以我真的需要使用 MEDIUMBLOB 作为列类型。但我没有什么困惑，因为实际占用的内存取决于上传文件本身的大小或文件大小本身被忽略，内存只是基于数据类型分配 。这是非常重要的，因为大多数每天上传的文件（很多文件）的大小和磁盘中可用的内存（空间）非常有限。
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.
当我阅读MySql文档的以下部分：
As I read the following section of MySql doc:
L表示给定字符串值的实际长度（以字节为单位）。
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
我认为使用的内存取决于上传的实际文件的大小。如果我的列类型为 MEDIUMBLOB ，如果我上传一个大小为114 KB的文件，那么只有 114 KBytes + 3 Bytes ，而不是（2 ^ 24）字节+3字节。
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.
我在想什么？或者，我将通过在 MEDIUMBLOB 字段中存储大量文件（大小为100到300 KB）来浪费大量磁盘内存。
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.
推荐答案
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.
这篇关于Blob和存储要求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
