store text of character length ~300,000 in mysql database

Question

I have a column of data I would like to add to a mysql database table. The column is raw text and the longest piece of text contains approximately 300,000 characters. Is it possible to store this in the table? How?

I have been reading that even LONGTEXT columns are limited somewhat.

Answer 1

Presumably you have ruled out the alternative of storing these items of text in files, and storing their pathnames in your table. If you have not considered that choice, please do. It's often the most practical way to handle this sort of application. That's especially true if you're using a web server to deliver your information to your users: by putting those objects in your file system you avoid a very serious production bottleneck (fetching the objects from the DBMS and then sending them to the user).

MySQL's LOBs (large objects) will take 300k characters without problems. MEDIUMTEXT handles 16 megabytes. But the programming work necessary to load those objects into the DBMS and get them out again can be a bit challenging. You haven't mentioned your application stack, so it's hard to give you specific advice about that. Where to start? read about the MySQL server parameter max_allowed_packet.

If this were my project, and for some reason using the file system was out of the question, I would store the large textual articles as segments in shorter rows. For example, instead of

 textid        textval
  (int)        (MEDIUMTEXT)
 number        lots and lots and lots of text.

I'd make a table like this:

 textid      segmentid     textval
  (int)         (int)      (VARCHAR(250))
 number           1        Lots and
 number           2        lots and
 number           3        lots of
 number           4        text.

The segment lengths should probably be around 250 characters each in length. I think you'd be smart to break the segments on word boundaries if you can; it will make stuff like FULLTEXT search easier. This will end up with many shorter rows for your big text items, but that will make your programming, your backups, and everything else about your system. easier to handle all around.

There is an upfront cost, but it's probably worth it.