166

Possible Duplicates:
Images in MySQL
Storing images in MySQL

I'm trying to develop a website where users upload their images as part of registration. I want it that for each image, there should be a thumb created with PHP (which is not that difficult). I want to save the thumbs (since they are very small) in the database and I use MySQL. (I don't want to save the thumbs as physical files on the drive.)
Does MySQL allow saving and retrieving image data and how do I go about it? If it doesn't support image data, is there any free database that does? I will be happy if a link can be provided.
Thanks.

Community
  • 1
  • 1
afaolek
  • 8,452
  • 13
  • 45
  • 60
  • http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/uploading-files-to-mysql-database.aspx – Jordan Brown Jun 24 '11 at 18:33
  • 2
    Duplicate of http://stackoverflow.com/questions/3014578/storing-images-in-mysql and http://stackoverflow.com/questions/1665730/images-in-mysql and http://stackoverflow.com/questions/1257488/mysql-storing-images-in-a-database – JYelton Jun 24 '11 at 18:35
  • yes you can but it is not optimal! so.... No, dont do it! – Drewdin Jun 24 '11 at 18:35
  • Google "mysql php blob example" and you find tons of help. – Alex Howansky Jun 24 '11 at 18:35
  • 1
    and http://stackoverflow.com/questions/2753193/saving-images-in-database-mysql and http://stackoverflow.com/questions/4133558/storing-images-in-a-mysql-database and http://stackoverflow.com/questions/5380699/mysql-works-with-images ... – JYelton Jun 24 '11 at 18:36
  • is there ANY good reason not to store thumbnails on the FS? oO – Karoly Horvath Jun 24 '11 at 18:36
  • I don't know a BD engine that has an image data type. Read about blob columns, they store any binary data. You can store the filename on a varchar column and the content on a blob column. You can make a PHP script that queries the DB and 'emits' the image stored. In html you will have something like ``. – aalku Jun 24 '11 at 18:37
  • @Shef I think your comment is not constructive. You gave no reasons. – aalku Jun 24 '11 at 18:39
  • @user270349: Thanks for pointing it out. Flag it! ;) I did give no reason, specifically for the OP to ask the `WHY`, because this question has been answered gazillions of times. Thus, the OP should do a little research before asking something like this. – Shef Jun 24 '11 at 18:42
  • I think those questions should be marked duplicate of this question, as there much better and clear solutions here! – Chirag Jain Jul 14 '18 at 11:58

4 Answers4

341

Yes, you can store images in the database, but it's not advisable in my opinion, and it's not general practice.

A general practice is to store images in directories on the file system and store references to the images in the database. e.g. path to the image,the image name, etc.. Or alternatively, you may even store images on a content delivery network (CDN) or numerous hosts across some great expanse of physical territory, and store references to access those resources in the database.

Images can get quite large, greater than 1MB. And so storing images in a database can potentially put unnecessary load on your database and the network between your database and your web server if they're on different hosts.

I've worked at startups, mid-size companies and large technology companies with 400K+ employees. In my 13 years of professional experience, I've never seen anyone store images in a database. I say this to support the statement it is an uncommon practice.

