Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 44.6Mb
PDF (A4) - 44.7Mb
PDF (RPM) - 40.4Mb
HTML Download (TGZ) - 10.5Mb
HTML Download (Zip) - 10.5Mb
HTML Download (RPM) - 9.1Mb
Man Pages (TGZ) - 205.6Kb
Man Pages (Zip) - 308.8Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  The BLOB and TEXT Types

11.4.3 The BLOB and TEXT Types

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. See Section 11.8, “Data Type Storage Requirements”.

BLOB values are treated as binary strings (byte strings). They have the binary character set and collation, and comparison and sorting are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). They have a character set other than binary, and values are sorted and compared based on the collation of the character set.

If strict SQL mode is not enabled and you assign a value to a BLOB or TEXT column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 5.1.11, “Server SQL Modes”.

Truncation of excess trailing spaces from values to be inserted into TEXT columns always generates a warning, regardless of the SQL mode.

For TEXT and BLOB columns, there is no padding on insert and no bytes are stripped on select.

If a TEXT column is indexed, index entry comparisons are space-padded at the end. This means that, if the index requires unique values, duplicate-key errors will occur for values that differ only in the number of trailing spaces. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error. This is not true for BLOB columns.

In most respects, you can regard a BLOB column as a VARBINARY column that can be as large as you like. Similarly, you can regard a TEXT column as a VARCHAR column. BLOB and TEXT differ from VARBINARY and VARCHAR in the following ways:

If you use the BINARY attribute with a TEXT data type, the column is assigned the binary (_bin) collation of the column character set.

LONG and LONG VARCHAR map to the MEDIUMTEXT data type. This is a compatibility feature.

MySQL Connector/ODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR.

Because BLOB and TEXT values can be extremely long, you might encounter some constraints in using them:

Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened.

In some cases, it may be desirable to store binary data such as media files in BLOB or TEXT columns. You may find MySQL's string handling functions useful for working with such data. See Section 12.5, “String Functions”. For security and other reasons, it is usually preferable to do so using application code rather than giving application users the FILE privilege. You can discuss specifics for various languages and platforms in the MySQL Forums (http://forums.mysql.com/).


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by TM Sch on April 21, 2012
This was my first time working with BLOB data, but I first wanted to test WITHOUT an intermediary programming language. (That would add another source of errors, I think.) I had a hard time trying to test inserting and selecting.

Probably this is old had to professional programmers, but my book's limited discussion of BLOB data was about the data type, not how to use it.

Newbies, to make your life easier, here's a quick how-to insert/return blob data at the command line:
****

1) Got errors about NULL value in NOT NULL column. Permissions were fixed as far as I could see (have MySQL installed as a service on Windows 7, and all users had at least read/execute permissions). I did not see any "max_allowed_packet" or "secure_file_priv" already existing in the "my.ini" file.

SOLUTION: In addition to proper permissions, I need to use "/", not "\" in path to blob data (which was a picture). Note, this worked properly even for a non-relative path starting at the drive letter! :D

Meanwhile, I tried moving the photo to a new directory to solve my so-called "permissions" problem, but that wasn't the problem. Truly, I was getting Error 1048 was because I needed to use forward slashes! (You will get Error 1048 if you try to insert a NULL value, or when using LOAD_FILE and it can't read it for any reason...not always permissions.)

2) After solving #1, SELECT statement seemed to confirm the picture is indeed stored in the table. However, this returned so many unreadable characters that I could not scroll back to see the complete results, and I had to wait a couple minutes until my computer stopped beeping. (Lucky for me, the system didn't crash.)

SOLUTION: use the SUBSTRING() function in the SELECT statement to only return so many characters from that blob field!

** SAMPLE DATA IF YOU WANT TO TRY YOURSELF **
** Edited, forgot to use double-backslashes in "Filename" column. **

CREATE TABLE Photos(
PhotoID int unsigned not null auto_increment primary key,
Filename varchar(255) not null unique,
Caption varchar(255) not null,
Photo longblob not null);

DESCRIBE Photos;

INSERT INTO Photos values (
NULL,
'D:\\mytemp\\WOC-logo.jpg',
'Walk of Champions official logo',
LOAD_FILE('D:/mytemp/WOC-logo.jpg')
);

SELECT PhotoID, Filename, Caption, SUBSTRING(Photo,1,20) from Photos;
Sign Up Login You must be logged in to post a comment.