Hello, directly asking, how does this type 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 as it works in practice, in the database it displays Size 80 , what is that 80 and how does it expand?
The Binary Large Object (BIN) is a field created for storing any type of information in a binary format within a table of a database.
MySQL works with blob fields, which are actually text fields (TEXT) with only one difference: text fields are case-insensitive, unlike blob's.
Blob's are divided into four types (in MySQL), and the difference between them is only the storage and working capacity of the field:
TINYBLOB - Maximum storage blob field equal to 255 characters (8 bits) plus 1 control;
BLOB - same as Tinyblob, however storing up to 16535 characters (16 bits) plus 2 control;
MEDIUMBLOB - same as tinyblob, however storing up to 16777216 characters (24 bits) plus 3 control;
LONGBLOB - the same as the tinyblob, however storing up to 4294967295 characters (32 bits) plus 4 control.
In addition, there are the following particularities with the blobs fields:
Can not be primary keys (excluding Tinyblob);
You can not use the GROUP and SORT commands with blob fields;
Are recognized as a LONGVARCHAR for ODBC drivers.
The segment size is set to increase the inbound and outbound performance of the data in the blob.
It should vary depending on the type of information you want to save.
For short memos - even if in some cases it extends a little more, you can define the size as 100 bytes, which informs you that 100 bytes bytes must be processed at a time. p>
For large videos or graphics - a longer length should be reported. the maximum length is 65536 bytes (64 Kb), this is because all blob indexes are stored and read in block.
When a blob is read, its content is extracted in blocks so the larger the block, the greater the number of bytes processed simultaneously, increasing the performance.
Previously the default value was 80 bytes (because 80 characters fit 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 size segment does not limit the size of the field, but rather how it is manipulated.
In essence, in mysql you do not need to specify the size segment because it can parse the size of the blob in your declaration and use proper pagination, 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'
Remembering that this way of updating the property will only work until Firebird 3 from this version the procedure to perform is to create a temporary column 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.
The blob (Binary Large Object) is for storing any kind of data, videos, images, sounds, etc.
This 80 you are looking at is the default SEGMENT SIZE used to create the field.
SEGMENT SIZE defines the amount of data that will come in and out 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 to grow, internally the database writes the value of the cell (row x column) that is being inserted into blocks of 4k (if I am not mistaken it is this value) in a 1: N relationship (row: blocks of blob).So the space occupied by the blob will depend on how many blocks the database needs to store all the information.