How to store .txt files MySQL database?

Question

Can I store data files (e.g. txt files) to the MySql server? If I can, how to store them?

Databases are not meant for storing text files, that's what your file system is for. — animuson, Mar 11 '11 at 21:50
Sure, if you have file write permissions on a server that happens to be running MySQL, you can write text files to it. I don't think that's what you mean, though. What are you trying to accomplish? — Andrew, Mar 11 '11 at 21:51
I am trying to fill txt files with data (integers) and store the whole file to the database; I preferred using the files since I have a lot of data to be stored in a very small interval ( every 0.4 s i have to query the data base with a large amount of data) I know that File system can do that, but i want that file to be accessible by all the users on the network .. — Rida Shamasneh, Mar 11 '11 at 21:55
Your question is ambiguous. There are two (three?) entirely different ways to interpret your question and given the limited description, either could be correct. Please specify in what format you want the data to be once stored in the database. Do you just want a transparent blob or do you want structured data? — Mark Byers, Mar 11 '11 at 21:59
I want the file to be stored as one unit / one object to the database regarding its contents — Rida Shamasneh, Mar 11 '11 at 22:03

score 18 · Accepted Answer · edited Mar 11 '11 at 21:58

18

You can use LOAD DATA INFILE to read the contents of a file and store it in a table in the database in a structured format.

This can be considerably faster than reading and parsing the file on the client and then using multiple INSERT statements.

Example:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

edited Mar 11 '11 at 21:58

Abe Miessler

answered Mar 11 '11 at 21:50

Mark Byers

score 7 · Answer 2 · edited May 23 '17 at 12:19

7

Yes you can, but you would probably be better off storing them on the file system and storing a path to the file in the DB.

There are a few SO Posts that discuss this:

edited May 23 '17 at 12:19

Community

answered Mar 11 '11 at 21:51

Abe Miessler

I know that File system can do that, but i want that file to be accessible by all the users on the network ... and I want to avoid querying the database server every 0.4s for more than 30 Minutes ... and that can be the worst solution with a centralized database – Rida Shamasneh Mar 11 '11 at 22:01

score 2 · Answer 3 · answered Mar 11 '11 at 21:52

Sure you can. I would suggest reading the data from your files and then saving it in your database, i would say in a text field.

You can use something like this to get the file's content:

$file = file_get_contents('./yourfile.txt');

Then insert it

Insert into myTable VALUES (mytextfile = $file)

score 0 · Answer 4 · answered Mar 11 '11 at 21:52

How much text are we talking about here? If there's not that much text, you can store just the content of the file in the database.

If you'll be dealing with many files, you'll probably be better off storing the files on your sever with the file path in the database.

4 Answers4