3

I’m trying to figure out if I should store text files (65535 bytes or less) in MySQL, or not use MySQL at all. Performance is the first consideration, and then ease of use since I am a novice.

What would be a good choice of data type if I wanted to use a database? Could VARCHAR be used for such large data, or TEXT, or BLOB? Not sure which.

Palec
  • 12,743
  • 8
  • 69
  • 138
user1505713
  • 615
  • 5
  • 20

1 Answers1

2

A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold. The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements

But Storing files in the database has some advantages and disatvantages:

Advantages:

1. Backups and switches to another server are easier to achieve
2. No need to add another method to access the files beside the database

Disadvantages:

1. Accessing an image means retrieving it's bytes and maybe storing that as temporary local files and may be more complicated
2. File systems are simply faster due to lower overhead

Text and blob are nearly identical, the main difference is that you have to care about the encoding yourself when using blob. On the other hand you will be forced to use the encoding of a text field...

The decission is yours, the most opinions that I have heard so far are to store the pictures as files; but that are opinions, try to base your decision on your projects needs.

Nidhish Krishnan
  • 20,593
  • 6
  • 63
  • 76
  • I'm sorry, but what's the difference? Binary vs. plain? Which one would be faster (not necessarily easier) to work with? – user1505713 Jan 01 '14 at 17:21
  • BLOB vs. TEXT: you get to use character sets when you use TEXT. – Bill Karwin Jan 01 '14 at 17:22
  • Text and blob are nearly identical, the main difference is that you have to care about the encoding yourself when using blob. On the other hand you will be forced to use the encoding of a text field... – Nidhish Krishnan Jan 01 '14 at 17:25
  • @BillKarwin This will be the model for a web application. So you'd recommend using TEXT if I wanted data from international visitors? – user1505713 Jan 01 '14 at 17:25
  • @user1505713, if it's textual data, yes, use TEXT -- and define the default character set utf8. I've seen people try to use BLOB for textual data, and they get into awful trouble with character set conversions. – Bill Karwin Jan 01 '14 at 17:29
  • Thanks both! I can get this project going now. – user1505713 Jan 01 '14 at 17:31
  • 1
    @NidhishKrishnan, another important feature of storing content in blob or text: when you DELETE the row, the content of the text is automatically deleted too. If you store data outside the database, a DELETE in SQL does not automatically delete files on your filesystem. You have to do it yourself as an extra step, or else risk collecting lots of orphaned files. Also you can rely on transaction support including, rollbacks and ACID, if you keep your data inside the database. – Bill Karwin Jan 01 '14 at 17:32
  • yes.......@BillKarwin you are right........... – Nidhish Krishnan Jan 01 '14 at 17:34