The number of images in web applications has been growing steadily in recent years. There is also a need to distinguish between images of different sizes, like thumbnails, web display images, and the like. For example, one application that I recently developed shows news items where each item has a thumbnail and main article image. Another app shows company logos in small and large sizes.
Most of the time, images can be stored on the web server and then referenced using the URL. That only requires storing the path string in the database, rather than the image itself. However, there are times that this is not feasible, such as where the app has insufficient rights on the filesystem. In those cases, you can store images directly in the database and then load them using application code.
Navicat development and administration tools provide excellent support for image management. In today's blog, we'll learn how Navicat makes storing images a simple process. For the purposes of demonstration, I'll be using Navicat Premium against a MySQL 8 database, but the same procedure would apply to other relational databases as well.
Designing the Table
In MySQL, the preferred data type for image storage is BLOB. However, there are actually three flavors of BLOB. The one you choose depends on the size of the images that you will be storing. If in doubt, go to the larger capacity BLOB! Here are the three BLOB types:
- BLOB: Can handle up to 65,535 bytes of data.
- MEDIUMBLOB: The maximum length supported is 16,777,215 bytes.
- LONGBLOB: Stores up to 4,294,967,295 bytes of data.
With that in mind, here's a table definition that would be well suited to thumbnail images, but not much larger:
Besides the image itself, you may find it useful to store other information about the image, such as an ID, name, description, size, type (JPEG, GIF, BITMAP, etc.), category, and so on.
Loading Images into the images Table
Using Navicat, there's no need to write SQL code to load images. Instead, you can use the standard File Browser to locate and insert image files.
Whenever you view table contents in either Grid of Form view, you can select how you want Navicat to treat data from the data type drop-down:
Choosing Image from the drop-down adds an image preview pane underneath the table/row contents:
On the left of the file preview, you'll find three icons: Load, Save to Disk, and Clear. To load an image, simply click the Load icon and select the image using the operating system's standard File Browser dialog. Once inserted, the image - as well as its size in bytes - will appear in the preview pane:
Note that the above image requires a MEDIUMBLOB as its size exceeds 65,535 bytes!
Conclusion
In today's blog, we learned how to store images in a MySQL 8 database using Navicat Premium 15.
Now is the perfect time to purchase Navicat Premium as version 15 adds over 100 enhancements and includes several new features to give you more ways that ever to build, manage, and maintain your databases than ever before!