MySQL supports four TEXT field types (TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT)
MyISAM tables in MySQL have a maximum row size 65,535 bytes and all the data in a row must fit within that limit.
Luckily however TEXT field types are stored outside of the table itself and thus only contribute 9 – 12 bytes towards that limit.
Further reading is here.
Because TEXT data types are able to store so much more data than VARCHAR and CHAR field types it makes sense to use them when storing web pages or similar content in the database.
The maximum amount of data that can be stored for each data type is approximately:
TINYTEXT 256 bytes
TEXT 65,535 bytes ~64kb
MEDIUMTEXT 16,777,215 bytes ~16MB
LONGTEXT 4,294,967,295 bytes ~4GB
So most of the time TEXT will suffice, but if you are scratch building a CMS it might be an idea to think about MEDIUMTEXT
Update: (20/05/2014) – I see a lot of hits on this page, so I thought I’d spell out the information here for the terms you seem to be searching for…
TINYTEXT is a string data type that can store up to to 255 characters.
TEXT is a string data type that can store up to 65,535 characters. TEXT is commonly used for storing blocks of text such as the body of an article.
MEDIUMTEXT is a string data type with a maximum length of 16,777,215 characters. Use MEDIUMTEXT if you need to store large blocks of text, such as a book.
Your update spelling out the information for terms searched for is inspired, thanks.
No probs Sam, glad it helped you out!
I found this article useful, and it was helpful that you clarified the text blob types (post from 20/05/2014).
Also, thanks a bunch for hosting this page. First hit on Google using key words “maximum size LONGTEXT mysql”
-KCM
Pingback: Do varchar/text/longtext columns in MySQL occupy full size in filesystem even for partially filled cells? | Question and Answer
Please keep in mind that, when using utf8 or utf8mb4, your characters may, worst case, take up to 3 or 4 (respectably) bytes per characters. The size is in bytes, not in characters. Hence, a TEXT field may be limited between 65,535 and 16,383 characters.
A completely valid point Marijn – back in 2009 I certainly was not taking such things into consideration.
Pingback: Maxlength for MySQL TEXT field types – Blog
I need to store database contents in LONGTEXT. the problem is to upload the database to server. The App is running on localhost. PHP Codeigniter, mysql. once in a week software backup will be uploaded to our online server when there in internet . phpmyadmin is password protected for the clients. user will not be able to download the backup. backup will be automatically once a week. so I am going to store the database contents in LONGTEXT and upload it to server where I will put this in database field. A whole database contents in a field of LONGTEXT. The database will be maximum 2GB after 10 years. What I am suppose to do in this situation ?
I’m not really sure why you would want to do this in all honesty. If you’re wanting to attach meta information to the backed up database blob I would do that inside a table and simply link to the filesystem where you store the binary itself. What were you intending to gain having backups inside a field?
“Use LONGTEXT if you need to store large blocks of text, such as a chapter of a book.”
MEDIUMTEXT fits like 30 300 page books inside of it, so you can safely store a book inside MEDIUMTEXT, let alone one chapter. A chapter may even fit inside TEXT, although that depends on the amount of pages per chapter, so it’s better to just use MEDIUMTEXT for anything book related.
You are of course quite right. I can see in the revision history where this post has been rewritten (several times in fact) to correct a few other errors, and this crept in at some point during the changes. Thanks for pointing that out
How about perfomance between text and mediumtext?
Regards
Nice article i like it 10K much dude!
TEXT fields may prevent the use of memory and in InnoDB depending on the ROW_FORMAT value TEXT fields may be stored in other blocks which could slow things down (both INSERTs and SELECTs) due to the extra hits to disk.
actually i inserted description field data into database it stored only some characters not entire inserted data.i used longtext datatype but it not stored total inserted data..how to solve this problem
It could be a lot of things – character or database encoding, your framework stripping special characters…
Do you have more details?