Page 1 of 1

TEXT and LONGTEXT field lengths

Posted: Thu Aug 16, 2007 10:34 am
by icesolid
I have a database that has some fields that are 50 characters and some that are 15,000.

For the fields that are 50 to 255 I use VARCHAR.

Any fields that are 15,000 I use LONGTEXT (just figuring that it holds more data).

I was wondering what is the maximum characters for TEXT? Should I use LONGTEXT for the fields that are 15,000 or TEXT.

I have looked at the storage requirements on the MySQL documentation and I have found this:
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 65,535 (216 – 1) characters.

An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long.
From that I get that TEXT has a maximum storage of 65,535?

Posted: Thu Aug 16, 2007 10:51 am
by hawleyjr

Code: Select all

String 	TEXT 	0-65,535 bytes 
String 	MEDIUMTEXT 	0-16,777,215 bytes 
String 	LONGTEXT 	0-4,294,967,295

Posted: Thu Aug 16, 2007 10:59 am
by icesolid
So TEXT is more than sufficient for my 15,000 character fields.

Posted: Thu Aug 16, 2007 11:03 am
by hawleyjr
You can expect 1 or 2 bytes for each character.

Posted: Thu Aug 16, 2007 11:04 am
by icesolid
So im still safe changing my LONGTEXT fields to just TEXT. 65,xxx should cover my 15,000 maximum

Posted: Thu Aug 16, 2007 5:17 pm
by superdezign
icesolid wrote:So im still safe changing my LONGTEXT fields to just TEXT. 65,xxx should cover my 15,000 maximum
LONGTEXT is overkill in most situations. Unless you are storing a bunch of data from files, 4 GB is far too much allocated space.

Characters will be 1B using ASCII, and 2B using UNICODE (I believe it's two.. I may have been four). TEXT should do the job fairly well.

Posted: Thu Aug 16, 2007 5:23 pm
by feyd
superdezign wrote:and 2B using UNICODE (I believe it's two.. I may have been four). TEXT should do the job fairly well.
UNICODE's character byte sizes vary depending on what encoding form you are using. They can be as low as one, but as great as four (unless they've changed it.)