Text datatypes in MySQL

Published on and tagged with mysql

Up to now I assumed that the size of text you can store in a field with data type TEXT is almost “infinite”. But that is not true. TEXT does only allow 64 kB of data. That’s more often than not enough. But if you want to store more data you have to use MEDIUMTEXT or LONGTEXT, which allows you to store 16 MB resp. 4 GB of data. See also http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

11 comments baked

  • KesheR

    Yeah, that’s true, I never consider it

  • Dieter@be

    If you don’t know that, you don’t know mysql ;-)

  • cakebaker

    @Dieter@be: I know, I know, shame on me ;-) But unfortunately, the maximal size of data for the data type TEXT is different for each database: in PostgreSQL it is (theoretically) unlimited, in MSSQL it is 2 GB, and in MySQL 64 kB.

  • Fred

    What always gets me is the question “is TEXT or MEDIUMTEXT bigger”? I always have to look that up. :-/

  • cakebaker

    @Fred: Yes, it is not very intuitive :|

  • Felix Geisendörfer

    Daniel: I used to hold this belief as well ; ). Thanks for pointing this out.

  • cakebaker

    @Felix: I am glad it was useful for you :)

  • Tom

    There’s “longblob” (which apparently is the same as longtext). Also, do not forget that posting data without having proper settings to php.ini may result in errors…because you timed out or what not. The real interesting thing that I found out (on a project unrelated to cake) was saving large amounts of text to a database via ajax. If the method is not POST (ajax can often be used with GET) then you can also max out there. I forget the exact size (I did actually figure it out…lol then read it somewhere). note: I may have get/post flipped, but I think POST is the one you want to use…see how minor it is =) Believe me, it’s annoying when it catches you off guard.

  • cakebaker

    @Tom: Thanks for mentioning the timeout problems. And yes, the length of an URL is limited. The limit is afaik dependent on the browser you use.

  • Dieter@be

    I think the http spec probably imposes a limit too

  • cakebaker

    @Dieter@be: According to RFC 2068 there is no limit: “The HTTP protocol does not place any a priori limit on the length of a URI.”

© daniel hofstetter. Licensed under a Creative Commons License