0

I want to make the maximum allowable size of image files 25MB within a table. However I haven't found a way to make this happen. Right now I'm using MEDIUMBLOB since its maximum is 16MB but I want it just a tiny bit larger. So is it possible?

I'm pretty new to MySQL so I apologize if what I'm trying to ask seems unclear.

sandbear
  • 9
  • 1
  • 1
    This question has been asked a gazillian times here. – e4c5 Sep 13 '15 at 05:09
  • 1
    possible duplicate of [What is the maximum length of data I can put in a BLOB column in MySQL?](http://stackoverflow.com/questions/5775571/what-is-the-maximum-length-of-data-i-can-put-in-a-blob-column-in-mysql) – steveluscher Sep 13 '15 at 05:14
  • @steveluscher he's asking for the ability to set a user limit, not for the default system one. – JB. Sep 13 '15 at 05:20

2 Answers2

0

I would recommended not to store images in the database. I would suggest to follow below process to store images If you want to store profile picture of every user while creating a user account - create a extra field user table in the database to store profile picture name. - First insert other detail in the database and get the last inserted id (ie primary key of a user table). - Upload image in one of the folder on the server and rename that image with unique key (for example : "profile_pic_" and concat with primary key of the user table) - Update the image name in the user table in the databse.

Vishal
  • 26
  • 2
0

Create a table whose rows represent possible ‘exceptions’

CREATE TABLE exceptions (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255),
    UNIQUE KEY (id, message)
);
INSERT INTO exceptions (1, 'Images must be <=25MB');

Define a trigger on your images table

Assuming the BLOB column is called ‘data’:

delimiter //
CREATE TRIGGER assert_image_size BEFORE INSERT ON images
  FOR EACH ROW 
  BEGIN
    IF LENGTH(NEW.data) > 25000000 THEN
      INSERT INTO exceptions (1, 'Images must be <=25MB');
    END IF;
  END;
//
delimiter ;

MySQL will throw a duplicate key error, and the error message will contain the text “Image too large”.

steveluscher
  • 4,144
  • 24
  • 42