Images in MySQL

1. Introduction

This tutorial will study storing images in a MySQL (version 8) database.

We’ll use our University schema to define the queries.

2. Images in a Database

Like text data, we can also store images in our MySQL database. There are two ways to store images in MySQL:

• Direct storage
• Indirect storage

In direct storage, we store complete image files in the database, while in indirect storage, we store the image on another storage (network file server) and then store the image path in the database.

2.1. Use Cases

There are several use cases for storing images in a database.

For example, let’s say we have a high-security application for money transactions (e.g., buying derivatives, options, and metals). We can authenticate users based on their biometric matching score (retina scan and thumbprints) to maintain maximum security. For this, we need a (highly secure) database to store each user’s original retina and thumbprint scan images.

Similarly, a short-form video platform such as Vimeo must store thumbnail images of each user profile and video directly in the database.

3. BLOB

In MySQL, there are four main data types. Those are string, numeric, date time, and BLOB/JSON:

The BLOB datatype can store images, audio, video, and other binary data types. We generally store images in MySQL as BLOB datatype.

The BLOB datatype has four subtypes that are based on the maximum length of the values they can hold:

• TINYBLOB (maximum of 255 bytes)
• BLOB (maximum of 65,535 bytes)
• MEDIUMBLOB (maximum of 16,777,215 bytes)
• LONGBLOB (maximum of 4,294,967,295 bytes)

4. MySQL Direct Image Storage: LOAD_FILE()

Let’s examine the direct storage of images in the MySQL database. We use the LOAD_FILE() method to store images directly in the database.

4.1. Prerequisites

We must ensure that the following conditions are met before we use the LOAD_FILE() function to load the image in our database.

First, we must check if our image exists in the file system at the MySQL server’s host location.

Second, MySQL needs all images placed into the path given by the system variable secure_file_priv. We can use MySQL’s default data folder or our custom folder. We can modify the MySQL configuration file (my.cnf) for this. The location for this file is system and installation-specific. For example, we can find this file for Mac OS  in /usr/local/etc/: 

[client]
 user=mysql
 socket=/var/lib/mysql/mysql.sock
 port=3306
 [mysqld]
 socket=/tmp/mysql2.sock
 secure-file-priv=/usr/local/mysql/data
 # Only allow connections from localhost
 bind-address = 127.0.0.1
 mysqlx-bind-address = 127.0.0.1

Now, we can verify the value for this secure_file_priv:

SHOW VARIABLES LIKE 'secure_file_priv';

+------------------+----------------------+
| Variable_name    | Value                |
+------------------+----------------------+
| secure_file_priv |/usr/local/mysql/data|
+------------------+----------------------+

Third, we must ensure that  MySQL has read permissions on the image. We must pass the full path of the file as an argument to LOAD_FILE().

Fourth, MySQL Server has a max_allowed_packet variable that determines the maximum allowed file size (bytes) for loading. We can check its value by the following command:

SHOW VARIABLES LIKE max_allowed_packet;

+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+

We need to ensure that the file size doesn’t exceed the value specified in this variable.

Lastly, we need to grant FILE privileges to the query user. We can grant the file privileges to a user (nikhil) on the host (192.168.3.22)  via the  following command:

GRANT FILE ON *.* TO 'nikhil'@'192.168.3.22';
FLUSH PRIVILEGES;

4.2. LOAD_FILE()

Let’s modify the tables DepartmentComplete and StudentComplete to include images:

CREATE TABLE DepartmentComplete
(
    id INT PRIMARY KEY NOT Null,
    name VARCHAR (50),
    code VARCHAR (4),
    logo BLOB,
    UNIQUE (id)
);

CREATE TABLE StudentComplete
(
    id INT PRIMARY KEY NOT null,
    name VARCHAR (60),
    national_id BIGINT NOT Null, 
    birth_date DATE,
    enrollment_date DATE,
    graduation_date DATE,
    gpa FLOAT,
    profile_pic BLOB,
    UNIQUE (id)
);

Now, we run our inserts by giving the full path of images:

