How to insert a file in MySQL database?
I want to insert a file in MYSQL database residing on a remote webserver using a webservice.
My question is: What type of table column (e.g. varchar, etc.) will store a file? And will the insert statement be somewhat different in case of a file?
Solution 1:
The BLOB datatype is best for storing files.
- See: How to store .pdf files into MySQL as BLOBs using PHP?
- The MySQL BLOB reference manual has some interesting comments
Solution 2:
File size by MySQL type:
- TINYBLOB 255 bytes = 0.000255 Mb
- BLOB 65535 bytes = 0.0655 Mb
- MEDIUMBLOB 16777215 bytes = 16.78 Mb
- LONGBLOB 4294967295 bytes = 4294.97 Mb = 4.295 Gb
Yet, in most cases, I would NOT recommend storing big blobs of bytes in database, even if it supports it, because it will increase overall database size & may cause real performance issues. You can read more on topic here. Many databases that care about consistent performance won't even let you do such thing. Like e.g. AWS DynamoDB, which is known to perform extremely well at any scale, limits single item record to 400KB. MongoDB does allow 16MB, which is also already too much, imo. MySQL allows all 4GB if you wish. But again, think twice before doing that. The case where you may be OK to store big blob of data with these column types would be - you have small traffic database and you just want to save all the stuff in one place for faster development. Like internal system in a small company.