CODEWITHSUNDEEP
sqlmysqldatabase-design
Humpton
Humpton

Reputation: 1529

varchar(255) v tinyblob v tinytext

My side question is there really any difference between tinyblob & tinytext?

Buy my real question is what reason, if any, would I choose varchar(255) over tinyblob or tinytext?

Upvotes: 15

Views: 17076

Answers (3)

sethbc
sethbc

Reputation: 3479

Primarily storage requirements and memory handling/speed:

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

VARCHAR(M), VARBINARY(M):
L + 1 bytes if column values require 0 – 255 bytes,
L + 2 bytes if values may require more than 255 bytes

TINYBLOB, TINYTEXT:
L + 1 bytes, where L < 28

Additionally, see this post:

For each table in use, MySQL allocates memory for 4 rows. For each of these rows CHAR(X)/VARCHAR(X) column takes up the X characters.

A TEXT/BLOB on the other hand is represented by a 8 byte pointer + a 1-4 byte length (depending on the BLOB/TEXT type). The BLOB/TEXT is allocated dynamicly on use. This will use less memory, but in some cases it may fragment your memory in the long run.

Edit: As an aside, blobs store binary data and text stores ASCII, thats the only difference between TINYBLOB and TINYTEXT.

Upvotes: 18

marol
marol

Reputation: 1

You can't apply CHARACTER SET to TINYTEXT, but you can to VARCHAR(255)

Upvotes: -8

Steve K
Steve K

Reputation: 19596

VARCHAR(255) is more SQL standard than tinyblob or tinytext. So your script, and application would be more portable across database vendors.

Upvotes: 5

Related Questions