Binary Data in MySQL

Mysql Problem Overview

How do I store binary data in MySQL?

Mysql Solutions

Solution 1 - Mysql

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.

Solution 2 - Mysql

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 <[email protected]>
    // 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>

Solution 3 - Mysql

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.

Solution 4 - Mysql

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>

Solution 5 - Mysql

It depends on the data you wish to store. The above example uses the LONGBLOB data type, but you should be aware that there are other binary data types:

TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB
VARBINARY
BINARY

Each has its use cases. If it is a known (short) length (e.g. packed data), BINARY or VARBINARY will work most of the time. They have the added benefit of being able to index on them.

Solution 6 - Mysql

While it shouldn't be necessary, you could try base64 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.

Solution 7 - Mysql

If the - not recommended - BLOB field exists, you can save data this way:

mysql_query("UPDATE table SET field=X'".bin2hex($bin_data)."' WHERE id=$id");

Idea taken from here.

Solution 8 - Mysql

When I need to store binary data I always use VARBINARY format as introduced by d0nut in one of the previous answers.

You can find documentation at MySQL website under documented topic: 12.4.2 The BINARY and VARBINARY Types.

If you are asking what are advantages, please read the question: why-varbinary-instead-of-varchar.

Solution 9 - Mysql

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 [mysql_real_escape_string][2] instead of addslashes). If the file exists on the database server, you can also use MySQL's [LOAD_FILE][1]

[1]: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_load-file "LOAD FILE" [2]: http://www.php.net/mysql-real-escape-string

Content Type	Original Author	Original Content on Stackoverflow
Question	Geoff Dalgas	View Question on Stackoverflow
Solution 1 - Mysql	Mat	View Answer on Stackoverflow
Solution 2 - Mysql	phpguy	View Answer on Stackoverflow
Solution 3 - Mysql	Alex Weinstein	View Answer on Stackoverflow
Solution 4 - Mysql	Issac Kelly	View Answer on Stackoverflow
Solution 5 - Mysql	d0nut	View Answer on Stackoverflow
Solution 6 - Mysql	user10117	View Answer on Stackoverflow
Solution 7 - Mysql	user669677	View Answer on Stackoverflow
Solution 8 - Mysql	zeppaman	View Answer on Stackoverflow
Solution 9 - Mysql	Scott Noyes	View Answer on Stackoverflow