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?
Add a comment
|
2 Answers
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.
1 Comment
Mchl
'As big as you want' as long as it's less than 2^32B (for LONGBLOB). Also: *TEXT and *BLOB columns are not stored alongside all other columns, so accessing them might be a bit slower, but on the plus side, they do not count towards 64kB max row size.
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).
1 Comment
Remember Monica
This highly upvoted answer is completely and utterly incorrect, not even details are correct. Different storage engines store blobs differently - innodb stores the first 768 octets normally and the rest elsewhere. the native storage engines store everything in the row. None distinguish between binary and blob for storage.
