Binary Data in MySQL [closed]

Asked 1/8, 2008 at 5:9 Answered 1/5, 2014 at 9:37

Solved mysql database binary-data data-storage

199

How do I store binary data in MySQL?

Vinegarette answered 1/8, 2008 at 5:9 Comment(3)

storing-images-in-db-yea-or-nay – Narcissus 20/1, 2018 at 10:10

@Nevir: What information are you specifically after? What do you feel is lacking from @phpguy's and @Mat's answers? – Defensive 20/1, 2018 at 13:31

Since I can't post an answer, I guess I will do it here. If you want to store certain binary data, you can create a table, set up a PHP file or script to store it, and write certain binary data in your table and let the script do it's job. Seriously, I don't actually know how to use PHP in other languages... – Groggy 17/10, 2020 at 16:7

143

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.

Gushy answered 1/8, 2008 at 12:16 Comment(0)

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>

Miscount answered 1/8, 2008 at 5:12 Comment(2)

This code looks like PHP3 (or maybe 4), which register_globals enabled. You don't want to run this code, and it will also not work on a semi up to date PHP installation (which is version 5). – Swirsky 14/9, 2008 at 14:21

-1 for addslashes() where mysql_real_escape_string() is needed. Can we please stop giving people code with SQL injection vulnerabilities in it? (No, addslashes() is NOT good enough.) – Doran 30/12, 2008 at 6:28

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.

Ptolemaic answered 17/9, 2008 at 20:37 Comment(8)

I didn't downvote, but it might be due him implying that you should NEVER do it, as opposed to saying it's a bad idea most of the time. I agree with him generally, but not in 100% of cases. There can be considerations other than performance. For example I'm working on something now where performance doesn't matter at all. Other factors such as centralisation, simplicity and backups mean that in this case storing in the database makes sense. Another common reason is replication. – Thatch 15/2, 2013 at 12:28

BLOB field has a fixed 64 kilobytes size. It is not varying, isn't it? – Antiphony 12/9, 2013 at 12:10

64KB won't fit many files inside - so you'll need to have more than one 64KB block to store the data. – Ptolemaic 29/9, 2013 at 2:10

On the other hand storing data in db is OS independent, which can be nice for weird filenames. db can store multiple files with same filename, the OS cannot. It has no read/write/delete issues. It doesn't need an additional backup system. And, it's not public. So sometimes it's fast in development. Btw. nobody is forcing you to store everything in the same database, in the end it all ends up on a disk. – Vaivode 9/12, 2013 at 16:2

@AlexWeinstein, You are confusing binary data with fixed width data. Binary data can be fixed width as well. And fixed width data is not good for all situations. Indeed, in many situations you would benefit from variable width data: read last paragraph of dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html – Gesture 18/10, 2014 at 18:19

Agree with @Gesture on this, BINARY(16) is stored fixed. As for BLOB: A BLOB has a fixed width pointer to data stored outside the table. That is unlike varchar or varbinary which store it inline.Searching a blob requires some extra steps, but leave it out of your WHERE clause and it is fine. – Computerize 15/9, 2015 at 4:57

i also think storing files in the filesystem is very broken and non-portable. what if the file gets deleted? – Computerize 15/9, 2015 at 4:59

Doesn't answer the question (question didn't ask for opinions on if it should be done). – Denotation 23/1, 2018 at 0:54

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

Morbilli answered 2/8, 2008 at 14:57 Comment(0)

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.

Expulsion answered 31/12, 2010 at 1:4 Comment(0)

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.

Despot answered 16/9, 2008 at 4:7 Comment(0)

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.

Antiphony answered 12/9, 2013 at 12:0 Comment(0)

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.

Positive answered 1/5, 2014 at 9:37 Comment(0)

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

Blas answered 27/8, 2008 at 15:13 Comment(1)

That link say, that MySQL_real_escape_string is deprecated. – Corkboard 3/11, 2019 at 19:39

Recommended topics

Hot tags