Shulou(Shulou.com)05/31 Report--

Editor to share with you what blob is in mysql, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

What is blob,mysql blob size configuration 2010-04-06 17:21BLOB introduction

BLOB (binary large object), a binary large object, is a container that can store binaries. In computers, BLOB is often the type of field used to store binary files in a database. BLOB is a

Large files, typically BLOB is a picture or a sound file, because of their size, must be processed in a special way (for example, upload, download or store in a database). According to Eric Raymond's

It is said that the main idea of dealing with BLOB is to let the file processor (such as the database manager) ignore what the file is, but care about how to deal with it. However, some experts stressed that this method of dealing with big data's object is to combine the two.

Blade sword, which may cause some problems, such as the storage of binaries is too large, it will degrade the performance of the database. Storing large multimedia objects in a database is a typical example of an application dealing with BLOB.

Mysql BLOB Typ

In MySQL, BLOB is a family of types, including TinyBlob, Blob, MediumBlob, and LongBlob. The only difference between these types is in the maximum size of the storage file.

Four BLOB types of MySQL

Type size (in bytes)

TinyBlob maximum 255i

Maximum of 65K for Blob

MediumBlob up to 16m

LongBlob maximum 4G

Linux modifies etc/my.cnf

[mysqld]

Max_allowed_packet = 16m / / is different from max_allowed_packet under [mysqldump]

BLOB is a large binary object that can hold a variable amount of data. There are four types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They just differ in the maximum length of the allowable value.

There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to four BLOB types with the same maximum length and storage requirements.

The BLOB column is treated as a binary string (byte string). The TEXT column is treated as a non-binary string (character string). BLOB columns have no character set, and sort and compare numeric values based on column value bytes. The TEXT column has a character set, and values are sorted and compared according to the proofreading rules of the character set.

There is no case conversion during the storage or retrieval of TEXT or BLOB columns.

When not running in strict mode, if you assign a value to a BLOB or TEXT column that exceeds the maximum length of the column type, the value is truncated to ensure a fit. If the truncated character is not a space, a warning will be generated. Using strict SQL mode, an error is generated and the value is rejected rather than intercepted and given a warning.

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

Trailing spaces are not deleted when the values of BLOB and TEXT columns are saved or retrieved. This is the same as the VARBINARY and VARCHAR columns.

Note that when comparing, TEXT will be expanded with spaces to fit the object of comparison, such as CHAR and VARCHAR.

For indexes of BLOB and TEXT columns, you must specify the length of the index prefix. For CHAR and VARCHAR, the prefix length is optional.

BLOB and TEXT columns cannot have default values.

LONG and LONG VARCHAR correspond to MEDIUMTEXT data types. This is to ensure compatibility. If the TEXT column type uses the BINARY attribute, the column will be assigned a binary proofreading rule for the column character set.

The MySQL linker / ODBC defines the blob value as LONGVARBINARY and the text value as LONGVARCHAR.

Because the BLOB and text values can be very long, you may encounter some constraints when using them:

Only the first max_sort_length bytes of the column are used when sorting. The default value for max_sort_length is 1024; this value can be changed using the-- max_sort_length option when starting the mysqld server.

Increasing the value of max_sort_length at run time makes more bytes meaningful when sorting or combining. Any client can change the value of its session max_sort_length variable:

Mysql > SET max_sort_length = 2000

Mysql > SELECT id, comment FROM tbl_name

-> ORDER BY comment

When you want to make more than max_sort_length meaningful, another way to use GROUP BY or ORDER BY for BLOB or TEXT columns with long values is to convert column values to fixed-length objects. The standard method is to use the SUBSTRING function. For example, the following statement sorts the 2000 bytes of the comment column:

Mysql > SELECT id, SUBSTRING (comment,1,2000) FROM tbl_name

-> ORDER BY SUBSTRING (comment,1,2000)

The maximum size of a BLOB or TEXT object is determined by its type, but the actual maximum value that can be passed between the client and server is determined by the amount of memory available and the size of the communication cache. You can change the size of the message cache by changing the value of the max_allowed_packet variable, but you must modify both the server and the client program. For example, you can use mysql and mysqldump to change the client's max_allowed_ packet value.

Each BLOB or text value is represented by an internally assigned object. This contrasts with other column types, which allocate a storage engine for each column when the table is opened.

The above is all the content of this article "what is blob in mysql?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.