TEXT and LONGTEXT field lengths

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

TEXT and LONGTEXT field lengths

Post 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?
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post 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
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

So TEXT is more than sufficient for my 15,000 character fields.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

You can expect 1 or 2 bytes for each character.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

So im still safe changing my LONGTEXT fields to just TEXT. 65,xxx should cover my 15,000 maximum
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.)
Post Reply