CODEWITHSUNDEEP
mysqldatabasefiletextvarchar
Josh Lyness
Josh Lyness

Reputation: 102

VARCHAR(MAX) vs TEXT vs .txt file for use in MySQL database

I tried to google this, but any results I found were related to importing data from a txt file to populate the database as opposed to storing data.

To me, it seems strange that the contents of a file should be stored in a database. We're working on building an eCommerce site, and each item has a description. I assumed the standard would be to store the description in a txt file and the URL in the database, and not to store the huge contents in the database to keep the file size low and speeds high. When you need to store images in a database, you reference it using a URL instead of storing all the pixel data - why would text be any different? That's what I thought, but everyone seems to be arguing about VARCHAR vs TEXT, so what really is the best way to store text data up to 1000 characters or so? Thanks!

Upvotes: 2

Views: 1265

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

Whether you store long text data or image data in a database or in external files has no right or wrong answer. There are pros and cons on both sides—despite many developers making unequivocal claims that you should store images outside the database.

Consider you might want the text data to:

  • Allow changes to be rolled back.
  • Support transaction isolation.
  • Enforce SQL access privileges to the data.
  • Be searchable in SQL when you create a fulltext index.
  • Support the NOT NULL constraint, so your text is required to contain something.
  • Automatically be included when you create a database backup (and the version of the text is the correct version, assuring consistency with other data).
  • Automatically transfer the text to replica database instances.

For all of the above, you would need the text to be stored in the database. If the text is outside the database, those features won't work.

With respect to the VARCHAR vs. TEXT, I can answer for MySQL (though you mentioned VARCHAR(MAX) so you might be referring to Microsoft SQL Server).

  • In MySQL, both VARCHAR and TEXT max out at 64KB in bytes. If you use a multibyte character set, the max number of characters is lower.

  • Both VARCHAR and TEXT have a character set and collation.

  • VARCHAR allows a DEFAULT, but TEXT does not.

  • Internally the InnoDB storage engine, VARCHAR and TEXT are stored identically (as well as VARBINARY and BLOB and all their cousins). See https://www.percona.com/blog/2010/02/09/blob-storage-in-innodb/

Upvotes: 2

Related Questions