MySQl databases are the most widely used databases. While storing data, sometimes it is needed to store binary files(like documents/images/videos) in a database, instead of storing them as files on the server. MySQL allows us to store such data in the database. For this purpose, MySQl has a special data type called as Blob (Binary Large OBject).
Blob can store any type of binary file. A BLOB column has a maximum length of 4,294,967,295 or 4GB (232 – 1) bytes. The effective maximum length of LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGBLOB value is stored using a four-byte length prefix that indicates the number of bytes in the value.
Now, when we are storing files in a database, it would also result in a considerable amount of increase the size of database itself. Since database is nothing but a file in itself, there are limitations on the maximum size of Database, depending on the Filesystem being used by the database.The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.
| Operating System | File-size Limit |
|---|---|
| Linux 2.2-Intel 32-bit | 2GB (LFS: 4GB) |
| Linux 2.4+ (ext3 file system) | 4TB |
| Linux 2.6+ (ext3 file system) | 16TB |
| Solaris 9/10 | 16TB |
| MacOS X w/ HFS+ | 2TB |
| NetWare w/NSS file system | 8TB |
| Win32 w/ FAT/FAT32 | 2GB/4GB |
| Win32 w/ NTFS | 2TB (possibly larger) |
Note: Even though MySQL is capable of storing this data, it is generally discouraged because it decreases the performance of MySQL drastically. The preferred Solution is to save the files on the server and store their path in the table.
In the next post, i will explain(with sample code) how to store an image/video in the database.
Here is the Link:
No comments:
Post a Comment