38

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?

Brian Webster
  • 30,033
  • 48
  • 152
  • 225
meetpd
  • 9,150
  • 21
  • 71
  • 119

4 Answers4

31

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.

Lukas Liesis
  • 24,652
  • 10
  • 111
  • 109
28

The BLOB datatype is best for storing files.

Brian Webster
  • 30,033
  • 48
  • 152
  • 225
21

The other answers will give you a good idea how to accomplish what you have asked for....

However

There are not many cases where this is a good idea. It is usually better to store only the filename in the database and the file on the file system.

That way your database is much smaller, can be transported around easier and more importantly is quicker to backup / restore.

David Steele
  • 3,433
  • 21
  • 23
  • 2
    I agree with this completely. File Systems are built for storing and serving up files -- they are remarkably good at this. However, the Blob is always there in case it makes your job easier. I can see some hosting setups where jamming files into a DB will save time, effort, and maybe even help with speed. – Brian Webster May 11 '11 at 04:44
  • 1
    @hamlin11 you are correct if it is only a few files. If it is for some kind of document storage area then doing this would be a big no no. – David Steele May 11 '11 at 04:51
  • But you have to backup the images at the same time anyway if you wanna have integrety which kind of counter your only good points. There's no real advantage to use one or the other. – Leccho Oct 26 '20 at 13:21
13

You need to use BLOB, there's TINY, MEDIUM, LONG, and just BLOB, as with other types, choose one according to your size needs.

TINYBLOB 255
BLOB 65535
MEDIUMBLOB 16777215
LONGBLOB 4294967295
(in bytes)

The insert statement would be fairly normal. You need to read the file using fread and then addslashes to it.

Daniel Bang
  • 715
  • 6
  • 21
  • Thanks for your reply. So when I create the insert statment, I just enter "abc.pdf" in the values part of the insert? Or do I need to do something else? – meetpd May 11 '11 at 04:06
  • Oh no, you insert the content, that's what you've read with fread. – Daniel Bang May 11 '11 at 04:07
  • Thanks again for quick reply. So, can you please show me a sample insert statement, how it will look? – meetpd May 11 '11 at 04:09
  • 2
    The link that hamlin11 posted (PHP sample) shows an example of the file reading and the insert statement. – Daniel Bang May 11 '11 at 04:12
  • 3
    `addslashes` ?? I bet that in all important languages with a mysql driver there is support for prepared statements (which is most likely safer)... – Andre Holzner Jan 18 '15 at 10:53