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?
MEDIUMTEXT
(16M limit) for data in a known character encoding (such as utf8) orMEDIUMBLOB
(16M) for non-text (eg images). Html qualifies as text.MEDIUMTEXT
will fit my needs. I should add that I confirmed that TEXT is stored off table via pointers, in case future readers wonder.