The first step is using MySQL withBLOB data is to configure the server. To start, create a table that can be accessed. File tables often have four columns: anAUTO_INCREMENT column of appropriate size (UNSIGNED SMALLINT) to serve as a primary key to identify the file, aVARCHAR column that stores the file name, anUNSIGNED MEDIUMINT column that stores the size of the file, and aMEDIUMBLOB column that stores the file itself. For this example, use the following table definition:
CREATE TABLE file(file_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,file_name VARCHAR(64) NOT NULL,file_size MEDIUMINT UNSIGNED NOT NULL,file MEDIUMBLOB NOT NULL); After creating a table, you might need to modify themax_allowed_packet system variable. This variable determines how large of a packet (that is, a single row) can be sent to the MySQL server. By default, the server only accepts a maximum size of 1MB from the client application. If you intend to exceed 1MB in your file transfers, increase this number.
Themax_allowed_packet option can be modified using the MySQL WorkbenchServer Administration screen. Adjust the Maximum permitted option in theData / Memory size section of the Networking tab to an appropriate setting. After adjusting the value, click theApply button and restart the server using theStartup / Shutdown screen of MySQL Workbench. You can also adjust this value directly in themy.cnf file (add a line that readsmax_allowed_packet=), or use thexxMSET max_allowed_packet= syntax from within MySQL.xxM;
Try to be conservative when settingmax_allowed_packet, as transfers of BLOB data can take some time to complete. Try to set a value that will be adequate for your intended use and increase the value if necessary.