Babra Cunningham Babra Cunningham - 2 months ago 9
MySQL Question

Storing large volume of text in a MySQL table?

I have the following table:

Name (Int), Id (Int), Description (VARCHAR)

The problem I have is Description is likely to be a paragraph of text (over 100 characters).

My Questionsl

1) Is VARCHAR a suitable datatype to store this type of data?

2) Is MySQL an ideal storage solution for this data, or should I look at writing to files instead?

N.B. I won't be querying the Description, just storing the data.

Dai Dai

MySQL (as of version 5.7) supports up to 65,535 bytes in a varchar column. Note that the length of a row (the combined maximum sizes of all columns) is also 65,535 bytes. Because it uses UTF-8 the number of characters stored might be less than this limit (as UTF-8 uses a varying number of bytes per character).

Note that in databases, a 100-character storage requirement isn't really significant. Database systems like these can store many kilobytes in a single row, and even larger data (single values sized in the gigabytes and over) can be stored in blob columns (BLOB, TEXT).

If you won't be querying the data and if the data length might excess 65KiB then you should use the TEXT data-type instead, which stores large-sized values in a different physical storage location which makes data-lookup somewhat more expensive, and hinders indexing.