How does the BLOB field work?

Asked

Viewed 23,371 times

12

Hello, directly asking, how does this kind of field work?

I know there are three types:

BLOB = 64KB, MEDIUMBLOB = 16MB and LONGBLOB = 4GB

Given this example:

CREATE TABLE COMPROMISSOS (
  ID         INTEGER NOT NULL,
  DESCRICAO  BLOB,
  DATA_HORA  TIMESTAMP NOT NULL
);

But how it works in practice, in the database it presents Size 80, what is this 80 and how it expands?

mysql database firebird
  • 2

    Is it Mysql or Firebird? Displays the size where? Have something specific you want to know?

  • So, in Firebird by Ibexpert presents 80, in Mysql I can not see pq only currently use phpmyadmin, even specifies how it works, this expansion of it I did not understand. That 80 there definitely did not understand.

  • First, it is not a "field".. it is a data type (data type). And your question is vague and confusing. The title asks one thing and in context and in the comments you ask another specific thing about the "Size 80" presented in the Firebird client. Define what you want first and describe more consistently in the question.

  • @Danielomine The question was very straightforward about what I wanted to know, even you didn’t read it right "the title is: How does the BLOB field work?" and on the first line I again asked, "Hello, directly asking, how does this kind of field work?" And about Size 80 just note that I added the 2 Tags directly in the question (Mysql and Firebird). If you don’t have something to help you answer, you don’t have to worry, because with the 2 Answers posted I have already overcome a lot of doubt! If you still insist that the question is Vague, just signal, we have Great moderators on duty! Thank you!!!!!

2 answers

11


What is a blob field?

The blob (Binary Large Object - large binary object) is a field created for storing any type of information in binary format, within a table of a database.

Mysql works with blob fields, which are actually text fields (TEXT) with a single difference: text fields are"case-insensitive", unlike blob"s.

The blob"s are divided into four types (in Mysql), with the difference existing from one to the other being solely the storage and work capacity of the field. These are:

TINYBLOB - maximum storage blob field of 255 characters (8 bits) plus 1 control;

BLOB - same as Tinyblob, but storing up to 16535 characters (16 bits) plus 2 control;

MEDIUMBLOB - the same as tinyblob, but storing up to 16777216 characters (24 bits) plus 3 control;

LONGBLOB - the same as tinyblob, but storing up to 4294967295 characters (32 bits) plus 4 control.

In addition, there are the following particularities with the blobs fields:

  1. Cannot be primary keys (excluding Tinyblob);

  2. Cannot use GROUP and SORT commands with blob fields;

  3. Are recognized as a LONGVARCHAR for ODBC drivers.

Segment Size

The segment size (segment size) is defined to increase the input and output performance of the data in the blob.

It should vary according to the type of information you want to store.

For short memos - even if in some cases it extends a little more you can define the size as 100 bytes, which informs that blocks of 100 bytes should be processed at a time.

For large video or graphics - a longer length shall be reported. the maximum length is 65536 bytes (64 Kb), because all the contents of the blob are stored and read in block.

When a blob is read, its content is extracted into blocks so the larger the block, the greater the number of bytes processed simultaneously, increasing performance.

Previously the default value was 80 bytes (because it fit 80 characters on a monitor line), but for current computers this value may be higher. 2048 is a recommended default value since Interbase version 6.

Remembering that the segment size does not limit the field size, but rather how it is handled.

Basically, in mysql you don’t need to specify the segment size because it can analyze the size of the blob in your declaration and use the proper paging, now in Firebird it is necessary, since there is only one type of blob.

To change the SEGMENT SIZE property in Firebird is like this:

UPDATE RDB$FIELDS set
RDB$SEGMENT_LENGTH = 1024
WHERE RDB$FIELD_NAME = 'RDB$85'

Recalling that this way of updating the property will only work until Firebird 3 from this version the procedure indicated is to create a temporary column to transfer all the content,delete the blob field and create it with the appropriate SEGMENT SIZE, and then move the data to the newly created column.

  • Got it, in Firebird how do I increase the 80 to a higher value ? I can increase to any value up to the limit or there are predefined sizes ?

5

The blob (Binary Large Object - large binary object) serves to store any kind of data, videos, images, sounds, etc.

This 80 you are viewing is the default SIZE SEGMENT used in field creation.

SEGMENT SIZE defines the amount of data that will enter and exit at the time of reading and writing at a time, in cases of large videos, for example, it is indicated that it is larger, and can reach the maximum limit of 65536 bytes (64 Kb)

It is important to understand that SEGMENT SIZE does not define the size of the field, but rather how it is manipulated.

When growing, internally the database records the value of the cell (row x column) being inserted into 4k blocks (if I’m not mistaken this value) in a 1:N relationship (line:blob blocks).

Then the space occupied by the blob will depend on how many blocks the database needs to store all the information.

Browser other questions tagged mysql database firebird

You are not signed in. Login or sign up in order to post.