Storing images in mysql
Mar 21, 2008
by
0
Some would say that this is a bad idea, I say it has it's benefits. My preference is to store images in the file system storing the image name/path in the database and allowing apache to serve the image straight from the file system, but the idea of storing images in the database is intriguing. If you would like to venture into the realm of storing images in your sql database, I have a few tips for you.
- keep images in their own table normalized away from the other data
this will keep the size of your main table down, if you decide to backup the table later and want to view the raw data in the sql file, it will be good to not have a bunch of binary jibberish in the way. - do not use "select * from table" for searches in your image table
this will decrease your overhead for your select statements when you are trying to retrieve the image id from the table, after you retrieve the image id you can specifically retrieve the field with your image in it - consider storing the database in a raw partition
this could relieve some file system overhead when you have a very large database
How do I store an image in a database? well it starts with a sql table
[sql]
CREATE TABLE `image_table` (
`id` int(4) unsigned NOT NULL AUTO_INCREMENT
,`image_name` varchar(100) NOT NULL
,`image` blob NOT NULL
,PRIMARY KEY(`id`)
,INDEX (`image_name`)
);
[/sql]
If you anticipate storing a very large image, you may want to use a mediumblob or even a longblob but remember, it is a good practice to use the smallest data field for your data requirements.
Next you need some code to store and retrieve the image data. Due to the wide variety of coding languages and practices I won't have an example for you today, but I will point you in the right direction. It essentially consists of reading a file in binary mode, taking that data and inserting it into the table. When you need to display the image, retrieve the binary data and then output it to the browser with the appropriate content mime type. You can even put an image tag referencing your code.
There are some benefits in storing an image in the database.
- referential data integrity ( direct relationship in database from data to image )
- easy backup
- keep everything in one place
- Categorization made simple
- easily store extra meta data for the image
have fun, happy coding
Leave a comment
To leave a comment, please log in / sign up
RSS Feed
Facebook
Twitter
LinkedIn
YouTube