Course
BLOB
MySQL Tutorial
This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.
BLOB
Many user applications require the storage of different types of data, including text, images, files, and more. Using BLOBs in a MySQL database allows you to store all these types of data within the same database, eliminating the need for a separate file system.
The MySQL BLOB Data Type
The MySQL BLOB (Binary Large Object) data type is used to store binary data, such as images, audio, video, or any other type of binary file. BLOB columns can store variable-length binary data, making it suitable for handling files of various sizes.
Consider an application that collects user information through forms. This information may include personal details, such as name and address, along with image proofs like PAN cards or AADHAR cards. Instead of managing these files separately in a file system, you can store them as BLOBs in a MySQL database.
Syntax
Following is the basic syntax to assign BLOB data type on a table field −
CREATE TABLE table_name (column_name BLOB,...)
Example
Let us consider a basic example to show how to assign BLOB datatype to a table field. Here, we are creating a table named 'demo_table' with two fields "ID" and "DEMO_FILE" −
CREATE TABLE demo_table ( ID INT NOT NULL, DEMO_FILE BLOB);
Following is the output obtained −
Query OK, 0 rows affected (0.01 sec)
You can see the table structure with the following command −
DESC demo_table;
The table obtained is as follows −
Inserting Data into BLOB Fields
You can insert some values into a database table, by loading a file to the BLOB field using the LOAD_FILE() function. However, before doing so, ensure that the following conditions are met −
- File Existence −The file you want to insert must exist on the MySQL server host location. To determine the required location, you can use the secure_file_priv variable with the following command. If the result of this command is not empty, the file to be loaded must be located in that specific directory.
- SHOW VARIABLES LIKE secure_file_priv;
- Specify Full File Path − When using the LOAD_FILE() function, you must pass the full path of the file as an argument, like '/users/tutorialspoint/file_name.txt'. For Windows users, remember to use double backslashes as escape characters in the path ('//users//tutorialspoint//file_name.txt').
- Check 'max_allowed_packet' Value − MySQL Server has a max_allowed_packet variable that determines the maximum allowed file size for loading. To check the value of this variable, you can use the following command −
- SHOW VARIABLES LIKE max_allowed_packet;
- Ensure that the file size does not exceed the value specified in this variable.
- Grant FILE Privileges − Make sure the MySQL user account has FILE privileges granted. To grant file privileges to a user, you can use the following command (usually performed by a user with administrative privileges, such as 'root') −
- GRANT FILE ON *.* TO 'username'@'hostname';FLUSH PRIVILEGES;
- File Readability − Lastly, make sure that the file is readable by the MySQL server.
Example
To insert values into a previously created table 'demo_table', you can use the following INSERT query −
INSERT INTO demo_table VALUES(1,LOAD_FILE("C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\sample.txt"));
To verify the insertion, you can retrieve the data from the 'demo_table' using the following query −
SELECT * FROM demo_table;
We can see in the output below, the table contains the hex string of content present in the 'sample.txt' file. You can load any type of files into MySQL, like images, multimedia files, PDF documents etc. −
Types of BLOB Datatype
MySQL provides four types of BLOB datatypes, each with varying maximum storage capacities. While they all serve the same purpose of storing binary data, such as images or files, they differ in the maximum size of objects they can accommodate. Here are the four BLOB datatypes −
- TINYBLOB − It can store a maximum of 255 bytes, or 255 characters.
- BLOB − It can store up to 65,535 (216 - 1) bytes, which is equivalent to 64KB of data.
- MEDIUMBLOB − It can store up to 16,777,215 (224 - 1) bytes, or 4GB.
- LONGBLOB − It is the largest among these datatypes and can store objects up to 4,294,967,295 bytes (232 - 1), or 4GB.
Let us try to create tables with all types of BLOB datatypes mentioned above.
Creating a Table with TINYBLOB Datatype
In this example, we are creating a table named 'demo_tinyblob' with TINYBLOB datatype on a field −
CREATE TABLE demo_tinyblob (ID INT, DEMO_FIELD TINYBLOB);
Output
Following is the output obtained −
Query OK, 0 rows affected (0.02 sec)
Verification
You can see the table structure with the following command −
DESC demo_tinyblob;
The table obtained is as follows −
Creating a Table with MEDIUMBLOB Datatype
Here, we are creating a table named 'demo_mediumblob' with a field of type MEDIUMBLOB using the following query −
CREATE TABLE demo_mediumblob (ID INT, DEMO_FIELD MEDIUMBLOB);
Output
Output of the above code is as follows −
Query OK, 0 rows affected (0.02 sec)
Verification
You can see the table structure with the following command −
DESC demo_mediumblob;
Following is the table obtained −
Creating a Table with LONGBLOB Datatype
In this case, we are creating a table named 'demo_longblob' with a field of type LONGBLOB −
CREATE TABLE demo_longblob (ID INT, DEMO_FIELD LONGBLOB);
Output
Following is the result produced −
Query OK, 0 rows affected (0.02 sec)
Verification
You can see the table structure with the command given below −
DESC demo_longblob;
The table produced is as shown below −