varchar(255) v tinyblob v tinytext

Question

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?

Answer 1

Primarily storage requirements and memory handling/speed:

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.

Answer 2

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

Answer 3

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