INSERT INTO DepartmentComplete (id, name, code, logo) VALUES
  (1, 'Computer Science', 'CS', LOAD_FILE('/usr/local/mysql/data/cs_logo.png')),
  (2, 'Electronics and Communications', 'EC', LOAD_FILE('/usr/local/mysql/data/ece_logo.png')),
  (3, 'Social Sciences', 'SS', LOAD_FILE('/usr/local/mysql/data/ss_logo.png')),
  (4, 'Computational Biology', 'CB', LOAD_FILE('/usr/local/mysql/data/cb_logo.png')),
  (5, 'Mathematics', 'MA', LOAD_FILE('/usr/local/mysql/data/ma_logo.png'));

INSERT INTO StudentComplete (id, name, national_id, birth_date, enrollment_date, graduation_date, gpa, profile_pic) VALUES
  (1001, 'John Liu', 123345566, '2001-04-05', '2020-01-15', '2024-06-15', 4, LOAD_FILE('/usr/local/mysql/data/john_pp.jpeg')),
  (1003, 'Rita Ora', 132345166, '2001-01-14', '2020-01-15', '2024-06-15', 4.2, LOAD_FILE('/usr/local/mysql/data/rita_pp.jpeg')),
  (1007, 'Philip Lose', 321345566, '2001-06-15', '2020-01-15', '2024-06-15', 3.8, LOAD_FILE('/usr/local/mysql/data/philip_pp.jpeg')),
  (1010, 'Samantha Prabhu', 3217165566, '2001-03-21', '2020-01-15', '2024-06-15', 4.9, LOAD_FILE('/usr/local/mysql/data/cs_logo.png')),
  (1011, 'Vikas Jain', 321345662, '2001-7-18', '2020-01-15', NULL, 3.3, LOAD_FILE('/usr/local/mysql/data/vikas_pp.jpeg'));

4.3. Displaying Images

SELECT logo FROM Department
WHERE code='CS';

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| logo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ?PNG

IHDR   ?      	m"H   ?PLTE???9?????XXZmnq?????????UUW???$??PPR?????麼???????ghk????????????????????rrt??⣥???????E?????k??IIL ???????\???????B?????婩????``bzz{???e??y?¸gO?  HIDATx???z?0F!????s?v?????tK?َlR?_s1???	ه ?                             @?f?E??7???o?B?*??b:ԻY?&??ۇ??????ϯ??W?z#???kꙘ???Oܱ,?ྡ?Z?0j?}?_?ao???p??h8???y?y??6㊪????1?p?Pݰ_$?g8䆡??,??7H??N
                                     ?0?p*
                                          *j8,?lÓ?
                                                  c?f
݌?t?H~???]????p???~?4G??z???K@'?ق????dp?(7߫????h8??t&'辪??ݲ%D?0??3???݁?077K"F
???7?*ZH?YE????[??D?֣t?=>C-?Yz???ɚ?n?xH?kh|?3?Y>?ZN?M??¦????;?
                                                             C!??E?X3
                                                                     ?Pa???a؏???6?P?}"?m??8?f⨺??ҕE'ʹy2?v_?5,?>
                                                                                                              ??x?W?z??;?]?]?J?z??xdKe#???^E????mX?%C?;????\l???-??\Uаp?I:VN??a??Qc?_?!*j?"?v??rx'?BMCm3?ҙ???h?ԋ?7Γ?2R??p?t???3???}?"???Rȟ?]    ?1????Ĺ????A?!?|????A;??jI?$l1u?CG$0>?#??+g|S???? l?? U??͆FVh???~???J??~??<?7?$??@n??!2?w?7?
Sr??a?]~??i%d????Z??)?Np?g?z??x???7??.?H=O+7Դm\??L`???i?"???&+2?+??P{?i?DCm'?SʰOd8??2&jGIq]?R
a? 3I??e"????2CWN????M??f?@C?F?40ZH%"???ZZ?'2Cy)%?cPƒ?j?$?                                   *?gV?4?2?U???>??E2$?ڐʙ
                                                          ??!??I$2?+
                                                                    ?fa,?&m??Js?xs???"7i??[??1Av$????p?|"?^??0??Ȉ_?[??;?$??i?h???e?/?K?%w??Է*?????џ?]??\??:M????ܐ?F
   r?:?TNhȖ??Ig{3"T?l7?CO?2d?X?c	????|"?jCƖO5??"?Q?G?s???????q
?#???Cx?y?ގ?~j???
                 ?
                  E???1
                       >????{Z????nG5?-                             ?p?jƧļ9    IEND?B`?                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

