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?
Best Answer
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 useUTF 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 useTEXTorBLOB, or you will have to zip the data before saving it (Check compress)