0

I am using Mysql database, and say I have large corpus of text to store in one cell. Is there a way that I put the text in the file on disk and store a file pointer in that cell to point to the specific file?

shirley
  • 1,640
  • 4
  • 12
  • 19
  • Store the filename in the DB? Sounds like a horrible pattern, why not just store the text in the DB? – John3136 Oct 23 '13 at 05:35
  • @Eternal-Learner If you store filenames in the DB now you need the filesystem and the DB to have a consistent set of data. May be ok depending on context, but it makes the solution more brittle. DBs are good at handling large amounts of data. I say the the DB do it's job! – John3136 Oct 23 '13 at 06:28
  • 1
    @John3136 not really. It is a commonly used pattern. It gives you freedom to relocate files from one disk to another or even move from local disk to cloud. Storing files in the DB, on the other hand, is a bad choice. It will bloat the DB, requiring more resources to run and manage. for example, backup and restore will take extra time which can mean unnecessarily long downtime. – Litmus Oct 23 '13 at 06:28
  • @John3136 you are right. DBs (let me qualify it saying RDBMS, since we are talking about MySQL here) are good at handling large amounts of data. But not objects. There are different kinds of databases designed to handle objects. Files are essentially objects. For example, Amazon S3 is an object store. Using MySQL to store objects is possible, but is a misuse of MySQL – Litmus Oct 23 '13 at 06:35
  • @Eternal-Learner OP was talking about text not objects. – John3136 Oct 23 '13 at 09:34
  • @John3136 an XML document, a JSON document, a YAML document etc., are all texts but are interpreted as objects. The question does not say anything about what the text contains. The context in which the question is asked makes me believe it is the object that is being stored. – Litmus Oct 23 '13 at 13:48
  • @Eternal-Learner We're both making assumptions. In my world I wouldn't do it because it make the solution "brittle" but I concede that in many cases it'll work just fine. If we're talking a production system you'd need to make sure your backups handle the 2 locations and that you've got test/dev/prod environments sorted so they don't interfere. – John3136 Oct 23 '13 at 22:08

2 Answers2

3

What do you mean by pointer, why not store the filename and path in the database?

create a varchar(1000) or whatever length you need as column and insert "/my/file/path.txt" or similar into the table

See also: Storing Images in DB - Yea or Nay?

Community
  • 1
  • 1
Kaffee
  • 1,563
  • 11
  • 21
0

As long as you have no need to do full text search, you can store the path to the file in a database column.(Technically speaking, you can do full text search even if the files are outside the database, but will require extra help, such as using SphinxSearch or Apache Solar)

As a matter of fact, if you use paperclip or CarrierWave gems that are used with Ruby on Rails, they do exactly this. They store the meta data about the file in the database, and the file itself on the disk or cloud. The meta data includes:

  1. File name and path
  2. File size
  3. Timestamp
  4. Mime type

I am not sure which programming language you are using, but the same pattern should work for you.

Litmus
  • 10,558
  • 6
  • 29
  • 44