How to store pictures in MySQL database

MySQL is among the popular database management systems. It can be used to store and retrieve data designed according to the applications. Most of the applications are required to manage the images. This tutorial provides the options to store images in a MySQL table.

Notes: Professionals can help you with image database development for your company or your project.

Store the Image Path

The simplest approach is to store images in directories on the file system and store references to the photos in the database like SQL & MySQL, for example, the path to the image, the image name, and so on. Alternatively, you may even keep images on a CDN or numerous hosts across some great expanse of substantial territory and keep their references to access those resources in that database.

In this way, we are not required to hold the entire image in the database but to store only the path to the image stored in a folder. Storing images is good if the images will always be in the same folder, i.e. if each image will have its unique path that will always be the same. However, in some cases, the images are to be transferred from one folder to another. In that case, the best solution is to create a dynamic path to the image file, so if the image has to be moved, there would be no obligation to modify the database.

We can update an existing table to add an image path field as shown below.

// Add column to store image path
ALTER TABLE `my_table`
ADD COLUMN `image_pathLocation` varchar(1024)

Another approach is to create a separate table for the images and add a reference in tables using the image table. In this way, multiple tables can store the images in a single table. You may use the below-mentioned table to store your images.

// Create the Image table
CREATE TABLE image (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(1024) NOT NULL,
  `title` VARCHAR(2048) NULL,
  `description` VARCHAR(4096) NULL,
  `extension` VARCHAR(160) NULL,
  `directory` VARCHAR(512) NULL,
  `size` FLOAT NOT NULL,
  `type` VARCHAR(50) NOT NULL,
  `url` VARCHAR(2048) NULL,
  `medium` VARCHAR(1024) NULL,
  `small` VARCHAR(1024) NULL,
  `thumb` VARCHAR(1024) NULL,
  `caption` VARCHAR(1024) NULL,
  `altText` VARCHAR(512) NULL,
  `createdAt` DATETIME NULL,
  `modifiedAt` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_image_name` (`name` ASC),
  INDEX `idx_image_type` (`type` ASC)
) ENGINE = InnoDB;

In the above-mentioned table, the name is the actual name of the image being stored, and the title is optional to get input from the users uploading the image. Also, the type column can decide whether the image is stored locally or on a CDN. If it's stored locally, then we can use the column directory to specify the path where the image is stored. In the case of CDN, we can use the column URL to get the base path of the image. Apart from type, directory, and URL, we can also use small, medium, and large to store the same image in multiple sizes.

When you need to use the pictures, retrieve them from the disk using the path specified. This approach's perk is that the pictures do not necessarily need to be stored on a disk; we can hold a URL instead of an image path and retrieve images from any internet-accessible location.

Store Image in MySQL Table

Another way to store an image in the MySQL database is to store it in the table itself. Images size can be quite big, sometimes bigger than 1 or 2MB. So, storing images in a database can put additional load on your database and the network between your database and your web server if they're on separate hosts.

In this approach, the image files can be challenging to manage. You must first restore them from the database before other operations are to be performed.

There are some exceptions where the entire database is stored in RAM. MySQL databases are sequentially stored on a disk. This means that your database picture files are converted to blobs, embedded into a database, and then kept on a disk. We can avoid a lot of problems by simply storing them on a disk as mentioned in the first approach.

Now create the table Image to store the images in the database. I have used id to identify each image and caption to store the name of the image for display purposes. The picture column stores the image in the table using the data type as LONGBLOB.

// Create the Image table
CREATE TABLE image (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(1024) NOT NULL,
  `title` VARCHAR(2048) NULL,
  `description` VARCHAR(4096) NULL,
  `extension` VARCHAR(160) NULL,
  `size` FLOAT NOT NULL,
  `caption` VARCHAR(1024) NULL,
  `altText` VARCHAR(512) NULL,
  `picture` LONGBLOB NOT NULL,
  `createdAt` DATETIME NULL,
  `modifiedAt` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_image_name` (`name` ASC),
  INDEX `idx_image_type` (`type` ASC)
) ENGINE = InnoDB;

Summary

This tutorial provided the options to store images using the MySQL database. The same approach can be followed for other databases.