4

Question: In my web application (php/MySQL) I will be saving rich text. This may be anything from a couple words to several pages length essay. I’m sure I could get away with using VARCHAR, TEXT or BLOB to store markdown or HTML from a rich text editor like TinyMCE. Which should I use?

My findings so far: In all my searching this week I’ve not found anything that tried to be a definitive source as to which would be best in general. I think VARCHAR is a standard and TEXT is not. BLOB is for binary data and so is overkill. TEXT may be deprecated in some DBMS. One major concern I have revolves around my ignorance as to how VARCHAR is saved to the database. I.e., if I max it out (or leave it undefined) will it waste storage or memory if there is only a few words in it? Or as a modern DB does MySQL not store blank data as reserved space? My research has indicated that the performance difference is very minor in modern systems between VARCHAR and TEXT queries. Also, I do not need to do many text searches in the content. I’m not planning on any, however, the ability to do so would be a benefit, long term.

Tentative Conclusion: I think to be best and standards compliant (low priority) I should make a VARCHAR type with no limit. Then just insert the markdown, HTML or textile data in there. I assume not specifying a limit would max out the data capacity on the server.

Would VARCHAR be the best standard to employ? Or have I gone completely off the rails in my research?

Improve this question
4
  • 1
    You will be appalled at how inconsistent databases are when it comes to standards. Compliance should be really low priority.
    – Rick James
    Apr 28, 2015 at 18:01
  • If you don't know how big things might get, use MEDIUMTEXT (16M limit) for data in a known character encoding (such as utf8) or MEDIUMBLOB (16M) for non-text (eg images). Html qualifies as text.
    – Rick James
    Apr 28, 2015 at 18:04
  • Thanks Rick James. I think MEDIUMTEXT will fit my needs. I should add that I confirmed that TEXT is stored off table via pointers, in case future readers wonder.
    – ShawnPConroy
    Apr 29, 2015 at 12:17
  • I think this answer on Stack Overflow addresses some of your concerns.
    – Phrancis
    Aug 30, 2015 at 4:08

1 Answer 1

Reset to default
1

It would depend on the size of the data you want to save in that field. You should know that there is a limit for VARCHAR, and that limit is in bytes, so number of characters that can be stored will depend on the character set you use. For example, if you use UTF 8, you will be able to store less amount of data.

To sum it up, there is no "best" solution, but if your data fits in VARCHAR, then use it :). If not, you will have to use TEXT or BLOB, or you will have to zip the data before saving it (Check compress)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.