Binary Data in MySQL

Question

How do I store binary data in MySQL?

[storing-images-in-db-yea-or-nay](https://stackoverflow.com/questions/3748/) — juergen d, Jan 20 '18 at 10:10
@Nevir: What information are you specifically after? What do you feel is lacking from [@phpguy's](https://stackoverflow.com/questions/17/binary-data-in-mysql#18) and [@Mat's](https://stackoverflow.com/questions/17/binary-data-in-mysql#26) answers? — eggyal, Jan 20 '18 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... — , Oct 17 '20 at 16:07

score 140 · Accepted Answer · edited Jun 02 '16 at 05:55

140

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.

edited Jun 02 '16 at 05:55

mauris

39,624
14
92
128

answered Aug 01 '08 at 12:16

Mat

6,416
7
31
39

score 58 · Answer 2 · edited Jun 02 '16 at 05:56

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>

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). — Till, Sep 14 '08 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.) — chaos, Dec 30 '08 at 06:28

score 40 · Answer 3 · edited Sep 02 '11 at 06:19

40

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.

edited Sep 02 '11 at 06:19

gcamp

14,491
4
52
81

answered Sep 17 '08 at 20:37

Alex Weinstein

9,499
8
38
58

11

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. – LaVache Feb 15 '13 at 12:28
1

BLOB field has a fixed 64 kilobytes size. It is not varying, isn't it? – Sep 12 '13 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. – Alex Weinstein Sep 29 '13 at 02:10
4

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. – Joeri Dec 09 '13 at 16:02
7

@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 http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html – Pacerier Oct 18 '14 at 18:19
4

Agree with @Pacerier 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. – Garr Godfrey Sep 15 '15 at 04:57
4

i also think storing files in the filesystem is very broken and non-portable. what if the file gets deleted? – Garr Godfrey Sep 15 '15 at 04:59
Doesn't answer the question (question didn't ask for opinions on if it should be done). – NateS Jan 23 '18 at 00:54

score 22 · Answer 4 · answered Aug 02 '08 at 14:57

22

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

answered Aug 02 '08 at 14:57

Issac Kelly

5,999
6
40
50

score 17 · Answer 5 · edited Jul 22 '20 at 14:01

17

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.

edited Jul 22 '20 at 14:01

Tanjim Khan

556
1
7
17

answered Dec 31 '10 at 01:04

d0nut

570
3
5

score 14 · Answer 6 · edited Jul 11 '14 at 14:51

14

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.

edited Jul 11 '14 at 14:51

Sid M

4,268
4
27
47

answered Sep 16 '08 at 04:07

user10117

161
3

score 11 · Answer 7 · edited May 23 '17 at 12:09

11

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.

edited May 23 '17 at 12:09

Community

1
1

answered Sep 12 '13 at 12:00

score 10 · Answer 8 · edited Jul 22 '20 at 19:25

10

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.

edited Jul 22 '20 at 19:25

Tanjim Khan

556
1
7
17

answered May 01 '14 at 09:37

zeppaman

724
6
22

score 10 · Answer 9 · answered Aug 27 '08 at 15:13

10

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

answered Aug 27 '08 at 15:13

Scott Noyes

2,226
1
13
3

That link say, that MySQL_real_escape_string is deprecated. – Poul Bak Nov 03 '19 at 19:39

Binary Data in MySQL

9 Answers9

Linked

Related