<p>How do I store binary data in <a href="http://en.wikipedia.org/wiki/MySQL" rel="noreferrer">MySQL</a>?</p>

[{'answer_id': 18, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 6, 'selected': False, 'text': '<p>For a table like this:</p>\n\n<pre><code>CREATE TABLE binary_data (\n    id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,\n    description CHAR(50),\n    bin_data LONGBLOB,\n    filename CHAR(50),\n    filesize CHAR(50),\n    filetype CHAR(50)\n);\n</code></pre>\n\n<p>Here is a PHP example:</p>\n\n<pre><code>&lt;?php\n    // store.php3 - by Florian Dittmer &lt;dittmer@gmx.net&gt;\n    // Example php script to demonstrate the storing of binary files into\n    // an sql database. More information can be found at http://www.phpbuilder.com/\n?&gt;\n\n&lt;html&gt;\n    &lt;head&gt;&lt;title&gt;Store binary data into SQL Database&lt;/title&gt;&lt;/head&gt;\n\n    &lt;body&gt;\n        &lt;?php\n            // Code that will be executed if the form has been submitted:\n\n            if ($submit) {\n                // Connect to the database (you may have to adjust\n                // the hostname, username or password).\n\n                mysql_connect("localhost", "root", "password");\n                mysql_select_db("binary_data");\n\n                $data = mysql_real_escape_string(fread(fopen($form_data, "r"), filesize($form_data)));\n\n                $result = mysql_query("INSERT INTO binary_data (description, bin_data, filename, filesize, filetype) ".\n                                    "VALUES (\'$form_description\', \'$data\', \'$form_data_name\', \'$form_data_size\', \'$form_data_type\')");\n\n                $id= mysql_insert_id();\n                print "&lt;p&gt;This file has the following Database ID: &lt;b&gt;$id&lt;/b&gt;";\n\n                mysql_close();\n            } else {\n\n                // else show the form to submit new data:\n        ?&gt;\n        &lt;form method="post" action="&lt;?php echo $PHP_SELF; ?&gt;" enctype="multipart/form-data"&gt;\n            File Description:&lt;br&gt;\n            &lt;input type="text" name="form_description"  size="40"&gt;\n            &lt;input type="hidden" name="MAX_FILE_SIZE" value="1000000"&gt;\n            &lt;br&gt;File to upload/store in database:&lt;br&gt;\n            &lt;input type="file" name="form_data"  size="40"&gt;\n            &lt;p&gt;&lt;input type="submit" name="submit" value="submit"&gt;\n        &lt;/form&gt;\n\n        &lt;?php\n            }\n        ?&gt;\n    &lt;/body&gt;\n&lt;/html&gt;\n</code></pre>\n'}, {'answer_id': 26, 'author': 'Mat', 'author_id': 48, 'author_profile': 'https://Stackoverflow.com/users/48', 'pm_score': 8, 'selected': True, 'text': '<p>The answer by phpguy is correct but I think there is a lot of confusion in the  additional details there.</p>\n\n<p>The basic answer is in a <code>BLOB</code> data type / attribute domain. <strong>BLOB</strong> is short for Binary Large Object and that column data type is specific for handling binary data.</p>\n\n<p>See <a href="http://dev.mysql.com/doc/refman/5.7/en/blob.html" rel="noreferrer">the relevant manual page for MySQL</a>.</p>\n'}, {'answer_id': 467, 'author': 'Issac Kelly', 'author_id': 144, 'author_profile': 'https://Stackoverflow.com/users/144', 'pm_score': 5, 'selected': False, 'text': '<p>While you haven\'t said what you\'re storing, and you may have a great reason for doing so, often the answer is \'as a filesystem reference\' and the actual data is on the filesystem somewhere.</p>\n\n<p><a href="http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html" rel="noreferrer">http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html</a></p>\n'}, {'answer_id': 30404, 'author': 'Scott Noyes', 'author_id': 3254, 'author_profile': 'https://Stackoverflow.com/users/3254', 'pm_score': 4, 'selected': False, 'text': '<p>The question also arises how to get the data into the BLOB. You can put the data in an INSERT statement, as the PHP example shows (although you should use <a href="http://www.php.net/mysql-real-escape-string" rel="noreferrer">mysql_real_escape_string</a> instead of addslashes). If the file exists on the database server, you can also use MySQL\'s <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_load-file" rel="noreferrer" title="LOAD FILE">LOAD_FILE</a></p>\n'}, {'answer_id': 69311, 'author': 'user10117', 'author_id': 10117, 'author_profile': 'https://Stackoverflow.com/users/10117', 'pm_score': 4, 'selected': False, 'text': "<p>While it shouldn't be necessary, you could try <code>base64</code> encoding data in and decoding it out. That means the db will just have ascii characters. It will take a bit more space and time, but any issue to do with the binary data will be eliminated.</p>\n"}, {'answer_id': 87385, 'author': 'Alex Weinstein', 'author_id': 16668, 'author_profile': 'https://Stackoverflow.com/users/16668', 'pm_score': 5, 'selected': False, 'text': '<p>I strongly recommend <strong>against</strong> storing binary data in a relational database. Relational databases are designed to work with fixed-size data; that\'s where their performance strength is: remember <a href="http://www.joelonsoftware.com/articles/fog0000000319.html" rel="noreferrer">Joel\'s old article</a> on why databases are so fast? because it takes exactly 1 pointer increment to move from a record to another record. If you add BLOB data of undefined and vastly varying size, you\'ll screw up performance. </p>\n\n<p>Instead, store files in the file system, and store file names in your database. </p>\n'}, {'answer_id': 4567420, 'author': 'd0nut', 'author_id': 499257, 'author_profile': 'https://Stackoverflow.com/users/499257', 'pm_score': 4, 'selected': False, 'text': '<p>It depends on the data you wish to store. The above example uses the <code>LONGBLOB</code> data type, but you should be aware that there are other binary data types:</p>\n<p><code>TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB</code><br />\n<code>VARBINARY</code><br />\n<code>BINARY</code></p>\n<p>Each has its use cases. If it is a known (short) length (e.g. packed data), <code>BINARY</code> or <code>VARBINARY</code> will work most of the time. They have the added benefit of being able to index on them.</p>\n'}, {'answer_id': 18763725, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 4, 'selected': False, 'text': '<p>If the - <a href="https://stackoverflow.com/a/87385/669677">not recommended</a> - BLOB field exists, you can save data this way:</p>\n\n<pre><code>mysql_query("UPDATE table SET field=X\'".bin2hex($bin_data)."\' WHERE id=$id");\n</code></pre>\n\n<p>Idea taken from <a href="https://stackoverflow.com/q/2558453/669677">here</a>.</p>\n'}, {'answer_id': 23405248, 'author': 'zeppaman', 'author_id': 3559251, 'author_profile': 'https://Stackoverflow.com/users/3559251', 'pm_score': 4, 'selected': False, 'text': '<p>When I need to store binary data I always use <code>VARBINARY</code> format as introduced by <a href="https://stackoverflow.com/users/499257/d0nut">d0nut</a> in one of the previous answers.</p>\n<p>You can find documentation at MySQL website under documented topic: <a href="http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html" rel="nofollow noreferrer">12.4.2 The BINARY and VARBINARY Types</a>.</p>\n<p>If you are asking what are advantages, please read the question: <a href="https://stackoverflow.com/questions/13393718/why-varbinary-instead-of-varchar">why-varbinary-instead-of-varchar</a>.</p>\n'}]
['https://Stackoverflow.com/questions/17', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2/']