Home » Questions » Computers [ Ask a new question ]

database - Binary Data in MySQL -

"Closed. This question needs to be more focused. It is not currently accepting answers.












Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 2 years ago.





Improve this question





How do I store binary data in MySQL?





mysql database binary-data data-storage












ShareShare a link to this question Copy linkCC BY-SA 3.0



Improve this question




Follow
Follow this question to receive notifications











edited Dec 3 '20 at 3:37





Samuel Liew♦

70k105105 gold badges151151 silver badges230230 bronze badges







asked Aug 1 '08 at 5:09





Geoff DalgasGeoff Dalgas

5,90866 gold badges4040 silver badges5858 bronze badges"

Asked by: Guest | Views: 156
Total answers/comments: 4
bert [Entry]

"The answer by phpguy is correct but I think there is a lot of confusion in the additional details there.

The basic answer is in a BLOB data type / attribute domain. BLOB is short for Binary Large Object and that column data type is specific for handling binary data.

See the relevant manual page for MySQL."
bert [Entry]

"For a table like this:

CREATE TABLE binary_data (
id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description CHAR(50),
bin_data LONGBLOB,
filename CHAR(50),
filesize CHAR(50),
filetype CHAR(50)
);

Here is a PHP example:

<?php
// store.php3 - by Florian Dittmer <dittmer@gmx.net>
// Example php script to demonstrate the storing of binary files into
// an sql database. More information can be found at http://www.phpbuilder.com/
?>

<html>
<head><title>Store binary data into SQL Database</title></head>

<body>
<?php
// Code that will be executed if the form has been submitted:

if ($submit) {
// Connect to the database (you may have to adjust
// the hostname, username or password).

mysql_connect(""localhost"", ""root"", ""password"");
mysql_select_db(""binary_data"");

$data = mysql_real_escape_string(fread(fopen($form_data, ""r""), filesize($form_data)));

$result = mysql_query(""INSERT INTO binary_data (description, bin_data, filename, filesize, filetype) "".
""VALUES ('$form_description', '$data', '$form_data_name', '$form_data_size', '$form_data_type')"");

$id= mysql_insert_id();
print ""<p>This file has the following Database ID: <b>$id</b>"";

mysql_close();
} else {

// else show the form to submit new data:
?>
<form method=""post"" action=""<?php echo $PHP_SELF; ?>"" enctype=""multipart/form-data"">
File Description:<br>
<input type=""text"" name=""form_description"" size=""40"">
<input type=""hidden"" name=""MAX_FILE_SIZE"" value=""1000000"">
<br>File to upload/store in database:<br>
<input type=""file"" name=""form_data"" size=""40"">
<p><input type=""submit"" name=""submit"" value=""submit"">
</form>

<?php
}
?>
</body>
</html>"
bert [Entry]

"I strongly recommend against 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 Joel's old article 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.

Instead, store files in the file system, and store file names in your database."
bert [Entry]

"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.

http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html"