Steve Nguyen
  • 5,854
  • 5
  • 21
  • 39
  • 81
    haha, Don't just scare people. I don't know how it was in 2011 but now DBs are so improved and store blob columns separated from the normal data, you'll never notice a change of speed. there is even `streamline` too – azerafati Sep 17 '15 at 13:49
  • 8
    Can you provide concrete arguments agains storing images in a database or examples of cases where this lead to problems? I share your opinion but I'd like to be able to provide facts in discussions about this. – Feuermurmel Dec 17 '15 at 14:30
  • 15
    This answer is certainly way too undifferentiated and generic to get so many upvotes. There are also papers on this issue and it's certainly not generally bad practice to store images in the database. In Bill Karwins book "SQL Antipatterns" is also a chapter about this. It almost always a good idea to store images in the dabase, if you don't want the dbms memory to explode (which won't happen anyways because of special handling (stream...) you can cache the images on the file system, which is trivial (on update: delete file). – Sam Jan 17 '16 at 16:10
  • 6
    "Do not store images in database" is just another wrong generalization, like this one itself. Well, it is probably not a good idea to store all public members photos/avatars in a database but let's say you want to store only your employees photos and you have <20 employee AND you do NOT want to setup a web server or some kind of "file-sharing" AND ALSO your application supports remote management. Guess what, storing the images in database is your one-and-only best shot! – Roni Tovi Mar 16 '16 at 14:51
  • 2
    That post was 5 years ago... I went ahead and edited the post so that it's more appropriate and contemporary. I gave additional rationalizations why it might be inappropriate to store images in databases. – Steve Nguyen May 05 '16 at 22:21
  • 1
    I generally disagree with the advice of not storing images in DB. Especially if you have physically separated DB and web servers. If your website user base is large, you probably have a load balanced setup with multiple web servers. If you are storing images on the web server just to avoid "unnecessary load on your database and the network between...", how do you keep the web servers synced? I think the best advice is the opposite of this answer: if you're small enough (single server), you can get away with storing on the FS; if not, the DB is the only option for data consistency. – light May 31 '17 at 06:26
  • 9
    You're giving really bad advice. Facebook does what I'm describing and other sites that store massive amounts of images. Just do a quick Google. https://code.facebook.com/posts/685565858139515/needle-in-a-haystack-efficient-storage-of-billions-of-photos/ It's simply not stored in the database. They use network file storage systems. This isn't a new problem. It's been solved repeatedly with file systems. Just Google how image sites store their images. – Steve Nguyen Jun 01 '17 at 07:46
  • @FinalForm To add up there isn't a bad choice, there is a bad choice related to a certain situation. As someone stated above, if your DB is meant to store few informations and is a local (not used in a web server for example) one it's not necessarily bad to store these images in the DB itself. In programming (in general) there isn't something BAD just like that because the BAD things get removed overtime everything can be good if certain conditions are met. – Kil jeaden Dec 15 '19 at 02:05
  • If you ever want to scale an application, you might want to go with BLOB storage in the database, unless you prefer using a network filesystem, which could get a bit slow. So, I also think there are indeed use cases, where it is desirable to store files in the database. – cslotty Jul 14 '20 at 16:12
  • And then put `` in the HTML. – Rick James May 20 '22 at 18:53
56

You'll need to save as a blob, LONGBLOB datatype in mysql will work.

Ex:

CREATE TABLE `test`.`pic` (
    `idpic` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT
        PRIMARY KEY,
    `caption` VARCHAR(45) NOT NULL,
    `img` LONGBLOB NOT NULL
)

As others have said, it's bad practice but it can be done. Not sure if this code would scale well, though.

MartinNajemi
  • 514
  • 3
  • 18
23

You can store images in MySQL as blobs. However, this is problematic for a couple of reasons:

  • The images can be harder to manipulate: you must first retrieve them from the database before bulk operations can be performed.
  • Except in very rare cases where the entire database is stored in RAM, MySQL databases are ultimately stored on disk. This means that your DB images are converted to blobs, inserted into a database, and then stored on disk; you can save a lot of overhead by simply storing them on disk.

Instead, consider updating your table to add an image_path field. For example:

ALTER TABLE `your_table`
ADD COLUMN `image_path` varchar(1024)

Then store your images on disk, and update the table with the image path. When you need to use the images, retrieve them from disk using the path specified.

An advantageous side-effect of this approach is that the images do not necessarily be stored on disk; you could just as easily store a URL instead of an image path, and retrieve images from any internet-connected location.

George Cummins
  • 28,485
  • 8
  • 71
  • 90
  • George, if the images are not supposed to be public would you still advocate this method? – Anthony Rutledge Apr 16 '17 at 19:44
  • @AnthonyRutledge Yes. The overhead of database storage doesn't provide any extra options with regard to security. Access control is easily handled by the web server, file system permissions, placing files outside the web server path, etc. – George Cummins Apr 17 '17 at 14:34
  • Say I accept your conclusions as valid and sound. Would you still advocate this method if you were building a content management system tied to e-commerce (for downloadable digital content, like a scholarly journal)? Storing paths requires you to manually (or, programmatically) maintain the parallel association between the database and the filesystem. Storing files in the database couples file data to the database, but it does not require the work of keeping stored paths in sync with filesystem. Since there is no path to worry about, there is less exposure for downloadable content. – Anthony Rutledge Apr 17 '17 at 17:47
  • That is to say, even though one can place things like images outside of the web root, you still have to manage it. You can do it, or the database can. I will have to investigate more. – Anthony Rutledge Apr 17 '17 at 17:51
  • Usage and scale of site matter too. If I was selling on-line books, I would probably put those books in a database. Anything of value, I would probably put in a database. But, for piddly images and such, sure find a file system solution if possible. – Anthony Rutledge Apr 17 '17 at 18:03
  • 1
    I think that Bill Karwin's answer has value. https://www.quora.com/Is-it-a-bad-design-to-store-images-as-blobs-in-a-database – Anthony Rutledge Apr 18 '17 at 13:03
7

You will need to store the image in the database as a BLOB.

you will want to create a column called PHOTO in your table and set it as a mediumblob.

Then you will want to get it from the form like so:

$data = file_get_contents($_FILES['photo']['tmp_name']);

and then set the column to the value in $data.

Of course, this is bad practice and you would probably want to store the file on the system with a name that corresponds to the users account.

Stevoisiak
  • 23,794
  • 27
  • 122
  • 225
bsimic
  • 926
  • 7
  • 15
  • Can you please tell me how I can create a blog like medium.com where images are within texts and preserved their orders? The text's content type is also HTML. – Htet Phyo Naing Aug 28 '21 at 07:46