How to store a blob (image) in MySQL?

Jocky Doe picture Jocky Doe · Nov 2, 2016 · Viewed 9.2k times · Source

I am using php and intervention

What is the db format of a blob?

Is MySQL saving it as base64?

Before I save an image file to db what should I do?

Image::make('......')->encode('data-url');

Is that it?

Answer

Praveen Vinny picture Praveen Vinny · Nov 3, 2016

How to store a Binary Large Object (BLOB)?

  • A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.
  • These differ only in the maximum length of the values they can hold.
  • The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements.

Hope the following code will help you:

CREATE TABLE IMAGE_TABLE(
    IMG_ID INT(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    IMG_DETAILS CHAR(50),
    IMG_DATA LONGBLOB,
    IMG_NAME CHAR(50),
    IMG_SIZE CHAR(50),
    IMG_TYPE CHAR(50)
);

This will create a table which will suit your requirement.

You may also refer the following SO answers:

You can refer the official documentation here. This link and this link would be worth a read to deepen your understanding.