5. Indirect Storage of Images in MySQL

Here, we store images in external folders (file system) and keep the references (instead of binary data) in the database.

Let’s first modify our tables to include the images’ absolute file paths:

ALTER TABLE DepartmentComplete
DROP COLUMN logo;
ALTER TABLE DepartmentComplete 
ADD logo_path VARCHAR (256);

ALTER TABLE StudentComplete
DROP COLUMN profile_pic;
ALTER TABLE StudentComplete 
ADD profile_pic_path VARCHAR (256);

We can store images in directories on the file system or a content delivery network (CDN):

UPDATE DepartmentComplete 
SET logo_path='/usr/local/mysql/data/cs_logo.png' 
WHERE code ='CS'; 
UPDATE DepartmentComplete 
SET logo_path='/usr/local/mysql/data/ece_logo.png' 
WHERE code ='EC'; 
UPDATE DepartmentComplete 
SET logo_path='/usr/local/mysql/data/ss_logo.png' 
WHERE code ='SS'; 
UPDATE DepartmentComplete 
SET logo_path='/usr/local/mysql/data/cb_logo.png' 
WHERE code ='CB'; 
UPDATE DepartmentComplete 
SET logo_path='/usr/local/mysql/data/ma_logo.png' 
WHERE code ='MA'; 

UPDATE StudentComplete 
SET profile_pic_path='/usr/local/mysql/data/john_pp.jpeg' 
WHERE name ='John Liu'; 
UPDATE StudentComplete 
SET profile_pic_path='/usr/local/mysql/data/rita_pp.jpeg' 
WHERE name ='Rita Ora'; 
UPDATE StudentComplete 
SET profile_pic_path='/usr/local/mysql/data/philip_pp.jpeg' 
WHERE name ='Philip Lose'; 
UPDATE StudentComplete 
SET profile_pic_path='/usr/local/mysql/data/samantha_pp.jpeg' 
WHERE name ='Samantha Prabhu'; 
UPDATE StudentComplete 
SET profile_pic_path='/usr/local/mysql/data/vikas_pp.jpeg' 
WHERE name ='Vikas Jain'; 

Now, we can use the following select query to see the file path of the Computer Science department:

SELECT logo_path 
FROM DepartmentComplete 
WHERE code='CS';

+-----------------------------------+
| logo_path                         |
+-----------------------------------+
| /usr/local/mysql/data/cs_logo.png |
+-----------------------------------+

So, we store references (file paths) in the database and use a third-party app to process the referenced image file (e.g., Python or PHP code to display the images).

6. Comparison

Both approaches have pros and cons:

Only a few applications use direct image database storage. This is because storing large images in a database permanently and directly incurs considerable storage, retrieval, and technical costs. Why?

First, images are static with infrequent changes. The size of an image can vary from a few KBs to 10s of MBs. So, we put an extra load on our database to store images and retrieve them if we use direct storage. Furthermore, this gets complicated when our database and user agent (web server that uploads and retrieves the image) are on different hosts.

Second, we can scale up faster and cheaper on a file server or CDN than on a database. Moreover, direct storing results in a lower image download speed since we use more hops to get an image from a database than from a specialized file server. This makes the direct storage option slower. Image servers (which usually have N+1 redundancy) will run even when the database is busy or down.

We store images in raw form (binary), making it harder for the database to perform internal optimization. The database can only handle small queries and transactions simultaneously, leading to degraded performance and a bad user experience. Further, most image processing work doesn’t require database constructs, so direct image storage offers no added processing benefit.

Last but not least, when we store raw data in the database, the database fills up the cache very rapidly. This will slow down our most often-run queries, and soon, we will encounter bottlenecks.

7. Conclusion

In this article, we learned about the direct and indirect storage methods to store images in MySQL. Direct image storage stores the image data as binary strings directly in the database, whereas we store file paths using the indirect method.

Except for a few cases primarily governed by security concerns, direct image storage is less valuable than indirect image storage, which offers lower operational costs and faster retrieval.