Feed Sign in with OpenID OpenID

Simon Willison’s Weblog

MySQL text limits

Today’s scary discovery: MySQL TEXT fields have a limit of 65,000 bytes. If you insert anything larger than that in to a normal TEXT field mySQL will silently truncate your data without telling you (meaning software checks are probably a good idea). MEDIUMTEXT will store 16 million characters and LONGTEXT can handle over 4 trillion, but this information does not appear to be readily available in the online mySQL manual (although it is hinted at in this table). Something to bare in mind when designing database applications.

Posted 1st August 2002 at 12:24 pm

This is MySQL text limits by Simon Willison, posted on 1st August 2002.

View blog reactions

Next: LUMS in CSS

Previous: Styles of blogging

14 comments

  1. Great blog, but I don't think you are quite right about this missing from the manual. While looking through it a little while ago, I came across section 6.2 Column Types, which says: "BLOB TEXT A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters." and also has similar entries for all the other column types.

    Nick - 2nd August 2002 03:49 - #

  2. Doh! I don't know how I didn't managed to spot that page (I looked al over the manual). Thanks for the link.

    Simon - 2nd August 2002 08:40 - #

  3. Hello. This is a test as to how many characters mysql can take. I have many problems as the database simply could not take in enough text for a text field. Not sure what is the problem with the way I handle my fields.

    Joen - 6th December 2003 13:36 - #

  4. Thanks for your blog on the limit of Text field in Mysql. I landed here looking for a solution to my 'Dissapearing Text' problem when posting large chunks of text into MySql. :)

    Vinu - 5th January 2004 09:46 - #

  5. Yes, thanks a lot for this. I did not see this in the manual either, and we had a problem at our work. We were stumped for a while because it had been working fine for over a year. Turns out none of our clients had ever reached that size limitation before.

    MG - 13th February 2004 19:04 - #

  6. Sjeez. You might want to, you know, have a look at the documentiation and take responsibility for the bugs in your code, instead of spinning it around so it's all mySQL's fault. When "developing database applications" surely, the database is a thing to keep in mind indeed.

    Also, a "trillion" doesn't quite mean what you think it does.

    Lurker - 27th March 2004 18:08 - #

  7. Thanks, I've been making the same mistake, and your comment made me realize to everything is "TEXT"

    Ramon - 10th February 2005 17:39 - #

  8. I have an application that some times stops with a "overhead" error. The MySQL tables use text data type. Do you know what is going wrong?

    Marcelo Leone - 29th June 2005 19:06 - #

  9. I don't think its appropriate to slam someone for finding a problem, blaming it on a vender (in this case MySQL), then only to realize it in your own code. This is so normal it's laughable. Nick provided a correction and I think Simon owned up to it - done. Lighten up.

    Greg - 7th November 2005 22:43 - #

  10. Hi!

    Someone told me that there is an option or maybe a "special" text field in MySQL that would keep new lines..

    uh.. I'm really not sure how to explain what I mean.. but.. There's exemple of what I'm talking about..

    Into the database text would be like that:

    -----------

    Les gourmands n'ont plus qu'à se mettre à la diète! Les plats que vous découvrirez vous feront voyager dans un univers d'épices dont vous ne soupçonniez même pas la saveur.

    De savoureux cocktails exotiques enflammeront vos papilles et vous découvrirez des goûts et des couleurs que votre imagination n'aurait jamais pu créer...

    -----------

    which is perfect but without me writing br or p it would be like that on the web site:

    -----------

    Les gourmands n'ont plus qu'à se mettre à la diète! Les plats que vous découvrirez vous feront voyager dans un univers d'épices dont vous ne soupçonniez même pas la saveur. De savoureux cocktails exotiques enflammeront vos papilles et vous découvrirez des goûts et des couleurs que votre imagination n'aurait jamais pu créer...

    -----------



    So that's it.. I'm looking for a way to display it as I want without html tags and without fonctions like nl2br and all that jazz..

    Thanks,

    Mel.

    Mel - 8th November 2005 17:36 - #

  11. Is there a limit to number of records in MYSQL?

    Go - 9th November 2005 03:57 - #

  12. That's a real pain (if you're not aware of it). See http://dev.mysql.com/doc/refman/5.0/en/storage-req uirements.html for the maximum sizes and data storage requirements.

    Aaron Watkins - 11th February 2006 16:13 - #

  13. I did some reading, and it appears that LONGTEXT can only handle just over 4 billion characters, not 4 trillion. It makes more sense too, since 4 billion characters rounds out to a nice 32 bits of address space instead of 42 bits. - Nick

    Nicholas Corey - 18th July 2006 09:55 - #

  14. nice one, saved me a monster headache while trying to find out why serialized strings where being chopped at 32k. well done sir!

    Ashley Etchell - 29th August 2006 15:35 - #

Comments are closed.

Previously hosted at http://simon.incutio.com/archive/2002/08/01/mysqlTextLimits

A django site