Hellp understanding string data types in MYSQL.

Paul_help_

Hi guys.

I am just in the process of learning about what the different string data types are in MYSQL.

In the book I am reading, it talks about:

TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

I know what the BLOB part stands for (binary large object).

But I do not understand what is meant by the word 'object'?

Is it supposed to mean the same thing as an object which is created from a class?

.... and I do not understand how the string data types: 'BINARY' and 'TINYBLOB' are different. They both hold 0-255 bytes. So what is the difference?

Paul.

laserlight

Paul help! wrote:
But I do not understand what is meant by the word 'object'?

Is it supposed to mean the same thing as an object which is created from a class?

No, it just means a piece of data.

Paul help! wrote:
I do not understand how the string data types: 'BINARY' and 'TINYBLOB' are different. They both hold 0-255 bytes. So what is the difference?

Refer to the MySQL manual entry on BLOB and TEXT types:

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

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 8.3.4, "Column Indexes".

BLOB and TEXT columns cannot have DEFAULT values.

Weedpacket

"Object" in this case means "thing". What's inside is irrelevant to the database.
See, for example
http://foldoc.org/binary%20large%20object

sneakyimp

From laserlight's link:

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). They have a character set, and values are sorted and compared based on the collation of the character set.

Both MySQL data types store a series of bytes. What these bytes represent is not of great importance to MySQL, although text fields have slightly more complex behavior than BLOBS thanks to the presence of a collation that determines sorting order and text functions that can operate on the text columns.

Any given series of bytes, e.g. some "BLOB", could represent some programming object. For example, one might [man]serialize[/man] some object in one's PHP script, store the resulting string of bytes in a BLOB in the database. You could then run a query to retrieve the blob and [man]unserialize[/man] it to get the object back. There are many different kinds of data serialization including the PHP serialization I just mentioned, JSON, AMF3, YAML, etc.

Paul_help_

Thanks.

So what is it that BLOBs, LOBs (etc) are primarily used for?

Is serialising and unserialsing something (like you explained above) a common reasion to use them?

Paul.

laserlight

Paul help! wrote:
So what is it that BLOBs, LOBs (etc) are primarily used for?

Is serialising and unserialsing something (like you explained above) a common reasion to use them?

Frankly, I rarely use BLOBs. I believe their original motivation was for storage of binary data like image and video, but the prevailing wisdom for the Web is to store these in the filesystem, then store the path to the file in the database. Using them for serialised data makes sense since, as for image and video content, you do not want the database engine to do anything more than store them as-is since all operations will be done by the script and elsewhere.

sneakyimp

I don't think I've ever used a blob data type while designing database. Like laserlight says, it makes sense to use BLOB for serialized data objects if you plan to store them in your database because it would prevent the db from worrying about things like character set collations -- things that apply to a text field. However, the file system is probably more effective for dealing with images/video/audio files or whatever.

Serializing or unserializing data is not a "reason" to use a blob field in your db. On the contrary, really. The need to take some data file or object and serialize it before you can get a PHP string that can be used in your INSERT/UPDATE query is extra work you must do to get binary data into a blob field. If anything, this extra serialization step is a reason not to use BLOBs to store them. Why bother if you can just use the file system to store them? As laserlight says, the 'prevailing wisdom' says that it's easier to store binary files using the file system and you just store the file system location of the binary file in your database.

I also find that most tools that help you work with a database (like phpMyAdmin) tend to be a little tricky when dealing with BLOB fields. While these tools will always just display the contents of some text field, they usually don't display the contents of BLOB fields without extra fiddling around. Instead of the blob contents, you typically see something like [BLOB - 34Kb].