I haven't seen any data type that can store a file in SQL. Is there something like that? What I'm particularly talking about is that I want to insert into my table a source code. What is the best method to do it? It can be either stored in my database as a nicely formatted text, or better (what I actually want) to store it as a single file. Please note that I'm using MySQL.
4 Answers
It is best not to store a file in your SQL database but to store a path to the file in the server or any other UNC path that your application can retrieve by itself and do with it what ever is unnecessary.
and this: Better way to store large files in a MySQL database?
and if you still want to store the file on the DB.. here is an example: http://mirificampress.com/permalink/saving_a_file_into_mysql
answered Apr 24, 2013 at 18:46
1 Comment
tadman
This is important. The MySQL database will never be as good at storing files as a filesystem.
If you can serialized the file you can store it as binary and then deserialize when needed http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html
Comments
You can also use a BLOB (http://dev.mysql.com/doc/refman/5.0/en/blob.html) which has some differences. Normally I just store the file in the filesystem and a pointer in the DB, which makes serving it back via something like HTTP a bit easier and doesn't bloat up the Database.
Comments
Storing the file in a table only makes sense if you need to do searches in that code. In other cases, you should only store a file's URL.
If you want to store a text file, use the TEXT datatype. Since it is a source code, you may consider using the ASCII character set to save space - but be aware that this will cause character set conversions during your queries, and this affects performances. Also, if it is ASCII you can use REGEXP for searches (that operator doesnt work with multi-byte charsets).
To load the file, if the file is on the same server as MySQL, you can use the FILE() function within an INSERT.
answered Apr 24, 2013 at 23:52
