[MySQL] best field type to use for unknown length data

[FiREFLi]

What's best? I think most data types have specific limits don't they? Which one most efficiently scales up/down?

[jamend]

tinytext/text?

[Cailin]

If it's just plain text, then TEXT, otherwise, BLOB (for binary data).

[FiREFLi]

And what does TEXT do that a VARCHAR or CHAR doesn't?

[voidpharoh]

The maximum length for char/varchar in MySQL is 255 characters I believe. Whereas, text/blob has a much higher maximum character length available.

[kyosuken]

varchar has been changed to allow 64k characters in mysql 5 if i am not wrong :)

[FiREFLi]

Does either have a variable size which increases as the amount of data increases?

[+mrbester MVC]

If the datatypes are anything like SQL Server ones then:

char is fixed size, meaning it will pad with spaces should the content length be less than the field size.

varchar is variable length (hint: var at the beginning ;) ); it contains a pointer to the data rather than the data itself.

How unknown is "unknown length"? Is it likely to be in the order of gigabytes, megabytes, kilobytes? If kilobytes or less then varchar is the best, otherwise go for text. If it isn't text in the first place, then BLOB.

[Josh]

  mrbester said:

If the datatypes are anything like SQL Server ones then:

char is fixed size, meaning it will pad with spaces should the content length be less than the field size.

varchar is variable length (hint: var at the beginning ;) ); it contains a pointer to the data rather than the data itself.

How unknown is "unknown length"? Is it likely to be in the order of gigabytes, megabytes, kilobytes? If kilobytes or less then varchar is the best, otherwise go for text. If it isn't text in the first place, then BLOB.

And if it's an "unknown file size" greater than 64kb then you're going to need to use mediumblob (holds up to 16MB) or largeblob(holds up to 4GB) instead.

[Cailin]

  Quote

And if it's an "unknown file size" greater than 64kb then you're going to need to use mediumblob (holds up to 16MB) or largeblob(holds up to 4GB) instead.

And this doesn't mean that each row will take up 64kb/16MB/4GB, but it does mean that there is an upper bound on the data size. The size of the row will increase dynamically as you insert larger data.