SQL Data Types: BINARY LARGE OBJECT

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

Watch out for the BLOB! A Binary Large Object, or BLOB, is a data type in SQL that allows for HUGE values in tables. This lesson will cover the concept and provide some examples.

What's a BLOB?

...the BLOB!
Blob Graphic

We've come a long way in computing power since the days when computers filled entire rooms. It's now possible to store and retrieve huge chunks of data. One of those chunks is really a shapeless, undefinable entity: a BLOB. It's not text, or numbers, or dates/times.

You might think this is science fiction, but it's very real. Technically a Binary Large Object, a BLOB is an object data type, meaning it refers to an object. Unlike a character or integer data type, the object data type only contains a pointer or reference to the value of the object. A BLOB can hold a very large block of data, anything from documents to images to videos. You could store your great American novel in a BLOB if you really wanted to (as a file).

A BLOB is really the object's agent, or handler: The database manager shouldn't need to know what's in the file or or to work it it, but it can still be a part of the database.

Let's take a look at some database management systems and how they support BLOBs: MySQL, Oracle, and SQL Server.

MySQL

MySQL supports four BLOB types:

  1. TINYBLOB
  2. BLOB
  3. MEDIUMBLOB
  4. LONGBLOB

These are all BLOBs, but they differ in how large they can be. TINYBLOB is only about 256 bytes, and LONGBLOB is 4 gigabytes! Why would we even create a TINYBLOB, since at 256 bytes, it can hardly be considered a large object? For MySQL, the focus is on the object: you could still store small text files in the database, as opposed to having to copy/paste the data from the text into another field. If you want to use a BLOB in MySQL, use the LONGBLOB option, as it supports a larger file size.

In order to save some memory and processing overhead, MySQL stores the BLOB information in a separate memory area than the normal table-processing memory.

Oracle

A BLOB can store up to four gigabytes. Like the other database tools, it's a great way to store digital information (files, images, audio, video, etc.).

Here's how you could create a table with BLOB in Oracle:

CREATE TABLE badge_photo(PhotoID, badge BLOB); INSERT INTO badge_photo VALUES(1, EMPTY_BLOB());

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support