Menu
I'm trying to understand mysql data types, but i don't get the difference between the (Var-)BINARY data fields and the BLOB-Fields. What ist the difference between these types?
794
BLOB type requires a smaller data size for base rows than BINARY type, so if there are many pieces of data, BLOB will yield better performance. However, if indexes are defined for the non-BINARY columns, index scans will eliminate the difference between BINARY and BLOB types.
BLOB is used for storing binary data while Text is used to store large string. BLOB values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings).
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 .
A BLOB (binary large object) is a varying-length binary string that can be up to 2,147,483,647 characters long. Like other binary types, BLOB strings are not associated with a code page. In addition, BLOB strings do not hold character data.
BLOB's can be as big as you want.
Also, reading the MySQL manual online:
BLOB and TEXT differ from VARBINARY and VARCHAR in the following ways:
There is no trailing-space removal for BLOB and TEXT columns when values are stored or retrieved. Before MySQL 5.0.3, this differs from VARBINARY and VARCHAR, for which trailing spaces are removed when values are stored.
On comparisons, TEXT is space extended to fit the compared object, exactly like CHAR and VARCHAR.
For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 7.5.1, “Column Indexes”.
BLOB and TEXT columns cannot have DEFAULT values.
67
The binary and varbinary types are binary strings whose actual values are stored in the table. The actual values blob (and text) types are stored elsewhere in the database with a 256 byte alias to that slot being placed in the table; the blob can therefore be "any" size (up to the max).
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
